mysql只有一列list,MySQL的SELECT凡在LIST而不是在同一个SQL LIST

I have this:

$ids = "1,2,3,4,5";

$sqlQuery = "SELECT id, moderation_date

FROM table_live

WHERE id IN (".$ids.")";

$q = $this->CI->db->query($sqlQuery);

if($q->num_rows() > 0) {

foreach ($q->result() as $row) {

$arr[] = $row;

}

}

return $arr;

This is just working fine if all ids exist in table_live

and return

array([id] => 1 [moderation_date] => 2012-04-11 12:55:57)....

The problem: If I send a list of ids 1-2-3-4-5 where only 1-2-5 match the IN LIST clause

I need to return all in list and for those don't match the list a null value.

array([id] => 3 [moderation_date] => null)

解决方案

generate an outer join statement so that you get:

SELECT ids.id, table_live.moderation_date

FROM (select 1 id union all select 2 union all ....) ids

LEFT JOIN table_live

ON ids.id = table_live.id

where ids is a subquery enumerating all the values, something like this:

$ids = '1,2,3,4,5'

$subquery = 'select '.str_replace(',', ' id union all select ', $ids).''

$sql = "SELECT ids.id, table_live.moderation_date

FROM ($subquery) ids

LEFT JOIN table_live

ON ids.id = table_live.id"

be sure to select ids.id, not table_live.id. That way, the ids will always show up, and the moderation_date only if the corresponding row exists in table_live.

Another approach would be to keep the query as you had it, store the result in an array, and then merge the arrays in php so that you retain all keys, and fill in the values only where the key matches in both arrays.

I am not really sure what kind of db library you're using so I don't know how to obtain an array of the resultset, but suppose you would have stored the rows in a php array, using a string representation of the id as key, and the date as value, then this code should do the trick:

$items = array(

'1' => NULL

, '2' => NULL

, ...

);

//note: use string keys in order to merge!!

$result = array_merge($items, $resultset);