Deleting database row via PHP

Question

I've looked through the "recommendations" based on the title I placed this as and the topics either weren't answered or didn't relate to my situation (from what I could see).

I've got a PHP HTML page that calls information from a database and inputs it into a table. I've got a a tag ready for a delete function but I just can't seem to get it right. I was hoping someone here would be able to help me out.

These are all the relevant pages.

connection.php

<?php
    try{
        $handler = new PDO('mysql:host=127.0.0.1;dbname=data', 'root', 'root');
        $handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }catch(PDOException $e){
        echo 'ERROR: ' . $e->getMessage();
    }
?>

location1.php

<?php
include('connection.php');
$query = $handler->query('SELECT * FROM subsordered WHERE location ="location1"');
$delete = $handler->query('DELETE * FROM subsordered WHERE id = :id');

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
    <h1 align="center">Location 1</h1>
    <table align="center" border="1" height="10%" width="80%">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Desc</th>
            <th>Location</th>
            <th></th>
    </tr>
    <?php while($row = $query->fetch()){ '<tr>
        <td align="center">';echo $row[''],'</td>
        <td align="center">';echo $row['id'],'</td>
        <td align="center">';echo $row['name'],'</td>
        <td align="center">';echo $row['desc'],'</td>
        <td align="center">';echo $row['location'],'</td>
        <td align="center"><a href="connection.php?id='.$row['id'].'">Delete</a></td>
    </tr>';}  ?>
</table>

When I try to keep the mysql delete call in the first php section in location1 it breaks the page, I'm pretty sure it has something to do with the fact that I am assigning 2 calls to 1 function but I other than making a brand new page jsut for a single delete call I don't know what else to do

You should descide if you wish to query or delete someting, the way your code is, you always execure both SQL statement — André Schild
– André Schild, Commented Jul 4, 2015 at 18:49

Mindastic · Accepted Answer · 2015-07-04 18:07:54Z

2

Try:

<?php
include('connection.php');
$query = $handler->query('SELECT * FROM subsordered WHERE location ="location1"');
if (isset($_GET["id"])) {
  $delete = $handler->exec('DELETE FROM subsordered WHERE id = \'' . $_GET["id"] . '\'');
}

?>

This should work. Anyway, i would consider changing the code in order to prevent SQL injection (Check this)

edited Jul 4, 2015 at 18:07

answered Jul 4, 2015 at 17:22

Mindastic

4,1413 gold badges21 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

John Over a year ago

That allows the page to still run. How do I run the delete call when I click on on the a tag? Also to do with the poor coding. I have used prepare in other sections of my program. This is just to get it running, from there I will make it more secure.

Mindastic Over a year ago

But, based in your code, you are doing a Server request each time you want to delete a record. WIth that code, you won't be able to delete a record without refreshing (if that is what you want). If you want to do it, you have to think about using ajax. Is that what you want? If not, i might have not understand your question correctly.

John Over a year ago

I was hoping to be able to delete the rows using just PHP and MySQL. Needing to refresh the page isn't a big deal because it can be done automatically. Ajax is brilliant I am using for a mobile aspect of this but I was hoping to avoid it for this section.

Mindastic Over a year ago

Well, in that case, isn't my first code working? Try by switching the order of the code, because it is getting the elements before deleting the record you want, so it will still show all the records despite one of them was deleted.

John Over a year ago

No, I checked the database just to make sure and it remained the same. I think the href may be the issue but I'm not 100% sure.

|

admiralchip · Accepted Answer · 2015-07-04 18:28:49Z

0

I'm not so familiar with PDO but from what I see this line has a problem:

$delete = $handler->query('DELETE * FROM subsordered WHERE id = :id');

It should be this I think:

$delete = $handler->query('DELETE FROM subsordered WHERE id = :id');

Have a look at the example here: http://www.mustbebuilt.co.uk/php/insert-update-and-delete-with-pdo/

edited Jul 4, 2015 at 18:28

answered Jul 4, 2015 at 17:24

admiralchip

1451 gold badge2 silver badges11 bronze badges

Collectives™ on Stack Overflow

Deleting database row via PHP

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related