-
One-to-Many Count Parent’s Children in mySQL
So I have two tables. The first one contains a list of images that belong to a job, the second one contains all the different properties of each image. Parent Table(t1) fileName jobID file1.jpg job01 file2.jpg job01 Child Table(t2) fileName jobID OptionName Value file1.jpg job01 contrast SomeValue file1.jpg job01 saturation SomeValue There can be zero…
-
Sorting characters and numbers in mySQL
So I have a table with a column in this form someColumn 1 A 2 C 3 10 When you ORDER BY someColumn you get result in this order 1,10,2,3,A,C as you can see the number sequence is all messed up, 10 doesn’t go after 1 to fix that you can do ORDER BY (someColum…
-
Delete expired entries in MYSQL
For those who want to delete items in a MySQL database based on a date field the process is very easy. First of all you need a DATE field, for example a coupon expiration date. the data is saved in the following format: YYYY-MM-DD Then all you need to delete older items is this: DELETE…
-
Efficient use of COUNT()
The other day I was trying to figure out how many records there were in my DB I found in a website that I needed to use COUNT(*). The example given went something like this: SELECT COUNT(*) FROM ‘table’ and while this worked as advertised, I got a very inconvenient side effect. The query was…