How do I code this loop ?

[jeddi]

Hi

I am trying to write a loop code to correct my database.

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 !!!

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 think I should 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 am doing "manually":

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

PHP Code:

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

PHP Code:

$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

** Update ** 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.

SO ...

Here are some screen shots from my database table:

They help show my table structure and the problem

[IMG]http://www.expert-world.com/EasyCapture1.jpg[/IMG]


[IMG]http://www.expert-world.com/EasyCapture2.jpg[/IMG]

** Update ** This is my latest code:

PHP Code:

$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 


Can anyone help ?

Thanks

[jeddi]

Hello again,

I have moved forward a little:

Now I have the three functions in a loop:

PHP Code:

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

[jeddi]

Please - if someone can help me
out I would really appreciate your input.

Thanks

[jeddi]

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.

PHP Code:


$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 last updated record   
  // 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 


Please help me code this properly.

Thanks

[jeddi]

Just worked on some screen shots.

They help show my table structure and the problem

First screen shot:



Second screen shot:



Hope this helps

Thanks again for any help