Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts

    How do I code this loop ?

    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
    Last edited by jeddi; 01-21-2011 at 06:19 PM.
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #2
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts
    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.
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #3
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Please - if someone can help me
    out I would really appreciate your input.

    Thanks
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #4
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts
    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
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #5
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts
    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
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •