I have two tables:
1st table (t1):
item_no | item_desc
-------------
200 | d1
250 | d2
257 | d3
305 | d4
605 | d5
606 | d6
2nd table (t2)
id | item_no | item_value
-------------
1 | 200 | v1_200
1 | 250 | v1_250
1 | 257 | v1_257
1 | 305 | v1_305
1 | 605 | v1_605
1 | 606 | v1_606
2 | 200 | v2_200
2 | 250 | v2_250
2 | 257 | v2_257
2 | 305 | v2_305
2 | 606 | v2_606
I get the value of the id field in t2 via $id= $_POST['id']; and do the security checks on it, etc.
I perform a join search of both tables with a query like this:
$items= array();
$items_no = array('200', '250', '257', '305', '605', '606');
$items_nos = implode(",", $nutrient_no);
$sql = "SELECT item_value FROM t1 join t2 ON t1.item_no=t2.item_no WHERE t1.id='$id' AND t2.item_no in ($items_nos)";
Finally, I extract the results like this:
$retval = mysql_query($sql) or die('Query failed: ' . mysql_error());
while($row = mysql_fetch_assoc($retval))
{
$item_value[] = $row['item_value'];
$first_item = $item_value['0'];
$second_item = $item_value['1'];
....
$last_item = $item_value['5'];
}
Problem is, for searches on id # 2, as illustrated in the example I gave above, item_no 605 does not exist. The search still works fine, but the array keys are no longer right, i.e. $last_item's value is not returned properly because the key is now 4 instead of 5.
IMPORTANT: depending on the id used in search, none, one or more rows may be missing in the results.
I have tried this as a solution:
$items_count = count($item_value);
for( $x = 1; $x <= $items_count ; $x++ ) {
$foo = 'item_value' . $x;
if ( isset( $$item_value[$x-1])) {
$$foo = $$item_value[$x-1];
}
else {
$$foo = '';
}
$last_item = $foo['5'];
}
but it doesn't seem to work.
What am I doing wrong? Is there a better way to do this? Any help greatly appreciated!
It's strange to me to have a variable number of variables depending on a query result... but you could try something like that:
for ($i=0; $i < count($arr)-1; $i++) {
${"item_n" . $i} = $arr[$i];
}
And for the last item:
$last_item = end(array_values($arr));