Deleting MySQL record

sandgnat

Warning-I'm a newbie!

Here's my problem.

I want to be able to delete a database record from an HTML page using a checkbox after each record and using one submit button to delete them all at once.

The database is storing transaction information. This info is displayed on an HTML page for company employees to see after successfully logging in. Once the page is printed, I want to have a way so that they can select a record using a checkbox and delete the ones selected with a submit button at the bottom of the page. I need to delete each record based on account number stored in the database field as 'account'.

Here's how I retrieve the records (there's probably a better way, I'm open to suggestions). All records are displayed in a table, so there's lots of "echo" formatting:

$query="SELECT * FROM info ORDER BY date";
$result=mysql_query($query)
or die ("Query failed.");

echo "Payment Information\n ".date("F d, Y")."\n";

while ($row=mysql_fetch_array($result))
{
echo "Transaction Date:".($row['date'])."";
echo "<table width=\"90%\" border=\"1\" align=\"center\" cellpadding=\"2\" cellspacing=\"0\">\n";

// table headings
echo "<tr align=\"center\" valign=\"top\" bgcolor=\"#FFFFCC\">\n";
echo " <td>Type</td>\n";
echo " <td>Account #</td>\n";
echo " <td>Name/Email</td>\n";
echo " <td>Address</td>\n";
echo "</tr>\n";

// database values
echo "<tr align=\"center\">\n";
echo " <td>".stripslashes($row['btctype'])."</td>\n";

echo " <td>".stripslashes($row['account'])."</td>\n";
echo " <td>".stripslashes( $row['fname'])." ".stripslashes($row['mname'])." ".stripslashes($row['lname'])." ".stripslashes($row['email'])."</td>\n";
echo " <td>".stripslashes($row['address'])." ".stripslashes($row['city']).", ".stripslashes($row['zip'])."</td>\n";
echo "</tr>\n";

// more table headings
echo "<tr align=\"center\" valign=\"top\" bgcolor=\"#FFFFCC\">\n";
echo " <td>Phone</td>\n";
echo " <td>Method</td>\n";
echo " <td>Card No./Exp</td>\n";
echo " <td>Amount</td>\n";
echo "</tr>\n";

//more database values
echo "<tr align=\"center\">\n";
echo " <td>".stripslashes($row['phone'])."</td>\n";
echo " <td>".stripslashes($row['method'])."</td>\n";
echo " <td>".stripslashes($row['cc_account'])." ".stripslashes($row['m_exp'])." ".stripslashes($row['y_exp'])."</td>\n";
echo " <td>$".stripslashes($row['amount'])."</td>\n";
echo "</tr>\n";

// last table heading
echo "<tr>\n";
echo "<tr valign=\"top\" bgcolor=\"#FFFFCC\">\n";
echo " <td colspan=\"4\">Comments</td>\n";
echo "</tr>\n";

// last database value
echo "<tr>\n";
echo " <td colspan=\"4\">".($row['comments'])."</td>\n";
echo "</tr>\n";
echo "</table>\n";
echo " \n";
}

I apologize for so much code. But thought it would be helpful to see that I'm calling each record from the array $row[xxx].

Thanks in advance. Any help is MUCH appreciated.

chinni_77

hello,

well, could you pls edit your code and re-post it again enclosed in PHP tags, so that would be easy to follow for many people.

Thanks

sandgnat

I'm not following your logic. What difference would it make if I reposted it enclosed in PHP tags? There's no need to post my entire script (it's lengthy and does many more things than this). I only posted the snippet that I use to display each payment record. I need a way to show each record and have the option to delete it.

The first part of the scripts verifies login (username and password). Once a match is found in the database, the 'info' table (where the payment records are) is made active and queried (hence where I started the post).

Can't someone follow me with what I've given? Is there a tutorial on this that I've missed somewhere?

Thanks

nnichols

edit your post so that the code is between [ PHP] and [/code] so that your post is "highlighted"

sandgnat

OH!!! Sorry, newbie mistake! :rolleyes:

$query="SELECT * FROM info ORDER BY date";
$result=mysql_query($query)
  or die ("Query failed.");

echo "<p align=\"center\"><font size=\"2\"><b>Payment Information</b>\n<br>".date("F d, Y")."</font></p>\n";

while ($row=mysql_fetch_array($result))
{
echo "<font size=\"2\">&nbsp;&nbsp;&nbsp;Transaction Date:".($row['date'])."</font>";

echo "<table width=\"90%\" border=\"1\" align=\"center\" cellpadding=\"2\" cellspacing=\"0\">\n";
echo "<tr align=\"center\" valign=\"top\" bgcolor=\"#FFFFCC\">\n";
echo "  <td><strong><font size=\"2\">Type</font></strong></td>\n";
echo "  <td><strong><font size=\"2\">Account #</font></strong></td>\n";
echo "  <td><strong><font size=\"2\">Name/Email</font></strong></td>\n";
echo "  <td><strong><font size=\"2\">Address</font></strong></td>\n";
echo "</tr>\n";

echo "<tr align=\"center\">\n";
echo "  <td><font size=\"2\">".stripslashes($row['btctype'])."</font></td>\n";  

echo "  <td><font size=\"2\">".stripslashes($row['account'])."</font></td>\n";
echo "  <td><font size=\"2\">".stripslashes( $row['fname'])." ".stripslashes($row['mname'])." ".stripslashes($row['lname'])."<br>".stripslashes($row['email'])."</font></td>\n";
echo "  <td><font size=\"2\">".stripslashes($row['address'])."<br>".stripslashes($row['city']).", ".stripslashes($row['zip'])."</font></td>\n"; 
echo "</tr>\n";

echo "<tr align=\"center\" valign=\"top\" bgcolor=\"#FFFFCC\">\n";
echo "  <td><strong><font size=\"2\">Phone</font></strong></td>\n";
echo "  <td><strong><font size=\"2\">Method</font></strong></td>\n";
echo "  <td><strong><font size=\"2\">Card No./Exp</font></strong></td>\n";
echo "  <td><strong><font size=\"2\">Amount</font></strong></td>\n";
echo "</tr>\n";

echo "<tr align=\"center\">\n";
echo "  <td><font size=\"2\">".stripslashes($row['phone'])."</font></td>\n"; 
echo "  <td><font size=\"2\">".stripslashes($row['method'])."</font></td>\n";
echo "  <td><font size=\"2\">".stripslashes($row['cc_account'])."<br>".stripslashes($row['m_exp'])." ".stripslashes($row['y_exp'])."</font></td>\n"; 
echo "  <td><font size=\"2\">$".stripslashes($row['amount'])."</font></td>\n"; 
echo "</tr>\n";

echo "<tr valign=\"top\" bgcolor=\"#FFFFCC\">\n";
echo "  <td colspan=\"4\"><strong><font size=\"2\">Comments</font></strong></td>\n";
echo "</tr>\n";

echo "<tr>\n";	
echo "  <td colspan=\"4\"><font size=\"2\">".($row['comments'])."</font></td>\n";
echo "</tr>\n";
echo "</table>\n";
echo "<br><br>\n";
}

nemonoman

I assume that each record displayed has some sort of unique identifier?

Maybe you'd like to use this type of code, which I often find effective.

Assuming you have a column called 'id'.

SELECT CONCAT('<input type=checkbox name=deletethese[] value=', id, '>',Delete this record')
FROM sometable

This will return for each record a check box that says "Delete this record"

You'll place these values in a <form>, with a submit button.

If you check the $_POST values, you'll see an array called deletethese;

Each element of $deletethese will contain the id of the checkboxes checked by the user and submitted.

You could use a bit of code like this:

$theinclause="(null":
$i=0;
$count=count($deletethese)
while($i<$count){
$theinclause.=$deletethese[$i];
$i++;
}
$theinclause.=")";

this will produce a list like

(null, 1, 2, 5)

You then say:

mysql_query("DELETE FROM sometable
WHERE id
IN $theinclause");

Which will delete every record checked by the user.

There are ways around including (null as part of the query. But an empty set in the parens like

SELECT *
FROM sometable
WHERE id
IN ()

Will return an error.

SELECT *
FROM sometable
WHERE id
IN (null)

Processes without an error.