MySQL ORDER BY messing up sort

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)
 
<?php
$sql = $this->db->GetAll("
  SELECT
    question_id,
    question
  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

SOakley54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kevin_uCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SOakley54Author Commented:
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))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.