How to delete row from table using PHP pdo

PHP
1

Hi I am just a beginner in web programming and I wanted to delete a specific row in my displayed table.

<?php
$con=mysqli_connect("localhost","user","pass","db");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM cpa_report");

echo "<head><style>
.page {
  font-family: 'Trebuchet MS', Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 100%;
  border-radius: 20px;
}
.page td, .page th {
  border: 1px solid #ddd;
  padding: 8px;
}

.page tr:nth-child(even){background-color: #f2f2f2;}

.page tr:hover {background-color: #BCBCBE;}

.page th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #FAE03C;
  color: black;
  border: 1px solid black;
}


</style></head>";
echo "<table class='page'>
<tr>
<th>ID</th>
<th>Agency</th>
<th>FileName</th>
<th>FileType</th>
<th>Date Received</th>
<th>Action</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['agency'] . "</td>";
echo "<td>" . $row['filename'] . "</td>";
echo "<td>" . $row['filetype'] . "</td>";
echo "<td>" . $row['date'] . "</td>";
echo "<td><a target='_blank' href='../annual/CPA/" . $row['filename'] . "'>OPEN</a></td>";
 echo "<td><a href='delete.php?id=$id';?>Delete</a></td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

this my delete.php

<?php
require_once("../include/dbcon.php");

    $pdoQuery = "DELETE FROM cpa_report where id =" . $_GET['id'];
    $pdoResult =  $pdoConnect->prepare($pdoQuery);
    $pdoResult->execute();
	header('location:../agencies/cpa.php');

	$pdoConnect = null;
?>
2

At least you have to provide some actual problem for someone to help you, instead of a description of what you want to achieve. What do you expect? What do you get? Where do you think is the problem?

  • Why do you use mysqli AND pdo?
  • Set your PDO to throw errors.

Also you should consider using Prepared Statements - it’s easy - if you want to protect your database to be deleted by random people.

3

Okay.

I suggest you go have a read of those, so you can better grasp prepared statements. Because you’re not using them.

Where is $pdoConnect defined? Your first code block doesnt define it.

1 Like
4 
 echo "<td><a href='delete.php?id=$id';?>Delete</a></td>";

Where does $id come from? What value does your browser show if you hover over or click this link?

Maybe it’s in the included dbcon.php file.

5

You are actually sort of on the right track in that you used a prepared statement but you just used it wrong and left out a step. You want something like:

$sql = 'DELETE FROM cpa_report where id = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$_GET['id']]);

There is still plenty of stuff to improve. Deleting (or just updating in general) using GET is generally frowned upon. And you will have to spend a bit of time going through the docs to see how prepared statements work.

http://php.net/manual/en/pdo.prepared-statements.php

1 Like
6

Looking further, once you have the link and the prepared statement working, before you delete the row from the table, surely you’ll need to retrieve the row, get the filename, delete the file, then delete the row? Otherwise you’ll end up with a load of orphaned files on the server that have no entry in the database table.

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.