Results 1 to 4 of 4

Thread: UPDATE issues

  1. #1
    Join Date
    Oct 2012
    Posts
    17

    UPDATE issues

    This used to work and suddenly it stopped and gives the following error:

    Code:
    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 'WHERE stock_available.id_product = product.id_product AND product.supplier_refer' at line 1

    Code:
    $handle = fopen("GeneratedList.csv", "r+");
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
        $num = count($data);
        for ($c=0; $c < $num; $c++) {
            if ($c = 1) {
    			$supplier_reference = $data[($c-1)];
    		} 
            if ($c = 2) {
    			$quantity = $data[($c-1)];
    			mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = $supplier_reference")
    				or die(mysql_error());
    			mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = $supplier_reference")
    				or die(mysql_error());
            }
        }
    }
    fclose($handle);
    It's possible I did something silly.

    Any and all help appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,445
    "UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = $supplier_reference")
    doesn't seem right to me

    I'd expect UPDATE MyTable SET blah di blah

    Guessign from the rest of the SQL its likely to be
    "UPDATE stock_available SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = $supplier_reference")
    doesn't seem right to me

    When debugging SQL i've alwasy found it easier to view the actual SQL being sent to the SQL engine, as opposed to the code that's creating the SQL. check that their is a sane value in $quantity (and for that matter in $supplier_reference


    Im not to certain how you will identify the product, you will probably need to JOIN the stock_available table to the product table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    I suspect that $quantity does not have a value and your left with SET stock_available.quantity = WHERE ...

    Your SQL for the update is correct.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

  4. #4
    Join Date
    Oct 2012
    Posts
    17
    Thanks to both of you for the help.

    It turns out I had 2 things screwy.

    1) I had somehow saved the csv file with a ; delimiter. Apparently PHP and /or MySQL doesn't like that much. Once I changed it to , all was fine.

    2) The $supplier_reference variable can also contain letters so I had to do this:

    mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")

    It updates correctly now.

    Cheers.

Posting Permissions

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