CODEWITHSUNDEEP
phpmysqlpdo
Liam
Liam

Reputation: 9855

Get all values from table using where operator

I am having trouble trying to retrieve all records from my table where the user ID isn't equal to that of the session id, and where value in column X is greater than that of column Y. I'd like to return my results in a loop.

My code so far is...

// Select all users that arent the current session users and have a 
// higher integer in bank column than that of the credits column

$stmt = $conn->prepare('SELECT * FROM users WHERE user_id!=? AND user_bank <= user_credits');
$stmt->bindParam(1, $uid, PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

// Return all records in a loop

How can I accomplish this?

Error Log

[Wed May 29 21:08:49 2013] [error] [client 89.240.62.228] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''19497918' AND user_bank <= user_credits' at line 1' in ...

Upvotes: 0

Views: 85

Answers (3)

Insyte
Insyte

Reputation: 2236

Follow up to my comments...

After seeing this before with PDO::PARAM_INT the solution I have used in the past is to type cast your variable like so:

$stmt = $conn->prepare('SELECT * FROM users WHERE user_id!=? AND user_bank <= user_credits');
$stmt->bindParam(1, (int)$uid, PDO::PARAM_INT); // <-- here
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

Upvotes: 0

Achrome
Achrome

Reputation: 7821

Two ways to do it.

Put a loop around $row assignment.

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   //do something
}

Or, just use fetchAll() and then loop on it. (This is bad for large databases)

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row) {
    //do something
}

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80647

The loop will be something like:

while( $row = $stmt->fetch(PDO::FETCH_ASSOC) ) {
    print_r( $row );
}

It is considered good practice to select known columns from table. Also, you can use <> for inequality operator.

SELECT user_id, user_bank, user_credits
FROM users 
WHERE user_id <> ? 
    AND user_bank <= user_credits

Upvotes: 1

Related Questions