Retrieving the data from an sql statement

Question

I have written this code which in theory i want to loop round an array and for every value use in a select statement to retrieve the applicable information. Then map a particular value id as a key and the value from the sql statement as its associated value. Though i cant seem to figure out how to add it as a value into my array im sure im a word out.

heres my code

    /*
     * Loop through the hasNewModelIdInYear and retrieve the exterior media paths
     * with a mapped id as a key.
     */

     $mediapatharray = array();

     foreach ($hasNewModelIdInYear as $key => $value) {

     $selectMediaPathFromValue = "SELECT `name` FROM `media` WHERE `id`='".$value['img1_media_id']."'";

     $res = $mysqli->query($selectMediaPathFromValue);


        $mediapatharray[$value['model_id']] = $res;
    }

All that array returns is an array full of keys but no values.. With the variable $res do i then have to ->fetch_value? as im not sure on the syntax needed in order to access the data from the query?

regards mike

Answer 1

it is not good writing whan you have query inside loop. you should search based on array of img1_media_id

you can do follwing

$selectMediaPathFromValue = "SELECT `name` FROM `media` 
WHERE `id` IN = '$hasNewModelIdInYear'";

array should be following format

$hasNewModelIdInYear = "12,21,22,65";

Answer 2

The result will return false on failure or the results on success. Mysqli result will be returned the first set of array that consist of the array index. You will need to fetch the values and store it in array. Try adding this code.

while($row = $res->fetch_assoc()){

  $mediapatharray[$value['model_id']] = $row['name'];
}

Answer 3

Thanks for your responses i was trying to make it more complicated than it needed to be. I done it by putting the whole media table in a multidimension array then looping through them both and comparing values and if mathcing id map the name.

simple connection and sql query to populate the array, then map the id to a key and name as its value. heres my code.

mediaarray is an array with the media table contents populated in it

    $mediaIdPAthFromOld = array();

    foreach ($hasNewModelIdInYear as $hnkey => $hsvalue) {
       foreach ($mediaarray as $mpavalue) {
          if ($hsvalue['img1_media_id'] == $mpavalue['id']) {
              $mediaIdPAthFromOld[$hsvalue['model_id']] = $mpavalue['name'];
              }
           }
      }

though this has done what i wanted i assume there is a more effient way to do this.

regards mike