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 5 of 5
  1. #1
    New Coder
    Join Date
    Nov 2010
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    use php to select items out of stock and enter into reorder table

    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.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You need to provide more information. What is your database schema like? What do you want to update in the reorder table?

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

    php product reorder

    Quote Originally Posted by dave14 View Post
    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?

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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)

  • Users who have thanked Fumigator for this post:

    dave14 (12-03-2010)

  • #5
    New Coder
    Join Date
    Nov 2010
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    [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


  •  

    Posting Permissions

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