Simple Insert, View, and Delete Record MySQL and PHP

[Soldiers3lite]

I have a form that submits a record and saves it to the database, I've got that working already, I'm trying to figure out if there is a cleaner way to delete a record with a button that gets a value from the unique key, in this case 'id'

here is how the delete button looks like:

	  	// get info from table
		$query  = "SELECT id, Title, Message FROM table_name";
		$result = mysql_query($query);

		//displaying all data
		while($row = mysql_fetch_assoc($result))
		{
			echo"
	<div class='status'><h3>{$row['Title']} <br></h3>" . "
         <h5>{$row['Message']} <br><br></h5>"; 
	 $id = $row['id']; //trying to get unique key from database
//delete button
	 echo "</div>
	 <form action='delete.php' method='post' />
	 <input type='hidden' name='delete' value='yes' /> 
	 <input type='hidden' name='id' value='$id' />
	 <input type='submit' value='remove' /></form>";
		}

As you can see, i'm trying to give $id a unique value but for some reason i'm not getting it.

The delete.php code looks like this:

	if (isset($_POST['delete']))// check if delete was clicked
        {
        	$query = "DELETE FROM table_name WHERE id='$id'";
		echo "$id Deleted successfully";
}
elseif(!mysql_query($query, $db_server))
{
			echo "DELETE failed: $query<br />" .
			myql_error() . "<br /><br />";		
         }
mysql_close();

I'm new to php and still learning so, if you think there are other ways to do this, plz let me know, the button won't do anything to the data.

 

Thanks in advance

[coupe-r]

A few things...

 

Are you just playing around right now?  Typically, you would have a deleteStatus column in your table and you change that from 0 to 1 to delete it.  Hard deletes are very risky in real applications plus if there is a mistake, you cannot undo it.

 

On to your issue...

 

What ID is echoed if you echo $id? 

 

When you have your initial SELECT statement, are you getting 1 record or an array of records?

[BLaZuRE]

I'm guessing your delete query acts after a form is submitted.

 

Variable are not transferred across pages (usually), so the $id you stored in a hidden field gets transferred in $_POST[] just like every other field item value.  So you probably have to get your id by doing $_POST['id'] (id was the name of the hidden field).

[Soldiers3lite]

yes, I was just playing around and of course test it if it works and try to deploy it on a real website, of course with user authentication to be able to access the page with the delete function. I was thinking of javascript for the error deletions, it would ask the user if they are sure, if not return false (pop-up window)

my code works now, it looks like this: insert.php

	  	// get info from table
		$query  = "SELECT id, Title, Message FROM table_name";
		$result = mysql_query($query);

		//displaying all data
		while($row = mysql_fetch_assoc($result))
		{
			echo"
	<div class='status'><h3>{$row['Title']} <br></h3>" . "
         <h5>{$row['Message']} <br><br></h5>"; 
	 $id = "{$row['id']}";// get unique key from id
	 ?>
	 </div>
         <!--works fine-->
	 <form action='delete.php' method='post' />
	 <input type='hidden' name='delete' value='yes' />
	 <input type='hidden' name='id' value='<?php echo $id; ?>' />
	 <input type='submit' value='remove' /></form>
	 <?php
		}

mysql_close($db_server);
	  ?>

and delete.php

                $delete = $_POST['delete'];
	$id = $_POST['id'];
	if (isset($delete))
        {
        	$query = "DELETE FROM table_name WHERE id='$id'";

		echo $id . " Deleted successfully";

         }
	 ?>

 

everything works fine, just needs some tweaking =)

[coupe-r]

Use mysqli, its faster   slightly......

 

example:

mysqli_query($connect, $sql);

[coupe-r]

Good luck though.  I'm not a PHP pro, but I will gladly help if I can.