0

I have the following php mysql statment that is working fine:

if (isset($_GET['name'])) {

        $data = "%".$_GET['name']."%";
        $sql = 'SELECT * FROM tbl_clients WHERE fname like ?';

        $stmt = $conn->prepare($sql);

        $results = $stmt->execute(array($data));

        $rows = $stmt->fetchAll();
        $error = $stmt->errorInfo();
    }

But i want to add the following so it can check to columns for the name variable:

$sql = 'SELECT * FROM tbl_clients WHERE fname like ? or lname like ?';

If i modify this statement to the above it errors out with the following:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /var/www/www.test.com/search.php on line 38
Brad Hazelnut
  • 1,603
  • 5
  • 21
  • 33

2 Answers2

3

It's pretty obvious, you have two ? and only one item in the array you are passing to $stmt->execute

$sql = 'SELECT * FROM tbl_clients WHERE fname like :something or lname like :something';

$data = array(':something'=>'SOME VALUE');
$results = $stmt->execute($data);
JorgeeFG
  • 5,651
  • 12
  • 59
  • 92
  • yeah that makes sense, but how would i be able to set the sql statement to be able to search both fields by passing only one value? – Brad Hazelnut Jun 25 '14 at 18:28
  • @BradHazelnut the first one you are using is positional, meaning the first ? is the first item in the array, and so on... Using names, you look for the names in the array. – JorgeeFG Jun 25 '14 at 18:36
2

In the updated query, you have two parameters, but you're only passing one value to execute. Just fix the latter problem, and it will work:

$results = $stmt->execute(array($data, $data));
Alexis King
  • 43,109
  • 15
  • 131
  • 205
  • Of course it worked, but it's not the way. It's just a dirty patch. – JorgeeFG Jun 25 '14 at 18:37
  • @Jorge Using named parameters would be a more elegant solution, I suppose, but there's nothing wrong with doing it this way. – Alexis King Jun 25 '14 at 18:38
  • @JakeKing, sure, here it's not so important, but would you recommend it for 3 or 4 `?`, just wasting resources. You are copying the same data over and over again. It's good to give best practices advices to newbies, so they grab them. – JorgeeFG Jun 25 '14 at 18:41