MySQL Loading Rows via String's Contents

Question

I'm making a "Like" button for a portfolio website I'm working on and I've gotten stumped by some of my own code!

I have two MySQL tables:

• img_all: contains all images on the server (each image has a 6 INT id)
• login: contains account information for all users of the website (each user has a 6 INT id as well a VARCHAR column labeled "likes")

The way my system is laid out. When a user "Likes" a picture, I save that picture's id to a column on the login table.

UPDATE login SET likes = CONCAT(likes,':$img_id:') WHERE user_key = $user_id;

and when they unlike a picture:

UPDATE login SET likes = REPLACE(likes,':$img_id:','') WHERE user_key = $user_id;

This will output strings in the likes column similar to this:

:456093:475829:203944:789203:

My problem starts here. I'm making a page that allows users to view all the pictures that they've liked (let's call this file "Likes.php"). However, the list of liked pictures are saved in the login table, while the actual picture information is saved in img_all.

How then do I take the list from my login table and translate it to select those images from img_all? I was thinking of using a mixture of:

SELECT user_key FROM login WHERE likes LIKE '%:$img_id:%';

and

while();

I also thought of a SQL query. I know it won't work. However, hopefully, it will also help relay what I'm trying to accomplish!

SELECT * FROM
img_all WHERE id =
      SELECT likes FROM login
            WHERE likes LIKE '%:$img_id:%' AND user_key = '$user_id';

Answer 1

You are almost there. Instead of using a subquery, you can turn your query into a JOIN, like :

SELECT i.*
FROM img_all i
INNER JOIN login l ON l.likes LIKE CONCAT('%:', l.img_id, ':%')
WHERE user_id = ?

---

While this might solve your question, please be aware that storing list of values in a single column is almost always an indication of poor design.

Accessing and modifying the data requires to manipulate strings, which is uneasy to do with SQL, error-prone and quite inefficient. Also, as commented by Bill Karwin, the number of likes that you can store for a single user is limited by the maximum size of the string column.

As commented by tim, you should use a separated table to store the likes, with foreign keys to the login and img_all tables.

CREATE TABLE likes (
    like_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    img_id  INT NOT NULL,
    PRIMARY KEY (like_id),
    FOREIGN KEY fk_likes_login(user_id) REFERENCES login(user_id),
    FOREIGN KEY fk_likes_img(img_id) REFERENCES  img_all(img_id)
);

NB : the auto-incremented primary key is not strictly necessary, and could be replaced by a composite unique index on the two foreign keys.

Then you can retrieve all images that a user liked with a simple JOINed query :

SELECT i.*
FROM likes 
INNER JOIN img_all ON img_all.img_id = likes.img_id
WHERE likes.user_id = ?