SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: query help.

  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query help.

    Hi,

    I have a query where I am well stuck.

    I have an array and two variables, which I need to use in my query. The only way I can think of doing it is in a loop and that isn't eficient - If I recall what I read.


    array : 1,2,3,6,8; /*room_numbers*/
    var1: 1; /*business_id*/
    var2: 4; /*product_id*/
    Code mysql:
    SELECT 
               rp.room_number
             , rp.business_id
             , rp.product_id
           from room_products rp
           where rp.business_id = ?
           and rp.room_number = ?
           and rp.product_id = ?

    Then I need to do an insert using the same values
    Code mysql:
    INSERT INTO room_products
                  VALUES (?, ?, ? );

    Can anyone please help out?
    Edit:

    sorry for the edit

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it possible that something like this will cut two queries down to one.

    Code mysql:
    INSERT INTO room_products
                     VALUES (?, ?, ? )
                   where room_number NOT IN ($room_number)
                   and business_id != ?
                   and product_id != ?

    bazz

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why don't you try explaining what you're really trying to do

    your sample queries leave me all confused as to what the purpose might be

    post #1 makes no sense at all, you're inserting the same values back into the table that you pulled them from???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy, thanks for that.

    literally speaking, I am querying the table to see if a three column pk entry is there. if it is, the insert does nothing. if it is not there, the insert is performed.

    basically, what I am trying to do is to perfrom an insert unless the values are already in the table. currently, the first query in post #1 checks the table for the entry. then the second query (the insert) is performed in a loop, conditional on the result of the first query.

    Post #2 is my attempt at trying to do the same thing, in another way, without inserting via a loop.

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use INSERT IGNORE

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, here's a failing attempt but, is it close?

    Code mysql:
    INSERT IGNORE INTO room_products
                  VALUES (?, ?, ? ),
                  select room_number 
                  from room_numbers
                  where room_number IN ($room_number)
                  and business_id = ?
                  and product_id = ?

    while I keep working on it, can anyone tell me if this is close to being able to input the rooms listed in $room_numbers (1,3,56,57,58 - for example) or should I put it into a scripted loop and insert each room_number, one at a time, like this.

    #prepare statement ahead of the loop
    my $insert_new_room_product = $db_connect->prepare("insert ignore into room_products
    values ( ?, ?, ? )
    ") or die......

    #begin looping thru room numbers
    foreach my $room_number(@room_numbers)
    {
    $insert_new_room_product->execute($business_id,$room_number,$product_id);
    }



  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    when you use an INSERT/SELECT statement, you cannot use the VALUES clause

    further, the number of values in the INSERT that you specify for the new rows must (obviously) match the number of values drawn by the SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yay. I seem to have it close.

    Code mysql:
    INSERT IGNORE INTO room_products 
                                                                 (business_id,room_number,product_id)
         (select r.business_id
                 , r.room_number
                 , pl.id
          from rooms r
          inner 
          join product_live pl
          on pl.id = ?
          and r.business_id = ?
          and r.room_number IN ($room_number)

    It still errors though, this time, on a child fk restriction.
    [Cannot add or update a child row: a foreign key constraint fails (db.name/room_products`, CONSTRAINT `room_products_product_live_fk` FOREIGN KEY (`product_id`) REFERENCES `product_live` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ]

    now my fk constraint is set up like this and so, I can't see why there is a problem.

    alter table room_products
    add constraint room_products_product_live_fk
    foreign key (product_id)
    references product_live(id) on delete cascade on update cascade
    The product_id is in the pl.id col
    The business_id is in the r.column
    The room_number is in the r.room_number col.


    I would appreciate any pointers, please.

    bazz

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I might have it. The PK does not yet include the product_id

    bazz


Bookmarks

Posting Permissions

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