Deleting a record in a Mysql database.
1st Delete the record from the database
2nd Delete the big photo from the appropriate folder.
3º Delete the small photo that was in the thumbs folder.
To do this you have to use the appropriate php instructions, as always!
Let’s start with
How to delete a record in Mysql
If there is an instruction in PHP for adding records (a collection of data) then there will also be instructions for making changes to and deleting a records. In this case we’re going to see how to delete a record and then how to make changes to one. To delete we use:
The first line is the the entry line used so that a certain piece of code is executed and it tells us that if the variable erasead appears then it should execute what appears in the braces. this instruction is isset..
In the form there is a button whose name receives the erasead value. In the form, there is a button called erasead. If it is pressed then a variable with the value $_POST[‘erasead’] appears. The first instruction simply verifies that this variable exists in the code (because it’s been created when we clicked on the button)
if(isset($_POST[‘erasead’]) ){
The first thing we have to do is delete the photo that has been saved in the appropriate folder. We can’t delete an announcement and leave the photo because we would fill up the server with obsolete photos. We have to delete them and it’s the first thing we should do.
The function we must use is unlink (). This instruction deletes the file that appears in brackets. As the route where the photo is saved has been registered in one of the fields, the field _9, we haveto see what it’s been called. To do this we create the variable $foto like this.
$foto = $row[‘field_9’];
// se borra del la carpeta
unlink ($foto);
Now we can delete the record and to do this we have to do a mysql_query with the following information -> DELETE FROM board WHERE id=$id. This tells us to delete the record with the identifier id given by the variable $id from the board. This last variable $id is taken from a previous consultation that you can see below where we see the full code
// ahora el registro
$sql = “DELETE FROM board WHERE id=$id”;
$result = mysql_query($sql);
echo “Anuncio borrado”;
We’ve now deleted the whole advert. But this always happens whenever we push the delete button. But what happens if we only want to change one value, for example, the price of the object?
To make changes in the announcement we have to follow a similar procedure but, in this case, we have to show the full announcement on the page and write in the field we wish to change, in this case, the object’s price.
When this page opens it shows all the ID fields corresponding to this entry. Then an UPDATE instruction will appear which is what is responsible for actualising all the fields with the information that we change in the form. This is what it looks like in a simplified form.
$sqlUpdate = mysql_query(“UPDATE board SET field_1 = ‘$nombre’, field_2 = ‘$apellidos’, field_3 = ‘$email’,
field_4 = ‘$password’, field_5 = ‘$telephone’, field_6 = ‘$object’,
field_7 = ‘$message’, field_8 = ‘$title’, field_9 = ‘$photo’
, field_10 = ‘$today’, field_11 = ‘$prices’
WHERE field_3 = ‘$email'”, $link) ;
This instruction causes us to look inside the table ‘board’ for a record where field three coincides with email (WHERE field_3 = ‘$email’ ) and once found field 1 is realised with the value $nombre and field 2 with the surname etc, etc.
The complete code can be seen below
<?php
// conectamos a la base de datos
include(“config.inc.php”);
$link = mysql_connect($db_host,$db_user,$db_pass);
if(!$link) {
die(“Error al intentar conectar: “.mysql_error());
}
// seleccionamos la base de datos
$db_link = mysql_select_db(“$db_name”, $link);
if(!$db_link) {
die(“Error al intentar seleccionar la base de datos”. mysql_error());
}
// ********fin conexion ********
$usuario = “email”;
// hacemos una consulta para mostrar los datos
//$sql = mysql_query(“SELECT * FROM $db_table WHERE field_3 = ‘$usuario’, $db_link)or die(mysql_error());
$queEmp = “SELECT * FROM $db_table WHERE field_3 = ‘$email’ ORDER BY id DESC LIMIT 10 “;
$resEmp = mysql_query($queEmp, $link) or die(mysql_error());
$row = mysql_fetch_array($resEmp);
echo $row[‘field_4’];
if ( $row[‘field_4’] == “$password”)
{
if(isset($_POST[‘actualizar’]) && $_POST[‘actualizar’] == ‘Update’){
// comprobamos que no lleguen campos vacios
if(!empty($_POST[‘nombre’]) && !empty($_POST[‘apellidos’]) && !empty($_POST[’email’])){
// creamos las variables
// que vamos a usar en la consulta UPDATE
// y le asignamos sus valores
//$usuario_ID = $_POST[‘usuario_ID’];
$nombre = $_POST[‘nombre’];
$apellidos = $_POST[‘apellidos’];
$message = $_POST[‘message’];
$password = $_POST[‘password’];
$telephone = $_POST[‘telephone’];
$object = $_POST[‘object’];
$message = $_POST[‘message’];
$title = $_POST[‘title’];
$prices = $_POST[‘prices’];
$image = $_POST[‘image’];
$_FILES[‘image’][‘name’] = $image;
echo “$image”;
// EN ESTA PARTE ENTRAMOS LA IMAGEN
if($_FILES[‘image’][‘name’]!=”)
{
$image_filename = “file_2_”.date(“sihdmY”).substr($_FILES[‘image’][‘name’],strlen($_FILES[‘image’][‘name’])-4);
if(!move_uploaded_file($_FILES[‘image’][‘tmp_name’], “./files/”.$image_filename)){
die(“File ” . $_FILES[‘image’][‘name’] . ” was not uploaded.”);
}
}
echo “Esto debe aparecer -> $image_filename”;
/*
//File upload handling
// lo dejo por ahora porque no me recarga la nueva foto
if($_FILES[‘field_2’][‘name’]!=”){
$field_2_filename = “file_2_”.date(“sihdmY”).substr($_FILES[‘field_2’][‘name’],strlen($_FILES[‘field_2’][‘name’])-4);
if(!move_uploaded_file($_FILES[‘field_2’][‘tmp_name’], “./files/”.$field_2_filename)){
die(“File ” . $_FILES[‘field_2’][‘name’] . ” was not uploaded.”);
}
}
*/
$photo = $row[‘field_9’];
$today = date(“F j, Y, g:i”);
echo “$image_filename”;
// la consulta UPDATE
$sqlUpdate = mysql_query(“UPDATE board SET field_1 = ‘$nombre’, field_2 = ‘$apellidos’, field_3 = ‘$email’,
field_4 = ‘$password’, field_5 = ‘$telephone’, field_6 = ‘$object’,
field_7 = ‘$message’, field_8 = ‘$title’, field_9 = ‘$photo’
, field_10 = ‘$today’, field_11 = ‘$prices’
WHERE field_3 = ‘$email'”, $link) or die(mysql_error());
echo “Registro actualizado correctamente”;
echo “$image_filename”;
}else{
echo “debe llenar todos los campos”;
}
}else{
// mostramos el mensaje
echo “<p>”.$mensaje.”</p>”;
?>
<!–
el formulario.
los values de los campos
son los valores que optenemos
de la consulta SELECT
–>
<form name=”actualizar-registro” method=”post” action=”<?php $_SERVER[‘PHP_SELF’]; ?>”>
Anuncio numero: <?php echo $row[‘id’];
$id = $row[‘id’];
// vamos a borrar el anuncio
if(isset($_POST[‘erasead’]) ){
// parte donde se borra la foto
//primero borramos la foto de la carpeta
// ya que la informacion de su nombre esta en la base mysql
$foto = $row[‘field_9’];
// se borra del la carpeta
unlink ($foto);
// ahora el registro
$sql = “DELETE FROM board WHERE id=$id”;
$result = mysql_query($sql);
echo “Anuncio borrado”;
}else{
echo “Debe especificar un ‘id’.\n”;
}
// fin de borrado
?>
<input type=”submit” value=”Erase” name=”erasead”><p>Nombre: <input type=”text” name=”nombre” value=”<?php echo $row[‘field_1’]; ?>” />
Apellidos: <input type=”text” name=”apellidos” value=”<?php echo $row[‘field_2’]; ?>” />
e-mail: <input type=”text” name=”email” value=”<?php echo $row[‘field_3’]; ?>” />
password: <input type=”text” name=”password” value=”<?php echo $row[‘field_4’]; ?>” /></p>
<p><br />
telephone: <input type=”text” name=”telephone” value=”<?php echo $row[‘field_5’]; ?>” />
object: <input type=”text” name=”object” value=”<?php echo $row[‘field_6’]; ?>” /> Prices:
<input type=”text” name=”prices” value=”<?php echo $row[‘field_11’]; ?>” size=”6″ />
€</p>
<p>title:
<input type=”text” name=”title” value=”<?php echo $row[‘field_8’]; ?>” size=”107″ /> </p>
<p>Message<textarea rows=”8″ name=”message” cols=”56″><?php echo $row[‘field_7’]; ?></textarea></p>
<p>image:
<input type=”text” name=”image” value=”<?php echo $row[‘field_9’]; ?>” size=”26″ />
<?
/*
if(isset($_POST[‘erase’]) ){
// parte donde se borra la foto
$foto = $row[‘field_9’];
$consulta = “DELETE FROM board WHERE field_6=’$foto'”;
$query = mysql_query($consulta) or die (mysql_error());
// se borra del la carpeta
unlink ($foto);
echo “Borrado”; }
else{}
*/
?>
e” name=”erase”> </p>
<font face=”Verdana”> New image <input type=”file” name=”image” value=”<?php echo $row[‘field_9’]; ?>” size=”57″></font><p><br />
<input type=”hidden” name=”usuario_ID” value=”<?php echo $row[‘usuario_ID’]; ?>” />
<input type=”submit” name=”actualizar” value=”Update” />
</p>
<p> </p>
</form>
<?php } }
else
{ echo “Anuncio y usuario dado de baja “;}
?>