Passing a db record to be edited / deleted

Hi, I have a few PHP pages set up which I'm using to display records from a mySQL database. I also have a page for editing a particular record and one for deleting a particular record, however the client wants the links for editing and deleting individual records to be next to each record in the display page. So in other words, records are outputted and next to each one should be a direct Edit and Delete link.

The Edit link should go to a page which outputs the whole record detail in a form (which the user can then edit and submit back to the db). The Delete link needs to automatically delete that particular record (and that record only!) from the database.

This is the relevant bit of code for the current display page:

// Build SQL Query
$query = "select * from specialofferstable ORDER BY category"; // specify the table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// get results
$result = mysql_query($query) or die("Couldn't execute query");
// display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["category"];
$title2 = $row["company_hotel"];
$title3 = $row["location"];
$title4 = $row["offer"];
$title5 = $row["price"];
$title6 = $row["offerends"];
$title7 = $row["mobile"];

echo '<table width="800"><tr><td width="100">' . $title . '</td><td width="100">' . $title2 . '</td><td width="100">' . $title3 . '</td><td width="100">' . $title4 . '</td><td width="100">' . $title5 . '</td><td width="100">' . $title6 . '</td><td width="100">' . $title7 . '</td><td width="100"><a href="specialoffers_update.html">Edit</a>  <a href="specialoffers_delete.html">Delete</a></td></tr></table>' ;
$count++ ;
}
?>

At present the specialoffers_update and specialoffers_delete pages are simply query forms where the user has to type in the location field for the record they want to amend or delete- another PHP file then gets this from the db and the user clicks Submit on the next form to complete the operation.

For example, this is how the Edit function currently works. The user goes to specialoffers_update.html, enters the location field for the record they want to edit, and when the form on this page is submitted it goes to the next page which grabs the data from the db, as follows:

<?php
if($_POST['specialoffers_update'])
{

include ('inc/dbconnect.php');

$searchstring1 = $_POST['_Location'];

$query = "select * from specialofferstable WHERE location like '$searchstring1'"; // specify the table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)
{
echo "<p>No special offer matching this location currently exists. Please check that the location you typed is correct.</p>";
}

// get results
$result = mysql_query($query) or die("The Special Offers system is undergoing maintenance at the moment and will be available shortly");

// begin to show results set
$count = 1 + $s ;
echo "<br>";
// display the results returned
while ($row= mysql_fetch_array($result)) {
$title1 = $row["index"];
$title2 = $row["category"];
$title3 = $row["company_hotel"];
$title4 = $row["location"];
$title5 = $row["offer"];
$title6 = $row["price"];
$title7 = $row["offerends"];
$title8 = $row["mobile"];

echo "<form action=\"specialoffers_updatetable.php\" method=\"post\" name=\"specialoffers_updatetable\">
<table width=\"400\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\">
<tr><td>Category</td><td><input name=\"_Index\" size=\"10\" value='$title1' />
<input name=\"_Category\" size=\"30\" value='$title2' /></td></tr>
<tr><td>Company or Hotel</td><td><input name=\"_CompanyHotel\" size=\"50\" value='$title3' /></td></tr>
<tr><td>Location</td><td><input name=\"_Location\" size=\"30\" value='$title4' /></td></tr>
<tr><td>Offer</td><td><textarea name=\"_Offer\" cols=\"30\" rows=\"5\">$title5</textarea></td></tr>
<tr><td>Price (£)</td><td><input name=\"_Price\" size=\"10\" value='$title6' /></td></tr>
<tr><td>Offer Ends (YYYY-MM-DD)</td><td><input name=\"_OfferEnds\" size=\"10\" value='$title7' /> <a href=\"javascript:showCal('Calendar1')\"><img src=\"/images/datepicker.gif\" alt=\"Date picker\"></a></td></tr>
<tr><td>Mobile</td><td><select name=\"_Mobile\" size=\"1\" value='$title8'><option>Yes</option><option>No</option></select></td></tr>
</table><br />
<input type=\"submit\" value=\"Submit\" name=\"specialoffers_updatetable\" />
  
<input type=\"reset\" value=\"Reset\" name=\"Reset\" /></form>";

$count++ ;
}
}
?>

The user checks the details, makes whichever amendments are needed, and then clicks Submit, which invokles this next PHP file:

<?php

include ('inc/dbconnect.php');

if($_POST['specialoffers_updatetable'])
{
// Get the search variable from URL
$title1 =@$_POST['_Index'];

$title2 =@$_POST['_Category'];
$title3 =@$_POST['_CompanyHotel'];
$title4 =@$_POST['_Location'];
$title5 =@$_POST['_Offer'];
$title6 =@$_POST['_Price'];
$title7 =@$_POST['_OfferEnds'];
$title8 =@$_POST['_Mobile'];

$trimmed2 = trim($title2);
$trimmed3 = trim($title3);
$trimmed4 = trim($title4);
$trimmed5 = trim($title5);
$trimmed6 = trim($title6);
$trimmed7 = trim($title7);
$trimmed8 = trim($title8);

// Build SQL Query
$query = "UPDATE specialofferstable SET category = '$trimmed2', company_hotel = '$trimmed3', location = '$trimmed4', offer = '$trimmed5', price = '$trimmed6', offerends = '$trimmed7', mobile = '$trimmed8' WHERE `index` = '$title1'"; // specify the table and field names for the SQL query
}
if($result = mysql_query($query))
{
//go to the new member confirmation page
header('location: admin_confirmed.php');
exit;
}
else
{
echo "ERROR: ".mysql_error();
}

But we need to be able to directly edit and delete from the Edit and Delete links, for ease of use.

Any ideas how this can be accomplished?

[edited by: eelixduppy at 2:13 pm (utc) on Sep. 14, 2009]
[edit reason] disabled smileys [/edit]

Passing a db record to be edited / deleted

db record, PHP

galahad2

optik

galahad2

galahad2

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week