PHP: How do I get my IF statement to work with PDO select?

Question

I want my below PDO select to work with the bottom two IF statements?
The first IF I just want to make sure there is no error.
The second IF I want to check how many rows it returns. I know that this number of rows == 0 will not work.
Is there a way to do that?

try {
    $conn = new PDO('mysql:host=localhost;dbname=zs', 'zs', 'rlkj08sfSsdf');
    $conn ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

$stmt = $conn->prepare("SELECT * FROM zip WHERE zip_code =:zip1");
$stmt->bindValue(':zip1', $_POST[zipcode], PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);


if($rows = "") {
    echo "<p><strong>There was a database error attempting to retrieve your ZIP Code.</strong></p>\n";
}
if(number of rows == 0) {
    echo "<p><strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.</p>\n"; 
}

`fetchAll()` returns an array. You can use `empty()` or `count()` to test if if has anything in it. — Alex Howansky, Jun 28 '17 at 20:18
Dont consider zip codes `int`s, you will have issues with leading zeros. https://stackoverflow.com/questions/893454/is-it-a-good-idea-to-use-an-integer-column-for-storing-us-zip-codes-in-a-databas — chris85, Jun 28 '17 at 20:23
You should NEVER EVER use a $_POST variable in SELECT without proper filtering - this is calling for SQL injection! — Zac67, Jun 28 '17 at 20:24
@Zac67 he's using prepared statements. Your comment is absolutely false. — N.B., Jun 28 '17 at 20:25
@Zac67 The OP is not doing that.. This is the correct implementation. — chris85, Jun 28 '17 at 20:25
Just a side note - I upvoted this, not because it's an excellent question, but because it's really one of rare PDO related questions where the OP correctly sets PDO in exception mode, uses `bindValue` instead of `bindParam` and even does explicit type binding. It's refreshing to see that at least some people read the manual right. — N.B., Jun 28 '17 at 20:27
@Barmar he did, and I did correct that in the answer I posted. Still, it's much better use of PDO than most of php + mysql related questions around here. — N.B., Jun 28 '17 at 20:46

score 1 · Accepted Answer · answered Jun 28 '17 at 20:22

You're interested only in whether there are records containing a particular value. It makes no sense to select everything and count the records in PHP. It's a waste of resources. Imagine what happens if there's a million records.

Solution you're after is to simply ask your database about the COUNT of rows containing a particular value. Your code should be quite simple:

$stmt = $conn->prepare("SELECT COUNT(*) AS num_rows FROM zip WHERE zip_code = :zip");
$stmt->bindValue(':zip', $_POST['zipcode'], PDO::PARAM_INT);

$stmt->execute();

$count = (int)$stmt->fetchColumn();

if($count)
{
    echo "Success";
}
else
{
    echo "Bummer";
}

Notes:

if successful, the above query will always return 1 row with 1 column, named num_rows which will be 0 for no matching records or an integer larger than 0 if there are records. If you use MySQL native driver with PHP, PHP will correctly represent this value as integer internally. I deliberately put typecasting in, you can remove it (the (int) part) if you have MySQL ND.
if something goes wrong during query execution, an exception will be thrown. The snippet doesn't cover that. You correctly set PDO in exception mode, and along with using bindValue instead of bindParam, this implies you did your research right and you're using PDO correctly which means that error handling should be implemented easily by you in this particular case.

PHP: How do I get my IF statement to work with PDO select?

1 Answers1