Viewed   97 times

I have an array full of random content item ids. I need to run a mysql query (id in the array goes in the WHERE clause), using each ID that's in the array, in the order that they appear in the said array. How would I do this?

This will be an UPDATE query, for each individual ID in the array.



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]);
    echo "Updated record ID: $idn";

// done

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

// done
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.

Tuesday, November 22, 2022

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

This worked like a charm!

    $db->query('set profiling=1'); //optional if profiling is already enabled
    $stmt = $db->query('show profiles');
    $db->query('set profiling=0'); //optional as well

    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $errmsg = $stmt->errorInfo()[2]; //Output the error message 

UPDATE (The following now works on innodb on my present setup)

$db->query('set profiling=1'); //optional if profiling is already enabled
$res = $db->query('show profiles');
$records = $res->fetchAll(PDO::FETCH_ASSOC);
$duration = $records[0]['Duration'];  // get the first record [0] and the Duration column ['Duration'] from the first record

Result of (show profiles) from phpmyadmin.

Query_ID    Duration    Query   
1           0.00010575  SELECT DATABASE()

Getting the actual (absolute) execution time of the last query in PHP (excluding network latency etc)

Saturday, November 19, 2022

When you're gathering the file information you're overwriting $imagename on every loop so it will be assigned to the last one. Try attaching it to the $files variable (hopefully this doesn't mess with the upload class you're using).

foreach ($l as $i => $v)
    if (!array_key_exists($i, $files))
    $files[$i] = array();
    $files[$i][$k] = $v;
    $files[$i]['imagename'] = $_POST['keyword'][$i];

Then update your $sql string to reference that

$sql = 'INSERT INTO cover (id, img, keyword, category_name, cat_id) 
     VALUES ("", "'.$this_upload['image'].'", "'.$file['imagename'].'", 
         "'.$category_name.'", "'.$category.'")';
Wednesday, November 16, 2022

As you know, when you define a method, you can use the * to turn a list of arguments into an array. Similarly when you call a method you can use the * to turn an array into a list of arguments. So in your example you can just do:*a)
Monday, August 29, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :