PHP PDO prepared statements syntax problems

Question

I am trying to use prepared statements using PDO in PHP. It seems to work fine until I am try return a name with white spaces.

For Example I can return a name like Apple with no issues but I can not return any results for packard bell, Hewlett Packard, etc.

I've tested on multiple different names and when I hard code in packard bell or Hewlett Packard the query returns the results as expected.

Here is the query with the prepared statement I am using.

$stmt = $conn->prepare('SELECT * FROM job WHERE client_name = :customer ORDER BY job_date DESC');
$stmt->execute(array(
    'customer' => $customer,
));

When I query for names with white spaces I do not receive any errors I am forwarded to the results page as usual. The problem is the results page is empty even though there are rows in the database. Is there something I have to do with inverted commas or quotations to fix this.

Thank You

What error do you get when you execute the query? (show the output of $stmt->errorInfo after you execute) — t.niese
– t.niese, Commented Jan 8, 2014 at 9:42
@railsbox: a single trailing comma after the last item in a PHP array is ignored. — outis
– outis, Commented Jan 8, 2014 at 9:55
@psycho: when asking for help with technical matters, include what you expected and what you actually get. — outis
– outis, Commented Jan 8, 2014 at 9:59
when I query for names with white spaces I do not receive any errors I am forwarded to the results page as usual. The problem is the results page is empty even though there are rows in the database. — psycho
– psycho, Commented Jan 8, 2014 at 10:03

Brightshine · Accepted Answer · 2014-01-08 10:14:49Z

3

While the client_name is ':customer', you must execute with an array ':customer' => $customer

$stmt = $conn->prepare('SELECT * FROM job WHERE client_name = :customer ORDER BY job_date DESC');
$stmt->execute(array(
    ':customer' => $customer
));

Ref: http://www.php.net/manual/en/pdo.prepare.php

edited Jan 8, 2014 at 10:14

answered Jan 8, 2014 at 9:49

Brightshine

9651 gold badge7 silver badges17 bronze badges

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

6 Comments

outis Over a year ago

With many (all?) DB drivers, the colon in the param key is optional.

psycho Over a year ago

Forgive me if I am not understanding correctly but am I not executing the array while binding the value entered for the customer in the following code. $stmt->execute(array( ':customer' => $customer, ));

Brightshine Over a year ago

Sorry, it should be array( ':customer' => $customer ) "," after $customer should be removed .

psycho Over a year ago

Thank you @Brightshine but that still has the same effect. The query is still returning no results when customer has whitespaces in its name.

Brightshine Over a year ago

Try enclose it in single quote? WHERE client_name = ':customer' ? You may debug using $stmt->errorInfo .

|

psycho · Accepted Answer · 2014-01-08 15:40:15Z

1

I have found the problem. It had nothing to do with the prepared statement for some reason the values set inside a dropdown option field on the search form is only setting the name of the customer up to the whitespace and not anything after it. I now have to figure out why this is happening.

Thank You

answered Jan 8, 2014 at 15:40

psycho

1,6514 gold badges21 silver badges39 bronze badges

Collectives™ on Stack Overflow

PHP PDO prepared statements syntax problems

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related