CODEWITHSUNDEEP
phpmysqlsqllimit
user2362601
user2362601

Reputation: 361

SQL statement won't limit. Show's no results

I'm creating a web app and I'm trying to limit the number of results that come in. When I do the query all the results come back but if I put LIMIT 5 at the end of the statement, then no results come back. Here is my code:

$query = $conn->prepare('SELECT * FROM notifications WHERE (needs=:username OR worker=:username) ORDER BY CASE WHEN needs=:username THEN needsread ELSE workerread END, time DESC LIMIT 5');

$query->bindParam(':username', $username);
$query->execute();

echo "<div id='notes_title' style='background-color: #333333; padding: 10px; text-align: center; font-weight: lighter; letter-spacing: 1px;'>Notes</div>";
$te = 0;
while ($rows = $query->fetch()) {

$needs = $rows['needs'];
$id = $rows['ID'];
$worker = $rows['worker'];
$title = $rows['title'];
$needsread = $rows['needsread'];
$workerread = $rows['workerread'];
$time = $rows['time'];
$type = $rows['type'];

Any clues as to why it's not working?

Upvotes: 0

Views: 168

Answers (3)

Marc B
Marc B

Reputation: 360672

You're using the same named parameter, :username, THREE TIMES in your query. This is not permitted (2nd paragraph of the man page "Description" section. Each placeholder/parameter must be UNIQUE within the query.

SELECT [...snip...] WHERE (needs=:username OR worker=:username)
                                  ^^^^^^^^           ^^^^^^^^^
ORDER BY CASE WHEN needs=:username THEN [...snip...]
                         ^^^^^^^^^

You could simplify the query and eliminate ONE of the duplicates by having

WHERE :username IN (needs, worker)

but then you're still stuck with the 2nd usage in the ORDER clause. You'll have to use two different names and bind the same value twice, e.g.

SELECT ... WHERE :username1 ... ORDER BY CASE when needs=:username2

$query->bindParam(':username1', $val);
$query->bindParam(':username2', $val);

Upvotes: 0

MangO_O
MangO_O

Reputation: 423

I'm gonna guess you're using MySQL since you're doing a webapp, so

try LIMIT 0, 5 This means you want the first 5 items starting from none

-J

Upvotes: 0

Lumberjack
Lumberjack

Reputation: 488

$query = $conn->prepare('SELECT * FROM notifications WHERE (needs=:username OR worker=:username) ORDER BY CASE WHEN needs=:username THEN needsread ELSE workerread END, time DESC LIMIT 5");

There is a bug in your code. You start your Query String with a single quote, and end it with a double quote. Your actual query string is:

'SELECT * FROM notifications WHERE (needs=:username OR worker=:username) ORDER BY CASE WHEN needs=:username THEN needsread ELSE workerread END, time DESC LIMIT 5"); $query->bindParam('

Upvotes: 3

Related Questions