How to do a form with prepared statement using LIMIT?

Question

I have been struggling with how to do a search script with prepare statement of php, Can you please guide me?

I have tried to do this but for some reason doesn't work when use the LIMIT function.

Below is my current script and I have these issue:

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 '10' at line 4' in C:\wamp\www\training\modules\training\index7.php:36 Stack trace: #0 C:\wamp\www\training\modules\training\index7.php(36): PDOStatement->execute() #1 {main} thrown in C:\wamp\www\training\modules\training\index7.php on line 36

I have edited this question taking your sugestions but I don't know what happend.

php code:

$maxRows = 10;
$pageNum = 0;
$startRow = $pageNum * $maxRows;
$sqlq = "SELECT ent_entrenamientos.id, ent_entrenamientos.descripcion, ent_entrenamientos.duracion, ent_instructores.proveedor, CONCAT(ent_instructores.nombre_pila, ' ', ent_instructores.apellido_paterno ) AS instructor, ent_entrenamientos.vencimiento, ent_tipos.descripcion AS tipo_entrenamiento, ent_entrenamientos.nivel_alerta  FROM ent_entrenamientos
   LEFT JOIN ent_instructores ON ent_entrenamientos.ent_instructor_id = ent_instructores.id
   LEFT JOIN ent_tipos ON ent_entrenamientos.ent_tipo_id = ent_tipos.id
    WHERE ent_entrenamientos.borrado_logico = FALSE AND ent_entrenamientos.descripcion LIKE :descripcion LIMIT :startow :maxrows ";
 $stmtq = $DB_con->prepare($sqlq);
 $stmtq->bindValue(':descripcion', "%$descripcion%", \PDO::PARAM_STR);
 $stmtq->bindValue(':startrow', $startRow, \PDO::PARAM_INT);
 $stmtq->bindValue(':maxrows', $maxRows, \PDO::PARAM_INT);
 $stmtq->execute();

I apreaciate your help in advance

can use LIKE :var , then just assign the $var = %$var$% prior — clearshot66, Jun 01 '17 at 20:19
Thank you for answer, but Where can I type this? `within bindParam` — Gerardo Ponce, Jun 01 '17 at 20:22
Just like normal bind `:var` in the query, but prior to your bindParam do `$var = "%".$var."%";` — clearshot66, Jun 01 '17 at 20:24
I have already done that, but doesn´t work, I edited my question just I added the variable. — Gerardo Ponce, Jun 01 '17 at 20:25

score 1 · Answer 1 · answered Jun 01 '17 at 20:29

1

$statement->bindValue(':description', "%$var%", \PDO::PARAM_STR);

This will do the work for you. basically bindValue will sanitise the string and while using like we give the data as string ( like '%test%').

And rather then using sprintf you can directly write the query and bing the required values.

$query = "select * from <table name> where descripcion LIKE :descripcion limit :limit :offset";
$statement = $DB_con->prepare($query);
$statement->bindValue(':description', "%{$var}%", \PDO::PARAM_STR);
$statement->bindValue(':limit', $limit, \PDO::PARAM_INT);
$statement->bindValue(':offset', $offset, \PDO::PARAM_INT);
$statement->execute();

answered Jun 01 '17 at 20:29

Ratan Phayade

86
3

Thank you Ratan, I am trying to follow your advice but How can I define the `$limit` var if in your example is `PARAM_INT` Can I do this? `$limit = $startRow." ".$maxRows;` – Gerardo Ponce Jun 01 '17 at 20:53
Already I understand how to define the `:limit` and `offset`, I did this whitin query I have `... LIKE :description LIMIT :limit :offset` **but** I have an issue... _Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in C:\wamp\www\training\modules\training\index7.php:36 Stack trace: #0 C:\wamp\www\training\modules\training\index7.php(36): PDOStatement->execute() #1 {main} thrown in C:\wamp\www\training\modules\training\index7.php on line 36_ – Gerardo Ponce Jun 02 '17 at 17:09
Do you please have any suggestion? Thank you in advance – Gerardo Ponce Jun 02 '17 at 17:12
I have updated my question :) – Gerardo Ponce Jun 02 '17 at 17:20
Thank you @Ratan I have already seen what was the mistake just I will add a comma `WHERE ent_entrenamientos.borrado_logico = FALSE AND ent_entrenamientos.descripcion LIKE :descripcion LIMIT :startrow, :maxrows` inside of my query. Kind Regards! – Gerardo Ponce Jun 02 '17 at 17:54
This resolve my issue – Gerardo Ponce Jun 02 '17 at 17:54

How to do a form with prepared statement using LIMIT?

1 Answers1