I'm trying to do a SELECT , where among other clauses, I use the LIMIT one :
$sql = "SELECT id, nombre, telefono, dni FROM clientes WHERE usuario = ? AND nombre LIKE ? AND telefono LIKE ? AND dni LIKE ? LIMIT ?, ?";
$stmt= $db->prepare($sql);
$stmt->execute([$id, $name, $tel, $dni, $from, $rows]);
$customers = $stmt->fetchAll();
Before finishing, clarify that I have tested the code without the limit part and it works correctly, in addition, I have also made one var_dump()
of the values that I pass to the LIMIT and they are the following:
$from = int(0)
$rows = int(10)
The problem is that basically when using the LIMIT it goes from giving me more than 17 records to giving me 0 records .
Another test that I have carried out is to change $from
for a 0 and $rows
for 10, and there are no results either, unlike when I directly modify those ?
of the select for the same values, 0 and 10, where the results do appear.
Apart from other propestas, I recommend the following:
:foo
bindParam()
with the following attributes to indicate the values to be interpolated::foo
which will be the name marker$foo
which is the variable that brings the value to interpolatePDO::PARAM_INT/PDO::PARAM_STR
which indicates the expected data type bring the variable to interpolateSo your query could look like this:
In
phpdelusions
report a problem with the clauseLIMIT
and PDO : when the emulated preparations are turned on (true
) it is not possible to pass in the array ofexecute
values for the clauseLIMIT
.In fact, an error similar to this occurs:
Two solutions are possible:
1. Turn off emulated preparations
If in the settings you
PDO
set the emulated preparations tofalse
, that is, you turn them off, it will work for you.In the context you're in, you can turn them off by doing this:
However, it is convenient that you turn off the emulated preparations in the connection itself , that is, when you create the instances of
$db
, there you can pass an array of options as the fourth parameternew PDO()
that includes this value:PDO::ATTR_EMULATE_PREPARES => false
, thus, every time you connect to PDO they will be turned off.It is IMPORTANT to do it, in my answer about SQL Injection it is shown that, in certain scenarios, even if you use prepared queries, you can get malicious code emulating deceptive prepared queries.
2. Use bookmarks
:nombre
This solution is also feasible as @Aprendiz indicates in his answer. Here the data is passed one by one and the syntax error that would cause the use of
LIMIT ?, ?
: