MySQL Join Question

[learning_brain]

I'm trying to create a basket on an e-commerce site and am using sessions to store item id's in a temporary row in a "sessions" table.

If an item from the "items" table is clicked, the MySQL insert (in the additem.php) adds the id ($_GET), separated by commas, to the "item_ids" column in table "sessions".

In this way, the "item_ids" column in the sessions table can contain many id numbers - all comma separated.

I'm now trying to finish off with the basket.php page which should return all items in that current session, together with all detail from table "items".

So... (trying to get my head round joins)

Something like..

SELECT items.*,sessions.* FROM items,sessions WHERE items.id LIKE %sessions.item_ids% AND sessions.session_id = $session_id

But this returns a syntax error.

I need to include a $session_id in here somewhere but I can't figure ..

a) which join system I should be using and
b) how I can filter where sessions.item_ids CONTAINS items.id
b) how to limit the results by current $session_id

I've read about JOIN types but can't really understand how to apply them in this situation.

Help????

Thanks

Rich

---------- Post added at 07:43 PM ---------- Previous post was at 06:49 PM ----------

OK - Sorry - worked it out.

SELECT items.*,sessions.*
FROM items,sessions
WHERE sessions.item_ids LIKE CONCAT('%',items.id,'%')
AND sessions.session_id = '$session'

Strange having to use CONCAT to get the wildcards in, but it works.

Rich

 

[misson]

Columns in RDB tables should be simple, meaning a single value. This is the 1st normal form property. You should have a separate entry for each item in a cart. A practical reason for this is that joining using LIKE with a leading wildcard is incredibly inefficient. MySQL will need to scan the entire items table. If the table is in 1st normal form, MySQL can use the primary key index to find the rows directly.

-- you could also call this table `line_items`
CREATE TABLE cart_items (
    `session` INT UNSIGNED,
    `item` INT UNSIGNED,
    CONSTRAINT `session_fk` FOREIGN KEY (`session`) REFERENCES `session` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `item_fk` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
);

-- the query:
SELECT i.name, i.price, i.[...], s.id
  FROM items AS i
    JOIN cart_items AS ci ON i.id = ci.item
    JOIN sessions AS s ON ci.session = s.id
  WHERE s.id = :session

For clarity's sake, join conditions (the sessions.item_ids LIKE CONCAT('%',items.id,'%') in your example) should be put in the JOIN clause rather than the WHERE clause.

Don't use SELECT *; select only the columns you need.

Strange having to use CONCAT to get the wildcards in, but it works.

Not really. The arguments to LIKE must have string values. How else are you going to combine the pieces into a pattern (assuming PIPES_AS_CONCAT isn't set)?