making a page where you can select a record

scourtwright

Hi everybody,

I'm new to PHP, MySQL, and Apache. I have one beginner book on it, and I'm trying to make a page that lists the titles of my my movies, so that I can select a title, push a "submit" button and have it deleted from the table. So far this is what I have and I'm hoping that someone can point me in the right direction for materials I can reference to solve this problem.

$db_name = "my_movies";
$table_name = "my_movies";
$connection = @mysql_connect("localhost", "xxxx", "xxxx")
or die(mysql_error());

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

$sql = "SELECT title FROM $table_name ORDER BY title";

$result = @($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

$title = stripslashes($row['title']);

$display_block .= "<P><strong>$title</strong></P>";

}

?>
<HTML>
<HEAD>
<TITLE>Delete a Record</TITLE>
</HEAD>
<BODY>
<H1>Deleting a Record from <? echo "$table_name"; ?></H1>
<FORM METHOD="POST" ACTION="do_deletemovie.php">
<P>Select a record to be deleted</P><BR>
<? echo "$display_block"; ?>

Right now all this does is list the titles. Thanks so much for your help

scourtwright

AdamBrill

On a side note, I noticed that most of your HTML is in all caps. All HTML should be in lower case. Just thought I would let you know... 🙂

As for your problem, the first thing that you would have to do is put all of the titles into a select list or something else that you would be able to select from to choose which one you want to delete. For example, these lines:

while ($row = mysql_fetch_array($result)) {

$title = stripslashes($row['title']);

$display_block .= "<P><strong>$title</strong></P>";

}

would look something like this:

$display_block = "<select name=\"title\">";

while ($row = mysql_fetch_array($result)) {

$title = stripslashes($row['title']);

$display_block .= "<option value=\"$title\">$title</option>\n";

} 
$display_block = "</select>";

And then on the form action page you would have to use something like this(after connecting to the DB, of course):

$sql = "DELETE FROM `$table_name` WHERE `title` = '" . mysql_real_escape_string($_POST['title']) . "';";
$results = mysql_query($sql);
if($results){
echo "Title deleted successfully!";
}else{
echo "Error deleting title!!!";
}

I hope this helps. You would also be better off to use a row id instead of the title when deleting the row, but since I didn't know any of your field names, I just used the title. If you don't know what I mean by this, let me know and I'll explain myself.

Please note that all of the code here is untested...

scourtwright

Thanks so much! I will be testing it soon. I think that I understand the Id feild for the row, but I didn't put one in. I thought that I would just use the "title" to search and sort or do whatever. Is that wrong?

scourtwright