11
votes
Accepted
How to create recurrent weekdays as columns in a pivot?
The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. ...
- 180k
9
votes
What does it mean that PostgreSQL "offers" PHP?
Language extensions allow you to write user-defined functions in additional programming languages. Most people are aware of SQL and PL/pgSQL, but Postgres also ships with extensions for Perl, Python, ...
- 1,507
8
votes
Accepted
MySQL: Why not create an index on every column? What is the cost of indexing a column?
An index literally stores a copy of the data, sorted on the fields covered by that index (and automatically includes the clustered index key), generally in a data structure called a B-Tree.
Creating ...
- 35k
6
votes
Combining 2 SELECT queries and printing the results in PostgreSQL
The primary solution is to add parentheses:
( -- parentheses required!
SELECT dt1.name AS name1, dt1.time AS time1
, dt2.name AS name2, dt2.time AS time2
FROM departure_times dt1
JOIN ...
- 172k
6
votes
Searching a text with space in mySql match against Query
FULLTEXT needs space to be space, not a "letter". That is, you cannot have spaces in a "word".
I would write application code to ignore 'short' words (such as 'g') and end up with
WHERE MATCH(...
- 76.9k
6
votes
Timeout error after SQL Server upgrade from 2008R2 to SQL Server 2014
The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance ...
- 1,381
6
votes
Accepted
Timeout error after SQL Server upgrade from 2008R2 to SQL Server 2014
I finally find out the problem. After days of straggling I checked the SQL Server report and noticed that there were very large occasions of autogrowth on temp db database. The cause of the problem ...
6
votes
Accepted
Disadvantage of Changing database schema - Insert or delete new columns/tables in middle
The foundation of your system is your database. If you change your database schema then you also have to change your code. That means that keeping a stable data model helps to reduce the amount of ...
- 12.5k
6
votes
Accepted
What happens if a delete query times out SQL Server for php?
When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data ...
- 27.8k
6
votes
Accepted
How to update "sorting_order" column of other rows when changing one?
Add new category :
$t_cat = 'jeans';//new category to insert
$so_to_set = 2;// so for new category
First update all so greater than or equal to $so_to_set
$query = "update categaries ...
- 747
6
votes
Accepted
Should I use VARCHAR or DATETIME?
You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR.
Additionally, if you stored ...
- 13k
6
votes
Accepted
Entering Exact Values into a Table Using SQL
Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks.
Use parameterized ...
- 4,764
5
votes
Accepted
PhpMyAdmin Import Error-MySQL server has gone away/Unrecognized Keyword
Changing the MySQL settings worked. This is what I changed, exactly:
In php.ini
Find:
post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
max_input_time = 60
memory_limit = 8M
...
5
votes
Accepted
Optimization of complicated sums and counts in related tables
Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.
The following query may help you understand this better:
SELECT p.customer_id
, c.name
...
- 69.7k
5
votes
Cassandra "Operation timed out - received only 0 responses." during write
You will need to increase commitlog_segment_size_in_mb to a higher value. Try 64MB instead.
It increases the size of the commit record segment. This property determines the maximum mutation size (...
- 1,439
5
votes
Accepted
How to select records where NOW is between two dates in fields?
You can use the NOW() function in mysql. Note, this assumes that termDate is the later date:
Select content, image, imageHeight, imageWidth, link, linkDescription
From news
Where appearanceDate &...
- 2,057
5
votes
Accepted
Mysql select rows with 7 continuous dates
MySQL 8
MySQL 8 provides window functions...
SELECT item_id
FROM (
SELECT
item_id,
date,
count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
FROM (
...
- 62.1k
5
votes
Order by numbers followed by alphabet and then null values
select
*
from
t
order by
case
when col regexp '^[0-9]' then 1
when col regexp '^[a-zA-Z]' then 2
when col = '' or col is null then 3
end
, col * 1 /*this converts to number, so that 100 is not sorted ...
- 3,148
5
votes
Why is ON DUPLICATE KEY not working as expected?
Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query?
Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. ...
- 97.3k
5
votes
Accepted
MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains non aggregated column fixed bug reappear every restart
That's not a bug. That's 'move to standard'.
Do not fix this problem by change server's SQL Mode. Fix it by altering the queries which uses partial GROUP BY expressions.
Details can be read in ...
- 19.4k
5
votes
Does this kind of data/field in this table needs normalization in MYSQL?
I would vote yes to normalize it. Units of measure, i.e. a UOM column, should literally be units of measure, e.g. "meters", "yards", "pounds", "tons" etc. I don'...
- 35k
5
votes
Accepted
Converting large database with many tables from latin1 to utf8mb4
At my last job we used pt-online-schema-change for such changes, or any other ALTER TABLE changes, hundreds of times per week on tables much larger than yours. I worked on an internal service and ...
- 13.8k
5
votes
Accepted
Is it secure to have database login in a PHP file? Other options?
What I have done for PHP projects is to define the database credentials (or any other credentials, for example web services) in files outside the http document root.
In fact, most of your code can and ...
- 13.8k
5
votes
Accepted
Delete records in different tables
You can't ordinarily delete from more than one table in a single T-SQL DELETE statement.
It is possible to achieve the same effect using triggers or cascading foreign keys with an ON DELETE CASCADE ...
- 82.9k
4
votes
Accepted
High CPU Usage for single query
I would recommend adding indexes on the columns participating in the JOIN conditions and the WHERE clauses.
Indexes are designed to help the query optimizer make better choices. Unless you enforce ...
- 3,673
4
votes
How to speed up mysql query using cache
(1) Consider designing the schema so that you join on users.id, not users.email. That will lead to a better way to formulate the query -- by computing the aggregates before joining to users.
(2) Don'...
- 76.9k
4
votes
Create table if not exist each time API called: bad practice?
So let's think forward a little. The application goes live, the tables are created and everything's lovely. Then, you have a new requirement and the tables must change. How do you code that? There's ...
- 24.7k
4
votes
Create table if not exist each time API called: bad practice?
I was searching for overhead info the same reasons. There is probably no overhead because the table is usually cached and there will be no disk access. The bottleneck of databases is usually disk ...
- 141
4
votes
Accepted
How to join 2 tables in mysql but with different values in one of the tables
You can use LEFT JOIN instead of JOIN.
With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.
SELECT * ...
- 316
3
votes
MySQL PDO Cannot assign requested address
Using a persistent connection worked for me. The linked recommondation above shows how to create a persistent connection with mysqli. I am using PDO, too. So I had to add the following to my ...
- 131
Only top scored, non community-wiki answers of a minimum length are eligible