Update multiple rows with multiple values in php

Question

I am trying to update multiple values from same column with an array of multiple values, but I am not getting exactly why the values from the same column are updated with only one value from the array.

PHP

$subscribers = $db->query('SELECT * FROM subscribers');
while ($subscriber = $subscribers->fetch_object()) {
  $email = $subscriber->email;
}
$pagerank_update = $db->query('SELECT * FROM pagerank_update');
$url_pr = array('1','2','3','4');
foreach ($url_pr as $url_pr_value) {
 while ($website = $pagerank_update->fetch_object()) {
  $url = $website->URL; //domain1.com...
  $pagerank = $website->CURRENT_PR; // 0
  $date = $website->PR_TODAY; // 0
  $id = $website->ID;
  //$url_pr = getpr($url);
    //var_dump($url);
        //if ($url_pr_value !== $pagerank) {
     $db->query("UPDATE pagerank_update SET PR_TODAY = '$url_pr_value' WHERE URL = '$url'");
     $var = "UPDATE pagerank_update SET PR_TODAY = '$url_pr_value' WHERE URL = '$url'";
     var_dump($var);
    //} else {}
 }
}

The var_dump looks in English like this:

UPDATE pagerank_update SET PR_TODAY = '1' WHERE URL = 'http://domain1.com'
UPDATE pagerank_update SET PR_TODAY = '1' WHERE URL = 'http://domain2.com'
UPDATE pagerank_update SET PR_TODAY = '1' WHERE URL = 'http://domain3.com'
UPDATE pagerank_update SET PR_TODAY = '1' WHERE URL = 'http://domain4.com'

EXPECTED OUTPUT would be

UPDATE pagerank_update SET PR_TODAY = '1' WHERE URL = 'http://domain1.com'
UPDATE pagerank_update SET PR_TODAY = '2' WHERE URL = 'http://domain2.com'
UPDATE pagerank_update SET PR_TODAY = '3' WHERE URL = 'http://domain3.com'
UPDATE pagerank_update SET PR_TODAY = '4' WHERE URL = 'http://domain4.com'

Where am I mistaking?

its because u have two loops one outside the data fetch so if there are 4 records being selected in data fetch then the loop will have SET PR_TODAY = 1 one in the first loop. — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 6, 2014 at 10:14
do one thing instead of outer loop of array declare a var as $url_pr_value = 1; then in the data fetch loop use the update command and then do $url_pr_value++ this should solve the problem. — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 6, 2014 at 10:18
The fact is that I don't want to increment the $url_pr_value++, that is for testing purposes, I am trying to assign different values to each urls. Function getpr() that is commented that is the function that should get different value for each url. The script is about getting the pagerank of each url and updating it as follows. — Adrian
– Adrian, Commented Apr 6, 2014 at 10:20

Andy Librian · Accepted Answer · 2014-04-06 10:23:10Z

1

try this:

$subscribers = $db->query('SELECT * FROM subscribers');
while ($subscriber = $subscribers->fetch_object()) {
  $email = $subscriber->email;
}
$pagerank_update = $db->query('SELECT * FROM pagerank_update');
$url_pr = array('1','2','3','4');
$i = 0;

while ($website = $pagerank_update->fetch_object()) {
  $url_pr_value = $url_pr[$i];
  $url = $website->URL; //domain1.com...
  $pagerank = $website->CURRENT_PR; // 0
  $date = $website->PR_TODAY; // 0
  $id = $website->ID;
  //$url_pr = getpr($url);
    //var_dump($url);
        //if ($url_pr_value !== $pagerank) {
     $db->query("UPDATE pagerank_update SET PR_TODAY = '$url_pr_value' WHERE URL = '$url'");
     $var = "UPDATE pagerank_update SET PR_TODAY = '$url_pr_value' WHERE URL = '$url'";
     var_dump($var);
    //} else {}
     $i++;
}

answered Apr 6, 2014 at 10:23

Andy Librian

9135 silver badges12 bronze badges

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

1 Comment

Adrian Over a year ago

Exactly what I needed. Now I see where am I mistaking., I did not even needed a foreach.

Collectives™ on Stack Overflow

Update multiple rows with multiple values in php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related