MYSQL query in PHP is deleting all the rows in my table

Question

I'm working on a PHP script that uses delete and I only want to delete 1 row. But everytime I tried to delete 1 row, the SQL executes but deletes the entire rows in the table.

The following is my PHP script

<table>
    <tr>
        <th>No.</th>
        <th>Publisher Code</th>
        <th>Publisher Name</th>
        <th>City</th>
        <th> </th>
    </tr>
    <!-- PHP FETCH/RETRIEVE FROM DB -->
    <?php
        include_once 'dbconnect.php';
        $sql = mysqli_query($conn, "SELECT * FROM tbl_publisher");
        $ctr = 1;
        $record = mysqli_num_rows($sql);
        if ($record > 0) {
            while ($record = mysqli_fetch_array($sql)) {
    ?>
            <tr>
                <td> <?php echo $ctr++ ?> </td>
                <td> <?php echo $record['TBL_PUBLISHER_CODE']; ?> </td>
                <td> <?php echo $record['TBL_PUBLISHER_NAME']; ?> </td>
                <td> <?php echo $record['CITY']; ?> </td>
                <td id="actions">
                <center>
                <a href="delete.php?del-pub=<?php echo htmlentities($record['TBL_PUBLISHER_CODE']);?>" onclick="return confirm('Do you really want to Delete?')">Delete</a>
                </center>
                </td>
            </tr>
    <!-- closing tag for php script -->
    <?php
            }
        }
    ?>
    <!--  -->
</table>

and below is my delete.php

if(isset($_GET['del-pub']))
{
    $row = intval($_GET['del-pub']);
    $sql = mysqli_query($conn,"DELETE FROM tbl_publisher WHERE TBL_PUBLISHER_CODE = $row;");

    if ($sql) {
        header("Location: publisher.php");     
    }
    else {
        echo "<script>alert('Publisher was not deleted.');</script>"; 
        header("Location: publisher.php"); 
    }
    
}

I want to know how to delete only the 1 row. But it keeps deleting the entire rows in the table.

Dump the contents of `$sql` and check what the generated query is. Also, you shouldn't do sensitive operations (like writing or deleting) via a `GET` request. Accidental visits to those URLs can cause undesired loss of data. Do it via a `POST`. — El_Vanja, May 14 '21 at 10:12
Plus you should look into [prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to avoid SQL injection. — El_Vanja, May 14 '21 at 10:13
Okay, thank you for the suggestion and feedback, I found my solution! — Taniyow, May 14 '21 at 12:09
That's great. Please post the solution below as an Answer, so everyone can benefit (and people will upvote you if they like it / find it useful). https://stackoverflow.com/help/self-answer — ADyson, May 14 '21 at 12:09

score 1 · Answer 1 · answered May 14 '21 at 12:14

After dumping the contents of $sql, I found out that my sql syntax is the culprit.

Since TBL_PUBLISHER_CODE uses char as ID. Instead of doing "="

DELETE FROM tbl_publisher WHERE TBL_PUBLISHER_CODE = $row;

I used

DELETE FROM tbl_publisher WHERE TBL_PUBLISHER_CODE LIKE '".$row."'

MYSQL query in PHP is deleting all the rows in my table

1 Answers1