Searching multiple columns in mySQL table from PHP array vs fixed

Question

Edit: Thanks to spencer7593 for helping me solve this. I have edited the main code to include the correct form for those who come later.

I am currently wrapping my head around PHP and mySQL and have run into a roadblock on searching mySQL.

I need to search multiple columns in a table for partial matches. I have got this working properly with a fixed set of columns to search, but when I try and use an array it fails, even though the output to mySQL seems identical.

Can anyone point out the tiny silly error I have made somewhere? That, or an improved way of doing it?

<form name="search" method="post" action="<?php $PHP_SELF; ?>">
    Search for: <input type="text" name="find" />
    <input type="hidden" name="searching" value="yes" />
    <input type="submit" name="search" value="Search" />
</form>


<?php

$dbdata=array(
    'servername'    => "localhost",
    'username'      => "Username",
    'password'      => "Password",
    'dbname'        => "Membership",
    'table_Members' => "Members"
);
$fields=array(
                'MemberID',
                'FirstName',
                'LastName',
                'Nickname',
                'Interests',
                'Skills');

$output=array();
if( isset($_POST['searching']) ) {
    $output=read_db_search($dbdata['table_Members'],$_POST['find'],$fields);
}
echo ('<pre>');
var_dump($output);
echo ('</pre>');

function read_db_search ($table,$searchQuery,$fields)
{
    global  $dbdata;
    $servername=$dbdata['servername'];
    $dbname=$dbdata['dbname'];
    $dbtable=$dbdata['table_Members'];

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbdata['username'], $dbdata['password']);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*This version works!!!!*/
$query = "SELECT * FROM `Members` WHERE `"
       . implode("` LIKE '%$searchQuery%' OR `",$fields) 
           . "` LIKE '%$searchQuery%' " ;

            /* This section should work but doesnt */
        //$query="SELECT * FROM `Members` WHERE`'.implode(' ` LIKE \'%$searchQuery%\' OR `',$fields).'` LIKE \'%$searchQuery%\'";
        //$stmt = $conn->prepare($query);



            /* This one works fine */
    /*  $stmt = $conn->prepare("SELECT * FROM `Members` WHERE
            `MemberID`  LIKE '%$searchQuery%' OR 
            `FirstName`     LIKE '%$searchQuery%' OR 
            `LastName`  LIKE '%$searchQuery%' OR 
            `Nickname`  LIKE '%$searchQuery%' OR 
            `Interests` LIKE '%$searchQuery%' OR 
            `Skills`    LIKE '%$searchQuery%'");
*/


        $stmt->execute();
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
            $data=$stmt->fetchAll();
        }
        catch(PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
        $conn = null;
        return $data;
}
?>

A var_dump of $query returns:

string(89) "SELECT * FROM `Members` WHERE`'.implode(' ` LIKE \'%searchterm%\' OR `',Array).'` LIKE \'%searchterm%\'"

with an error of:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'%searchterm%\' OR `',Array).'` LIKE \'%searchterm%\'' at line 1

"it fails" is a rather vague description of the observed behavior. We're left wondering if a PDO exception is being thrown, whether there's a MySQL syntax error, or if the query is returning results that are unexpected. Please edit the question to more explicitly explain the behavior you are observing. And, echo out the value of $query, and include that in your question as well. — spencer7593
– spencer7593, Commented May 2, 2015 at 17:28
It's pretty clear now, I missed it before. The .implode( needs to be outside of the double quotes... put a double quote before that dot, (to terminate the preceding string literal). And you'll need another double quote after the ). at the end of the implode(). And it looks like "Array" needs to be replaced with a reference to $fields. This isn't really a MySQL problem, this is a problem with PHP string handling. (The contents of $query need to be valid SQL text.) — spencer7593
– spencer7593, Commented May 2, 2015 at 17:40

spencer7593 · Accepted Answer · 2015-05-03 18:17:19Z

1

As a first step in debugging this, do an echo or var_dump of $query before it's submitted to the database.

But as some suggestions... here:

$fields=array( 
            `MemberID`,
            `FirstName`,

What's the deal with the backticks? Do you want MemberID to be executed, and have the return from that put into an array? Or did you want to put string literals into an array? For example:

$fields=array(
           '`MemberID`',
           '`FirstName`',

-or-

$fields=array(
           'MemberID',
           'FirstName',

If the string literals include the backticks, then you wouldn't need to muck with adding backticks in the "implode", when you generate the SQL text into $query.

And here:

   WHERE`'.implode(' ` LIKE \'
        ^           ^

It looks like this is not leaving a space between the keyword WHERE and the identifier. And it looks like a space is added to the identifier, between the last character of the column name (e.g. MemberID) and that closing backtick. so your SQL is going to reference an identifier that includes a space character, e.g.

  ... WHERE`MemberID ` LIKE '%...
           ^        ^

That looks like a problem to me. Looks like you want to generate

  ... WHERE `MemberID` LIKE '%...
           ^         ^

If you've already included backticks around the identifiers in the array, then you don't need to add backticks in the generation of $query. If you do include the backticks, then they need to be "tight" to the value supplied for the identifier (the column name), and not include extra space characters inside the backticks.

To explain that a little more clearly with an example... this column name:

`abc`

Is not the same as this column name:

`abc `

Again, for debugging this, just do an echo or var_dump of your generated query string, and take a close look at the SQL that's going to be submitted to the database. If you can't spot an error, and try taking that SQL text and testing it against the database in another client.

EDIT

There's several problems in the PHP string manipulation that's assigning a value to $query.

Try something like this:

$query = "SELECT * FROM `Members` WHERE `"
       . implode("` LIKE '%$searchQuery%' OR `",$fields) 
               . "` LIKE '%$searchQuery%' " ;

When that's complete, $query should contain something that looks like a valid SQL statement, something like this

SELECT *
  FROM `Member`
 WHERE `MemberID`  LIKE '%foo%'
    OR `FirstName` LIKE '%foo%'

(I've added line breaks and whitespace for formatting.)

edited May 3, 2015 at 18:17

answered May 2, 2015 at 17:05

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

4 Comments

Cynar Over a year ago

The backticks are inadvertent from my attempts at fixing the issue previously. The code is stripped from a larger project and reduced to focus in on the issue. As for testing. If I take $query, echo it and paste it into the $conn->prepare() it works fine, but not when fed directly.

Cynar Over a year ago

The last one was there other than a ` :D It now works. I will edit the original to include the correct solution for future hair tearers.

spencer7593 Over a year ago

@Cynar: You'll want to verify that $fields is an array that contains at least one element. That string concatenation isn't going to produce a valid SQL statement if $fields is an empty array.

Cynar Over a year ago

Agreed. In my main code there is some validation code to check that and a few other bits. I just stripped it out to focus on the main issue.

Collectives™ on Stack Overflow

Searching multiple columns in mySQL table from PHP array vs fixed

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related