PHP not deleting row in mysql

Question

Been trying to delete a row in mysql like this but its not working. It echoes "failed to delete".

I dont know what is wrong with the code. Any suggestions will be welcomed.

<?php
 error_reporting(0);
 include("db_config.php");

// array for JSON response
$response = array();
if( isset($_GET['id'] ) ) {

    $id=$_GET['id'];
    $data=$_GET['data'];
    $item=$_GET['item'];
    $time=$_GET['time'];

    $result = mysql_query("delete from myorder where id='$id' ");

    $row_count = mysql_affected_rows();

    if($row_count>0){
        $response["success"] = 1;
        $response["message"] = "Deleted Sucessfully.";
       }
    else{
        $response["success"] = 0;
        $response["message"] = "Failed To Delete";
     }
  // echoing JSON response
  echo json_encode($response);

 }
?>

My table structure:

CREATE TABLE `myorder` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `data` varchar(20) NOT NULL,
  `item` varchar(255) DEFAULT NULL,
  `time` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

And my primary data is:

INSERT INTO `myorder` VALUES(23, '500MB', 'safaricom \nsteve\n0715611306', '11:32 PM 26 Aug, 2015 ');
INSERT INTO `myorder` VALUES(24, '4GB', 'safaricom \nsteve\n0715611306', '00:30 AM 27 Aug, 2015 ');
INSERT INTO `myorder` VALUES(25, '9\nGB', 'airtel \nsteve\n0715611306', '00:31 AM 27 Aug, 2015 ');
INSERT INTO `myorder` VALUES(26, '9\nGB', 'airtel \nsteve\n0715611306', '00:31 AM 27 Aug, 2015 ');

Answer 1

You are encasing your $id field with single quotes meaning that the SQL engine will parse '$id' as a string literal, this is not what you want as you would like to use the dynamic id passed to the function. To do this you can append the ID to the query by doing:

"DELETE FROM myorder WHERE id = " . $id;

Whilst this may work, there is a serious risk for SQL injection from a malicious user, I understand you're new to PHP but the sooner you find out about these dangers the better.

For example, a user may set $id to ;DELETE FROM myorder which would terminate the first query and run the second query, deleting all records in your myorder table. Even worse than this a malicious attacker may attempt to guess common names used for tables in schemas such as users and run a query like

;SELECT * FROM users

This could expose sensitive information such as usernames and passwords and really put you in a position you don't want to be in...

To solve this, there are some really popular database wrappers, my favourite PDO. It allows you to safely bind values to a query before it executes, prepared values will default to strings so they won't be parsed as queries on the server, thus voiding you from this sort of attack.

I have wrote a simple Database wrapper class that you could use to handle all CRUD operations, it can be found here. You could then use it like:

// Require the DB class and create an instance
require('../path/to/file/Database.php');
$db = new Database();

// Create a query, use the :param_name syntax to bind values
$query = "DELETE FROM myorder WHERE id = :id";

// Create the associative array that has data binding for values
$data = Array(":id" => $id);

// Run the query and capture the result
$deleted = $db->delete($query, $data);

// Check the result
if($deleted > 0)
    // deleted successfully
else
    // handle errors here

The query will now bind and run safely without risk of SQL injection, if you need any further help please ask.

Answer 2

Take the quotes out from around $id in your query and concatenate just in case.

$sql = "DELETE FROM myorder WHERE id = ".$id;

Edit: As comments mentioned, you should be cleaning your variables before using them in a query to prevent sql injection.

I would recommend to first stop using mysql_ since it is no longer supported and switch to either mysqli_ or PDO. Personally I prefer PDO.

Here is a link to a post all about SQL injection How can I prevent SQL injection in PHP?

Answer 3

As this is I assume a script that is run from an AJAX call you are not going to be able to debug it using simple echo statements so try this, and then look with the broswer debugger at what is being sent to the browser in the JSON.

It might also be useful to see what is in db_config.php

<?php
 error_reporting(0);
 include("db_config.php");

// array for JSON response
$response = array();
if( isset($_GET['id'] ) ) {

    $id=$_GET['id'];
    //$data=$_GET['data'];   // not tested for so dont use
    //$item=$_GET['item'];   // not tested for so dont use
    //$time=$_GET['time'];   // not tested for so dont use

    $sql = "delete from myorder where id=$id";
    $result = mysql_query($sql);

    // you shoud always be checking error status after this command.
    if ( ! $result ) {
        $response['mysql_query'] = $sql;
        $response['mysql_error'] = mysql_error();
        echo json_encode($response);
        exit;
    }

    $row_count = mysql_affected_rows();
    $response['affectedRows'] = mysql_affected_rows();

    if($row_count>0){
        $response["success"] = 1;
        $response["message"] = "Deleted Sucessfully.";
    } else {
        $response["success"] = 0;
        $response["message"] = "Failed To Delete";
    }
  // echoing JSON response
  echo json_encode($response);

 }
?>

Also as you are obviously just learning PHP and MYSQL please dont waste your time learning the mysql_ database extension, it is deprecated (soon to be removed completely) in PHP7 its has been removed. Instead use your time more fruitfully and learn either the mysqli_ or PDO extensions. See this post for help in deciding which you prefer.