CODEWITHSUNDEEP
phphtmlmysqldatabasepdo
user3537990
user3537990

Reputation:

MYSQL result not returning correctly

So I have a piece of code that will check if you have followed a user or not. And basically let you follow them if you haven't. So here it is 

if($_SESSION['loggedIn'] == true){
    $result = $con->prepare("SELECT * FROM followers WHERE follow_from = :username AND follow_to = :post_id");
    $result->bindParam(':username', $username);
    $result->bindParam(':post_id', $follower);
    $result->execute();
    $reprint = $result->fetchAll(PDO::FETCH_ASSOC);
}
print_r($reprint);
if($reprint < 1){
    $stmt = $con->prepare("INSERT INTO followers (follow_from, follow_to) VALUES (:ff, :ft)");
    $stmt->bindValue(':ff', $follower, PDO::PARAM_STR);
    $stmt->bindValue(':ft', $username, PDO::PARAM_STR);
    $stmt->execute();
}
else{
    echo 'Error';
    exit();
}
//Display follower
$stmt1 = $con->prepare("SELECT COUNT(*) AS count FROM followers WHERE follow_to = :username");
$stmt1->bindValue(':username', $username, PDO::PARAM_STR);
$stmt1->execute();
$likes = $stmt1->fetchAll(PDO::FETCH_ASSOC);
print_r($likes);

So when I run it once. I get the else statement echoed. My question is why does this happen? In the database I have no record, so I'd expect it to go in once. I get no errors at all. loggedIn is true. And variables are being passed through successfully. Any ideas?

Upvotes: 4

Views: 93

Answers (3)

O. Jones
O. Jones

Reputation: 108651

You're misusing the result you get from fetchAll(). It's an associative array, not a scalar value. It could, as you've probably guessed, be empty.

But, more significantly than that, your code has a potential race condition. What happens if two different sessions are trying to set this same followers row? (Admittedly, in a small system that is unlikely, but in a large system it might happen).

What you actually do is just the INSERT operation. If your followers row has a unique key on the (follow_from,follow_to) columns, then, if that row is already there you'll get a 'Duplicate entry' error on the INSERT. Otherwise it will just happen. You can just ignore the 'Duplicate entry' error, because all you want is for that row to make it into that table.

So your code would go like this:

$stmt = $con->prepare("INSERT 
                         INTO followers (follow_from, follow_to)
                       VALUES (:ff, :ft)");
$stmt->bindValue(':ff', $follower, PDO::PARAM_STR);
$stmt->bindValue(':ft', $username, PDO::PARAM_STR);
$result = $stmt->execute();
if ($result) {
     /* this follow pair was successfully added */
} else {
     /* MySQL may return the error 'Duplicate entry' */
     if (false == stripos($stmt->errorCode,'Duplicate')){
        echo 'Something failed in the insert: ' . '$stmt->errorCode';
     }
     else {
         /* this follow pair was already in your table */
     }
}

Pro tip: Don't use SELECT * in software; it can mess up query optimization; it often sends more data than you need from the server to your program, and it makes your program less resilient if your change your table definitions.

Pro tip: If you must count rows matching a particular WHERE statement, use COUNT() rather than fetching the rows and counting them in the client. What if you get a million rows?

Upvotes: 4

Jonast92
Jonast92

Reputation: 4967

PDOStatement::fetchAll

PDOStatement::fetchAll — Returns an array containing all of the result set rows

If you check the size of the array then you would actually know if something happened.

Using proper error handling can tell you if something's failing deep down:

try
{
    ...
}
catch (PDOException $e)
{
    echo $e->getMessage();
}

You will need to enable PDO error-displaying:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

If checking the size of the array doesn't do it and you get no errors then it's simply some logic error.

Most likely the logic error is that 

$reprint = $result->fetchAll(PDO::FETCH_ASSOC);

doesn't get executed (wrapping the error handling around that should tell you why), so 

$reprint = $result->fetchAll(PDO::FETCH_ASSOC);

isn't given a proper value, meaning you'll always hit the else statement.

Edit

Your original problem was that "So when I run it once. I get the else statement echoed. [...] In the database I have no record" but now you're saying "It adds the record, but doesn't limit it to me one".

Can you be more clear about the actual, current, problem?

Upvotes: 1

Vladimir Hraban
Vladimir Hraban

Reputation: 3581

You'd want to use count($reprint) other that a direct comparison. $reprint is an array, not a number

if(count($reprint) < 1)
{
    $stmt = $con->prepare("INSERT INTO followers (follow_from, follow_to) VALUES (:ff, :ft)");
    $stmt->bindValue(':ff', $follower, PDO::PARAM_STR);
    $stmt->bindValue(':ft', $username, PDO::PARAM_STR);
    $stmt->execute();
}
else
{
    echo 'Error';
    exit();
}

Upvotes: 1

Related Questions