在PHP搜索中显示列数据

Question

I have MySQL table called products and in this table I have data:

id | category | product1 | price1 | discount1 | product2 | price2 | discount2 | product3 | price3 |discount3 |
----------------------------------------------------------------------------------------------------------
| 1 | Meat    | Pork 1kg | 3.99   | -30%    | Lamb 1kg    | 5.94 | -25%      | Ham 200g    | 1.99 | -10% |
| 2 | Fruit   | Apple 1kg| 1.25   | -32%    | Banana 1 kg | 0.99 | -15%      | Melon 1 kg  | 0.79 | -12% |

PHP search script I have looks like this:

//-query the database table
$sql="SELECT id, category, product1, price1, discount1, 
product2, price2, discount2, product3, price3, discount3 
FROM products 
WHERE 
product1 LIKE '%" . $name . "%' OR 
price1 LIKE '%" . $name ."%' OR 
discount1 LIKE '%" . $name ."%' OR 
product2 LIKE '%" . $name ."%' OR 
price2 LIKE '%" . $name ."%' OR 
discount2 LIKE '%" . $name ."%' OR 
product3 LIKE '%" . $name ."%' OR 
price3 LIKE '%" . $name ."%' OR 
discount3 LIKE '%" . $name ."%'";

//-run the query against the mysql query function
$result=mysql_query($sql);

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$id=$row['id'];
$category =$row['category'];
$product1=$row['product1'];
$price1=$row['price1'];
$discount1=$row['discount1'];
$product2=$row['product2'];
$price2=$row['price2'];
$discount2=$row['discount2'];
$product3=$row['product3'];
$price3=$row['price3'];
$discount3=$row['discount3'];

//-display the result of the array

echo "<ul>
"; 
echo  "<li>" . $id . "</li>
"; 
echo "<li>" . $category . "</li>
";
echo "<li>" . $product1 . "</li>
"; 
echo "<li>" . $price1 . "</li>
";
echo "<li>" . $discount1 . "</li>
";
echo "<li>" . $product2 . "</li>
";
echo "<li>" . $price2 . "</li>
";
echo "<li>" . $discount2 . "</li>
";
echo "<li>" . $product3 . "</li>
";
echo "<li>" . $price3 . "</li>
";
echo "<li>" . $discount3 . "</li>
";
echo "</ul>";
}  

The problem is that when I search for let's say Apple, Banana and Melon are also displayed.

How can I make it to show only Apple and it's price and discount when searching with keyword apple.

Answer 1

Your DB structure is very odd, which is giving you this headache. Instead use normal relations.

Table categories

id  name
1   Meat
2   Fruit

Table products

id  category_id  product      price  discount
1   1            Pork 1kg     3.99   -30%
2   1            Lamb 1kg     5.94   -25%
3   1            Ham 200g     1.99   -10%
4   2            Apple 1kg    1.25   -32%
5   2            Banana 1 kg  0.99   -15%
6   2            Melon 1 kg   0.79   -12%

You can find a product (eg 'pork') using this query

SELECT product.*, category.name as category FROM product INNER JOIN category ON product.category_id = category.id WHERE product.name LIKE 'pork%'