SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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);

  2. #2
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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");';

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Try
    PHP Code:
    $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.
    Last edited by guido2004; Mar 30, 2012 at 01:52. Reason: corrected typo

  4. #4
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by GHicks View Post
    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:
    PHP Code:
    $UpdateCatNavDB mysql_query($CatNavDB) or die('mysql error ' mysql_error() . ' in query ' $CatNavDB); 
    The important thing is the exact value of $CatNavDB

  6. #6
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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');

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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.

  8. #8
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

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