Retrieve mysql data without any order applied

Question

I have a table with the name of actions with primary key of action_id, i am retrieving data from this table as passing my own ordered action_ids for example

$actionIds = array(5,9,10,21,3,18,4);
$actionsTb = Engine_Api::_()->getDbtable('actions','activity');

$postSelect = $actionsTb->select()
              ->where('action_id IN(?)', $actionIds)
              ->where('type = ?', 'status')
             ;

now the issue is when i get the result back its in ascending order, like ( 3,4,5,9,10,18,21 ) but what i want the order of result as i passed the action ids means don't want to apply any order on the result. please help me. you can reply with simple query too.

Answer 1

Since your using php, why not use loop to dynamically create where clause here is the code

$where = "action_id IN(";

for($x=0; $x<count($actionIds); $x++)
{

    // code for adding comma in every end of id 
    if($x==count($actionIds)-1)
    {
       // found the last data in array add closing parenthesis in IN funtion
       $where.=$actionIds[$x].")";

    }
    else
    {
       $where.=$actionIds[$x].",";
    }
}

to test the output echo it first

echo $where; //so i think the result is "action_id IN(5,9,10,21,3,18,4)"

Here is the complete code

$actionIds = array(5,9,10,21,3,18,4);
$where = "action_id IN(";

for($x=0; $x<count($actionIds); $x++)
{

    // code for adding comma in every end of id 
    if($x==count($actionIds)-1)
    {
       $where.=$actionIds[$x].",";
    }
    else
    {
      // add closing parenthesis in IN funtion
       $where.=$actionIds[$x].")";
    }
}

$actionsTb = Engine_Api::_()->getDbtable('actions','activity');

$postSelect = $actionsTb->select()
          ->where($where)
          ->where('type = ?', 'status')
         ;

Answer 2

I don't know zend coding but following approach may help you.

->where( 'FIND_IN_SET( action_id, ? )', $actionIds )

I am not sure if zend's where converts the array $actionIds to be linear item values i.e. '5,9,10,21,3,18,4'. If converted, part of the resulting query would look like:

where find_in_set( action_id, '5,9,10,21,3,18,4' )

Example:

mysql> select find_in_set( 18, '5,9,10,21,3,18,4' );
+---------------------------------------+
| find_in_set( 18, '5,9,10,21,3,18,4' ) |
+---------------------------------------+
|                                     6 |
+---------------------------------------+
1 row in set (0.00 sec)

Reference:
Read MySQL documentation on FIND_IN_SET