MySql query returned array not ordered like input array

Question

I have a array with some id's I use to select a row set form the database.

The selection goes well but I dont receive my results back in the ordered given array

Inserted array (var_dump)

  0 => string '40201' (length=5)
  1 => string '44089' (length=5)
  2 => string '42106' (length=5)
  3 => string '42740' (length=5)
  4 => string '43812' (length=5)
  5 => string '44331' (length=5)
  6 => string '42109' (length=5)
  7 => string '44147' (length=5)
  8 => string '40464' (length=5)
  9 => string '42108' (length=5)

Output array

array
  0 => 
    array
      'id' => string '40201' (length=5)
  1 => 
    array
      'id' => string '40464' (length=5)
  2 => 
    array
      'id' => string '42106' (length=5)
  3 => 
    array
      'id' => string '42108' (length=5)
  4 => 
    array
      'id' => string '42109' (length=5)
  5 => 
    array
      'id' => string '42740' (length=5)
  6 => 
    array
      'id' => string '43812' (length=5)
  7 => 
    array
      'id' => string '44089' (length=5)
  8 => 
    array
      'id' => string '44147' (length=5)
  9 => 
    array
      'id' => string '44331' (length=5)

My zend query:

    $select = $this->_db
        ->select()
    ->from(array('file' => 'filehosts'), array('id'))
    ->where('file.id IN(?)', $array);

    $result = $this->getAdapter()->fetchAll($select);
    return $result

I think it's the conclusion of the id following up, first one is taken and then he just take the one who he finds first who is in the array right? How can I make this more strict so the query doesn't change the array order?

Thanks in advance. Nick

Im not familiar with zend, but it looks to me like the SELECT statement is missing an ORDER BY — OneThreeSeven
– OneThreeSeven, Commented Apr 5, 2012 at 13:28

Weltschmerz · Accepted Answer · 2012-04-05 15:33:01Z

1

Mysql FIELD function will work.

$select = $this->_db->select()
->from(array('file' => 'filehosts'), array('id'))
->where('file.id IN(?)', $array)
->order(new Zend_Db_Expr('FIELD(file.id, ' . implode(',', $array) . ')'));

$result = $this->getAdapter()->fetchAll($select);
return $result

edited Apr 5, 2012 at 15:33

answered Apr 5, 2012 at 15:24

Weltschmerz

2,1861 gold badge15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank van Wijk · Accepted Answer · 2012-04-05 13:31:58Z

0

When you do a query, you can order the results with ORDER BY

SELECT * FROM `file` WHERE `file`.`id` IN $array ORDER BY `file`.`id` ASC

I'm not familiar with zend, but I think ->order('file.id') could do the job.

See Zend documentation

answered Apr 5, 2012 at 13:31

Frank van Wijk

3,28222 silver badges42 bronze badges

4 Comments

directory Over a year ago

I know the order but as you can see, I created an array who is already ordered (comes form sphinxSearch) so I want to have my results back in an array like I put them in

Frank van Wijk Over a year ago

I'm pretty sure that the IN part of the query ingores the ordering of your array. You could order your results afterwards with ksort() or one of the other array sorting functions of php.

directory Over a year ago

Yes, I am sure now it ignores the order of my array and I think creates his own most efficient way to search the given array. ksort() is not an option because the key is still the same so the value is not in the good order.

Frank van Wijk Over a year ago

Yeah true, so you can use usort in combination with your own (anonymous) function in to sort on id.

Tony Hopkinson · Accepted Answer · 2012-04-05 13:45:52Z

0

Think about what the underlying code would be doing.

It's going through them in the file which is almost certainly ordered by id and the testing if it's in your array which is in some compeletely different order.

The order of the items in $array, is totally irrelevant to the request, all that matters is exists or not.

answered Apr 5, 2012 at 13:45

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

2 Comments

directory Over a year ago

Why is it irrelevant? I get my array results from the sphinxSearch in the good order, with this array I can do 1 query for one page to get the 'search results' This is what I try to reach now, just one step away from the finishing line to print the results (i print now just id to make it easier to see).

Tony Hopkinson Over a year ago

It's irrelevant to SQl's in function. To get what you want you'd have to reverse the operation select from array where in Select from Table, which would be a 'erm difficult.

Collectives™ on Stack Overflow

MySql query returned array not ordered like input array

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related