SQLSTATE [42000]: Syntax error or access violation: 1064

0

I'm having trouble viewing and entering data into the database, it's giving this error:

  

SQLSTATE [42000]: Syntax error or access violation: 1064 You have an   error in your SQL syntax; check the manual that corresponds to your   MariaDB server version for the right syntax to use near '0,3' at line   1

Code:

dbconfig.php

<?php
$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "dbpdo";

try
{
    $DB_con = new PDO('mysql:host{$DB_host}; dbname={$DB_name}',$DB_user,$DB_pass);
    $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
    echo $e->getMessage();
}

include_once 'class.crud.php';
$crud = new crud($DB_con); ?>

crud.php

<?php

class crud
{
    private $db;

    function __construct($DB_con)
    {
        $this->db = $DB_con;
    }

    /*para criar novo*/
    public function create($xnome,$xsobrenome,$xemail,$xtelefone)
    {
        try
        {
            $stmt = $this->db->prepare("INSERT INTO tbl_usuarios(nome,sobrenome,email,telefone) VALUES(:xnome, :xsobrenome, :xemail, :xtelefone)");

            $stmt->bindparam(":xnome",$xnome);
            $stmt->bindparam(":xsobrenome",$xsobrenome);
            $stmt->bindparam(":xemail",$xemail);
            $stmt->bindparam(":xtelefone",$xtelefone);
            $stmt->execute();
            return true;
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
            return false;
        }
    }

    /*para buscar por id*/
    public function getID($id)
    {
        $stmt=$this->db->prepare("SELECT * FROM tbl_usuarios WHERE id=:id");
        $stmt->execute(array(":id"=>$id));
        $editRow=$stmt->fetch(PDO::FETCH_ASSOC);
        return $editRow;
    }

    /*para actualizar*/
    public function update($xid, $xnome, $xsobrenome, $xemail, $xtelefone)
    {
        try
        {
            $stmt=$this->db->prepare("UPDATE tbl_usuarios SET nome=:ynome, sobrenome=:ysobrenome, email=:yemail, telefone=:ytelefone WHERE id=:yid ");
        $stmt->bindparam(":ynome",xnome);
        $stmt->bindparam(":ysobrenome",xsobrenome);
        $stmt->bindparam(":yemail",xemail);
        $stmt->bindparam(":ytelefone",xtelefone);
        $stmt->bindparam(":yid",xid);
        $stmt->execute();

        return true;
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
            return false;
        }

    }

    /*para eliminar*/
    public function delete($id)
    {
        $stmt=$this->db->prepare("DELETE tbl_usuarios WHERE id=:id");
        $stmt->bindparam(":id",$id);
        $stmt->execute();
        return true;
    }

    /*para paginar*/
    public function dataview($query)
    {
        try
        {
            $stmt=$this->db->prepare($query);
            $stmt->execute();

            if($stmt->rowCount()>0)
            {
                while($row=$stmt->fetch(PDO::FETCH_ASSOC))
                {
                    ?>
                        <tr>
                        <td><?php print($row['id']); ?></td>
                        <td><?php print($row['nome']); ?></td>
                        <td><?php print($row['sobrenome']); ?></td>
                        <td><?php print($row['email']); ?></td>
                        <td><?php print($row['telefone']); ?></td>
                        <td align="center">
                        <a href="edit-data.php?edit_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-edit"></i></a>
                        </td>
                        <td align="center">
                        <a href="delete.php?delete_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-remove-circle"></i></a>
                        </td>
                        </tr>
                    <?php
                }
            }
            else
            {
                ?>
                    <tr>
                        <td>Não existem dados para visualizar!</td>
                    </tr>
                <?php
            }
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
        }           
    }


    public function paging($query, $record_per_page)
    {
        $starting_position=0;
        if(isset($_GET["page_no"]))
        {
            $starting_position=($_GET["page_no"]-1)*$record_per_page;
        }
        $query2=$query."limit $starting_position,$record_per_page";
        return $query2;
    }

    public function paginglink($query, $record_per_page)
    {
        $self=$_SERVER['PHP_SELF'];

        $stmt=$this->db->prepare($query);
        $stmt->execute();
        $total_no_of_records=$stmt->rowCount();
        echo $query;

        if($total_no_of_records > 0)
        {
            ?><ul class="pagination"><?php
            $total_no_of_records=ceil($total_no_of_records/$record_per_page);
            $current_page=1;

            if(isset($_GET["page_no"]))
            {
                $current_page=$_GET["page_no"];
            }

            if($current_page!=1)
            {
                $previous=$current_page-1;
                echo "<li><a href='".$self."?page_no=1'>Primeira</a></li>";
                echo "<li><a href='".$self."?page_no=".$previous."'>Anterior</a></li>";
            }

            for($i=1;$i<=$total_no_of_records;$i++)
            {
                if($i==$current_page)
                {
                    echo "<li><a href='".self."?page_no=".$i."'style='color:red;'>".$i."</a></li>";
                }
                else
                {
                    echo "<li><a href='".$self."?page_no=".$i."'>".$i."</a></li>";
                }
            }

            if($current_page!=$total_no_of_records)
            {
                $next=$current_page+1;
                echo "<li><a href='".$self."?page_no=".$next."'>Próximo</a></li>";
                echo "<li><a href='".$self."?page_no=".$total_no_of_records."'>Última</a></li>";                        
            }
            ?></ul><?php
        }           
    }
} ?>

index.php

<?php include_once 'dbconfig.php'; ?>
<?php include_once 'header3.php'; ?>

<div class="clearfix"></div>

<div class="container">
    <a href="add-data.php" class="btn btn-large btn-info"><i class="glyphicon glyphicon-plus"></i> &nbsp; Adicionar Novo</a>
</div><!--container-->

<div class="clearfix"></div><br />

<div class="container">
    <table class='table table-border table-responsive'>
        <tr>
            <th>#</th>
            <th>Nome</th>
            <th>Sobrenome</th>
            <th>Email</th>
            <th>Telefone</th>
            <th colspan="2" align="center">Ação</th>            
        </tr>
        <?php
            $query1="SELECT * FROM tbl_usuarios";
            $record_per_page=3;
            $query=$crud->paging($query1,$record_per_page);
            $crud->dataview($query);
        ?>
        <tr>
            <td colspan="7" align="center">
                <div class="pagination-wrap">
                    <?php $crud->paginglink($query1,$record_per_page); ?>

                </div><!--pagination-wrap-->                
            </td>
        </tr>

    </table>

</div><!--container-->

<?php include_once 'footer.php';?>
    
asked by anonymous 22.03.2017 / 23:57

1 answer

2

I think there is a gap between the LIMIT and the query:

$query2=$query."limit $starting_position,$record_per_page";

It should look like this:

$query2=$query." limit $starting_position,$record_per_page";
    
23.03.2017 / 00:40