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 9 of 9
  1. #1
    Senior Coder
    Join Date
    May 2006
    Posts
    1,673
    Thanks
    28
    Thanked 4 Times in 4 Posts

    Nearly there - just need a little help with arrays

    Hi,

    I have been working on updating one table from another and
    think that I am nearly there but I don't think I am using the arrays
    quite right.

    The code basically works through the update table comparing it to the
    main table, finds the new rows and puts them into an array, fins any rows with changed data and puts those rows into and array.

    When finished it should update the main table with the two rows in the two arrays. That is the bit that does not work.

    Here is my code:
    PHP Code:
    $dayno 12;

    $updt_new = array();
    $updt_val = array();
    $updt_rept = array();

    $ctr 1;

    $sql "SELECT * FROM cb_update WHERE day_no = '$dayno' ORDER BY id";
    $result mysql_query($sql)
        or die(
    "could not OPEN CB_update."mysql_error());  

        
    $sql_ins "INSERT INTO cb_main (mcb_date, mday_no, mid, mtitle, mdescrip, mrecurr, mgrav, mearn, mpercent, mtotearn, mrebill, mrefer, mcomm ) VALUES"

    $sql_rept "INSERT INTO cb_report (rpt_date, rpt_id, rpt_data) VALUES"

    $sql_new "INSERT INTO cb_new (Ncb_date, Nday_no, Nid, Ntitle, Ndescrip, Nrecurr, Ngrav, Nearn, Npercent, Ntotearn, Nrebill, Nrefer, Ncomm ) VALUES";
                               
      while(
    $row mysql_fetch_assoc($result)){
        
    $the_id $row['id'];
                 
        
    $sql_main "SELECT * FROM cb_main WHERE mid = '$the_id' ORDER BY mid";
        
    $result_main mysql_query($sql_main)
                        or die(
    "could not FIND ID in cb_main."mysql_error()); 
            
        
    $num_main mysql_num_rows($result_main);
            
        if (
    $num_main == ) {     // If the product is NEW
        
    extract($row);
           
    $updt_new[] = "('$cb_date', '$day_no', '$id', '$title', '$descrip', '$recurr', '$grav', '$earn', '$percent', '$totearn', '$rebill', '$refer', '$comm' )";

          }
       else {    
    // If the product is NOT NEW
               
    extract($row);

         
    $row_main mysql_fetch_assoc($result_main);
         
    extract($row_main);
         
    $new_data 'n';
                 
         
    // Look for changes in permanent data
         
    $report_data "";
                 
         if( 
    $mtitle != $title) { 
           
    $new_data 'y';
         
    $report_data .= "New Title: $title<br>Previous Title: $mtitle";
         
    $updt_rept[] = "('$id', '$day_no', '$report_data')";
           } 
                     
         if( 
    $mdescrip != $descrip) { 
           
    $new_data 'y';
            
    $report_data .= "New Description: $descrip<br>Previous Description: $mdescrip";
          
    $updt_rept[] = "('$id', '$day_no', '$report_data')";
          } 
                     
         if( 
    $mrecurr != $recurr) { 
          
    $new_data 'y';
         
    $report_data .= "New Recurr Flag: $recurr<br>Previous Recurr Flag: $mrecurr";
         
    $updt_rept[] = "('$id', '$day_no', '$report_data')";
           } 
                     
         if(
    $new_data == 'y') {    
          
    $updt_val[] = "('$cb_date', '$day_no', '$id', '$title', '$descrip', '$recurr', '$grav', '$earn', '$percent', '$totearn', '$rebill', '$refer', '$comm' )";
         
        } 
    // end if
     
    // end else
     
    $ctr++; 

    // end while

     
    print_r($updt_new);
    print_r($updt_val);
    print_r($updt_rept);
      
             
    $sql_ins $sql_ins."($updt_new.$updt_val)";         
             
    $result_ins mysql_query($sql_ins) or die("could not execute INSERT to cb_main."mysql_error());          
             
             
    $sql_new $sql_new."($updt_new)"
             
    $result_new mysql_query($sql_new) or die("could not execute INSERT to cb_report."mysql_error());           

             
    $sql_rept $sql_rept."($updt_rept)";
             
    $result_rept mysql_query($sql_rept) or die("could not execute INSERT to cb_report."mysql_error()); 
    It fails with the error :
    could not execute INSERT to cb_main.Column count doesn't match value count at row 1

    Can someone help me out with using these arrays.
    Maybe I need to push them together ?

    OR - should I explode the arrays ? - please help.

    PS The contents of the arrays display what I expect, the new rows and the changes.
    Last edited by jeddi; 01-13-2010 at 05:18 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 tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    If I'm looking at it correctly, echoing the $sql_ins variable just before the attempted query should show you the problem. You'll probably need to explode (or something similar) to get into the correct structure for the query.

    BTW, that mysql error means that you have a different number of values required than are provided in the query. For example, INSERT INTO foo (foo1, foo2, foo3) VALUES ('bar') would give the same error (I think). It expects three values, but only gets one.
    Are you a Help Vampire?

  • #3
    Senior Coder
    Join Date
    May 2006
    Posts
    1,673
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Good idea.

    This is the result:

    INSERT INTO cb_main (mcb_date, mday_no, mid, mtitle, mdescrip, mrecurr, mgrav, mearn, mpercent, mtotearn, mrebill, mrefer, mcomm ) VALUES(Array.Array)

    hmm. I am not too good with arrays,

    I will try to explode them and see what happens.
    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,673
    Thanks
    28
    Thanked 4 Times in 4 Posts
    OK I have added some comments so that I can see the contents of the arrays a bit better.


    fixed it !!!
    Last edited by jeddi; 01-13-2010 at 07:48 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.

  • #5
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Are you attempting to insert each item in $updt_new and each item in $updt_val into the db? If so, yes, implode should be what you're looking for. Be sure to wrap the value sets together and glue them correctly.
    Are you a Help Vampire?

  • #6
    Senior Coder
    Join Date
    May 2006
    Posts
    1,673
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Thanks for your help.

    I have solved it by getting rid of the arrays !

    e.g. $updt_val = $updt_val."('$cb_date', '$day_no', ...

    Now its working fine !
    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.

  • #7
    Regular Coder
    Join Date
    Apr 2005
    Location
    Ohio
    Posts
    254
    Thanks
    1
    Thanked 63 Times in 63 Posts
    Quote Originally Posted by jeddi View Post
    I have been working on updating one table from another

    PHP Code:
    $sql_ins "INSERT INTO cb_main (mcb_date, mday_no, mid, mtitle, mdescrip, mrecurr, mgrav, mearn, mpercent, mtotearn, mrebill, mrefer, mcomm ) VALUES"

    $sql_rept "INSERT INTO cb_report (rpt_date, rpt_id, rpt_data) VALUES"

    $sql_new "INSERT INTO cb_new (Ncb_date, Nday_no, Nid, Ntitle, Ndescrip, Nrecurr, Ngrav, Nearn, Npercent, Ntotearn, Nrebill, Nrefer, Ncomm ) VALUES"
    You have 2 tables. One table contains existing data that's being used to display information to the users (call this 'table1') and the other table contains data that has been updated by users/staff (call this 'table2').
    You're trying to take data from table2 and insert it into table1 if you don't find a matching ID and if there's a matching ID between table1 and table2, you want to update table1 with data from table2 as well as create a report.

    Is that correct?
    I believe that's what I understood but all your SQL queries are INSERTs and there's no UPDATEs at all as per the quoted code above.

  • #8
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    I didn't take the time to look through the posted code, but are you scrubbing the data before the original insert and/or during this moving process? If you end up with apostrophes in one of those strings, your queries will go bonkers.
    Are you a Help Vampire?

  • #9
    Senior Coder
    Join Date
    May 2006
    Posts
    1,673
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Hi shadowmaniac
    Yes I am only doing inserts because I want to keep the history.

    Tom:
    Yes it DID go bonkers ( great phrase)
    until I inserted:
    PHP Code:
    $mtitle str_replace("'","",$mtitle);
    $mdescrip str_replace("'","",$mdescrip); 
    I didn't have it in my code but as soon as I saw an error I realsized
    the problem.

    Not sure if I need to scrub those fields for any other characters.

    I have not done so yet but I better run the
    data through my "safe_sql" function before putting it in my database
    in the beginning.

    PHP Code:
    // Make variable SQL safe
    function safe_sql$value )
    {
         
    $value strip_tags(trim($value));
             
        
    // Stripslashes
            
    if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())  {
            
    $value stripslashes($value);
        }
        
    // Quote if not integer
        
    if (!is_numeric($value)) {
            
    $value mysql_real_escape_string($value);
        }
        return 
    $value;
    // End of Function 
    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
    •