Delete a row in PHP

Question

I'm looking to delete a row in a database so when I click the button it deletes that row. I've looked around and found examples but these relate to having external PHP sheets but I'm looking to include mine on the same sheet. My code is:

if(isset($_GET['deleteId']))
{ 
        $query = mysql_query("DELETE FROM kingswinfordcc_vehicles WHERE vehicleid = '$_GET['deleteId']'");
        header("Location: vehicle-table.php");  
}

which if I'm correct deletes the row from the specified table and then moves the user to the new page.

Then my table holds a button which I'm currently using as a link, as follows so when clicked links back to the same page

    <td> <a href="vehicle-table.php?deleteId=<?php echo $vehicle_row['vehicleid'] ?>">Delete</a></td>

I'm only doing it this way as a senior colleague has told me to. Any advice/help is greatfully appreciated.

try to send the query **index.php?deleteId=NULL'; drop database kingiwnfordcc; '** OR read about SQL injection — opalenzuela, Oct 29 '13 at 14:11
This "senior colleague" of yours needs to update their knowledge. The mysql_ functions have been deprecated and will be removed from PHP. Check out PDO or mysqli_ — j08691, Oct 29 '13 at 14:11
[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [pdo](https://wiki.php.net/rfc/mysql_deprecation) or [mysqli](http://stackoverflow.com/questions/tagged/mysqli). — zessx, Oct 29 '13 at 14:11
What's your question? Are you asking how to do it without reloading the page? You need to use AJAX. — Barmar, Oct 29 '13 at 14:13
Use POST for such a task, not GET. See http://stackoverflow.com/questions/46585/when-do-you-use-post-and-when-do-you-use-get — Reeno, Oct 29 '13 at 14:16
Cheers guys for taking the time to look and the advice - i shall have a look — user2729578, Oct 29 '13 at 14:27
@opalenzuela I did what you asked, but nothing happened. Like at all... — PeeHaa, Oct 29 '13 at 14:48
@opalenzuela `mysql` does not support multi queries, the `drop database` part would not be executed — Daniel W., Oct 29 '13 at 15:01

Kender · Answer 1 · 2013-10-29T14:39:05.410

You can do something like this, have the action of the form lead to the same page as the button you want clicked

if(!isset($_GET['deleteId'])
{
    $mysqli = new mysqli("host", "user", "pass", "table");

    $sql = "DELETE FROM kingswinfordcc_vehicles
            WHERE vehicleid = ?"

    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $_GET['deleteId']);
    $stmt->execute();
    $stmt->close();

    $mysqli->close();
}

I highly recommend adding in some verification that they want the item permanently deleted and you can even add in a try/catch statement

score 0 · Accepted Answer · answered Oct 29 '13 at 14:13

0

Your query contains multiple quotes of the same type. I think the following will work:

if(isset($_GET['deleteId']))
{ 
     $query = mysql_query('DELETE FROM kingswinfordcc_vehicles WHERE vehicleid = ' . mysql_real_escape_string($_GET['deleteId']));
     header("Location: vehicle-table.php");  
}

answered Oct 29 '13 at 14:13

Coanda

373
5
12

Correct :) Thanks for the help/advice - muchly appreciated!! – user2729578 Oct 29 '13 at 14:37

Robert · Answer 3 · 2013-10-29T14:50:31.157

You need DB connection to do it.
Don't use mysql_* functions they are depracated. Check mysqli and pdo.
SQL Injection read about it

It's for academic so I will give you really simple answer with mysql_* functions

$id = intval($_GET['deleteId'], 0); //this function parses the param to int if it is not number there will be 0
if($id) //checking if id is true(not zero)
{ 
    $query = mysql_query("DELETE FROM `kingswinfordcc_vehicles` WHERE `vehicleid` = '$id'");
    header("Location: vehicle-table.php");  //after query moving to vehicle-table.php if query fail and show msg it this line won't move user to this page!
}

Remember you need mysql connection to do such query.

PDO solution:

<?php
/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1'; //fill with proper data
$user = 'dbuser'; 
$password = 'dbpass';
$id = (int)$_GET['id'];
try{
    $dbh = new PDO($dsn, $user, $password);
    $st  = $a->prepare("UPDATE `users` SET user=:id");
    $st->bindParam(":id", $id, PDO::PARAM_INT);
    $st->execute();
    header("Location: vehicle-table.php"); 
    //echo $st->rowCount(); //this line will show how many rows were deleted
}
catch (PDOException $e){
    echo 'Error: ' . $e->getMessage();
}

?>

Thanks @Robert - my DB connection is up the top with the relevant config files. I'll give this a try - thanks for replying — user2729578, Oct 29 '13 at 14:31
typo error :var need to be changed to :id ? or seems that query invalid :) — Павел Иванов, Oct 29 '13 at 14:33
@Павел Иванов yes you can because PDOException extends Exception. About var it should be :id instead of :var that's for mentioning — Robert, Oct 29 '13 at 14:49

Delete a row in PHP

3 Answers3