Updating 1000’s records

[Jayram121]

I am trying to update my table using while loop, unfortunately it is only updating the first record. Why can I not update the whole 27000 records?

Thank you.

<?php

 $sql = "SELECT * FROM spammers ORDER BY spamID;";
 $res = mysql_query($sql);
 if(!$res) {
  trigger_error("Could not connect to the database!\n <br/>MySQL Error: " . mysqli_connect_error());
 }

 while ($rows=mysql_fetch_assoc($res)) {
  
    
    $sql = "UPDATE spammers 
      SET abc='". 5252 ."'
      WHERE spamID ='".$rows['spamID']."';";
    $res = mysql_query($sql);
    if(!$res) {
     die(" Could not query the database: <br/>". mysql_error() );
    }
 }

?>

[sasa]

He

change name of variable $res in this part of code

while ($rows=mysql_fetch_assoc($res)) {
  
    
    $sql = "UPDATE spammers 
      SET abc='". 5252 ."'
      WHERE spamID ='".$rows['spamID']."';";
    $res = mysql_query($sql); // change to something diferent
    if(!$res) { // and in this line 
     die(" Could not query the database: <br/>". mysql_error() );
    }

[requinix]

You're overwriting $res inside the loop.

 

You know you can just update everything in one statement?

UPDATE spammers SET abc=5252

If you've hidden something important and can't do that (like you don't actually want to update all of them) then I guarantee you there's a much better way of doing it than the horribly inefficient SELECT/UPDATE loop you have now.

[Jayram121]

 This the code.

 $sql = "SELECT * FROM spammers ORDER BY spamID;";
 $res = mysql_query($sql);
 if(!$res) {
  trigger_error("Could not connect to the database!\n <br/>MySQL Error: " . mysqli_connect_error());
 }

 while ($rows=mysql_fetch_assoc($res)) {
  
  if(!filter_var( $rows['email'], FILTER_VALIDATE_EMAIL)) {
   if(filter_var( $rows['countery'], FILTER_VALIDATE_EMAIL)) {
  
    
    $sql = "UPDATE spammers 
      SET email='".$rows['countery'] ."'
      WHERE spamID ='".$rows['spamID']."';";
    $res = mysql_query($sql);
    if(!$res) {
     die(" Could not query the database: <br/>". mysql_error() );
    }
    
   }
  }
 }

 

requinix

can you advice me what is the better way of doing it than the horribly inefficient SELECT/UPDATE loop .          

Thank u

[requinix]

If you're alright with a simpler email validity check, maybe like "*@*.*", then

UPDATE spammers SET email = countery WHERE email NOT REGEXP ".+@.+\..+" AND countery REGEXP ".+@.+\..+"

[Jayram121]

Thank you requinix.

 

also can you please tell me what is the better way of doing SELECT/UPDATE loop .

[requinix]

I just did.