get data from multiple tables mysql php

Question

I have a db table that contains a score information for each user with each item. I want to get the scores of the items not seen (not added to the shopping cart or wish list) by the user. I have this query:

$query = $this->db->query("
select product_id
     , score 
    from score where ( customer_id= '$customer_id' and product_id not in ( 
        ( select product_id from cart where customer_id= '$customer_id' ) 
        UNION 
        ( select product_id from customer_wishlist where customer_id= '$customer_id' ) 
    ) ) 
    order by score desc 
    limit 4");

but I've got the following fatal error:

Uncaught exception 'Exception' with message 'Error: 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 'UNION (select product_id from customer_wishlist where customer_id= '8') ))

any help?

Get rid of the closing and opening parentheses either side of the UNION — Strawberry
– Strawberry, Commented Nov 18, 2017 at 8:33

ScaisEdge · Accepted Answer · 2017-11-18 08:34:42Z

1

If the customer id is integer don'y use quotes around it and don't use unuseful () around select UNION select

  $query = $this->db->query("select 
       product_id  
      , score 
      from score 
      where ( customer_id= '$customer_id' and product_id not in ( 
          ( 
            select product_id from cart where customer_id= $customer_id  
            UNION 
            select product_id from customer_wishlist where customer_id= $customer_id
          ) 
      ) ) 
      order by score desc 
      limit 4");

answered Nov 18, 2017 at 8:34

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

get data from multiple tables mysql php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related