CODEWITHSUNDEEP
phpsqlmysql
Roger Sinks
Roger Sinks

Reputation: 1

Problem with quotes and paramterized SQL query

I have a very simple search form, that takes in a term from GET_, and then incorporates this into an SQL query. I am trying to use this string from GET in a paramterized query, like so:

$searchString = '%' . $searchString . '%';
$recordsQuery = "SELECT username, firstname, lastname FROM $table WHERE lastname = $searchString" . $max;

  if ($getRecords = $con->prepare($recordsQuery)) {
        $getRecords->bind_param("s", $searchString);
        $getRecords->execute();
        $getRecords->bind_result($username, $firstname, $lastname);
        $rows = array();

        while ($getRecords->fetch()) {
            $row = array(
                'username' => $username,
                'firstname' => $firstname,
                'lastname' => $lastname,
            );
             $rows[] = $row;
        }
        return $rows;
    }

However, this results in the error

Unkown column 'term' in 'where clause'.

I assume this is becase my term is not quoted, however adding escaped quotes to the variable did nothing.

Any syntax errors or such are a product of modification to ask this question, and do not exist in my actaul code.

OK, I fixed this by changing the following lines:

 $searchstring = "'" . $searchstring . "'";
$recordsQuery = "SELECT username, firstname, lastname FROM $table WHERE lastname = $searchString" . $max;

I am sure this approach is probably bad because it is not paramterized..., but I was unable to get it working any other way.

Upvotes: 0

Views: 189

Answers (1)

Arkh
Arkh

Reputation: 8459

You forgot some quotes : $searchString = '"%' . $searchString . '%"';

But why do you build the request like that when you could use bound parameters : http://www.php.net/manual/fr/pdostatement.bindparam.php


$searchString = '%' . $searchString . '%';
$recordsQuery = "SELECT username, firstname, lastname FROM $table WHERE lastname = :lastname" . $max;
if ($getRecords = $con->prepare($recordsQuery)) {
        $getRecords->bind_param(":lastname", "%".$searchString."%");
        $getRecords->execute();
        $getRecords->bind_result($username, $firstname, $lastname);
        $rows = array();
        while ($getRecords->fetch()) {
            $row = array(
                'username' => $username,
                'firstname' => $firstname,
                'lastname' => $lastname,
            );
             $rows[] = $row;
        }
        return $rows;
    }

Upvotes: 1

Related Questions