Stuck with displaying accessories

hi all

There is field name "model" for all products in products table.

there is one category "ACCESSORIES" in which "model" field has values like "e71,e72,e73,e74....." all together in one field, because some accessories are compatible with many phones.

when e71 phone is displayed on the page, then i want to display accessories whose "model" field has values e71.

here is what i have tried

PHP Code:

---


/* query to select all models from accessory table */

$qry_model="select * from product_table where status='Y'and category_id=4";
$qry_model_result=mysql_query($qry_model);
while($qry_model_row=mysql_fetch_array($qry_model_result))
{
$desc_model_all = $qry_model_row['model'];
$desc_model_all = explode(',', $desc_model_all); 
}

/* query to display accessories  */

$qryc="select * from product_table where model LIKE '$desc_model_all[0]' and status='Y' and category_id=4"; 


---

i m stuck with the limitation of my display query.

because i will have to write

PHP Code:

---

'$desc_model_all[0]' '$desc_model_all[1]' and so on.... 


---

vineet

So you want to find items where model contains e71 ?

PHP Code:

---

"SELECT .....  WHERE model LIKE '%e71%' AND status......" 


---

NOTE: This will also find anything that contains models e710, or he71 or.... anything that contains the three characters 'e' '7' and '1' back to back.

then what would i have to do so that it finds and display result for "e71" only

WHERE model = 'e71' OR model LIKE 'e71,%' OR model LIKE '%,e71' OR model LIKE '%,e71,%'

Quote:

---

i m stuck with the limitation of my display query.

---

You are plumbing the depths of what you can with arrays kept in a table, so perhaps you can now better understand the purpose of normalizing data in a database.

Quote:

---

Originally Posted by StarLion

WHERE model = 'e71' OR model LIKE 'e71,%' OR model LIKE '%,e71' OR model LIKE '%,e71,%'

---

this is quite clumsy, yes?

better to use the FIELD() function

however, far better would be to normalize the data

Quote:

---

Originally Posted by r937

this is quite clumsy, yes?

better to use the FIELD() function

however, far better would be to normalize the data

---

Field works with column names? I was trying to do it with a single query rather than pulling all results and then filtering in a separate one. Yes, it's very clumsy, which IS why the data should be normalized.