Jump to content

Updating 1000’s records


Jayram121

Recommended Posts

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() );
    }
 }

?>

Link to comment
Share on other sites

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() );
    }
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

 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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.