Category Archives: MySQL

UPDATE: Keep order of rows while using WHERE IN () – MySQL

MySQL, PHP & MySQL, Tutorials & guides,

I was just solving issue, where I needed to keep an order of items returned from the MySQL database listen in the WHERE IN clause.
Original query:

SELECT * FROM table WHERE id IN (1,5,8,73,5,4,88)

This will return rows ordered by its primary key (in my case column id). Here is updated query, which will keep order of items listed in WHERE IN clause:

SELECT * FROM table WHERE id IN (1,5,8,73,5,4,88) ORDER BY FIELD(id,1,5,8,73,5,4,88)

Please note first item in brackets behind ORDER BY – there should be set same column, which was used at WHERE IN clause.

UPDATE: There was missing function called FIELD after ORDER BY – code is fixed now.

Generate random birth date in MySQL table and get Age by select

MySQL, PHP & MySQL

Today I was solving generating of random data for testing and manipulation with the date of the birth.

Here are two simple SQL queries solving both:

Query for generate random date of birth in whole table

UPDATE

people

SET

date_of_birth=CONCAT_WS('-',(FLOOR( 1900 + RAND( ) *100)),(FLOOR( 1 + RAND( ) *12 )),(FLOOR( 1 + RAND( ) *28 )))

Query for selecting date of birth and calculation of age in MySQL:

SELECT
date_of_birth,
DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')) AS age
FROM people

MySQL visual database design

MySQL, PHP & MySQL, ,

I was looking for some nice freeware visual mysql database designer couple of weeks ago. I found two of them: DBDesigner 4 and MySQL Workbench. I must say, that both programs are really very similar, I mean from the view of the UI and menus structure. I gave a little user testing to both of them and designing database using these tools is very easy and fast… Continue reading MySQL visual database design →