How to limit the number of SQL lines according to a variable?

Asked

Viewed 66 times

-1

I have an SQL query in PHP to get the information according to the line number of a file entered by the user. I need to take this number of rows of a variable and replace it with the variable in SQL.

Currently I have this code in the function:

$conn = $this->connect();
    $sql =  " select * from requisicao r 
    inner join usuario u on r.id_requisitante = u.id_usuario 
    left join aprovador a   on a.id_aprovador = r.id_aprovador 
    left join categoria_requisicao cr on cr.id_cat_requisicao = r.id_cat_requisicao
    left join categoria c on  c.id_categoria = cr.id_categoria 
    left join sub_categoria_requisicao scr on scr.id_sub_cat_req = r.id_sub_cat_req
    left join sub_categoria sc on  sc.id_sub_categoria = scr.id_sub_categoria
    left join fornecedor_requisicao fr on fr.id_fornecedor_requisicao = r.id_fornecedor_requisicao
    left join fornecedor f on f.id_fornecedor = fr.id_fornecedor
    where r.id_requisitante = :id_usuario ORDER BY id_requisicao DESC limit :numeroL ";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':id_usuario', $id_usuario);
    $stmt->bindParam(':numeroL', $numeroL);
    $stmt->execute();
    $result = $stmt->fetchAll();
    return $result;

But it makes a mistake:

Fatal error : Uncaught Pdoexception: SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax;

php mysql

  • have you tried to display how the query looked in "$sql" and see if there are any errors, or still take the query and try to run directly in the database? By the message the query was badly formed, it may be time to replace the parameters. Where the variable comes from $numeroL? ask in your question

  • The query works in sql when I do not put the variable and put the number itself, when I try to use mysql itself to pass a variable of the same error. The variable comes from another function I tested display it and ta picking the right value.

  • Check whether the parameters :id_usuario and :numeroL are with values. I noticed that in ORDER BY id_requisicao the field id_requisicao does not appear in the rest of the query, should not be ORDER BY r.id_requisitante?

2 answers

-1

1º Deactivate the PDO::ATTR_EMULATE_PREPARES

What prevents the values passed by ->execute([...]) always appear as strings.

$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

2º LIMIT must receive an INT as a parameter

Try changing your line to:

$stmt->bindParam(':numeroL', intval($numeroL), PDO::PARAM_INT);

So the LIMIT value is an integer.

-3

If you want to take some field from your database you must do a variable to receive the value, for example:

$var = 'select campoquerpegodado from your table id_record = 1 (you should know which one is)

From that moment on, you have the value you want in $var, now in your role you must concatenate, for example:

"SELECT * FROM products WHERE categoria=' { $var } '";.

Then when registering you will have the value you want to replace.

  • The variable I’m already taking from php itself, it already takes the value only when I pass it in the "ORDER BY id_requisicao DESC limit :numeroL" error, says that can not pass variable in desc limit

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.