Query error: 'SQLSTATE[42000]

Question

I’m making database data call with php and PDO , but whenever I do it with bindparam(); give me the following mistake:

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 ''SELECT * FROM data' LIMIT 0,'5'' at line 1' in D: xampp htdocs paginacao index.php:25 Stack trace: #0 D: xampp htdocs paginacao index.php(25): Pdostatement->execute() #1 {main} thrown in D: xampp htdocs paginacao index.php on line 25

Down here have the code, to help:

<?php
require_once "class.user.php";

$liga = new USER();
$busca = "SELECT * FROM dados";

// número de registros por página

$total_reg = "5";
$pagina = $_GET['pagina'];

if (!$pagina)
    {
    $pc = "1";
    }
  else
    {
    $pc = $pagina;
    }

$inicio = $pc - 1;
$inicio = $inicio * $total_reg;
$limite = $res = $liga->runQuery(':busca LIMIT :ini,:totalreg');
$res->bindparam(':busca', $busca, PDO::PARAM_STR);
$res->bindparam(':ini', $inicio, PDO::PARAM_INT);
$res->bindparam(':totalreg', $total_reg, PDO::PARAM_STR);
$res->execute();
$todos = $res1 = $liga->runQuery(":busca");
$res1->bindparam(":busca", $busca);
$res1->execute();
$tr = $res1->rowCount($todos);

// verifica o número total de registros

$tp = $tr / $total_reg;

// verifica o número total de páginas // vamos criar a visualização

while ($dados = $limite->fetch(PDO::FETCH_ASSOC))
    {
    $nome = $dados["nome"];
    echo "Nome: $nome<br />";
    } // agora vamos criar os botões "Anterior e próximo"
$anterior = $pc - 1;
$proximo = $pc + 1;

if ($pc > 1)
    {
    echo " <a href='?pagina=$anterior'><- Anterior</a> ";
    }

echo "|";

if ($pc < $tp)
    {
    echo " <a href='?pagina=$proximo'>Próxima -></a>";
    }

?>

Where is the problem that causes this error?

Answer 1

The problem is that you are concatenating the first query as string type parameter, i.e., you are saying that the code

SELECT * FROM dados

is actually a string, not a query, to solve you can add the entire code in the first variable (since you perform the limit filter every time):

$busca = "SELECT * FROM dados LIMIT :ini,:totalreg";
$limite = $res = $liga->runQuery($busca);
$res->bindparam(':ini', $inicio, PDO::PARAM_INT);
$res->bindparam(':totalreg', $total_reg, PDO::PARAM_INT);
$res->execute();