Why is index value not assigned in PHP associative array?

Question

PHP

There is a code..

$db = new Database();
          $options=array();
          $data=array();
          $db->query('SELECT * FROM products LIMIT 0, 10');          
          $db->execute();
          $prod = $db->resultset();
          foreach ($prod as $d) {
            $db->query('SELECT product_lists_val.*, lists.ru AS VAL, lists_name.ru AS LNAME from product_lists_val inner join lists_name on lists_name.id = product_lists_val.list_id inner join lists on lists.id=product_lists_val.val WHERE product_lists_val.prod_id='.$d['id']);          
            $db->execute(); 
            $lists = $db->resultset();
            if (count($lists) != 0 ) {
              $options[$d['id']]=$lists;    //вот тут ошибка
            }

I need the product ID to be used instead of the standard indexes in the $options[] array (0,1,2,3... ). To do this, I do $options[$d['id']]=$lists;
but I get the following result:

[0] => Array
        (
            [id] => 1
            [prod_id] => 8
            [list_id] => 1
            [val] => 60
            [VAL] => Красный
            [LNAME] => Общий список цветов
        )

    [1] => Array
        (
            [id] => 2
            [prod_id] => 8
            [list_id] => 2
            [val] => 85
            [VAL] => Алюминий
            [LNAME] => Материал
        )

And I need [0] => Array ... [1] => Array to be [ID_PRODUCT1] => Array [ID_PRODUCT2]
=> Array
Created an example separately - everything works as it should..

<?php
$names["Иванов"]="Иван";
$names["Сидоров"]="Николай";
$names["Петров"]="Петр";
var_dump($names);
?>

array(3) {
  ["Иванов"]=>
  string(8) "Иван"
  ["Сидоров"]=>
  string(14) "Николай"
  ["Петров"]=>
  string(8) "Петр"
}

Why is the array index not being assigned?
UPD
I tried to insert an arbitrary value together with the product ID from the database

$i=100;
    foreach ($prod as $d) {
            $db->query('SELECT product_lists_val.*, lists.ru AS VAL, lists_name.ru AS LNAME from product_lists_val inner join lists_name on lists_name.id = product_lists_val.list_id inner join lists on lists.id=product_lists_val.val WHERE product_lists_val.prod_id='.$d['id']);          
            $db->execute(); 
            $lists = $db->resultset();
            if (count($lists) != 0 ) {
              $options[$i]=$lists;    //вот тут ошибка
            }
$i=$i+1;
}

still goes standard numbering 0,1,2,3....

Answer 1

I don't know the structure of your products table, but I mean not id , but prod_id from the first query:

if (count($lists) != 0 ) {
       $options[$d['prod_id']] = $lists; 
}

Or use the prod_id from the second request:

if (count($lists) != 0 ) {
       $options[$lists['prod_id']] = $lists; 
}

Answer 2

$db = new Database();
$options = $data = $prodID = array();
$db->query('SELECT * FROM products LIMIT 0, 10');          
$db->execute();
$prod = $db->resultset();

foreach ($prod as $d) 
  $prodID[] = $d['id'];
  
if ( !empty($prodID) ){
  $prodID = implode(',', $prodID);
  $db->query("SELECT product_lists_val.*, lists.ru AS VAL, lists_name.ru AS LNAME from product_lists_val inner join lists_name on lists_name.id = product_lists_val.list_id inner join lists on lists.id=product_lists_val.val WHERE product_lists_val.prod_id in({$prodID})");          
  $db->execute();
  $lists = $db->resultset();
  if( !empty($lists) )
    foreach ($lists as $key => $value)
      $options[ $value['id'] ] = $value;
}

Answer 3

So everything is correct, so, as always, the devil is in the details.
Is it possible to paste your code? Write echo at the beginning of $d[id] to see if what you expect is there; (though, however, judging by $lists, it should be ok) then we check it: output (well, or var_dump, whatever you like) $options[$d['id']]=$lists;; print_r($options[$d['id']])if everything is fine in this place, then, perhaps, further in the body of the program, these $options are overwritten by some other function. In a word, we test every step. It's boring, but what to do.