I have an array of integers, how do I use each one in a mysql query (in php)? [duplicate]

by

As with nearly all “How do I do SQL from within PHP” questions – You really should use prepared statements. It’s not that hard:

$ids  = array(2, 4, 6, 8);

// prepare an SQL statement with a single parameter placeholder
$sql  = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id = ?";
$stmt = $mysqli->prepare($sql);

// bind a different value to the placeholder with each execution
for ($i = 0; $i < count($ids); $i++)
{
    $stmt->bind_param("i", $ids[$i]);
    $stmt->execute();
    echo "Updated record ID: $id\n";
}

// done
$stmt->close();

Alternatively, you can do it like this:

$ids    = array(2, 4, 6, 8);

// prepare an SQL statement with multiple parameter placeholders
$params = implode(",", array_fill(0, count($ids), "?"));
$sql    = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id IN ($params)";
$stmt   = $mysqli->prepare($sql);

// dynamic call of mysqli_stmt::bind_param                    hard-coded eqivalent
$types = str_repeat("i", count($ids));                        // "iiii"
$args = array_merge(array($types), $ids);                     // ["iiii", 2, 4, 6, 8]
call_user_func_array(array($stmt, 'bind_param'), ref($args)); // $stmt->bind_param("iiii", 2, 4, 6, 8)

// execute the query for all input values in one step
$stmt->execute();

// done
$stmt->close();
echo "Updated record IDs: " . implode("," $ids) ."\n";

// ----------------------------------------------------------------------------------
// helper function to turn an array of values into an array of value references
// necessary because mysqli_stmt::bind_param needs value refereces for no good reason
function ref($arr) {
    $refs = array();
    foreach ($arr as $key => $val) $refs[$key] = &$arr[$key];
    return $refs;
}

Add more parameter placeholders for other fields as you need them.

Which one to pick?

  • The first variant works with a variable number of records iteratively, hitting the database multiple times. This is most useful for UPDATE and INSERT operations.

  • The second variant works with a variable number of records too, but it hits the database only once. This is much more efficient than the iterative approach, obviously you can only do the same thing to all affected records. This is most useful for SELECT and DELETE operations, or when you want to UPDATE multiple records with the same data.

Why prepared statements?

  • Prepared statements are a lot safer because they make SQL injection attacks impossible. This is the primary reason to use prepared statements, even if it is more work to write them. A sensible habit to get into is: Always use prepared statements, even if you think it’s “not really necessary.” Neglect will come and bite you (or your customers).
  • Re-using the same prepared statement multiple times with different parameter values is more efficient than sending multiple full SQL strings to the database, because the database only needs to compile the statement once and can re-use it as well.
  • Only parameter values are sent to the database on execute(), so less data needs to go over the wire when used repeatedly.

In longer loops the execution time difference between using a prepared statement and sending plain SQL will become noticeable.

More Related Contents:

  1. Sign in form always showing "Wrong Credentials"
  2. Prevent creating duplicate entries in PHP
  3. i have stored array in database as a string how can retrieve that array?
  4. Insert array into MySQL database with PHP
  5. Sum values of multidimensional array by key without loop
  6. Save PHP array to MySQL?
  7. Notice: Array to string conversion in
  8. mysql_fetch_array, mysql_fetch_assoc, mysql_fetch_object
  9. How get all values in a column using PHP?
  10. mysql select query within a serialized array
  11. PDO fetch one column from table into 1-dimensional array
  12. Get all mysql selected rows into an array
  13. Select from mysql table WHERE field=’$array’?
  14. MySQL query using an array [duplicate]
  15. How to insert json array into mysql database
  16. Why do I get “Resource id #4” when I apply print_r() to an array in PHP? [duplicate]
  17. PHP/SQL Insert Error when using Named Placeholders
  18. Create PHP array from MySQL column
  19. Save array in mysql database
  20. MySQL PHP – SELECT WHERE id = array()? [duplicate]
  21. How to get ID of the last updated row in MySQL?
  22. When should I use MySQLi instead of MySQL?
  23. How can I properly use a PDO object for a parameterized SELECT query
  24. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  25. Convert array of single-element arrays to one a dimensional array
  26. PHP – Merging two arrays into one array (also Remove Duplicates)
  27. Updating from MYSQL to MYSQLI [duplicate]
  28. Merge row data from multiple arrays
  29. Group rows in a 2d array and count number of rows in each respective group
  30. Which is faster in PHP, $array[] = $value or array_push($array, $value)?

Leave a Comment