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;

@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
– Ivan, Commented Jul 7, 2016 at 19:43
@u_mulder I need to keep mininum count of requests to mysql database — Ivan
– Ivan, Commented Jul 7, 2016 at 19:45

Martin S. · Accepted Answer · 2016-07-08 06:53:08Z

1

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;
?>

answered Jul 8, 2016 at 6:53

Martin S.

2561 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin S. · Accepted Answer · 2016-07-07 21:50:18Z

1

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;
?>

answered Jul 7, 2016 at 21:50

Martin S.

2561 silver badge10 bronze badges

1 Comment

Iceman Over a year ago

I think the author wants minimum number of queries. this would require x queries and connections for x length arrays.

Iceman · Accepted Answer · 2016-07-07 22:00:37Z

1

$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.

answered Jul 7, 2016 at 22:00

Iceman

6,1652 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to update a row in sql with loop?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related