PublicDisplayName PublicDisplayName - 4 months ago 15
SQL Question

SELECT count(*) with OR operator

I'm trying to use

SELECT COUNT(*)
in a prepared statement.

Below is my PHP:

if(!($stmt = $link->prepare("SELECT COUNT(*) AS failed FROM LoginAttempts WHERE (email = ? OR IP = ?) AND LastLogin BETWEEN now() and subdate(now(),INTERVAL 5 MINUTE)"))){
}

$stmt->bind_param('ss', $email, $ip);

$stmt->execute();

$stmt->bind_result($failed);

$stmt->close();


What I'm trying to achieve from this, is when a user tries to login to their account (and provides incorrect login information) their IP, and the entered email address is logged in a table.

When an attempt is tried to login, using their email or IP, I count how many records match either their IP or email address.

The issue I'm having, is when accessing
$failed
the result is
NULL
even though there are records in the database within the last 5 minutes.

Where exactly am I going wrong? There are no errors in my apache error log, or with:

error_reporting(E_ALL);
ini_set('display_errors',1);


Thanks for your time

Edit: I needed to use $stmt->fetch(); - not sure how I missed that. Thanks to Saty for your comment!

Answer

Few mistake in your code

1) Not looking for errors

2) Close if condition at the end

3) Forget to fetch data form query result

You code would be

if (!($stmt = $link->prepare("SELECT COUNT(*) AS failed FROM LoginAttempts WHERE (email = ? OR IP = ?) AND LastLogin BETWEEN now() and subdate(now(),INTERVAL 5 MINUTE)"))) {
    /* bind parameters for markers */
    $stmt->bind_param('ss', $email, $ip);
    /* execute query */
    $stmt->execute();
    /* bind result variables */
    $stmt->bind_result($failed);
    /* fetch value */
    while ($stmt->fetch()) {
        printf("%s", $failed);
    }
    /* close statement */
    $stmt->close();
}
Comments