0

I'm new to PDO and I'm struggling to use the 'where' statement in a prepared statement. Below is the code I tried. It returns Data could not be retrieved from the database.

Can anyone tell me what the issue is? Anything to try etc?

Appreciate it!

$user_id = 1; try { $query = $db->prepare(" SELECT title, img, id FROM listings ORDER BY id DESC WHERE userID = :user_id LIMIT 2"); $query->bindParam(':user_id', $user_id, PDO::PARAM_INT); $query->execute(); } catch (Exception $e) { echo "Data could not be retrieved from the database."; exit; } $data = $query->fetchAll( PDO::FETCH_ASSOC); return $data;

UPDATE: I swapped the order & where clause, but it still returns nothing (as oppose to the previous error message).

Improve this question
4
  • 2
    Why don't you inspect the exception, $e, to see what was the problem? Commented Dec 10, 2013 at 11:28
  • I suggest you to show an example to us through this: sqlfiddle.com at least you can see if the query is ok. Commented Dec 10, 2013 at 23:46
  • Please don't do that with exceptions. Just let them go at this low level. Catch them higher up if you must but for development work, you're going to want to know all the details of a PDOException. Also, if you've changed your code, update your question Commented Dec 10, 2013 at 23:50
  • @Fire-Dragon-DoL sqlfiddle.com/#!2/c6c94/5 Commented Dec 11, 2013 at 0:02

3 Answers 3

Reset to default
4

Rewrite your query like this. [You need to put ORDER BY clause after the WHERE ]

 $query = $db->prepare(" SELECT title, img, id FROM listings WHERE userID = :user_id ORDER BY id DESC LIMIT 2");
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, that removes the error message. But now it returns nothing, just a blank screen. Could there be an issue in the other code?
@AlexDanieli Yes. We can't see what you do with $data once it's returned
@Phil it's inside a function, even if i just do this: print_r(get_products_userDashboard()); it returns nothing
@AlexDanieli Nothing as in nothing at all? Try var_dump instead of print_r. Also, remove the try and catch block
@Phil cheers, got it working. There was an issue with starting the session twice (a file was getting included twice).
1

ORDER BY id DESC need to be added after where condition.

$query = $db->prepare(" SELECT title, img, id FROM listings WHERE userID = :user_id ORDER BY id DESC LIMIT 2");

Mysql Generic Select syntax:

 SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
Improve this answer

1 Comment

Thanks, that removes the error message. But now it returns nothing, just a blank screen. Could there be an issue in the other code?
0

where clause should be before the order clause Try this 

$query = $db->prepare(" SELECT title, img, id FROM listings WHERE userID = :user_id ORDER BY id DESC LIMIT 2");
Improve this answer

1 Comment

Thanks, that removes the error message. But now it returns nothing, just a blank screen. Could there be an issue in the other code?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.