How to update a row in sql with loop?

Question

I'm trying to figure how to call a query once.

I have 6 different variables for images, title and desc.

In this code, I need to know how to loop for id from 0 to 6.

    $date = new DateTime("NOW");

    $image1 = 'SSSS';
    $title1 = 'AAAA';
    $desc1 =  'BBBB';

    $image2 = 'RRRR';
    $title2 = 'GGGG';
    $desc2 =  'VVVV';

    ///  4 vars later....

    $id = 6;


    $get = $this->db->queryRow("UPDATE `featured` SET `image` = '{$image.$id}', `title` = '{$title.$id}', `desc` = '{$desc.$id}', `date` = '{$date->format('Y-m-d H:i:s')}' WHERE id = '{$id}'");
    return(object) $get;

This should help: http://stackoverflow.com/questions/9257505/dynamic-variable-names-in-php — ckimbrell, Jul 07 '16 at 19:35
@ckimbrell But I don't want to call query six times due to performance reasons, it's possible to update 6 rows once in some sql command. — Ivan, Jul 07 '16 at 19:43
@u_mulder I need to keep mininum count of requests to mysql database — Ivan, Jul 07 '16 at 19:45
@Iceman PHP Drivers for MySQL (mysqli, ext/mysqli, PDO_MYSQL, PHP_MYSQLND) — Ivan, Jul 07 '16 at 21:43
@so `$this->db->queryRow` is actually calling `mysqli_query`.Am I right. Coz, then you can run multiple queries in one connection — Iceman, Jul 07 '16 at 21:45
@Iceman queryRow(); = returns a single complete record based on the query entered. — Ivan, Jul 07 '16 at 21:54
What you can do is concate all the queries together and query after the loop is over. ie. a single string with `;` seperated many queries. will be done in one connection — Iceman, Jul 07 '16 at 21:56
@Ivan check my answer. It should execute all the queries in one single db connection. — Iceman, Jul 07 '16 at 22:02

score 1 · Answer 1 · answered Jul 07 '16 at 21:50

I have tried to use a simple $query model and it works fine. Create a valid Query string to pass to the db

<?php
$query = "UPDATE `featured` SET `image` = '".$image.$id."', `title` = ".$title.$id."', `desc` = '".$desc.$id}."', `date` = '".$date->format('Y-m-d H:i:s')."' WHERE id = '".$id."';"

$result=$mysqli->query($query);
  // Verify results
if(!$result) {
  $ErrMessage  =  "ErrSqlQuery:" . $mysqli->error . "\n";
  $mysqli->close();
  die($ErrMessage);        
}

you can check also the result by

echo  $mysqli->affected_rows;
?>

I think the author wants minimum number of queries. this would require `x` queries and connections for `x` length arrays. — Iceman, Jul 07 '16 at 21:58

score 1 · Answer 2 · answered Jul 07 '16 at 22:00

$query_build = "";
foreach($arr as $$image){
    $query_build .= "UPDATE `featured` SET `image` = '{$image.$id}', `title` = '{$title.$id}', `desc` = '{$desc.$id}', `date` = '{$date->format('Y-m-d H:i:s')}' WHERE id = '{$id}';";
}
$get = $this->db->queryRow($query_build);

Accumulate all the queries and execute all at once.

score 1 · Accepted Answer · answered Jul 08 '16 at 06:53

To build a collection of Querys use the multi_query function. Loop to build your Query string to pass to the db and concatenated by a semicolon.

<?php
for($i=0;$i <= $maxquerys;$i++){
  $query = "UPDATE `featured` SET `image` = '".$image.$id."', `title` =     ".$title.$id."', `desc` = '".$desc.$id."', `date` = '".$date->format('Y-m-d H:i:s')."' WHERE id = '".$id."';"
}

/* execute multi query */
if ($mysqli->multi_query($query)) {

  while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();

you may check also the result by

echo  $mysqli->affected_rows;
?>

How to update a row in sql with loop?

3 Answers3