CODEWITHSUNDEEP
mysql
Recif
Recif

Reputation: 479

Where in with mysql or better?

Here is my actual query:

$result = mysqli_query($link,"select ids_billets from pref_tags where id='$_GET[id]'");
$tags_ids = $result->fetch_object();

$result = mysqli_query($link,"select id, html from pref_posts where id in ($tags_ids->ids_billets)");
while($posts = $result->fetch_object()) {
  .....
}

I have ids in one varchar field of the pref_tags table (ids_billets) - example : "12,16,158"

Is there a better way to query this? Thanks

Upvotes: 0

Views: 69

Answers (2)

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

You should not put multiple values in a single column, because doing so breaks first normal form. Have a look at that link for examples of the problems you're likely to come across, and how to fix them.

Rather, create a separate table where you can have the ID and the Tag ID in separate columns. Then you can pull back the IDs in a subquery in your second example query, and get the benefits of being able to search for and manipulate individual IDs in other queries.

Upvotes: 1

Brendan Long
Brendan Long

Reputation: 54242

Instead of one row with a comma-separated list, I would create a new table linking posts to tags, with one row per post/tag combo, i.e.:

posts
---------------------
post_id | html | etc.


posts_tags
----------------
post_id | tag_id

tags
------------------------
tag_id | tag_name | etc.

Then do something like this:

SELECT p.post_id, p.html
FROM posts p
INNER JOIN posts_tags pt
ON p.post_id = pt.post_id
INNER JOIN tags t
ON pt.tag_id = t.tag_id
WHERE t.tag_name = ?

Or if you already have the tag_id, like you seem to:

SELECT p.post_id, p.html
FROM posts p
INNER JOIN posts_tags pt
ON p.post_id = pt.post_id
WHERE pt.tag_id = ?

You can also do the same query in a different form, using a subquery:

SELECT post_id, html
FROM posts
WHERE post_id IN (SELECT post_id FROM posts_tags WHERE tag_id = ?)

Also, look at prepared statements, which will make it easy to avoid the serious SQL injection problems your current code has.

Upvotes: 1

Related Questions