CODEWITHSUNDEEP
mysqlsql
Umair A.
Umair A.

Reputation: 6873

What's wrong with this MySQL Query

I have this table.

id  name                    parents  
1   Apparel                                                                    
46  Apparel                 1                                                  
47  Child Apparel           1,46                                               
49  Child Apparel 2         46,1

I tried this query and it works fine.

SELECT * FROM categories WHERE 1 IN (parents)

id  name           parents
46  Apparel        1                                                  
47  Child Apparel  1,46

Why this query brings 1 record instead of 2?

SELECT * FROM categories WHERE 46 IN (parents)

id  name             parents
49  Child Apparel 2  46,1

Upvotes: 1

Views: 73

Answers (2)

Mrugen Ramani
Mrugen Ramani

Reputation: 64

If you want to maintain category table there is worng way you have maintain this table.

root category in parent only contain 1 sub category in parent contain value of the root category

so parent column only maintain 1 value not more then 1 value with , as you mention

you can also fetch that record using recursive function

function showlist($parent) {
$result = mysql_query("SELECT ID FROM categorie WHERE parentID='$parent'");
while ($line = mysql_fetch_array($result)) {
if($catlistids!=""){ $catlistids .= ", "; }
$catlistids .= $line["ID"];
showlist($line["ID"]);

}
return $catlistids;

}

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838226

You should use FIND_IN_SET instead of IN:

SELECT * FROM categories WHERE FIND_IN_SET('46', parents)

Note that this query will be slow. You should redesign your database. I suggest you look at this slideshow for better ways to store heirarchical data in MySQL:

Upvotes: 7

Related Questions