PHP and MYSQLi - Bind parameters using loop and store in array?

Hi, It will be easier to explain with the next code (It's wrong, by the way):

  $selectGenre_sql = 'SELECT genreID FROM genres WHERE dbGenre = ?';

  if ($stmt->prepare($selectGenre_sql)) {
  // bind the query parameters
  $stmt->bind_param('s', $genre);
  // bind the results to variables
  $stmt->bind_result($genres);
  // execute the query
  $stmt->execute();
  $genre = array();
   while ($stmt->fetch()) {
    $genre[] = $genres;
   }
  }

The code above gets the value from 'genreID' when 'dbGenre' is equal to '$genre'. And then store the results in an array. But i'ts not working. Why? I think because '$genre' is an array, so I need to loop trough it to get a different value from 'genreID' each time.

$genre is an enumerated array containing movie genres, for example:

[0] => Action [1] => Adventure [2] => Fantasy

I need to compare de value (like 'Action' for example)

The 'genres' table contains two columns: genreID (INT) and dbGenre (VARCHAR)

I just need each genreID (that is a number).... Lets say.. when dbGenre is equal to Action, then store the genreID in an array1, and then loop the $genre array to get the genreID for the next value and store it again in array1

How can I fix it?? I'm new to programming so please be as detailed as you can. Thanks!!

From stackoverflow

• A few things.

  • Could it be is't because your overwriting the $genre var, try changeing it to $genreArray in the sedond case?

  • Make sure that the database is actually returning things (try it in phpMyAdmin or something similar)

  • Try processing like this:

  .

   $genreId = -1;
   $stmt->bind_results($genreId);
   $stmt->execute();
   while($stmt->fetch()){
    $genreArray[] = $genreId;
   }
  

  Jonathan : I just need each genreID (that is a number).... Lets say.. when dbGenre is equal to Action, then store the genreID in the array, and then loop to get the genreID for the next array value..

  Pim Jager : ah okay, I'll edit my answer.

• You can't bind an array to an SQL parameter. You can use a parameter in SQL in place of a single literal value. Not a list of values, or an expression, or a column name or table name.

  To solve the task in your case, you can use either of two solutions:

  First solution: loop over $genre array, bind each value one at a time, and execute the SQL query for each value.

  if ($stmt->prepare($selectGenre_sql)) {
    $genre = array();
    foreach ($gengre as $genreID) {
      $stmt->bind_param('s', $genreID);
      $stmt->execute();
      $stmt->bind_result($genres);
      while ($stmt->fetch()) {
        $genre[] = $genres;
      }
    }
  }
  

  Second solution: execute the query once, with multiple parameters, one for each value in the array. This requires some tricky code to build a variable number of ? placeholders in the SQL query, separated by commas.

  $selectGenre_sql = 'SELECT genreID FROM genres WHERE dbGenre IN ('
   . join(',', array_fill(0, count($genre), '?')) . ')';
  
  if ($stmt->prepare($selectGenre_sql)) {
    $genre = array();
    . . .
  

  Also you need to get tricky calling bind_param() with a variable number of arguments based on the elements in your $genre array:

    . . .
    call_user_func_array( array($stmt, 'bind_param'), 
      array_unshift($genre, str_repeat('i', count($genre)));
  
    $stmt->execute();
  
    $stmt->bind_result($genres);
  
    while ($stmt->fetch()) {
      $genre[] = $genres;
    }
  }
  

  You might want to consider using PDO::mysql because it's easier to bind parameters from an array. The MySQLi interface is pretty awkward for this case.