Deleting a database row when delete link is clicked

Question

The following PHP script is used to retrieve a set of data (order numbers) in my database

<?php
            require("includes/db.php");

            $sql="SELECT * FROM `order` ";
            $result=mysqli_query($db,$sql);
echo"<head>";
echo'
        <link rel="stylesheet" href="view.css">
            <head>


        ';
echo"</head>";

echo "<body >";
echo "<table border=1 cellspacing=0 cellpadding=4 > " ;
echo"<tr bgcolor=grey>";
echo"<td align=center>";
echo "<font size=4>";
echo "<B>";
echo "Order No.";
echo "</B>";
echo"</td>";
echo"</tr>";


while($row=mysqli_fetch_array($result))
{

    echo"<tr>";
     echo"<td align=center>";
    echo $row["OrderNo."];
     echo "<br>";
     echo"</td>";
    echo"<td align=center>";
    echo "<a href='delete.php?del=";
    echo $row['OrderNo.'];
    echo">delete</a>";
     echo "<br>";
     echo"</td>";
    echo"</tr>";
}
    echo"</table>";

?>

When the delete link in each ow is clicked that specific row must be deleted! The PHP script for deletion is as follows

< ?php
     include("includes/db.php");

    if( isset($_GET['del']) )
    {
        $id = $_GET['del'];
        $sql= "DELETE FROM order WHERE OrderNo.='$id'";
        $res= mysqli_query($db,$sql) or die("Failed".mysql_error());

    }
?>

I did not redirect the 2nd PHP script to the initial one in order to identify the error! when the delete link is clicked i get the following on the screen with out any deletion of the row!

< ?php 
    include("includes/db.php"); 
    if( isset($_GET['del']) ) { 
        $id = $_GET['del']; 
        $sql= "DELETE FROM order WHERE OrderNo.='$id'"; 
        $res= mysqli_query($db,$sql) or die("Failed".mysql_error()); 
    } 
?>

How can I rectify this in order to delete a row when its delete link is clicked?

Look at your first query vs your second and the word `order` specifically. You also are open to SQL injections, use parameterized queries. `SELECT * FROM \`order\`` == correct. `DELETE FROM order` == invalid. — chris85, Oct 14 '16 at 03:46
Possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — chris85, Oct 14 '16 at 03:47
What is your column name? "OrderNo" or "OrderNo." (ie with a dot) — user3419778, Oct 14 '16 at 04:27

user3419778 · Accepted Answer · 2016-10-14T06:59:00.680

1

Try this

$sql= "DELETE FROM `order` WHERE `OrderNo.` = '$id' ";

Also Something wrong with html table row. You have not closed the single quote(') before delete. Please try with below code.

echo "<td align=center>";
echo "<a href='delete.php?del=";
echo $row['OrderNo.'];
echo "'>delete</a>";
echo "<br>";
echo"</td>";

edited Oct 14 '16 at 06:59

answered Oct 14 '16 at 04:33

user3419778

856
3
8
11

nope its still not working ! i still get the error "failed"! – Amal Oct 14 '16 at 05:28
if i am to delete that row and transfer the values to another table how can i do it? – Amal Oct 14 '16 at 07:29
Before delete, you have insert the row from this table to another. INSERT INTO Table2(Column21, Column22 ,...) SELECT Column11, Column12,... FROM `order` WHERE `OrderNo.` = '$id' "; – user3419778 Oct 14 '16 at 07:36
now there are 2 sql queries on the same php script! do i need to execute mysqli_query() method twice? creating 2 variables? – Amal Oct 14 '16 at 12:53

score 0 · Answer 2 · answered Oct 14 '16 at 03:31

0

Try this :

$sql= "DELETE FROM order WHERE OrderNo = '".$id."'";

answered Oct 14 '16 at 03:31

Razib Al Mamun

2,663
1
16
24

no error occurs ! only the 2nd php script appears on the web browser! like echoin a certain string in php – Amal Oct 14 '16 at 03:36
Integers don't need to be quoted. – chris85 Oct 14 '16 at 03:49
1

can you explain what you getting actual mysqli_error()? – Razib Al Mamun Oct 14 '16 at 04:59
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order WHERE OrderNo. = '1>delete 2 – Amal Oct 14 '16 at 06:17
@Amal What is your column name? "OrderNo" or "OrderNo." i thinks you added extra dot after OrderNo – Razib Al Mamun Oct 14 '16 at 06:21

score 0 · Answer 3 · answered Oct 14 '16 at 03:36

0

your second code says it all. change < ?php to <?php. you are misidentifying the file as a php a script in the first place.

answered Oct 14 '16 at 03:36

mahmoudajawad

105
2
10

thanx now its identified as a php file but when i click delete i get the fail message that means there is still an error :( – Amal Oct 14 '16 at 04:01
you are not getting the error because you are using `mysql_error()` with the `MySQLi` extension. replace `mysql_error` with `mysqli_error($db)` – mahmoudajawad Oct 14 '16 at 05:44
thanx now i get the error! You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order WHERE OrderNo. = '1>delete – Amal Oct 14 '16 at 06:14
how can i coorect this? – Amal Oct 14 '16 at 06:14
A dot in a column name could be an issue try this `WHERE \`OrderNo.\` = '$id'` as others suggested. – mahmoudajawad Oct 14 '16 at 06:39

Deleting a database row when delete link is clicked

3 Answers3