PHP MYSQL search string everyhwere

Question

Is it possible to search a string in table without specifing column?

$string = 'hello';

$sql = mysql_query("SELECT * FROM table WHERE all_columns LIKE '%,$string,%' ");

and i would like to get one response p.e if there would be two columns in one row which containts same or similar content, but this is not important, i can handle it.

// MY IDEA, NOT SO FAST BUT MIGHT WORK

    $sql = mysql_query("SELECT * FROM particular_table LIMIT = 1");
    $data = mysql_fetch_array($sql);

    $limit = count($data);
    $where_condition = 'WHERE published = 1 AND (';

    $index = 1;
    foreach($data as $key=>$val){
          if($index==$limit){
              $where_condition .= ' '.$key.' LIKE %,'.$string.',%'; 
          } else {
              $where_condition .= ' '.$key.' LIKE %,'.$string.',% OR'; 
          }
          $index++;       
    }
    $where_condition .= ')';

    $get = mysql_query("SELECT * FROM particular_table $where_condition"); 
    $res = mysql_fetch_array($get);

It can be stupid and unnecessary so give me your opintion please

So.. ALL columns have to be `LIKE` a particular value? Or do you mean search all columns and if any of them are `LIKE` a particular value? — SubjectCurio, Jun 30 '14 at 07:10
See https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — vhu, Jun 30 '14 at 07:11
You can also use a dedicated Text-Search engine like Sphinx, which will take data from your MySQL Server and search all the columns for the search string, and will give one result per row. — Rohit Aggarwal, Jun 30 '14 at 07:15

score 1 · Accepted Answer · answered Jun 30 '14 at 07:16

Here's a solution combined with some PHP to search all fields in a specific table.

include("db_con.php");
//search all fields
$searchphrase = "banan";
$table = "apa303";
$sql_search = "select * from ".$table." where ";
$sql_search_fields = Array();
$sql = "SHOW COLUMNS FROM ".$table;
$rs = mysql_query($sql);
    while($r = mysql_fetch_array($rs)){
        $colum = $r[0];
        $sql_search_fields[] = $colum." like('%".$searchphrase."%')";
    }

$sql_search .= implode(" OR ", $sql_search_fields);
$rs2 = mysql_query($sql_search);
$out = mysql_num_rows($rs2)."\n";
echo "Number of search hits in $table " . $out;

Now adjust the query as you want...

i just made something similar thank you ill try it ;) – user3051762 Jun 30 '14 at 07:22 — user3051762, Jun 30 '14 at 07:22

PHP MYSQL search string everyhwere

1 Answers1