0

I have an HTML table displaying data from a MySQL database. I want to add a "delete row" button, so that with a click I can delete a row from the database table (data linked to the Member's ID).

I can see what the SQL should be doing, but I don't know how to implement it with PHP.

I also have a search page where you can search for a member by inputting their first name and I want to add the same delete function to that search page too.

But for now I want to get it working on an HTML table that shows all the data first!

Here is my code (minus the connecting and disconnecting from database and all that):

// 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>";

Code for Search page:

<?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);
  ?>
Improve this question
2
  • 1
    please Do not use Mysql_* funcitons these are depreciated in latest versions of PHP Commented Nov 27, 2015 at 22:08
  • I'm unclear on the problem. What happens when you run this? Commented Nov 27, 2015 at 23:10

2 Answers 2

Reset to default
1

You should be using PHP Data Objects (http://php.net/manual/en/ref.pdo-mysql.php) or MySQLi (http://php.net/manual/en/book.mysqli.php).

The Ending <tr> table on the echo line is not closed (</tr>) which might be messing with the HTML output.

SQL query for deleting:

$query = sprintf("DELETE FROM Members WHERE MemberID = '%s'",
mysql_real_escape_string($member_id));
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Here is the full code to delete row from database. First you have to add delete link and pass member id to be deleted.

// 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>";

Search page code

<?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);
?>
Improve this answer

8 Comments

I still can't get it working. Thanks for your help though.
What issue you are getting ?
The delete option appears, but only for the last record in the table. When I press delete, nothing is actually being deleted.
Okay I have edited above script. Can you try it now by copying fresh script from my answer ?
Is it possible to get this to run in an external file? I am wanting to get the members to be deleted when they are searched for (I have a search query) but I can't execute the search query and then the delete query at the same time?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.