Difference in outcome query between PhpMyAdmin en PDO

Question

I have the following query being prepared in PHP/PDO.

SELECT
    count(distinct(p.accid)) as aantal
FROM 
    winter_accommodaties_prijzen p
INNER JOIN winter_accommodaties a ON (a.id = p.accid)
INNER JOIN winter_dorpen d ON (d.id = a.did)
INNER JOIN winter_gebied g ON (g.id = d.gid)
INNER JOIN winter_accommodaties_types t ON (a.tid = t.id)
WHERE
    g.lid IN(:lid0) AND 
    g.min_hoogte >= :hoogte AND
    (g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart) >= :km AND 
    d.hoogte_dorp >= :hoogte_dorp AND 
    d.afstand_utrecht <= :afstand_utrecht AND 
    prijs < : max_prijs AND 
    p.persons >= :aantal_personen

Then I bind the following array to this query:

 Array
 (
  [:lid0] => 1
  [:hoogte] => 500
  [:km] => 50
  [:hoogte_dorp] => 500
  [:afstand_utrecht] => 2500
  [:max_prijs] => 1200
  [:aantal_personen] => 4
 )

Running this via PHP I get as a result for 'aantal': 97 (results). If I run the above query, replacing all the variables with their corresponding value, I get via PhpMyAdmin 124 results. Running the same query on the old fashion way in PHP using mysql_query, I also get 124 results.

What am I missing in the PDO query that results in the difference?

UPDATE to the request of AL_ => Creation of the PDO connection:

$conn = new PDO(DB_DSN, DB_USER, DB_PASSWORD);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "See query above"

$query_pag_no = $conn->prepare($query); // the above query
$query_pag_no->execute($bind_array);    // the above array

$aantal_resultaten = $query_pag_no->fetchColumn();

The outcome in $aantal_resultaten is then 97 (rows).

Better if you post the pdo object you're using, from its creation to the query execution. — Al_
Please print_r() the contents of the actual array and post them above. — Michael Berkowski
It's really weird. I'd debug the query at mysql level. Are you on a dev environment and have root access to mysql? Then set general_log = 1 in my.cnf. Mysql will log all received queries to the log file. Compare the queries when using the pdo and mysql_*. — Al_

Ander2 Ander2 · Accepted Answer · 2012-10-21T14:44:46

0

votes

:min_personen looks like is not defined in the param Array. Check it out.

Difference in outcome query between PhpMyAdmin en PDO

2 Answers