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> 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';?>