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.
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.
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
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:
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
I just needed to specify special layout for the articles of selected categories. I was looking for some solution online, but none of them worked. Mostly you can find this:
JRequest::getInt('catid', '', 'get');
Continue reading Joomla: get category ID in template →
I was just asked to update tax classes on products in the catalogue. I found a easy and fast solution. Continue reading Magento: Changing Tax class on all products →
I just spent couple of hours by finding out, why is my Magento administration logging me out after moving my Magento store to new server. I found many hacks, modification and setup tips on the Google, but anything helped to me. Continue reading SOLVED: Magento keeps logging out from administration →
I needed to find out how to work with Google Geo API in my last project and especially with calculating a radius and distance between two geo points. Another goal was to get a latitude and longtitude from the address typed in the register form (in the background). Finally it was quiet easy. Here is the solution: Continue reading Google Maps API & PHP+MySQL – working with radius and distance →
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 →
Yesterday, I was solving simple database Query, where I needed to get some specific data. And while browsing thru MySQL documentation I found a very nice GROUP BY modifier which allows me make less queries and save some CPU on server. Let me explain how it works… Continue reading MySQL WITH ROLLUP modifier →