How to do a select with two parameters after the WHERE using PDO?

Question

I try to select data from a database, but I'm unable to get it when I have two parameters after the WHERE.

Code that works:

$conn = null;
$host = 'localhost';
$db   = 'database';
$user = 'root';
$pwd  = 'root';

$auth = 'EP';
$nr = 2007;

try {
    $conn = new \PDO('mysql:host='.$host.';dbname='.$db, $user, $pwd);      
    $stmt = $conn->prepare('SELECT family FROM table WHERE nr = :nr');
    $stmt->execute(array('nr' => $nr));

    while($row = $stmt->fetch(PDO :: FETCH_ASSOC)) {
        echo '<pre>';
        print_r($row);
        echo '</pre>';
    }
}
catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

but when I use following select it doesn't work:

SELECT family FROM table WHERE auth = :auth AND nr = :nr

I think it's a problem in the line

    $stmt->execute(array('nr' => $nr));

When I do the following I have no result on the screen:

    $stmt->execute(array('nr' => $nr, 'auth' => $auth));

Has anybody an idea what I'm doing wrong?

Do you get any error? Maybe you didn't have any row that matches with `nr` and `auth`, check the query too. — Marcio Mazzucato, Nov 02 '14 at 16:05
Olá @MarcioSimao no error at all!!! Strange that when I write only: $stmt = $conn->prepare('SELECT * FROM publn_data WHERE publn_nr = :publn_nr'); $stmt->execute(array(':publn_nr' => $publnNr)); it works... Alguma idéia? — Aloysia de Argenteuil, Nov 02 '14 at 16:28
Opa! Searching directly in your DB you get any result? Are you using a local or remote server? — Marcio Mazzucato, Nov 02 '14 at 16:33
@AloysiadeArgenteuil what are the column names of the table ? — CS GO, Nov 02 '14 at 17:13
Hello @AncientGeek, the name of the columns are nr and auth. — Aloysia de Argenteuil, Nov 02 '14 at 18:07
@MarcioSimao, direct in phpMyAdmin it works. The select must be right. — Aloysia de Argenteuil, Nov 02 '14 at 18:08
Is there any other way to get data from the db using pdo? Do you guys know any example in the net which I could use? — Aloysia de Argenteuil, Nov 02 '14 at 18:09
@AloysiadeArgenteuil just try `var_dump($row)` without using while loop. — CS GO, Nov 02 '14 at 18:58
Apparently everything is okay, check if everything is okay with PHP installation and PDO drivers — Marcio Mazzucato, Nov 02 '14 at 23:56
Thank you all for your help! The problem was that what I searched was AB 2007, but the values were AB and 0002007. With only 2007 it worked in a SQL query in phpmyadmin and with mysqli as well. But somehow it doesn't work with pdo if we write only 2007 and not the entire value of the database field, namely 0002007. When I write EP and 0002007 it works fine. Thank you all again! — Aloysia de Argenteuil, Nov 08 '14 at 13:44
Does this answer your question? [My PDO Statement doesn't work](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work) — Dharman, Mar 21 '20 at 00:44

score 0 · Answer 1 · answered Nov 02 '14 at 14:30

0

CHANGE THIS

stmt->execute(array('nr' => $nr, 'auth' => $auth));

TO

$stmt->execute(array(':nr' => $nr, ':auth' => $auth));

: is a small typo error but PDO is Serious :( and the parameters will be empty.

answered Nov 02 '14 at 14:30

CS GO

914
6
20

1

This is incorrect - internally a colon will be added to the parameter if it's missing (https://github.com/php/php-src/blob/PHP-5.5/ext/pdo/pdo_stmt.c#L363) – dave Nov 02 '14 at 14:34
@dave But if i didn't add a colon PHP throws error :( why would be that ? – CS GO Nov 02 '14 at 14:37
Hey @AncientGeek, I followed you, but still no array on screen. Only when I use just $nr... The select in phpmysql works just fine... – Aloysia de Argenteuil Nov 02 '14 at 16:30
Example 2 of PHP Tutorial says the same: http://php.net/manual/en/pdostatement.execute.php It should work. @dave in the Manual it shows the solution of Ancient Geek. Is this really incorrect as you say? – Aloysia de Argenteuil Nov 02 '14 at 16:32
1

@AloysiadeArgenteuil - I linked to the PHP source code - I'm 100% sure it adds the colon for you if it's not there. – dave Nov 03 '14 at 02:38

How to do a select with two parameters after the WHERE using PDO?

1 Answers1