Delete MySQL PHP

Question

    mysql_connect('localhost', 'root', '')
            or die(mysql_error());
    mysql_select_db('shuttle_service_system') 
    or die(mysql_error());

    $insert="INSERT INTO inactive (ID_No, User_Password, First_Name, Last_Name, Email, Contact_Number)
    VALUES('". $ID_No ."','". $UserPassword ."','". $FirstName ."','". $LastName ."','". $Email ."','". $ContactNumber ."')";
    $result=mysql_query($insert);
    $sql="DELETE FROM users WHERE ID_No = '$ID_No'";
    $result2=mysql_query($sql);

    if($result && $result2){    
        echo"Successful!";
    } else {
        echo "&nbsp Error";
    }

Hi guys I have been stuck in delete function of MySQL, I have tried searching the net but when I ran my code it always goes to the else part which means there is an error, the insert is already okay but the delete is not.

Please avoid using mysql_* function they have been deprecated. use PDOor mysqli_* functions — Krimson, Apr 02 '14 at 17:35
**By building SQL statements with outside variables, you are leaving yourself open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. You can also see http://bobby-tables.com/php for alternatives and explanation of the danger. — Andy Lester, Apr 02 '14 at 17:37
Hi guys what does this mean? Cannot delete or update a parent row: a foreign key constraint fails (`shuttle_service_system`.`balance`, CONSTRAINT `balance_ibfk_1` FOREIGN KEY (`ID_No`) REFERENCES `users` (`ID_No`)) — Gaston Velarde, Apr 02 '14 at 18:00
That's because of referential integrity constraint. A parent cannot be deleted until all its child references are deleted. An ugly hack would be http://stackoverflow.com/a/17828127/1003917 — Shankar Narayana Damodaran, Apr 02 '14 at 18:05

score 0 · Answer 1 · answered Apr 02 '14 at 17:37

0

PHP variables are allowed in double quotes. Hence try this,

$sql="DELETE FROM users WHERE ID_No = $ID_No";

answered Apr 02 '14 at 17:37

Parag Tyagi

8,780
3
42
47

That's good until `$ID_no` has the value of `1 OR (1=1)` and the resulting SQL is `DELETE FROM users WHERE ID_No =1 OR (1=1)` and the entire USERS table gets deleted. – Andy Lester Apr 02 '14 at 17:38

score 0 · Answer 2 · answered Apr 02 '14 at 17:39

0

Your first query was not properly escaped. Rewrite like

$insert="INSERT INTO inactive (`ID_No`, `User_Password`, `First_Name`, `Last_Name`, `Email`, `Contact_Number`)
    VALUES('$ID_No','$UserPassword','$FirstName','$LastName','$Email','$ContactNumber')";

This (mysql_*) extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. Switching to PreparedStatements is even more better to ward off SQL Injection attacks !

answered Apr 02 '14 at 17:39

Shankar Narayana Damodaran

68,075
43
96
126

hi man, it says Cannot delete or update a parent row: a foreign key constraint fails (`shuttle_service_system`.`balance`, CONSTRAINT `balance_ibfk_1` FOREIGN KEY (`ID_No`) REFERENCES `users` (`ID_No`)) what will I do? – Gaston Velarde Apr 02 '14 at 17:58
That's because of referential integrity constraint. A parent cannot be deleted until all its child references are deleted. – Shankar Narayana Damodaran Apr 02 '14 at 18:03
So I must first delete all the data that is connected to the table users? – Gaston Velarde Apr 02 '14 at 18:14
Yes exactly or you if you don't want that to happen , you can delete the link between those two tables. – Shankar Narayana Damodaran Apr 02 '14 at 18:16

Lapinou · Answer 3 · 2014-04-02T17:57:27.353

First, use PDO.

Make your connection Database like this:

function connectToDB(){

    $host='localhost';

    try {
        $user = 'username';
        $pass = 'password';
        $bdd = 'databaseName';
        $dns = 'mysql:host='.$host.';dbname='.$bdd.'';
        $options = array(
            PDO::ATTR_PERSISTENT => true,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        );
        return $connexion = new PDO($dns, $user, $pass, $options);
    }catch ( Exception $e ) {
        echo "Fail to connect: ", $e->getMessage();
        die();
    }
}

To delete something, here is an example:

function deleteUserWithId($ID_No){

    $connexion = connectToDB();

    try{
        $connexion->exec('DELETE FROM users WHERE ID_No = '.$ID_No);
    }catch(Exception $e){
        echo "Error: ".$e->getMessage();
    }
}

To insert something:

function addInactiveUser($UserPassword,$FirstName ,$LastName ,$Email,$ContactNumber){

    $connexion = connectToDB();

    $insert = $connexion->prepare('INSERT INTO inactive VALUES(:ID_No,
                                                            :User_Password,
                                                            :First_Name,
                                                            :Last_Name,
                                                            :Email,
                                                            :Contact_Number
                                                            )');
    try {

        // executing the request
        $success = $insert->execute(array(
            'ID_No'=>'',
            'User_Password'=>$UserPassword,
            'First_Name'=>$FirstName ,
            'Last_Name'=>$LastName ,
            'Email'=>$Email,
            'Contact_Number'=>$ContactNumber
        ));

        if($success)
            // OK
        else
            // KO

    }
    catch (Exception $e){
        echo "Error: ".$e->getMessage();
    }
}

To make a select:

// If you want to display X user per pages for example
function getAllInactiveUsers($page, $numberInactiveUserPerPage){

    $connexion = connectToDB();

    $firstInactiveUser = ($page - 1) * $numberInactiveUserPerPage;

    $selectAllInactiveUsers = $connexion->prepare('SELECT * FROM inactive ORDER BY ID_No DESC LIMIT '.$firstInactiveUser.','.$numberInactiveUserPerPage);

    return $selectAllInactiveUsers ;

}

To get the results of this methods, just do something like this:

$inactiveUsers= getAllInactiveUsers(1,15); // for page 1, display 15 users
$inactiveUsers->execute();

     while($row = $inactiveUsers->fetch(PDO::FETCH_OBJ)){

           $id = $row->ID_No;
           $first_name = $row->First_Name;

           // etc...
     }

Hope that's help :)

score 0 · Answer 4 · edited Apr 02 '14 at 21:52

0

I am not sure if this helps you, but as an alternative you could delete the last entry in the table:

$delQ = mysql_query("SELECT * FROM ph ORDER BY id DESC LIMIT 1" ); 
while(( $ar = mysql_fetch_array($delQ)) !== false){
    mysql_query("DELETE FROM ph WHERE id= $ar[id]");
}

edited Apr 02 '14 at 21:52

mit

11,083
11
50
74

answered Apr 02 '14 at 21:10

Abdullah Aden

882
9
13

Delete MySQL PHP

4 Answers4