2014-03-04 16:27
浏览 61


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.

$colors = array("red","green","blue","yellow"); 
foreach ($colors as $value)
      $result = mysql_query("SELECT * FROM products where $value ...... ") or die(mysql_error());
        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)

图片转代码服务由CSDN问答提供 功能建议


对于每个字符串,我想检查它是否存在于数据库中(整个表)。 它可能在另一个字符串里面(所以如果表包含heyredcat,你检查红色,它也会返回true)。

我想出了这个示例脚本,但是我 无法提出MySQL查询,谷歌搜索为“如果列等于字符串”而不是整个表中的字符串提出了许多很好的建议。

 &lt;?  php 
 $ colors = array(“red”,“green”,“blue”,“yellow”);  
nforeach($ colors as $ value)
 $ result = mysql_query(“SELECT * FROM products where $ value ......”)或die(mysql_error()); 
 if($ result)  {
 echo“Color”。$ value。  “存在”; 
 echo“颜色”。$ value。  “不存在”; 

我应该使用哪种MySQL查询? 另外,我想知道,这是一种有效的方法吗? (耗费时间)

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongpo7467 2014-03-04 16:31

    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.

    解决 无用
    打赏 举报

相关推荐 更多相似问题