Not displaying any results from the database

[CSS-Regex]

I don't know if I've got the MySQL statement right, when I don't have the LIMIT and OFFSET, it displays all the results, but need the LIMIT for pagination

CREATE TABLE photos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  date_taken DATE,
  caption VARCHAR(255),
  type VARCHAR(255)
);

This is in my MySQL statement 

SELECT * FROM photos WHERE LIMIT :limit_page OFFSET :offset

Is the reason for lack of results due to the lack of column names? If so, is there anyway I can display the lot, but only limited to the LIMIT statement?

This is the MySQL version

mysqlnd 8.0.30

Edited February 11 by CSS-Regex

[CSS-Regex]

Don't worry, I found the problem, and it wasn't the MySQL statement

[gizmola]

On 2/11/2024 at 4:18 PM, CSS-Regex said:

Don't worry, I found the problem, and it wasn't the MySQL statement

The SQL statement you had was definitely wrong.  LIMIT constrains a result set, and is not a valid part of a WHERE clause.  Whatever you might have fixed, you also must have fixed that issue.

[Strider64]

Here's my query ->
 

 $sql = 'SELECT * FROM '. $this->table . ' WHERE page =:page AND category =:category ORDER BY id DESC, date_added DESC LIMIT :perPage OFFSET :blogOffset';

it might help?

[Barand]

Aside from the empty WHERE clause mentioned by @gizmola above, you may also have a problem with passing your limit and offset parameters.

You don't show us the relevant connection and execution code, but If your PDO connection code does not set ATTR_EMULATE_PREPARES to false (the default is true) then you cannot pass the limit and offset as parameters in an array when executing. You can, however, pass them using bindParam()

You should always set this attribute to false

[CSS-Regex]

On 2/11/2024 at 11:51 PM, CSS-Regex said:

SELECT * FROM photos WHERE LIMIT :limit_page OFFSET :offset

Yeah, well this isn't correct, its this as defined in the MySQL manual

SELECT * FROM photos LIMIT :offset, :limit_page