PHP check if Database contains string

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)

tssss bad edit. Anyway, hopefully my whole database searching script will help somebody else. BOUH you! — Loïc
– Loïc, Commented Mar 4, 2014 at 16:45

Halcyon · Accepted Answer · 2014-03-04 16:51:38Z

3

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.

edited Mar 4, 2014 at 16:51

answered Mar 4, 2014 at 16:31

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mdlc Over a year ago

Thanks for your Answer (& edits). It works great, good you mentioned the real_escape, I'd almost forget. Only note is that your method requires a column, and I prefer to search the whole table

Loïc Over a year ago

"For each string, I want to check if it is present in the database (whole database)."

Mdlc Over a year ago

I meant table instead of whole database btw (I'll edit right away), but indeed @Loic this answer does not involve this.

Mdlc Over a year ago

Bedankt @Frits van Campen, je was de eerste.

Loïc Over a year ago

Can't believe this is the good answer while being so resteined to one only example. sniff. Shame on you guys!

|

Loïc · Accepted Answer · 2014-03-04 16:46:55Z

2

If you want to check the whole database then you are doing it wrong.

Here are the main lines on how to do it :

First gather all database tables and columns :

Select table_schema, table_name, column_name from information_schema.COLUMNS where table_schema=[your_db_name] and table_name=[your_table_name]

You just got the whole database server columns.

Now, you'll need to check, for each column if it's like your string.

<?php
while ($row = mysqli_fetch_array($result)){
    mysqli_select_db($row['table_schema']);
    $res = mysqli_query('select * from '.$row['table_name'].' where '.$row['column_name'].' like "%'.$your_string.'%"');
    while($res_line = mysqli_fetch_array($res)){
         var_dump($res_line);
     }
}

?>

Oh, I'm not responsible for any db lags :D

And you might want to add the table_schema, table_name, and column_name in your dump, I won't take all the fun, have some.

edited Mar 4, 2014 at 16:46

answered Mar 4, 2014 at 16:39

Loïc

12k2 gold badges35 silver badges52 bronze badges

1 Comment

Mdlc Over a year ago

Thanks for you answer, Especially for integrating my request to search the whole table.

user2879327 · Accepted Answer · 2014-03-04 16:31:24Z

0

you will also need a list of every table in your database to loop through, and every column in every table to query. I am not aware of a query that can query every column in every table in a database. Maybe you would be better off using mysqldump and grep?

answered Mar 4, 2014 at 16:31

user2879327

Collectives™ on Stack Overflow

PHP check if Database contains string

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related