I've created a database with two tables. I connected this database with a PHP page for a small CRUD app.
To execute the CRUD operations, I've created a crud.class.php that hold all operations.
Operations on the first table (membri) work but into the second (articoli) they fail, in the update articoli page.
Simply, I've create the same PHP function for the first table for the second, but it send me back this error:
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
Here are my crud.class.php and edi_articoli.php page:
Class.crud.php:
class crud
{
private $db;
function __construct($DB_con)
{
$this->db = $DB_con;
}
/***************************************** Crea Articoli******************************/
public function crea_articolo($data,$autore,$titolo)
{
try
{
$stmt = $this->db->prepare("INSERT INTO articoli(data,autore,titolo) VALUES(:data, :autore, :titolo)");
$stmt->bindparam(":data",$data);
$stmt->bindparam(":autore",$autore);
$stmt->bindparam(":titolo",$titolo);
$stmt->execute();
return true;
}
catch(PDOException $e)
{
echo $e->getMessage();
return false;
}
}
/***************************************** Update Articolo******************************/
public function getID_articoli($id)
{
$stmt = $this->db->prepare("SELECT * FROM articoli WHERE id=:id");
$stmt->execute(array(":id"=>$id));
$editRow=$stmt->fetch(PDO::FETCH_ASSOC);
return $editRow;
}
public function update_articoli($id,$data,$autore,$titolo)
{
try
{
$stmt=$this->db->prepare("UPDATE articoli SET data=:data,
autore=:autore,
titolo:titolo,
WHERE id=:id ");
$stmt->bindparam(":data",$data);
$stmt->bindparam(":autore",$autore);
$stmt->bindparam(":titolo",$titolo);
$stmt->bindparam(":id",$id);
$stmt->execute();
return true;
}
catch(PDOException $e)
{
echo $e->getMessage();
return false;
}
}
/***************************************** End Crea e aggiungi Articoli******************************/
/***************************************** Articoli******************************/
public function articoliview($query)
{
$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['data']); ?></td>
<td><?php print($row['autore']); ?></td>
<td class="setWidth concat"><div><?php print($row['titolo']); ?></div></td>
<td align="center">
<a href="articoli_view.php?view_articolo_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-eye-open"></i></a>
</td>
<td align="center">
<a href="edit_articolo.php?edit_articolo_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-edit"></i></a>
</td>
<td align="center">
<a href="delete_articolo.php?delete_articolo_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-remove-circle"></i></a>
</td>
</tr>
<?php
}
}
else
{
?>
<tr>
<td>Non c'è nulla qui...</td>
</tr>
<?php
}
}
/***************************************** End Articoli******************************/
/***************************************** CREAZIONE UTENTI******************************/
/****************************************************************************************/
public function create($nome,$cognome,$brev_descr,$descrizione)
{
try
{
$stmt = $this->db->prepare("INSERT INTO membri(nome,cognome,brev_descr,descrizione) VALUES(:nome, :cognome, :brev_descr, :descrizione)");
$stmt->bindparam(":nome",$nome);
$stmt->bindparam(":cognome",$cognome);
$stmt->bindparam(":brev_descr",$brev_descr);
$stmt->bindparam(":descrizione",$descrizione);
$stmt->execute();
return true;
}
catch(PDOException $e)
{
echo $e->getMessage();
return false;
}
}
public function getID($id)
{
$stmt = $this->db->prepare("SELECT * FROM membri WHERE id=:id");
$stmt->execute(array(":id"=>$id));
$editRow=$stmt->fetch(PDO::FETCH_ASSOC);
return $editRow;
}
/***************************************** END CREAZIONE UTENTI******************************/
/***************************************** UPDATE UTENTI ******************************/
public function update($id,$nome,$cognome,$brev_descr,$descrizione)
{
try
{
$stmt=$this->db->prepare("UPDATE membri SET nome=:nome,
cognome=:cognome,
brev_descr=:brev_descr,
descrizione=:descrizione
WHERE id=:id ");
$stmt->bindparam(":nome",$nome);
$stmt->bindparam(":cognome",$cognome);
$stmt->bindparam(":brev_descr",$brev_descr);
$stmt->bindparam(":descrizione",$descrizione);
$stmt->bindparam(":id",$id);
$stmt->execute();
return true;
}
catch(PDOException $e)
{
echo $e->getMessage();
return false;
}
}
/***************************************** END UPDATE UTENTI ******************************/
/***************************************** DELETE UTENTI******************************/
public function delete($id)
{
$stmt = $this->db->prepare("DELETE FROM membri WHERE id=:id");
$stmt->bindparam(":id",$id);
$stmt->execute();
return true;
}
/***************************************** END DELETE UTENTI******************************/
/***************************************** VIEW UTENTI ******************************/
public function dataview($query)
{
$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['cognome']); ?></td>
<td class="setWidth concat"><div><?php print($row['brev_descr']); ?></div></td>
<td align="center">
<a href="view.php?view_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-eye-open"></i></a>
</td>
<td align="center">
<a href="edit.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>Non c'è nulla qui...</td>
</tr>
<?php
}
}
/***************************************** END VIEW UTENTI ******************************/
/***************************************** Paging ******************************/
public function paging($query,$records_per_page)
{
$starting_position=0;
if(isset($_GET["page_no"]))
{
$starting_position=($_GET["page_no"]-1)*$records_per_page;
}
$query2=$query." limit $starting_position,$records_per_page";
return $query2;
}
public function paginglink($query,$records_per_page)
{
$self = $_SERVER['PHP_SELF'];
$stmt = $this->db->prepare($query);
$stmt->execute();
$total_no_of_records = $stmt->rowCount();
if($total_no_of_records > 0)
{
?><ul class="pagination"><?php
$total_no_of_pages=ceil($total_no_of_records/$records_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'>Primo</a></li>";
echo "<li><a href='".$self."?page_no=".$previous."'>Precedente</a></li>";
}
for($i=1;$i<=$total_no_of_pages;$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_pages)
{
$next=$current_page+1;
echo "<li><a href='".$self."?page_no=".$next."'>Prossimo</a></li>";
echo "<li><a href='".$self."?page_no=".$total_no_of_pages."'>Ultimo</a></li>";
}
?></ul><?php
}
}
/* paging */
}
Here my "update_articoli.php" page:
<?php
include_once 'dbconfig.php';
if(isset($_POST['btn-update']))
{
$id = $_GET['edit_articolo_id'];
$data = $_POST['data'];
$autore = $_POST['autore'];
$titolo = $_POST['titolo'];
if($crud->update_articoli($id,$data,$autore,$titolo))
{
$msg = "<br/><div class='alert alert-success'>
<strong>L'articolo è stato aggiornato correttamente!</strong>
</div>";
}
else
{
$msg = "<div class='alert alert-warning'>
<strong> C'è stato un errore durante l' aggiornamento dell' articolo !</strong>
</div>";
}
}
if(isset($_GET['edit_articolo_id']))
{
$id = $_GET['edit_articolo_id'];
extract($crud->getID_articoli($id));
}
?>
<?php include_once 'header.php'; ?>
<div class="clearfix"></div>
<div class="container">
<?php
if(isset($msg))
{
echo $msg;
}
?>
</div>
<div class="clearfix"></div>
<div class="container">
<h1>Modifica articolo</h1><hr>
<a href="articoli.php" class="btn btn-large btn-success"><i class="glyphicon glyphicon-backward"></i> Torna alla lista articoli</a>
<br/><br/>
<form method='post'>
<table class='table table-bordered'>
<tr>
<td>Data</td>
<td><input type='text' name='data' class='form-control' value="<?php echo $data; ?>" required></td>
</tr>
<tr>
<td>Autore</td>
<td><input type='text' name='autore' class='form-control' value="<?php echo $autore; ?>" required></td>
</tr>
<tr>
<td>Titolo</td>
<td><textarea name='titolo' rows="5" maxlength="100" class='form-control' required><?php echo $titolo; ?></textarea></td>
</tr>
<tr>
<td colspan="2">
<button type="submit" class="btn btn-primary" name="btn-update">
<span class="glyphicon glyphicon-edit"></span> Aggiorna articolo
</button>
<a href="articoli.php" class="btn btn-large btn-success"><i class="glyphicon glyphicon-backward"></i> CANCEL</a>
</td>
</tr>
</table>
</form>
</div>
<?php include_once 'footer.php'; ?>Thanks for support.
</div>