...

View Full Version : Nearly there - just need a little help with arrays



jeddi
01-13-2010, 04:51 PM
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:


$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 == 0 ) { // 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.

tomws
01-13-2010, 06:05 PM
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.

jeddi
01-13-2010, 06:52 PM
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.

jeddi
01-13-2010, 07:11 PM
OK I have added some comments so that I can see the contents of the arrays a bit better.


fixed it !!!

tomws
01-13-2010, 07:36 PM
Are you attempting to insert each item in $updt_new and each item in $updt_val into the db? If so, yes, implode (http://php.net/implode) should be what you're looking for. Be sure to wrap the value sets together and glue them correctly.

jeddi
01-13-2010, 07:48 PM
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 !

shadowmaniac
01-13-2010, 07:51 PM
I have been working on updating one table from another



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

tomws
01-13-2010, 08:00 PM
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.

jeddi
01-14-2010, 06:29 AM
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:

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



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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum