Adding a delete button to query results...

[gskurski]

Currently I have a page in my members area that queries all the entries the logged in user has made and displays the results. The code for that is as follows:

<?php

$user_id = check_input($_SESSION['user_id']);
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("localhost_db", $con);

$result = mysql_query("SELECT * FROM Bourbons WHERE user_id='$user_id'");
$num_rows = mysql_num_rows($result);

if ($num_rows==0){

echo "<h3>You have not submitted any Bourbon reviews.</h3>";
}else{

echo "<table border='1'>
<tr>
<th>Date</th>
<th>Name</th>
<th>Region</th>
<th>Years Aged</th>
<th>Proof</th>
<th>Price</th>
<th>Review</th>
<th>Ratings</th>
<th>Avg Rating</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Date'] . "</td>";
  echo "<td>" . $row['Name'] . "</td>";
  echo "<td>" . $row['Region'] . "</td>";
  echo "<td>" . $row['Years Aged'] . "</td>";
  echo "<td>" . $row['Proof'] . "</td>";
  echo "<td>" . $row['Price'] . "</td>";
  echo "<td>" . $row['Review'] . "</td>";
  echo "<td>" . $row['Smoothness'] ."/". $row['Taste'] ."/". $row['Value'] . "</td>";
  echo "<td>" . $row['Rating'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
}
?> 

 

This part works perfectly. What I want to be able to do is to have each record that is displayed also have a Delete button, that when clicked will remove that record from the database. Ideally I would like to have it prompt the user to verify the action, but that's secondary to the first part.

 

Any help is really appreciated!

 

Thanks~~

 

Gerry

[flappy_warbucks]

Normally what i do: is when creating a table in the database is have a unique identifier, normally as an integer that the program uses to reference data in the tables.

 

I would then do something like this:

 

<?php

$user_id = check_input($_SESSION['user_id']);
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("localhost_db", $con);

$result = mysql_query("SELECT * FROM Bourbons WHERE user_id='$user_id'");
$num_rows = mysql_num_rows($result);

if ($num_rows==0){

echo "<h3>You have not submitted any Bourbon reviews.</h3>";
}else{

echo "<table border='1'>
<tr>
<th>Date</th>
<th>Name</th>
<th>Region</th>
<th>Years Aged</th>
<th>Proof</th>
<th>Price</th>
<th>Review</th>
<th>Ratings</th>
<th>Avg Rating</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Date'] . "</td>";
  echo "<td>" . $row['Name'] . "</td>";
  echo "<td>" . $row['Region'] . "</td>";
  echo "<td>" . $row['Years Aged'] . "</td>";
  echo "<td>" . $row['Proof'] . "</td>";
  echo "<td>" . $row['Price'] . "</td>";
  echo "<td>" . $row['Review'] . "</td>";
  echo "<td>" . $row['Smoothness'] ."/". $row['Taste'] ."/". $row['Value'] . "</td>";
  echo "<td>" . $row['Rating'] . "</td>";
  echo "<td><input type=\"button\" onclick=\"delete_item('". $row['unique_id']. "')" value="Delete" />
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
}
?> 

 

and then set up some Javascript with AJAX to delete the thing from the database:

 

// javascript code
function delete_item(item)
{
    var agree = confirm("Are you sure you would like to delete this item?");
    if (agree == true)
    {
        ajax_delete_item(item);
    }
}
function ajax_delete_item(item)
{
  // Do ajax to the server with the item reference, and delete from the database.
}

[gskurski]

Thanks for replying! I'm not very familiar with Ajax yet -- do you know of a good website that would have examples/tutorials of the type of script I would need to do the query I need?

 

Also, in this line:

echo "<td><input type=\"button\" onclick=\"delete_item('". $row['unique_id']. "')" value="Delete" />

Should there be a

";

at the end?

 

Thanks again~~

[gskurski]

So I've looked into Ajax and this is what I've come up with so far, can someone please let me know how far off I am?

 

I've added the button to each row and when you click it, it does ask whether or not you want to delete the record. However, nothing happens if you click okay. Here is the coding:

I've added this to my original php file:

    echo "<td><input type=\"button\" onclick=\"delete_item('". $row['record_id']. "')\" value=\"Delete\" id="id" /></td>";

 

Here is the javascript/Ajax I have implemented:

<script language="javascript">
// javascript code
function delete_item(item)
{
    var agree = confirm("Are you sure you would like to delete this review?");
    if (agree == true)
    {
        ajax_delete_item(item);
    }
}
function ajax_delete_item(item)
{
var ajaxRequest;  // The variable that makes Ajax possible!

try{
	// Opera 8.0+, Firefox, Safari
	ajaxRequest = new XMLHttpRequest();
} catch (e){
	// Internet Explorer Browsers
	try{
		ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
	} catch (e) {
		try{
			ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
		} catch (e){
			// Something went wrong
			alert("Your browser broke!");
			return false;
		}
	}
}

ajaxRequest.onreadystatechange = function(){
	if(ajaxRequest.readyState == 4){
		document.myForm.time.value = ajaxRequest.responseText;
	}
}
var record = document.getElementById('id').value;
	var queryString = "?record=" + record;
ajaxRequest.open("GET", "deleterev.php" + queryString, true);
ajaxRequest.send(null); 


  
}
</script>

And here is the PHP file it refers to:

<?php

$record_id = $_GET['record_id'];

$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("localhost_mydb", $con);

$sql = mysql_query("DELETE FROM Bourbons WHERE record_id='".$record_id."'");

mysql_close($con);
?>

 

Again, any help is really appreciated.

 

 

[jdavidbakr]

If you don't want to mess with AJAX (I'd suggest looking into a framework if you want to do AJAX, I use mootools and love it, I know others love jQuery), you can do it via a form.  Have the output of the PHP script look something like this:

 

<form method="post" action="delete_selected.php">
  <table>
    <tr>
      <td>Item 1</td>
      <td><input type="checkbox" name="delete[1]"> Delete</td>
    </tr>
    <tr>
      <td>Item 2</td>
      <td><input type="checkbox" name="delete[2]"> Delete</td>
    </tr>
    <tr>
      <td>Item 3</td>
      <td><input type="checkbox" name="delete[3]"> Delete</td>
    </tr>
  </table>
  <input type="submit">
</form>

 

then your php code would be something like:

 

if (is_array($_POST['delete'])) {
  foreach($_POST['delete'] as $id) {
    $statement = "delete from table where id = '".mysql_real_escape_string($id)."'";
    mysql_query($statement);
  }
}

 

Of course you'll want to put in appropriate restrictions to make sure that items don't get deleted that shouldn't, but that's a basic non-ajax way to do it.

[gskurski]

I was able to solve my problem. I didn't end up using Ajax, but here's my code if anyone is interested:

 

PHP file with the button:

<?php

$user_id = check_input($_SESSION['user_id']);
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db_liquors", $con);

$result = mysql_query("SELECT * FROM Bourbons WHERE user_id='$user_id'");
$num_rows = mysql_num_rows($result);

if ($num_rows==0){

echo "<h3>You have not submitted any Bourbon reviews.</h3>";
}else{

echo "<table border='1'>
<tr>
<th>Date</th>
<th>Name</th>
<th>Region</th>
<th>Years Aged</th>
<th>Proof</th>
<th>Price</th>
<th>Review</th>
<th>Ratings</th>
<th>Avg Rating</th>
<th>Delete?</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Date'] . "</td>";
  echo "<td>" . $row['Name'] . "</td>";
  echo "<td>" . $row['Region'] . "</td>";
  echo "<td>" . $row['Years Aged'] . "</td>";
  echo "<td>" . $row['Proof'] . "</td>";
  echo "<td>" . $row['Price'] . "</td>";
  echo "<td>" . $row['Review'] . "</td>";
  echo "<td>" . $row['Smoothness'] ."/". $row['Taste'] ."/". $row['Value'] . "</td>";
  echo "<td>" . $row['Rating'] . "</td>";
echo "<td>" . "<form action='deleterev.php' method='post' onsubmit='return confirmSubmit()'>" . "<input type='hidden' value='" . $row['review_id'] . "' name='record'>" . "<input type='hidden' value='Bourbons' name='table'>" . "<input type='submit' value='Delete?'>" . "</form>" . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
}
?> 

 

The simple javascript confirmation script:

 

<script LANGUAGE="JavaScript">
<!--

function confirmSubmit()
{
var agree=confirm("Are you sure you wish to delete this review?");
if (agree)
return true ;
else
return false ;
}
// -->
</script>

 

Then the "deleterev.php" file:

<?php

$record_id = $_POST['record'];
$table = $_POST['table'];

$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db_liquors", $con);
$record_id = $_POST['record'];

$table = $_POST['table'];

$sql = mysql_query("DELETE FROM $table WHERE review_id='$record_id'");

mysql_close($con);

?>
<html>
<body>
<head>
<link rel="stylesheet" href="style.css" />
<meta http-equiv="Refresh"
content="0;url=http://locahost.com/myreviews.php">

</head>
</html>

The "bourbons" table is just one out of many. The original PHP script is repeated several times for the different types of liquors and each time the delete button submits a hidden value with the table name so it knows which table to delete from.

 

Hope this helps for anyone else who wants a similar function!