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

Question

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?

If that is the actual code you have, just look at the colours that SO has assigned everything. You have miss matched quotes. The script itself is probably silently failing. Check PHPs error logs for more details. — Justin Wood, Sep 12 '13 at 20:47
hope it helps http://stackoverflow.com/questions/11738451/error-while-using-pdo-prepared-statements-and-limit-in-query — M Khalid Junaid, Sep 12 '13 at 20:48
Negative he didn't nail it. You're right about the quotes but that didn't solve the LIMIT issue. — user2362601, Sep 12 '13 at 21:23

score 3 · Answer 1 · answered Sep 12 '13 at 20:48

$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('

stop voting this up. this is a syntax error but no it didn't fix the problem...try again? — user2362601, Sep 12 '13 at 21:02

score 0 · Answer 2 · answered Sep 12 '13 at 20:54

0

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

answered Sep 12 '13 at 20:54

MangO_O

393
1
3
15

try this $query = $conn->prepare(' SELECT * FROM notifications WHERE (needs=:username OR worker=:username) ORDER BY CASE WHEN needs=:username THEN needsread ELSE workerread END ORDER BY time DESC LIMIT 5') – MangO_O Sep 12 '13 at 21:15
That gave me 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 'ORDER BY time DESC LIMIT 5' at line 1' in /home/content/17/9932517/html/project_y/mobile_site/getnotes.php:39 Stack trace: #0 /home/content/17/9932517/html/project_y/mobile_site/getnotes.php(39): PDOStatement->execute() #1 {main} thrown in /home/content/17/9932517/html/project_y/mobile_site/getnotes.php on line 39 – user2362601 Sep 12 '13 at 21:18
1

@user2774299: `LIMIT 5` and `LIMIT 0,5` are functionally identical. This is NOT the problem. – Marc B Sep 12 '13 at 21:19

score 0 · Answer 3 · answered Sep 12 '13 at 21:12

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);

While @MarcB's code may not solve ALL the problems in your original question, he isn't wrong. "You cannot use a named parameter marker of the same name twice in a prepared statement." - http://php.net/manual/en/pdo.prepare.php You should update your question. — Lumberjack, Sep 12 '13 at 23:54

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

3 Answers3