CODEWITHSUNDEEP
phpmysql
Mdlc
Mdlc

Reputation: 7288

PHP check if Database contains string

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)

Upvotes: 0

Views: 5486

Answers (3)

Halcyon
Halcyon

Reputation: 57709

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.

Upvotes: 3

Lo&#239;c
Lo&#239;c

Reputation: 11943

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.

Upvotes: 2

user2879327
user2879327

Reputation:

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?

Upvotes: 0

Related Questions