Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Nov 2010
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    using insert with select does not seem to work with wamp server

    create table REORDER (
    order_code varchar(5) NOT NULL,
    cat_code varchar(5) NOT NULL, // this is the foreign key from product(cat_num)
    quantity INT(4) NOT NULL,
    d_date date,
    delivery char(1) NOT NULL,
    PRIMARY KEY (order_code));



    CREATE TABLE PRODUCT (
    cat_num varchar(5) NOT NULL,
    p_name varchar(30) NOT NULL,
    p_price decimal(19,2) NOT NULL DEFAULT 0,
    description varchar(100) NOT NULL,
    stock_no int(4) NOT NULL DEFAULT 0,
    s_code varchar(5) NOT NULL,
    PRIMARY KEY (cat_num));

    with this query i want to update the reorder table with items not in stock in product table and not already in reorder table
    $sQry = "INSERT INTO REORDER VALUES(cat_num,s_code,14,'2011-01-14','n')SELECT cat_num,s_code FROM PRODUCT WHERE stock_no=0 AND NOT EXISTS (SELECT 1 FROM restock WHERE product.cat_num = restock.cat_code)";

    open connection here
    mysql_query($sQry);

    This statement seems to work no errors come up but the reorder table is not updated l am using the WAMP server.
    any suggestions thanks

  • #2
    New Coder
    Join Date
    Nov 2010
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    using insert with select does not seem to work with wamp

    create table reorder (
    order_code varchar(5) NOT NULL,
    cat_code varchar(5) NOT NULL, // this is the foreign key from product(cat_num)
    quantity INT(4) NOT NULL,
    d_date date,
    delivery char(1) NOT NULL,
    PRIMARY KEY (order_code));



    CREATE TABLE product (
    cat_num varchar(5) NOT NULL,
    p_name varchar(30) NOT NULL,
    p_price decimal(19,2) NOT NULL DEFAULT 0,
    description varchar(100) NOT NULL,
    stock_no int(4) NOT NULL DEFAULT 0,
    s_code varchar(5) NOT NULL,
    PRIMARY KEY (cat_num));

    with this query i want to update the reorder table with items not in stock in product table and not already in reorder table
    $sQry = ' INSERT INTO reorder VALUES(cat_num,s_code,14,'2011-01-14','n')SELECT cat_num,s_code FROM product WHERE stock_no=0 AND NOT EXISTS (SELECT 1 FROM restock WHERE product.cat_num = restock.cat_code)';

    open connection here
    mysql_query($sQry);

    This statement seems to work no errors come up but the reorder table is not updated l am using the WAMP server.
    any suggestions thanks

  • #3
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    mysql_query($sQry);
    Change that line to
    PHP Code:
    mysql_query($sQry) or die('Error in query: <br>'.mysql_error()); 
    to see the errors in your query.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    dave14 (12-04-2010)

  • #4
    New Coder
    Join Date
    Nov 2010
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by abduraooft View Post
    Change that line to
    PHP Code:
    mysql_query($sQry) or die('Error in query: <br>'.mysql_error()); 
    to see the errors in your query.
    thanks l put it in
    $sQry = "INSERT INTO reorder VALUES(cat_num,s_code,14,'2011-01-14','n') SELECT cat_num FROM product WHERE stock_no=0 AND NOT EXISTS
    (SELECT 1 FROM reorder WHERE product.cat_num = reorder.cat_code)";
    mysql_query($sQry) or die('Error in query: <br>'.mysql_error());

    this is the error message i get

    Error in query:
    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 'SELECT cat_num FROM product WHERE stock_no=0 AND NOT EXISTS (SELECT 1 FROM res' at line 1

    this seems to work ok on its own its when i add the insert
    SELECT cat_num FROM product WHERE stock_no=0 AND NOT EXISTS
    (SELECT 1 FROM reorder WHERE product.cat_num = reorder.cat_code)";
    l cant see whats wrong?

  • #5
    New Coder
    Join Date
    Nov 2010
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    get this error cant figure whats wrong

    thanks l put it in
    $sQry = "INSERT INTO reorder VALUES(cat_num,s_code,14,'2011-01-14','n') SELECT cat_num FROM product WHERE stock_no=0 AND NOT EXISTS
    (SELECT 1 FROM reorder WHERE product.cat_num = reorder.cat_code)";
    mysql_query($sQry) or die('Error in query: <br>'.mysql_error());

    this is the error message i get

    Error in query:
    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 'SELECT cat_num FROM product WHERE stock_no=0 AND NOT EXISTS (SELECT 1 FROM res' at line 1

    this seems to work ok on its own its when i add the insert
    SELECT cat_num FROM product WHERE stock_no=0 AND NOT EXISTS
    (SELECT 1 FROM reorder WHERE product.cat_num = reorder.cat_code)";
    l cant see whats wrong?[/QUOTE]

  • #6
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Try
    Code:
    $sQry = "INSERT INTO REORDER VALUES(cat_num,s_code,14,'2011-01-14','n')SELECT cat_num,s_code FROM PRODUCT WHERE 
    stock_no=0 AND stock_no NOT EXISTS IN (SELECT 1 FROM restock WHERE product.cat_num = restock.cat_code)";
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #7
    New Coder
    Join Date
    Nov 2010
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    $sQry = "INSERT INTO restock VALUES(cat_num,s_code,14,'2011-01-14','n')SELECT cat_num,s_code FROM PRODUCT WHERE
    stock_no=0 AND stock_no NOT EXISTS IN (SELECT 1 FROM restock WHERE product.cat_num = restock.cat_code)";

    l still get the same error message if l use the select without the insert
    it picks out the correct fields

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Enable your error_reporting:
    PHP Code:
    ini_set('display_errors'1);
    error_reporting(E_ALL); 
    You have a syntax error in that string. Change your surrounding quotations to double quotations to eliminate the error. The two queries also need a semi-colon to separate them.

    I don't think that this will work in PHP though. I remember trying this not all that long ago, and it failed. Seems that PHP will reject grouped queries unless they are the same DMS type, which insert and select are not. Works fine in a cli environment though.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Ugh, that merge did not go well. Oh well, the OP will need to figure out which posts reply to which topic since they are different code blocks.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

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