我有一个显示来自MySQL数据库的数据的HTML表。我想添加一个“删除行”按钮,以便单击一下就可以从数据库表中删除一行(链接到会员ID的数据)。
我可以看到SQL应该做什么,但是我不知道如何用PHP实现它。
我还有一个搜索页面,您可以在其中输入成员的名字来搜索成员,我也想在该搜索页面中添加相同的删除功能。
但是现在我想让它在HTML表格上工作,该表格首先显示所有数据!
这是我的代码(减去与数据库的连接和断开连接):
// Retrieve Member Details
$sql = "SELECT MemberID, FirstName, Surname, DOB, Address, County,
PostCode, MobileNo, Email FROM Members";
$result = mysql_query($sql, $connection);
//create table
echo "<table>";
// Loop through the data and then display chosen data
echo "<h2>Member Details</h2>";
echo "<tr><th>Member ID</th><th>First Name</th><th>Surname</th>
<th>DOB</th><th>Address</th><th>County</th><th>Post Code</th>
<th>Mobile Number</th><th>Email</th><tr>";
while($a_row = mysql_fetch_assoc($result))
echo "<tr><td>" . $a_row['MemberID']
. "</td><td>" . $a_row['FirstName']
. "</td><td>" . $a_row['Surname']
. "</td><td>" . $a_row['DOB']
. "</td><td>" . $a_row['Address']
. "</td><td>" . $a_row['County']
. "</td><td>" . $a_row['PostCode']
. "</td><td>" . $a_row['MobileNo']
. "</td><td>" . $a_row['Email']
. "</td><tr>";
//close table
echo "</table>";
搜索代码页:
<?php
// Connecting to Database Server
$connection = mysql_connect("localhost", "1520621",
// If connection cannot be made to Database Server
if (!$connection)
die("Cannot connect to Database");
// Select the database
mysql_select_db("db1520621", $connection)
// If Database cannot be found
or die("Cannot find Database");
// SQL
// Select all data from Members table where FirstName matches that which is inserted into searchName using POST
$sql ="SELECT * FROM Members";
$sql.=" WHERE FirstName=\"".$_POST["searchName"]."\"";
// Execute the query
$queryResult=mysql_query($sql);
//Check for errors and display following messages if there is
if (mysql_error())
{
echo "Problem with Query<br>";
echo "The following error message was returned from MySQL:<br>";
echo mysql_error();
exit;
}
//Create table
echo "<table>";
echo "<h2>Member Details</h2>";
echo "<tr><th>First Name</th><th>Surname</th><th>DOB</th><th>Address</th><th>County</th><th>Post Code</th><th>Mobile Number</th><th>Email</th><tr>";
// If no results found show message. If results found, loop if there is more than 1 result
if (mysql_num_rows($queryResult)==0)
{
echo "No members with that name";
}
else
{
while ($dbRecord=mysql_fetch_array($queryResult))
{
echo "<tr><td>".$dbRecord["FirstName"]."</td><td>".$dbRecord["Surname"]."</td><td>".$dbRecord["DOB"]."</td><td>".$dbRecord["Address"]."</td><td>".$dbRecord["County"]."</td><td>".$dbRecord["PostCode"]."</td><td>".$dbRecord["MobileNo"]."</td><td>".$dbRecord["Email"]."</td></tr>";
}
}
// Close connection to Database
mysql_close($connection);
?>
最佳答案
这是从数据库中删除行的完整代码。首先,您必须添加删除链接并传递要删除的成员ID。
// Check if delete button is clicked and delete respective row
if(isset($_GET['DeleteID']) AND !empty($_GET['DeleteID']))
mysql_query("DELETE FROM Members where MemberID = '".$_GET['DeleteID']."'", $connection);
// Retrieve Member Details
$sql = "SELECT MemberID, FirstName, Surname, DOB, Address, County, PostCode, MobileNo, Email FROM Members";
$result = mysql_query($sql, $connection);
//create table
echo "<table>";
// Loop through the data and then display chosen data
echo "<h2>Member Details</h2>";
echo "<tr><th>Member ID</th><th>First Name</th><th>Surname</th><th>DOB</th><th>Address</th><th>County</th><th>Post Code</th><th>Mobile Number</th><th>Email</th><th>Delete</th><tr>";
while($a_row = mysql_fetch_assoc($result)){
echo "<tr><td>" . $a_row['MemberID'] . "</td><td>" . $a_row['FirstName'] . "</td><td>" . $a_row['Surname'] . "</td><td>" . $a_row['DOB'] . "</td><td>" . $a_row['Address'] . "</td><td>" . $a_row['County'] . "</td><td>" . $a_row['PostCode'] . "</td><td>" . $a_row['MobileNo'] . "</td><td>" . $a_row['Email'] . "</td>";
echo "<td><a href='?DeleteID=" . $a_row['MemberID'] . "' onclick=\"return confirm('Delete?')\">Delete</a></td></tr>";
}
//close table
echo "</table>";
搜索页面代码
<?php
// Connecting to Database Server
$connection = mysql_connect("localhost", "1520621", "w9p1n5");
// If connection cannot be made to Database Server
if (!$connection)
die("Cannot connect to Database");
// Select the database
mysql_select_db("db1520621", $connection)
// If Database cannot be found
or die("Cannot find Database");
// SQL
if(isset($_GET['DeleteID']) AND !empty($_GET['DeleteID']))
mysql_query("DELETE FROM Members where MemberID = '".$_GET['DeleteID']."'", $connection);
// Select all data from Members table where FirstName matches that which is inserted into searchName using POST
$sql ="SELECT * FROM Members";
$sql.=" WHERE FirstName=\"".$_REQUEST["searchName"]."\"";
// Execute the query
$queryResult=mysql_query($sql);
//Check for errors and display following messages if there is
if (mysql_error())
{
echo "Problem with Query<br>";
echo "The following error message was returned from MySQL:<br>";
echo mysql_error();
exit;
}
//Create table
echo "<table>";
echo "<h2>Member Details</h2>";
echo "<tr><th>First Name</th><th>Surname</th><th>DOB</th><th>Address</th><th>County</th><th>Post Code</th><th>Mobile Number</th><th>Email</th><tr>";
// If no results found show message. If results found, loop if there is more than 1 result
if (mysql_num_rows($queryResult)==0)
{
echo "No members with that name";
}
else
{
while ($dbRecord=mysql_fetch_array($queryResult))
{
echo "<tr><td>".$dbRecord["FirstName"]."</td><td>".$dbRecord["Surname"]."</td><td>".$dbRecord["DOB"]."</td><td>".$dbRecord["Address"]."</td><td>".$dbRecord["County"]."</td><td>".$dbRecord["PostCode"]."</td><td>".$dbRecord["MobileNo"]."</td><td>".$dbRecord["Email"]."</td>";
echo "<td><a href='?DeleteID=" . $dbRecord['MemberID'] .(isset($_REQUEST['searchName'])?'&searchName='.$_REQUEST['searchName']:''). "' onclick=\"return confirm('Delete?')\">Delete</a></td></tr>";
}
}
// Close connection to Database
mysql_close($connection);
?>