mysql_query not processing

My problem is in the last line of code I’ve attached below. The variable is constructed validly and I am able to copy/paste it into my MySQL admin interface and run it without an error. However the mysql_query command at the end of this fails and I am not exactly sure why. Just out of curiosity I built the mysql_query line above it and when I process this script THAT line runs by the one right below doesn’t. I’m hoping someone here will spot what I am overlooking as I can’t seem to figure out exactly where the error resides.

Thanks in advance!

Greg


Snippet from my script

// We need to purge the old table data first
$CatNavDB = ‘TRUNCATE TABLE tbl_catnav;’;
// Now assemble the insert query to repopulate the table
$CatNavDB .= ‘INSERT INTO tbl_catnav (lastcatid, category_set) VALUES ‘;
while($row=mysql_fetch_array($GetMainCat, MYSQL_ASSOC))
{
if ($BaseCat != ($row[‘root_product_id’])) {
$Output = ($row[‘root_category_name’]);
$CatNavDB .= “(” . ($row[‘root_product_id’]) . “, '” . $Output . "’),";
}
if (($row[‘down1_product_id’] != $Lvl2Cat) AND ($row[‘down2_product_id’] != ‘’)) {
$Output = ($row[‘root_category_name’]) . " - " . ($row[‘down1_category_name’]);
$CatNavDB .= “(” . ($row[‘down1_product_id’]) . “, '” . $Output . "’),";
}
if (is_null($row[‘down2_product_id’])) {
$Output = ($row[‘root_category_name’]) . " - " . ($row[‘down1_category_name’]);
$CatNavDB .= “(” . ($row[‘down1_product_id’]) . “, '” . $Output . “'),”;
} else {
$Output = ($row[‘root_category_name’]) . " - " . ($row[‘down1_category_name’]) . " - " . ($row[‘down2_category_name’]);
$CatNavDB .= “(” . ($row[‘down2_product_id’]) . “, '” . $Output . “'),”;
}
$BaseCat = ($row[‘root_product_id’]);
$Lvl2Cat = ($row[‘down1_product_id’]);
}
// Add the closing ; to the insert query
$CatNavDB .= “(999999,‘RemoveThis’);”;
$CatNavDB .= “DELETE FROM tbl_catnav WHERE lastcatid = 999999;”;
// And then execute the query on the DB
$Test = (mysql_query(‘INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (888888,\‘Test value\’);’));
$UpdateCatNavDB = mysql_query($CatNavDB);

Think I got it narrowed down to something in this query, but I can’t see the forest for the trees I guess!

$CatNavDB .= ‘INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (1, “Accessories”);’;

Try


$UpdateCatNavDB = mysql_query($CatNavDB) or die('mysql error ' . mysql_error() . ' in query ' . $CatNavDB);

and see what error you got, and what the query actually looks like.

I had already kind of gotten that result, here is the feedback:

mysql 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 ‘INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (1, “Accessories”)’ at line 1 in query

Which (at least as far as I am seeing) matches the query I posted above. I’m not seeing what the problem is with this format, but I am sure that is just due to the fact that I am not experienced enough and it’s probably some lack of a quote or semicolon that is causing this problem.

Greg

There was a type in the code I posted (I corrected it), I think that’s why the message you got didn’t give you the query at the end.
Please try again with the corrected version:

$UpdateCatNavDB = mysql_query($CatNavDB) or die('mysql error ' . mysql_error() . ' in query ' . $CatNavDB);  

The important thing is the exact value of $CatNavDB

I’ve posted the reply below. Now if I take the query returned starting at the TRUNCATED command (after the at line 1 in query portion. And execute it directly against the database using my MySQL admin tools it operates without an error and inputs the data I am expecting.

I’ve reset so this is the full returned value:

mysql 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 ‘INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (1, ‘Accessories’);INSER’ at line 1 in query TRUNCATE TABLE tbl_catnav;INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (1, ‘Accessories’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (5, ‘Accessories - Air Grills’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (6, ‘Accessories - Ash Trays’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (7, ‘Accessories - Coat Hooks’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (8, ‘Accessories - Cup Holders’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (9, ‘Accessories - Information Signs’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (10, ‘Accessories - Switch Modules’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (2, ‘Air Valve’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (11, ‘Air Valve - Disc Type’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (15, ‘Air Valve - Disc Type-.88 ID’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (16, ‘Air Valve - Disc Type-1.16 ID’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (17, ‘Air Valve - Disc Type-1.25 ID’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (18, ‘Air Valve - Disc Type-1.50 ID’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (19, ‘Air Valve - Disc Type-1.63 ID’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (12, ‘Air Valve - Louver Type’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (13, ‘Air Valve - Poppet Type’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (20, ‘Air Valve - Poppet Type-High Flow’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (21, ‘Air Valve - Poppet Type-Low Flow’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (3, ‘Lights’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (14, ‘Lights - Halogen’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (4, ‘Plenum Caps’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (22, ‘Plenum Caps - .75 OD Hose Port’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (23, ‘Plenum Caps - 1.00 OD Hose Port’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (24, ‘Plenum Caps - 1.25 OD Hose Port’);INSERT INTO tbl_catnav (lastcatid, category_set) VALUES (25, ‘Plenum Caps - 1.75 OD Hose Port’);

As far as I know, you can’t run multiple sql statements with mysql_query. Each statement must be a separate mysql_query function call.

That was it EXACTLY! Don’t remember ever seeing it mentioned anywhere (or I wasn’t paying attention when it did). Thank you so much! Now that makes perfect sense. Couldn’t understand why I could run the command by hand but not through my PHP script. Also cleaned up some of the other ridiculousness in my original script and it is doing just what I need.

Greg