I have two questions about Prepared Statements from the code below:
#1:
The purpose of the first $query is to determine if the authorization code supplied is both accurate and also live
- When the authorization code is generated it is given a time frame when it must be used in. The columns `start_date` and `end_date` are both DATETIME. This is why I am using NOW() to check the “shelf life”.
My question: Is there a better way to confirm the record was found than using:
if ( $row['authorization_code'] == $authorization_code ) {
In another way of executing a mySQL database query using PHP I can count the # rows the result brought with the command “mysql_numrows”. I don’t know how to do this in Prepared Statements. I wonder if comparing the # of rows found is a better method? or what other programmers are using?
#2:
How can I tell if the UPDATE $query executed successfully? I am wanting to do something like:
echo “Update Successful – No changes were required during this review”;
if the UPDATE is successful --- otherwise I need to direct the user to try again with a different message:
echo “Update was unsuccessful – Follow this link to try again”;
I am still getting use to Prepared Statements, this is why I am asking these questions --- Thank you for helping me. Ron
===
<?php
$dsh = 'mysql:host=localhost;dbname='.$database;
$dbh = new PDO($dsh, $username, $password);
#query for the authorization code
$query = "SELECT `authorization_code` FROM `directory_listing_update_authorizations` WHERE NOW() BETWEEN `start_date` AND `end_date` AND `authorization_code` = :authorization_code AND `directory_entries_reference` = :directory_entries_reference LIMIT 1;";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':directory_entries_reference', $directory_entries_reference, PDO::PARAM_STR);
$stmt->bindValue(':authorization_code', $authorization_code, PDO::PARAM_STR);
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
while ($row = $stmt->fetch()) {
if ( $row['authorization_code'] == $authorization_code ) {
#update directory_entries.last_review with today's date
$query = "UPDATE `directory_entries` SET `last_review` = NOW() WHERE `reference` = :directory_entries_reference LIMIT 1;";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':directory_entries_reference', $directory_entries_reference, PDO::PARAM_STR);
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
} else {
#failure, direct user to request new authorization code or login manually
}
}
www.TheVerseOfTheDay.info
