Newbie Question about Query and Delete

Anon

I am new to PHP and am trying to query and delete records from a MySQL db using PHP and an HTML Form. What I want to do is query the db, and then have a "delete button" or checkbox next to each result. When I hit "delete" or select the checkboxes and maybe hit a single delete button, the record(s) would be removed from the db, and I would go to a new HTML page.

I have some code written (see below), but it isn't really working. The search works, and there is a "delete" tag next to each record. However, when I click "delete", I get an error, although the record is sucessfully deleted. Am I trying to do too much in one script?

Any suggestions would be helpful, or if you could point me to a script out there that already does what I want that I could look at, that would be helpful too.

Thanks!

Here is the code:

<?
$hostname = "localhost";
$username = "username";
$password = "pw";
$usertable = "tb";
$dbName = "db";
MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database");
@mysql_select_db( "$dbName") or die( "Unable to select database");
?>
<?
PRINT "Your Search Results: ";

$query = mysql_query("SELECT * FROM $usertable WHERE $feedback LIKE '%$search%' LIMIT 0, 30 ");
$sql = mysql_query("DELETE FROM $usertable WHERE id=$id"); while
($row = mysql_fetch_array($query))
{
PRINT "First Name: ";
print $row["FirstName"] ;
print (" ");
print (" ");
PRINT "Last Name: ";
print $row["LastName"];
print (" ");
printf("<a href=\"%s?id=%s&delete=yes\">(DELETE)</a> ", $PHP_SELF, $row["id"]);
print ("");
}
?>

mrskhris

Just an idea... why don't you switch "select" and "delete" statements? First delete, then select I mean.

Anon

Thanks, I tried that but it didn't seem to work. The search itself crashes, then.

mrskhris

SELECT * FROM $usertable WHERE $feedback LIKE '%$search%' LIMIT 0, 30

Janey, if you try to print this query on the page, what do you see? I assume you're coming from other page (search page), so at first you have $feedback and $search set. But when you're clicking on the "delete" link, the only variable passed is $id, so your query is now invalid.

janey

Yes, there is a search page that uses an HTML form to call this PHP script. When I run the search initially, it works well. The PHP script displays the records that match the search criteria, and there is a "delete" link next to each record.

When I click the "delete" link, the record is removed from the db, but the page that results says "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource".

I am really new to this, but I think I understand what you are saying about the query being invalid when I click "delete". I just don't "get" how I execute a search, then delete records from those results without executing the search again.

mrskhris

Just add "&search=".rawurlencode($search)."&feedback=".rawurlencode($feedback) to the end of delete link:

printf("<a href=\"%s?id=%s&delete=yes&search=".rawurlencode($search)."&feedback=".rawurlencode($feedback)."\">(DELETE)</a> ", $PHP_SELF, $row["id"]);

This way your "search" and "feedback" variables will be passed from page to page.