SQL Delete Row by id

Question

I'm trying to delete a row in an SQL database by an id. I have found questions here related to this but nothing seems to work, perhaps because my page is populated (dynamically?) based on selecting a variable. The rows are displayed on my page based on a dropdown (locationlab) and I have a delete button after each row. It looks like this.

I have the Id displayed temporarily at the end of the row just be sure that the code sees the variable (& it does!).

The code to populate the page looks like this:

<?php
    $locationlab = $_POST[locationlab];
    $sql = "SELECT * FROM lab WHERE locationlab LIKE '{$locationlab}'";
    echo($locationlab);
    $result = $conn->query($sql);
     if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo'
  <table>
  <form action=testpage2.php method=post>
    <td width="10%"><input type=text name=make value='. $row["make"].'></td>
    <td width="10%"><input type=text name=model value='. $row["model"].'></td>
    <td width="20%"><input type=text name=hostname value='. $row["hostname"].'></td>
    <td width="15%"><input type=text name=ipaddress value='. $row["ipaddress"].'></td>
    <td width="20%"><input type=text name=ipmiipaddress value='. $row["ipmiipaddress"].'></td>
    <td width="15%"><input type=text name=terminalserveraddress value='. $row["terminalserveraddress"].'></td>
    <td width="10%"><input type=text name=locationlab value='. $row["locationlab"].'></td>
    <td><input type=submit name=update value=update></td>
    <td><input type=submit name=delete value=delete></td>
    <td id=id name=id value='. $row["id"].'>'. $row["id"].'</td>
</table>
</form>';
        }}
        ?>

I can input the SQL query below manually in the phpMyAdmin page so I know it is correct. The code for the Delete button looks like this:

   <?php
if(isset($_POST['delete'])) {
$deletequery = ("DELETE * FROM lab WHERE ='$_POST[id]'");
mysql_query($deletequery, $conn);  
};
?>

When I click the delete button it appears to refresh the page but nothing changes. I imagine that if I can get the delete button working, the update will work in a similar fashion but for now I'm stumped.

Its not even vaguely correct `"DELETE FROM lab WHERE SOME_COLUMN_NAME = '{$_POST['id']}'"` — RiggsFolly, May 01 '16 at 14:51
Please dont use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, May 01 '16 at 14:53

score 0 · Answer 1 · edited May 01 '16 at 14:52

0

<?php
if(isset($_POST['delete'])) {
    $deletequery = ("DELETE FROM lab WHERE **columnName** ='$_POST[id]'");
    mysql_query($deletequery, $conn);  
}
?>

You are missing column name in query. Also there is no * in DELETE statement, because deleting means deleting row.

edited May 01 '16 at 14:52

RiggsFolly

93,638
21
103
149

answered May 01 '16 at 14:48

Siim Kallari

851
6
17

1

Please dont use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) – RiggsFolly May 01 '16 at 14:53

Arjun Bala · Answer 2 · 2016-05-02T14:47:51.740

0

First of all let me help you in formatting the code

you should not write entire HTML code in echo...

instead try this one....

<?php
while($row = $result->fetch_assoc()) {
?>
<table>
<form action="testpage2.php" method="pos">
   <td width="10%"><input type="text" name="make" value="<?= $row["make"] ?>"></td>
 ....
 ....

</table>
</form>
  <?php
    }
  ?>

also you should use mysqli instead of mysql

and your database query is also incorrect, it must be like this..

DELETE FROM lab WHERE id ='$_POST[id]'

if you use mysqli then you can also use some functions like this..

mysqli_query($con,$deletequery)
if(mysqli_errno($con))
{
    echo("SOme error while executing query : ".mysqli_error($con));
}

edited May 02 '16 at 14:47

answered May 01 '16 at 16:38

Arjun Bala

287
1
8

Thanks so much for your advice. I have adjusted the code as you recommended. It appears to refresh the page when I press the delete button but it still does not delete the record. – user3524358 May 02 '16 at 14:32
check the answer I have edited, you can use mysqli_error function to check whats the problem in your query – Arjun Bala May 02 '16 at 14:48
So when I click the delete button, it refreshes the page & displays one of the values in that row (terminalserver) & nothing else. It doesn't perform the delete operation. Its doing something, but not what I expect. – user3524358 May 02 '16 at 17:34

SQL Delete Row by id

2 Answers2