CODEWITHSUNDEEP
php
the110boy
the110boy

Reputation: 71

Trouble with showing posts of a category

my `cats` table: id - title
my `posts` table: id - title - content - cat

the categories selected when inserting posts is inserted like this:

cat1id-cat2id-cat3id

for example: 1-2-3 or 2-3-1 or 2-1-3

the not working code:

<?php
    $id=mysql_real_escape_string($_GET['id']);
    $query=mysql_query("SELECT * FROM `posts` WHERE `cat`='{$id}-'");
    $query=mysql_fetch_array($query);
?>

how can i show the posts of a category?

Upvotes: 2

Views: 28

Answers (1)

Jeremy Harris
Jeremy Harris

Reputation: 24599

First off, don't use the mysql_* set of functions -- they are deprecated. Look into PDO.

Second, for your query, you are more suffering from a database architecture issue. IMHO, you should have a posts table, a categories table, and a post_categories table that lets you assign one or more categories to a post. When you do it this way, you have more control.

Finally, to answer your question, when you use the equals operator in your query, it looks for an exact match. To match on a partial piece of text, you most likely need to use the LIKE operator like so:

$query = "
    SELECT *
    FROM posts
    WHERE
        cat LIKE '$id-%'   OR
        cat LIKE '%-$id-%' OR
        cat LIKE '%-$id'   OR
        cat LIKE '$id'
";

That addresses the category being found in the beginning, in the middle, or at the end of the concatenated list (although I cringe looking at it).

But seriously, take a look a refactoring your database structure so you don't even have this problem.

Upvotes: 1

Related Questions