Query returns 0 results in php, but in oracle SQL developer it returns 52 results

Question

I have a problem with with my oracle query. When I run it in PHP, it returns 0 results. If I run the same Query in Oracle SQLDeveloper, it returns 52 results.

This is the query in PHP:

SELECT name , zipcity FROM  import_persons WHERE upper(:zoekop) LIKE '%:zoekwaarde%' AND status < 3 ORDER BY name
$parameters = array(':zoekop' => $zoekop, ':zoekwaarde' => $zoekwaarde);

The query I run in SQLDeveloper, with the variables filled in manually.(Copied them from an echo in the PHP).

SELECT  name , zipcity FROM import_persons WHERE upper(name) LIKE '%Q%' AND status < 3 ORDER BY name

I can't seem to find the error, before asking, I tried to bind the params but that didn't work.

I've moved the addendum answer to its own answer post, so questions and answers are kept nicely separated (helpful mainly for data extract and API users). — halfer
– halfer, Commented Aug 20, 2015 at 9:06

halfer · Accepted Answer · 2015-08-20 08:08:21Z

2

There are two problems here:

SELECT name , zipcity FROM  import_persons
    WHERE UPPER(:zoekop) LIKE '%:zoekwaarde%' AND status < 3
    ORDER BY name

It is good you're using bound parameters, but unfortunately only values can be bound; since :zoekop is a placeholder for a column, you'll have to use traditional string concatenation (and if the column name comes from user input, use a whitelist for security).

You can bind for :zoekwaarde, but the wildcards need to be part of the string you bind, not in the query. Your SQL will therefore look a bit like this:

SELECT name , zipcity FROM  import_persons
    WHERE UPPER(zoekop_col) LIKE :zoekwaarde AND status < 3
    ORDER BY name

Notice that the query does not include quotes - they are not required, since the binding system knows the type of the parameter you are binding in this position. Wrapping it all up, you now only have one parameter to bind:

$parameters = array(':zoekwaarde' => "%{$zoekwaarde}%", );

edited Aug 20, 2015 at 8:08

answered Aug 20, 2015 at 8:02

halfer

20.2k20 gold badges111 silver badges208 bronze badges

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

5 Comments

halfer Over a year ago

No probs @McBurgerKong, pleased that's got it working!

halfer Over a year ago

@McBurgerKong: it may be worth adding some error checking to your query call - I think the original code should have resulted in an error, but I wonder if you did not see that. With databases it is always good to check for errors at runtime, as much as possible.

McBurgerKong Over a year ago

Now you say that I should've indeed. I did not see any error when I executed, but maybe if I checked using the PDO functions errorCode and errorInfo I would've seen earlier. Thanks for the advice though

halfer Over a year ago

Yes @McBurgerKong, you would need to test for errors explicitly, and it's still worth doing now. Of course, don't show technical errors to users in production - just in dev/staging.

McBurgerKong Over a year ago

Yes it truly worth your while. @halfer I would never do that indeed, I only show things on my test server, and when I deploy I make sure every debug info is hidden.

davegreen100 · Accepted Answer · 2015-08-20 07:50:09Z

0

i don't know PHP so this is a bit of a guess, but i wonder if the

LIKE '%:zoekwaarde%'

is taking the variable name as a literal string and you many be better off with something like

LIKE '%' || :zoekwaarde || '%'

answered Aug 20, 2015 at 7:50

davegreen100

2,1253 gold badges15 silver badges25 bronze badges

2 Comments

McBurgerKong Over a year ago

Thanks for your fast response, I've tried this and still get no results in PHP. SQLDeveloper still gives results, even with the edit you suggested

davegreen100 Over a year ago

My only other suggestion would be to hard code in the values in your PHP code just to prove that it's a problem with the variables. But as i don't know PHP I will have to bow out on this, good luck with finding a solution.

halfer · Accepted Answer · 2015-08-20 09:05:01Z

0

(Posted on behalf of the OP.)

Thanks to halfer's answer, I was able to fix the problem. The fix:

$query_01 = "SELECT name , zipcity FROM  import_persons WHERE upper(name) LIKE :zoekwaarde AND status < 3 ORDER BY name";
$parameters = array(':zoekwaarde' => "%{$zoekwaarde}%", );

answered Aug 20, 2015 at 9:05

community wiki

halfer

1 Comment

halfer Over a year ago

OP, if you wish to add your own answer (so that you can possibly get upvotes for doing so) then let me know, so I can delete this one. Thanks!

Collectives™ on Stack Overflow

Query returns 0 results in php, but in oracle SQL developer it returns 52 results

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related