Page 1 of 2

What am i missing in this MySQL statement?

Posted: Wed Nov 09, 2005 11:25 am
by vincenzobar
Hi again!!!
I had to change routes on my previous questions and i almost have it working except.... My INSERT statement will not work for the archiving table

All rows match up except i did not include the auto increment of course.. but I still get the error...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
here is the code... What am i doing wrong???

Code: Select all

if(!empty($num_rows_rp)){

//fetch all the DB IDs in an array to reduce tons of DB query...  
	$idrs = mysql_query("SELECT productid FROM CB_rateplan;") or die(mysql_error()); 
	

	while($row = mysql_fetch_row($idrs)){ 
	$DB_IDList[] = $row[0]; 
	$Update_Query = "UPDATE CB_rateplan SET <1> WHERE productid=<2>";
	}
	//match and update/add as needed... trifle now  
		foreach($XML_IDList as $p_id) { 
			//print "p_id; ".$p_id. "<BR>";
	$all_rows = mysql_query('SELECT * FROM CB_rateplan WHERE productid = '.$p_id.';');// get rows from production table
	$archive = "INSERT INTO archive_rateplan VALUES $f_move ;";//insert matching rows into archive table
	$delete = 'DELETE FROM CB_rateplan WHERE productid = '.$p_id.';';//Delete all matching products after they have been archived
	
	
			$move = array();
			if(in_array($p_id, $DB_IDList)) { 
				$db_row = mysql_fetch_row($all_rows);
					$move[] = "'".implode("', '", $db_row)."'";
					$f_move = "(".implode(", ", $move). ")<br>";
					print $f_move;
				mysql_query($archive) or die(mysql_error());
				//mysql_query($delete);//commented out until Archive  works
				//mysql_query($add_rp);//commented out until Archive  works
			}
		
		}		
		
	}else { // Else to First If
 		mysql_query($add_rp); 
  		echo $add_rp;
  
  
}//FIRST IF
OUT PUT
connected
('1', 'Greater Freedom 700 - 2YR', '700', '39.9900', '0.4000', '20.0000', '0.9900', 'up to $.99/min', '24', '$200.00', '', '', '1000', '', '1000', '', '0', '$0.00/min within home calling area ($0.25/min outside home calling area)', '', '', '', '', '53037', 'alltel wireless', 'images/carriers/', 'alltel_logo.gif', 'false', '13283')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Posted: Wed Nov 09, 2005 11:36 am
by Grim...
<edit>What goat says</edit>

Posted: Wed Nov 09, 2005 11:37 am
by Luke
Why are your sql queries above the variables that are inside of them. You're putting variables in your query before they are defined.

Code: Select all

$archive = "INSERT INTO archive_rateplan VALUES $f_move <<-- Not Defined ;";//insert matching rows into archive table

Posted: Wed Nov 09, 2005 11:39 am
by vincenzobar
no that isn't it... i use it everywhere and the others work fine.

echo $archive; ????

Posted: Wed Nov 09, 2005 11:40 am
by Grim...

Code: Select all

$archive = "INSERT INTO archive_rateplan VALUES $f_move ;";//insert matching rows into archive table
^^
$archive

Echo that in the same place as you are currently echoing $f_move .

Posted: Wed Nov 09, 2005 11:43 am
by Luke

Code: Select all

$archive = "INSERT INTO archive_rateplan VALUES $f_move ;";//insert matching rows into archive table
    $delete = 'DELETE FROM CB_rateplan WHERE productid = '.$p_id.';';//Delete all matching products after they have been archived
    
    
            $move = array();
            if(in_array($p_id, $DB_IDList)) {
                $db_row = mysql_fetch_row($all_rows);
                    $move[] = "'".implode("', '", $db_row)."'";
                    $f_move = "(".implode(", ", $move). ")<br>";
                    print $f_move;
                mysql_query($archive) or die(mysql_error());
if you echo out the archive variable it's gonna look like this...
INSERT INTO archive_rateplan VALUES
Because $f_move isn't defined. It's going to print out nothing.

Posted: Wed Nov 09, 2005 11:43 am
by vincenzobar
because the call is below the defined variable

that won't work????

Posted: Wed Nov 09, 2005 11:44 am
by vincenzobar
I see!!!

Posted: Wed Nov 09, 2005 11:44 am
by Luke
A variable acts like whatever is stored in it. If nothing is stored in it, nothing will be stored in the new variable.

Posted: Wed Nov 09, 2005 11:50 am
by vincenzobar
still have a problem..

echo $archive = INSERT INTO archive_rateplan VALUES ('1', 'Greater Freedom 700 - 2YR', '700', '39.9900', '0.4000', '20.0000', '0.9900', 'up to $.99/min', '24', '$200.00', '', '', '1000', '', '1000', '', '0', '$0.00/min within home calling area ($0.25/min outside home calling area)', '', '', '', '', '53037', 'alltel wireless', 'images/carriers/', 'alltel_logo.gif', 'false', '13283')

but i am now getting

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
' at line 1

Code: Select all

$move = array();
			if(in_array($p_id, $DB_IDList)) { 
				$db_row = mysql_fetch_row($all_rows);
					$move[] = "'".implode("', '", $db_row)."'";
					$f_move = "(".implode(", ", $move). ")<br>";
					print $f_move;
				$archive = "INSERT INTO archive_rateplan VALUES ".$f_move.""; // moved
				echo $archive;
				mysql_query($archive) or die(mysql_error());
				//mysql_query($delete);
				//mysql_query($add_rp);

Posted: Wed Nov 09, 2005 11:55 am
by Luke
I think you have to list the field names before you list the values...

Code: Select all

INSERT INTO `seminarschedule` ( `sem_id` , `sem_title` , `sem_month` , `sem_date` , `sem_day` , `sem_time_start` , `sem_time_end` , `sem_ampm` , `sem_status` )
VALUES (
'', '', '', '', '', '', '', '', ''
);

Posted: Wed Nov 09, 2005 11:56 am
by Grim...
You don't need to state the field names as long as you use the right amount of values (in the right order, obviously).

Posted: Wed Nov 09, 2005 11:57 am
by vincenzobar
no INSERT INTO "nothing" is same as all

Its the implodes!!!!

not formated for SQL insert!!! see <br> should be ", "


lets see!!!

Posted: Wed Nov 09, 2005 12:05 pm
by vincenzobar
Now i got this

Column count doesn't match value count at row 1

the archive table is a copy of CB_rateplan but i added a ar_id that auto increments.

Is this causing the problem? do i need to add another field to the beginning to fake the A Inc?

Posted: Wed Nov 09, 2005 12:07 pm
by Luke
vincenzobar wrote:Now i got this

Column count doesn't match value count at row 1

the archive table is a copy of CB_rateplan but i added a ar_id that auto increments.

Is this causing the problem? do i need to add another field to the beginning to fake the A Inc?
Huh?