...

View Full Version : using insert with select does not seem to work with wamp server



dave14
12-04-2010, 03:01 PM
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

dave14
12-04-2010, 03:07 PM
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

abduraooft
12-04-2010, 03:11 PM
mysql_query($sQry); Change that line to

mysql_query($sQry) or die('Error in query: <br>'.mysql_error()); to see the errors in your query.

dave14
12-04-2010, 03:31 PM
Change that line to

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?

dave14
12-04-2010, 03:47 PM
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]

abduraooft
12-04-2010, 03:50 PM
Try
$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)";

dave14
12-04-2010, 04:47 PM
$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

Fou-Lu
12-04-2010, 04:50 PM
Enable your error_reporting:


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.

Fou-Lu
12-04-2010, 05:04 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum