MySQL ORDER BY messing up sort

Posted on 2009-07-01
Medium Priority
Last Modified: 2013-12-13
I got help with a query yesterday that orders by a set of random results. The code is below. The problem I'm having now is a weird one and I'm not entirely sure how to fix it.

I'm pulling records: 5,8,3,6,10,7,11,2,4,9,1
When I loop through the results however I'm getting: 5,8,3,6,1,10,7,11,2,4,9

Notice the `1` was pushed right before the 10?

I cut out a few of the unnecessary pieces of code and threw the ID's I was trying to pull in the query for testing purposes.
mysql: SELECT question_id, question FROM questions WHERE question_id IN (5,8,3,6,10,7,11,2,4,9,1) ORDER BY INSTR('5,8,3,6,10,7,11,2,4,9,1', question_id)
$sql = $this->db->GetAll("
  FROM " . $this->db_prepend . "questions
  WHERE question_id IN (5,8,3,6,10,7,11,2,4,9,1)
  ORDER BY INSTR('5,8,3,6,10,7,11,2,4,9,1', question_id)
foreach ($sql as $r) {
	echo $r['question_id'] . ' - ';

Open in new window

Question by:SOakley54
LVL 12

Accepted Solution

kevin_u earned 2000 total points
ID: 24758926
INSTR does a string match without regard to a delimter.
So 1 matches the 1 in 10.

try this:
ORDER BY INSTR('05,08,03,06,10,07,11,02,04,09,01', lpad(question_id,2,'0')

this makes the leading zeros part of the string and the search string.

Author Closing Comment

ID: 31598998
Nice observation. It worked with a small tweak. Thank you!

ORDER BY INSTR('05,08,03,06,10,07,11,02,04,09,01', LPAD(question_id, 2, 0))

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question