Hot answers tagged

6

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 it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, ...


2

The most immediate reason your attempt did not work was because it was syntactically invalid. You can use the result of a SELECT statement as a scalar value in an expression but you must enclose the said SELECT statement in parentheses. In your case, there are two such SELECT statements. Below is your attempted query slightly reformatted for readability ...


2

MAMP seems to handle the root username differently than WAMP. I had to include the root password as 'root' in my PHP script, instead of leaving it blank, as such: $link = mysqli_connect('localhost', 'root', 'root');


2

Find the correct configuration file: su - postgres -c "psql -t -P format=unaligned -c 'show hba_file';" Add the following at the end of file: local all all peer Then restart your PostgreSQL application: /bin/systemctl restart postgresql*.service


2

DROP TEMPORARY TABLE IF EXISTS tempTable; CREATE TEMPORARY TABLE tempTable (id int ,minId int PRIMARY KEY (id) ); -- filling temporary table with list of duplicate ids -- and corresponding minimal id within each group of duplicates WITH CTE AS ( SELECT id ,MIN(id) OVER (PARTITION BY modern_source_id, location_within_source ) AS minId ,ROW_NUMBER ...


1

Test: SELECT CASE WHEN sender_id = 1 THEN receiver_id ELSE sender_id END user_id FROM messages WHERE 1 IN (sender_id, receiver_id) GROUP BY user_id ORDER BY MAX(id) DESC LIMIT 3


1

If your tables are not big you can try this query: SELECT c.cname ,(SELECT COUNT(1) FROM blog WHERE FIND_IN_SET(c.cname, cname) > 0 ) AS count FROM categories AS c For large amount of data it may be better to normalize your tables and use indexes to join them. Something like this: CREATE TABLE blog_to_categories ( bid int ,cid int ,...


1

Definitions and sample data, either provide a fiddle or definitions and sample data as below, to get more attraction to your question: create table junc_modern_source_has_reference ( id int not null primary key , modern_source_id int not null ); insert into junc_modern_source_has_reference values (52,17),(89,17),(99,17); create table ...


1

one big table with all these rows was a better way to go? Yes, absolutely With proper table structures and indexing, MySQL will comfortably handle millions of rows in any table. The "Table-per-?" model is never a Good Idea and invariably comes back to haunt you (usually when you need to summarise across all those tables).


1

PHP & Ruby drivers are put on hold - they will receive only critical bug fixes but not the new functionality. You should be able to compile existing code for PHP versions beyond 7.1, but DataStax doesn't provide pre-built versions.


1

To directly answer your question, no, you can't use the exact same code from that tutorial with the IPv6 version. Here are a few reasons: The LOAD DATA LOCAL INFILE query that inserts the data uses MySQL's INET_ATON() function, which won't work with IPv6. Thankfully MySQL has another function INET6_ATON() which supports both IPv4 and IPv6, although it ...


1

For Unique Impression IP as there is no need for a autoincrement primary key, use VARBINARY(16) for IP address, and as the primary key. Use INET6_ATON() when inserting it to keep it in a compact form. Use: INSERT IGNORE INTO `Unique Impression IP` VALUES (INET6_ATON($var)) And look at rows affected to see if that exists or not for your UPDATE Banner ...


1

The connection information suggest the SQL instance is simply a local SQL Server instance installed in the VM like any other software so you don't use the Azure portal to manage it. Instead, ssh into the VM and run systemctl status mssql-server --no-pager to see if mssql-server.service is listed. The instance could also be a docker database container, in ...


1

(" or mysql") ( SELECT ... FROM ... WHERE id < x ORDER BY id DESC LIMIT 3 ) UNION ALL ( SELECT ... FROM ... WHERE id > x ORDER BY id ASC LIMIT 5 ) ORDER BY id -- optional If you want to include id=x, then change < to <= or > to >=. How to turn that into Laravel? I don't know. Maybe you will need something clumsier.


1

Your best option is to take and then restore a backup of the database. If your sample database is fairly static then I suggest making a gold standard backup and using that. If it's under constant modification then taking a backup and then restoring it on the spot should be sufficient. BACKUP DATABASE [Sample] TO DISK=N'<path to file>' RESTORE ...


1

The most obvious solution to this would perhaps be to use triggers. You can have triggers that fire before/after inserts, deletes and updates on the table(s) in question, and then log the changes in your special table, including a time stamp and what type of write operation it was. Another approach could be to use an audit plugin - MariaDB's audit plugin ...


1

You need to add some flags (or something) to the data. "None of the above" needs a flag saying it must be last. Two questions that must be consecutive -- Indicate on the first that a particular question must immediately follow it. Once you get "business logic" like this, you will probably find that the handling of the flags and the randomization may as ...


1

Put t3.store_number LIKE :store_number in your JOIN ON criteria rather than the WHERE criteria. Being in WHERE means the the store_number of NULL won't match the criteria however being in a LEFT JOIN, t3.* are padded in the result when the JOIN criteria isn't met.


1

Its possible to use type JSON https://dev.mysql.com/doc/refman/8.0/en/json.html this negates the need for a table of custom fields, however if you need to do join/look ups on this data, it will be difficult.


1

Wow, that's a relevant problem, because you can't identify an error in a procedure, an this passes hidden next to some valid result! I think your question should be more popular. After many tries and tests I found a way to solve it. You'd expect to loop through the procedure result set, until no more results are available (the PHP Manual way). However, I ...


1

SQL standards less ancient than 92 have got rid of the hard-to-kill misconception that composite data are anathema. What makes an atomic data value is in the eye of the beholder, or, expressed less poetically, in the semantics of the data. Few people would argue that a string is composed of characters and so has to be "normalized" and stored as individual ...


Only top voted, non community-wiki answers of a minimum length are eligible