How can I write variables inside PHP query with LIMIT

Question

I'm trying to get some data from MySql database - but when I'm adding variables after LIMIT and LIMIT at all - I don't get anything from DB.

Where's the problem?

$start = $_POST["start"];
$limit = $_POST["limit"];
$link = mysqli_connect("localhost", "root", "root", "admins");
$result = mysqli_query($link , "SELECT * FROM pikabu_news WHERE id>'".$start."' LIMIT '".$start."','".$limit."'");

by using prepared statement. This way = SQL injection - and that's a BIG no no.. — treyBake, May 01 '19 at 13:53
`id` and your `limit` probably aren't related. That is the number of rows to return, and where to start the offset at. — user3783243, May 01 '19 at 13:58
SQL tables/resultsets are by SQL standards definition **orderless**, using `LIMIT` without `ORDER BY` is pretty much **meaningless**.. In fact using `LIMIT` without `ORDER BY` without atleast using a column that has a primary key or unique key would cause non deterministic (random) results — Raymond Nijland, May 01 '19 at 14:16

score -1 · Accepted Answer · answered May 01 '19 at 13:51

-1

 "SELECT * FROM pikabu_news WHERE id>'".$start."' LIMIT '".$start."','".$limit."'");

has to be

 "SELECT * FROM pikabu_news WHERE id>".$start." LIMIT ".$start.", ".$limit);

You don't need to give ' in limit values.

Passing parameters like this would lead to SQL Injection.

answered May 01 '19 at 13:51

Danyal Sandeelo

12,196
10
47
78

I'd do a prepared statement example (or mark as dupe to the other posts) to encourage safer querying :) – treyBake May 01 '19 at 13:54
OMG, the truth was so near. Thank You! – Никита Лямный May 01 '19 at 13:56
@НикитаЛямный welcome, mark it as answered if that solved your problem. – Danyal Sandeelo May 01 '19 at 13:56
SQL tables/resultsets are by SQL standards definition **orderless**, using `LIMIT` without `ORDER BY` is pretty much **meaningless**.. In fact using `LIMIT` without `ORDER BY` without atleast using a column that has a primary key or unique key would cause non deterministic (random) results – Raymond Nijland May 01 '19 at 14:16
limit is just used to limit the dataset, maybe, he is fetching the data for pagination, getting a limited dataset and sending it back via json or anything. There could be multiple reasons where it can be used even without order. @RaymondNijland – Danyal Sandeelo May 01 '19 at 14:19
i know how limit works @DanyalSandeelo Yes if you like playing russian roulette with your resultset, mine previous statement/comment is mentioned in the MySQL [manual](https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html) *"If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this: "* – Raymond Nijland May 01 '19 at 14:25
We have a system we CDRs (call detail records are added). The insertion takes place via a cron job. For example, I want to view those million records with no order, I just want to view them in the way they were stored. Calling a server side pagination would be fine there. Not sure if I am getting your point correctly or not. – Danyal Sandeelo May 01 '19 at 14:30

How can I write variables inside PHP query with LIMIT

1 Answers1