2

How to search multiple fields in a MySQL database with a PHP array.

For example, search fields like place_name, admin_name1, and admin_name2 using an array of ["Cambridge","Massachusetts","US"] with using the wildcard %

Below is the exact structure of database ]]

Improve this question
2
  • 1
    how do you want to search? clarify with more example. Commented Oct 20, 2015 at 9:46
  • to be exact I'm trying to implement a php in a such way that it outputs a JSON array of objects using header("Content-type: application/json"); print(json_encode($places, JSON_PRETTY_PRINT));, each of which represents a row from places that somehow matches the value of parameters passed such as: ["Cambridge", "Massachusetts", "US"] ["Cambridge", "Massachusetts"] ["Cambridge","MA"] ["02138"] Commented Oct 20, 2015 at 10:00

3 Answers 3

Reset to default
1

Imagining your array index names are the same as your table column names:

$query = "select * from tableName";
$arrCount = count($arrName);
$i=1;
if($arrCount) > 0){
    $query .= " where";
    foreach($arrName as $key->$val){
        $query .= $key." LIKE '%".$val."%'";
        if($i <= $arrCount)
            $query .= " AND";
        $i++;
    }
}

Then run the query!

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

5 Comments

Querying data and then filtering it in code is bad practice. Do it directly in the query instead.
Actually in this approach the data is queried after the query is structured!'
I was mistaken, you are right. But now you are appending plain text to an SQL query. This is bad practice due to the peril of SQL-injection. It'd be better to use prepared statements instead.
@klaar That would be the best practice for the values. There's not much that can be done with the keys however.
$query .= $key." LIKE %".$val."%"; you need to put quotation marks before and after % symbol. Otherwise this will end up with error.
0 
$array = ["Cambridge","Massachusetts","US"];
$list = implode(",", $array);

Then select:

... WHERE place_name IN($list) OR admin_name1 IN($list) OR admin_name2 IN($list)
Improve this answer

7 Comments

$list = implode(",", $geo); $places = query("SELECT * FROM places WHERE place_name IN($list) OR admin_name1 IN($list) OR admin_name2 IN($list)");
Can you give me full error, i haven't find issue in this code yet.
Fatal error: Unknown column 'Cambridge' in 'where clause' for an array of [ "Cambridge", "Massachusetts" ]
Ok, please try this: $list = implode(' "," ', $geo); $list = $list = ' " '.$list.' " ';
(please remove the space character between quotes)
|
0

Try this

$fields =  array("place_name", "admin_name1",  "admin_name2");
$kwd    =  array("Cambridge","Massachusetts","US");

$condition  =  array();
$n = 0;
foreach($fields as $field){
    $condition[]  .=   $field." LIKE '%".$kwd[$n]."%'";
    $n++;
}
$condition  =   implode(" AND ", $condition);
echo $qry = "SELECT * FROM `table` WHERE ( ".$condition." )";
Improve this answer

2 Comments

You're relying on $n having the correct index for an array unrelated to $fields. These assumptions are usually where errors are born.
assumption since the OP not responding.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.