...

View Full Version : use php to select items out of stock and enter into reorder table



dave14
12-03-2010, 07:38 PM
l want to use php to select all products that are out of stock in the product table and update the reorder table with them and check that the products selected are not already in the reorder table.
how do l construct a sql query to do this if thats possible.

Fumigator
12-03-2010, 07:40 PM
You need to provide more information. What is your database schema like? What do you want to update in the reorder table?

dave14
12-03-2010, 08:34 PM
l want to use php to select all products that are out of stock in the product table and update the reorder table with them and check that the products selected are not already in the reorder table.
how do l construct a sql query to do this if thats possible.

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

SO basically i want to select all cat_num(catalogue numbers) from product table where stock_no=0 and where (cat_nums not already in reorder table)
cat_num != cat_code (catalogue numbers in reorder table)
then insert this list of products (cat_nums)in the reorder table.
$sQry = 'SELECT cat_num,cat_code FROM product ,restock WHERE (product.cat_num != restock.cat_code and stock_no = 0)';
this worked but it was dupliacting each cat_num execpt for the ones that where in reorder table which it output once.
e.g
0001
0001
0004
0004
0005 this was in the reorder table so it just output one copy.
0006
0006
can u see why this happens?

Fumigator
12-03-2010, 09:29 PM
Yeah you can't JOIN two tables using "not equals" logic. Simply won't work. You end up with a cartesian join, about the opposite of what you're after.

What you want to do is SELECT blah FROM product WHERE NOT EXISTS (SELECT 1 FROM reorder WHERE product.cat_num = reorder.cat_code)

dave14
12-04-2010, 10:28 AM
[QUOTE=dave14;1023396]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 alreday 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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum