search entire table except date fields using mysql and php

Question

I have a large table and would like to search all of the fields in one go but some of the fields are dates so the search I have created falls over when it hits those fields.

Is there a way to exclude certain types of fields when using this type of search?

$table = 'accounts';
$condition = 'tracey';

$sql = "SHOW COLUMNS FROM accounts";

$result = mysqli_query($mysqli,$sql);

if (mysqli_num_rows($result) > 0)
{
    $i=0;
    while ($row = mysqli_fetch_array($result))
    {
        if($i==0)
        {
            $q="select * from ".$table." where ".$row[0]." LIKE %".$condition."%";
        }
        else
        {
            $q.=" or ".$row[0]."="." LIKE %".$condition."%";
        }
        $i++;
    }
}

$sql = $q;
$result = mysqli_query($mysqli,$sql) or die(mysqli_error($mysqli));
$row = mysqli_fetch_array($result);
$answer = $row['phone_office'];
echo '<br><br>answer = '.$answer;

Or perhaps someone can suggest a better way of searching all of the fields in a table in one go?

http://stackoverflow.com/questions/14392126/how-to-select-all-columns-in-sql-except-one-column this link may be helpfull for you — A l w a y s S u n n y, Jul 30 '14 at 10:18

score 0 · Answer 1 · edited Jul 30 '14 at 12:36

0

To exclude certain types of fields You need to change the query SHOW COLUMNS FROM accounts with this one:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE (table_name=".$table.")AND(NOT(DATA_TYPE IN ('field_type_1','field_type_2',...)));

Where field_type_i is the name of an excluded type (for example 'timestamp')

edited Jul 30 '14 at 12:36

tobias_k

81,265
12
120
179

answered Jul 30 '14 at 12:32

user3886741

1

thanks but I get an error unknown column accounts. accounts is the table name – tatty27 Jul 30 '14 at 13:36

search entire table except date fields using mysql and php

1 Answers1