Please help constructing this while loop.

[habbardone]

My database table has got screwed up

It is a table which is automatically updated every day.
I have six months of messed up data !!!

So I am trying to write this script to correct things:

Now I have the three functions in a loop:

$the_day = 171; 
$the_date = 1276948800; 
$the_row_no = 410631; 

LOOP start { 

  $row = "UPDATE main SET mrecurr = 'D' WHERE mday_no = '$mday' AND  is_numeric mid > '9999' "; 
  $result = mysql_query($row) or  die("could not MARK FOR DELETION - non-numeric". mysql_error());   

  // Here I need to capture the  mprod_id from the last updated record  

  // from above and put it in this variable: 
  $new_row_no =  

  $sql_adm = "UPDATE main SET mrecurr = 'D' WHERE mday_no = '$the_day' AND  mprod_id > '$the_row_n' "; 
  $result = mysql_query($row) or  die("could not MARK FOR DELETION - duplicates". mysql_error()); 

  $sql_adm = "UPDATE main SET m_date = '$the_date' WHERE mday_no = '$mday' "; 
  $result = mysql_query($row) or  die("could not UPDATE timestamp". mysql_error()); 

  $the_day = $the_day+1; 
  $the_date = $the_date+86400; 
  $the_row_no = $new_row_no; 
  }  

But I still need some help

First - finding those non-numeric
Second - stepping through with the loop - should I use a while loop ?
Third - capturing the data for $new_row_no from the update.

Thanks for any advice on these.

[dagon]

if the mprod_id is AUTO_INCREMENT, then you can use mysql_insert_id()

[habbardone]

Hi,

Thanks for your reply.

The field is an auto_increment BUT it was auto_incremented months ago.
As I am not INSERTING but updating, I don't think this would apply.

Again - thanks,

can you assist on my other problems ?

thanks

[DaveRandom]

What I would say you want to do is start with a SELECT that gets all the records you want to change, then loop through them with...

while ($row=@mysql_fetch_array($result)) {
  // Do stuff here
}

...so you already have data you want to put into $new_row_no at the beginning of each iteration.

I think the is_numeric clause should be IsNumeric() in MySQL, but I have never used it so I can't really help you there.

If you put exactly what is wrong about the rows, and some more info about the table structure on here I may be able to help.

[habbardone]

Hi,

Thanks for your reply,

I changed my code a bit to what you suggested:

$the_day = 171;
$the_date = 1276948800;
$the_row_no = 410631;



$row = "SELECT main WHERE mday_no = '$the_day' AND  mid = '0' ";

while ($row=@mysql_fetch_array($result)) { 
  $row_upd = "UPDATE main SET mrecurr = 'D' ";
  $result = mysql_query($row_upd) or  die("could not MARK FOR DELETION - non-numeric". mysql_error());
}

Don't I need to specify what $result is in the
while ($row=@mysql_fetch_array($result)) { line ?

Also this will step through all of the "171" but will not then advance to 172

[DaveRandom]

$start_day = 171; 
$end_day = 181;
$the_date = 1276948800; 
$the_row_no = 410631; 

for ($the_day = $start_day; $the_day <= $end_day; $the_day++) {

  $result = "SELECT * FROM `main` WHERE `mday_no` = '$the_day' AND  mid = '0' "; 

  while ($row=@mysql_fetch_array($result)) { 
    $row_upd = "UPDATE main SET mrecurr = 'D' "; 
    $result = mysql_query($row_upd) or  die("could not MARK FOR DELETION - non-numeric". mysql_error()); 
  }

} 

...will step through all days from $start_day to $end_day. $result comes from the SELECT query.

You still need a WHERE clause in the UPDATE query though... Is main your table name and mday_no your primary key?

How many rows are added per day?

[habbardone]

OK - I decided to change the type of the data in my table
from varchar to int(10)

... so the alphcharacters have all gone and they are now zero.

So that should make the coding easier.

Just noticed some error in my code:

I have repaired them - but I still need some help with the LOOP structure.

$the_day = 171; 
$the_date = 1276948800; 
$the_row_no = 410631; 

LOOP start {  

  $row = "UPDATE main SET mrecurr = 'D' WHERE mday_no = '$the_day' AND  mid = '0' ";  

  $result = mysql_query($row) or  die("could not MARK FOR DELETION - non-numeric". mysql_error());     

  // Here I need to capture the  mprod_id from the [B]last updated record [/B]   

  // from above and put it in this variable:  

  $new_row_no =    

  $row = "UPDATE main SET mrecurr = 'D' WHERE mday_no = '$the_day' AND  mprod_id > '$the_row_n' ";  

  $result = mysql_query($row) or  die("could not MARK FOR DELETION - duplicates". mysql_error());  

  $row = "UPDATE main SET m_date = '$the_date' WHERE mday_no = '$the_day' ";  

  $result = mysql_query($row) or  die("could not UPDATE timestamp". mysql_error());  

  $the_day = $the_day+1;  

  $the_date = $the_date+86400;  

  $the_row_no = $new_row_no;  

 } // End LOOP  

Here are a couple of images from my table that help to explain my
table structure and the problem I am trying to solve.

http://www.expert-world.com/EasyCapture1.jpg

http://www.expert-world.com/EasyCapture2.jpg

Thanks

[DaveRandom]

WOW, that is some complicated work that needs doing, you have a lot of conditions that need to be met before deciding what to do with any given row, from what I can see...

Which column holds the primary key?

I think you may have to step through all records from a given point and SELECT them individually, and decide what to do with each one. I suggest you make a copy of the existing table before you let a script loose on it - you don't want to make the problem even worse!

I don't have time to look through this at the moment, I will try and have a look later tonight (UK time).

[habbardone]

Hi Dave,

Yes it is a bit of a mess!

The table is automatically updated every day.
I have six months of messed up data !!!

Actually I have neglected to check it and I have to wade through it deleting and changing data.

I have already spent a few hours doing this - and thought it best to try and write a script to do it.

( I have got to day 170, and I need to go up 380 ! )

This is what I have been doing "manually":

First delete duplicates that appear by using the sequential mprod_id number:

$sql_adm = "DELETE FROM main WHERE mday_no = '169' AND  mprod_id > '407148' ";

$result_adm = mysql_query($sql_adm) or  die("could not DELETE FROM main ". mysql_error());  

Then second correct the time stamp that was wriiten as zero for the next day:

$sql_adm = "UPDATE main SET m_date = '1276948800' WHERE mday_no = '170' ";

$result_adm = mysql_query($sql_adm) or  die("could not DELETE FROM main ". mysql_error());  

Finally third, I manually delete about five records which have characters in their id munber field

Well as you know - third is now donealt with as I have converted that field to int(10) so now those records have zero in them

Those three operations fix ONLY one day, so I move onto the next day by locating the next mprod_id where the day number changes.
(ın this cae it changes from 169 to 170 )

As there about 1700 entries a day - I look at the database to find the number ,then manually update my script, upload it to my server and rerun it. For each day.

Very tedious and I now see it will take me 10 - 12 hours to complete.

The main index is on the auto-insert field named "mprod_id"
That is what I use to find positions in the table.

I am going to work on a copy of the table - I'll do that now 🙂

Now I just realized that my loop can be a simple while loop ( I think )

ALSO,
I think I can find the last "Mark for Deletion" row by SELECTING and LIMITING

Like this:

$the_day = 171; 
$the_date = 1276948800; 
$the_row_no = 410631; 

While ($the_day < 381 ) {   

  $row = "UPDATE main SET mrecurr = 'D' WHERE mday_no = '$the_day' AND  mid = '0' ";   

  $result = mysql_query($row) or  die("could not MARK FOR DELETION - non-numeric". mysql_error());      

// Now find the last deleted item 

  $deleted = "SELECT * FROM `main` WHERE `mday_no` = '$the_day' AND  mrecurr = 'D' ORDER BY mprod_id DESC limit 1";  ";
  $result = mysql_query($deleted) or  die("could not FIND DELETIONS". mysql_error());  

  $row = mysql_fetch_assoc($result);
  $new_row_no =  = $row['mprod_id'];


  $row = "UPDATE main SET mrecurr = 'D' WHERE mday_no = '$the_day' AND  mprod_id > '$the_row_n' ";   

  $result = mysql_query($row) or  die("could not MARK FOR DELETION - duplicates". mysql_error());   

  $row = "UPDATE main SET m_date = '$the_date' WHERE mday_no = '$the_day' ";   

  $result = mysql_query($row) or  die("could not UPDATE timestamp". mysql_error());   

  $the_day = $the_day+1;   

  $the_date = $the_date+86400;   

  $the_row_no = $new_row_no;   

} // End While

What do you think ?

Does it look like it will do what I have been doing manually ?

I am not sure about the DESC with the LİMİT 1, will that give me the last row, i.e. the one with the highest mprod_id that has just been marked for deletion ?

Thanks.