PHP检查数据库是否包含字符串

Question

I have a Array with data, and I am looping through the data.

For each string, I want to check if it is present in the database (whole table). It might be there inside another string (so if the table contains heyredcat, and you check for red, it also returns true).

I've came up with this sample script, but I can't come up with a MySQL query, Googling resulted in many great suggestions for "if column equals string" but not for a string in the whole table.

<?php 
$colors = array("red","green","blue","yellow"); 
foreach ($colors as $value)
   {
      $result = mysql_query("SELECT * FROM products where $value ...... ") or die(mysql_error());
      if($result){
        echo "Color" .$value. "is present";
      else {
        echo "Color" .$value. "is not present";
      }
   }
?>   

What MySQL query should I use? Also, I'm wonding, is this an efficient way to do this? (time consuming)

Answer 1

This is what the query can look like:

$res = mysql_query("SELECT 1 FROM `products` WHERE
    `col1` LIKE '%" . $value . "%'
    OR `col2` LIKE '%" . $value . "%'
    OR `col3` LIKE '%" . $value . "%'
    .. etc
");
$num = mysql_num_rows($res);
if ($num === 0) { // not found

But keep in mind that this is susceptible to SQL injection if the list of colors can be picked by a user.

So you will want to do:

$value = mysql_real_escape_string($value);
mysql_query("SELECT 1 FROM `products` WHERE
    `col1` LIKE '%" . $value . "%'
    OR `col2` LIKE '%" . $value . "%'
    OR `col3` LIKE '%" . $value . "%'
    .. etc
");

You can dynamically generate the column names by looking at the mysql schema. The cheaty way would be to do a SELECT * FROM table LIMIT 1 first to get all the column names.

---

Even better is to use the PDO driver which has a better (less error prone) way of inserting parameters into queries.