PDO delete specified row from table

Question

I have a little problem ... I am using PDO and part of my code is to delete a specific row from the table in my database. My code is the below ...

function deleteFromWorkWhere($db,$table,$user,$rowId){
        switch($table){
            case 'work':
                $tbl = 'work';
                break;
        }
        if($rowId=='all'){ // delete all records
            $sql = 'DELETE FROM '.$tbl.' WHERE username=?';  // "?"s here will get replaced with the array elements below
            $stmt = $db->prepare($sql);
            $stmt->execute(array($user)); // these array elements will replace the above "?"s in this same order
            // check for errors 
            if($stmt->errorCode() == 0) {
                // no errors, show alert and refresh page
                return '<script type="text/javascript">alert("All work history was successfully cleared!"); window.location="CV.php"; </script>';
            } else {
                // had errors
                $errors = $stmt->errorInfo();
                return '<script type="text/javascript">alert("Error deleting work history!: '.$errors[2].'"); window.location="CV.php"; </script>'; 
            }
        }
        elseif($rowId){ // delete specified row 
            $sql = 'DELETE FROM '.$tbl.' WHERE username = ? AND id = ?';  // "?"s here will get replaced with the array elements below
            $stmt = $db->prepare($sql);
            $stmt->execute(array($user,$rowId)); // these array elements will replace the above "?"s in this same order
            $affected_rows = $stmt->rowCount(); // get the number of rows affected by this change
            return $affected_rows.' row deleted.';
            // check for errors 
            if($stmt->errorCode() == 0) {
                // no errors, show alert and refresh page
                return '<script type="text/javascript">alert("Selected work history was successfully cleared!"); window.location="CV.php"; </script>';
            } else {
                // had errors
                $errors = $stmt->errorInfo();
                return '<script type="text/javascript">alert("Error deleting work history: '.$errors[2].'"); window.location="CV.php"; </script>';  
            }
        }
        else{ /// return error
        }
    }   
    if(isset($_POST['clear_work'])){
            deleteFromWorkWhere($db,'work',$_SESSION['username'],'all');    
    }
    if(isset($_POST['clear_selected_work'])){
            deleteFromWorkWhere($db,'work',$_SESSION['username']);  
    }

The first if statement is used to delete ALL the data from the table and the else I want to use in order to delete a specific row, but it doesn't work, what am I doing wrong?

This is the button...

<input type="submit" value="Clear Selected Work History" name="clear_selected_work" />

@ultranaut it does not delete the specific row , it doesn't do nothing — Waaaaat, Apr 30 '15 at 15:48
If you're not getting any errors, it sounds like `$user` and/or `$rowId` aren't getting set to what you're expecting. — ultranaut, Apr 30 '15 at 15:51
@ultranaut the weird is that it works when i want to delete them all, that means that `$user` doesn't have any problem. If you see in the code for all the if isset look like this `if(isset($_POST['clear_education'])){ deleteFromEduWhere($db,'education',$_SESSION['username'],'all'); }` but for the specific row after the session we dont have something and I dont know what to write there — Waaaaat, Apr 30 '15 at 15:54
I think the code you posted above is missing the actual function declaration, but yeah if you're calling the function without a `$rowId` argument, then it's not even going to execute that block of code. — ultranaut, Apr 30 '15 at 16:17
@ultranaut if i do it like this `deleteFromWorkWhere($db,'work',$_SESSION['username'],$rowId);` still doesnt work — Waaaaat, Apr 30 '15 at 17:29
@ultranaut if I pass the id for example 2 it will delete it .... Weird??? — Waaaaat, Apr 30 '15 at 18:09
Do you ever get to that part of the code? Check with `var_dump($rowId);die();` after the elseif statement. Also, on another note, you're using `return` to show how many rows were affected but then you try to check for errors. You'll never get to the error checking if you use `return` before. — vcanales, Apr 30 '15 at 18:25
@devJunk If i do it like that (http://prntscr.com/6zztwp) still does not work :-/ — Waaaaat, Apr 30 '15 at 18:27
Did you get any output? If you didn't, that means `$rowId` doesn't pass the `if` part of the `elseif` and that'd be where to look for the problem. — vcanales, Apr 30 '15 at 18:48
@devJunk No i dont get any output. I can understand that it doesn't pass a value to the `$rowId` but i can understand why — Waaaaat, Apr 30 '15 at 18:51
We would need to see where `$rowId` gets assigned then. The part of the code you posted is almost irrelevant now. — vcanales, Apr 30 '15 at 19:44
How `var_dump($rowId);die();` before the call to the function to see what you're passing into it? You still didn't show the origin of the `$rowId` you're giving to the function. — vcanales, Apr 30 '15 at 20:11

score 2 · Accepted Answer · answered May 01 '15 at 07:56

Actually no one here would really be able to answer this with just the code you show here. But @ultranaut and @devJunk both pretty much nailed it. When I originally wrote the the function for you, your form allowed a user to add records to the database and had a button to "Clear All Work History" but did not have a method of deleting individual records.

I wrote the function so that:

passing the string value 'all' as the $rowId parameter would delete all records ( which is wha the application needed)
passing a database row id as the $rowId parameter would delete just that specific row ( not needed at the time but made sense to add it )

Because you only had the one button at the time to delete everything, I only implemented that with this check:

if(isset($_POST['clear_work'])){
        // see explanation of params in function declaration above for `deleteFromWhere()`
        deleteFromWhere($db,'work',$_SESSION['username'],'all');    
}

If you want to delete a specific record, you'll need to do two things:

Add a button or similar on your first page that will delete an individual record.

<form action="addCV.php" method="post"> 
    <input type="hidden" value="12345" name="clear_this_work" /><!--you'll need to set the value here to the database row id of the currently displayed record -->                  
    <input type="submit" value="Clear This Work Record" style="border: 1px solid #006; color:#F87F25; font: bold 16px Tahoma; border-radius:7px; padding:4px; background:#ffffff;"/>
</form>

Add a check in the second page to see if this button was pressed and call the function passing in the supplied id.

if(isset($_POST['clear_this_work'])){
        // see explanination of params in function declaration above for `deleteFromWhere()`
        deleteFromWhere($db,'work',$_SESSION['username'],$_POST['clear_this_work']);    
}

Final amended php:

// a function that deletes records 
// $table is the table to delete from
// $user is the current username
// $rowId is the row id of the record to be deleted
// if $rowId is passed as the string "all", 
// all matching records will be deleted 
function deleteFromWhere($db,$table,$user,$rowId){
    // PDO will sanitize most vars automatically
    // however Table and Column names cannot be replaced by parameters in PDO. 
    // In this case we will simply want to filter and sanitize the data manually.
    // By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.
    // http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter
    switch($table){
        case 'work':
            $tbl = 'work'; // add more here when you want to start deleting from other tables
            break;
    }
    if($rowId=='all'){ // delete all records
        $sql = 'DELETE FROM '.$tbl.' WHERE username=?';  // "?"s here will get replaced with the array elements below
        $stmt = $db->prepare($sql);
        $stmt->execute(array($user)); // these array elements will replace the above "?"s in this same order
        // check for errors 
        if($stmt->errorCode() == 0) {
            // no errors, show alert and refresh page
            return '<script type="text/javascript">alert("All work history was successfully cleared!"); window.location="addCV.php"; </script>';
        } else {
            // had errors
            $errors = $stmt->errorInfo();
            return '<script type="text/javascript">alert("Error deleting work history!: '.$errors[2].'"); window.location="addCV.php"; </script>';  
        }
    }
    elseif($rowId){ // delete specified row 
        $sql = 'DELETE FROM '.$tbl.' WHERE username = ? AND id = ?';  // "?"s here will get replaced with the array elements below
        $stmt = $db->prepare($sql);
        $stmt->execute(array($user,$rowId)); // these array elements will replace the above "?"s in this same order
        $affected_rows = $stmt->rowCount(); // get the number of rows affected by this change
        return $affected_rows.' row deleted.';
        // check for errors 
        if($stmt->errorCode() == 0) {
            // no errors, show alert and refresh page
            return '<script type="text/javascript">alert("Selected work history was successfully cleared!"); window.location="addCV.php"; </script>';
        } else {
            // had errors
            $errors = $stmt->errorInfo();
            return '<script type="text/javascript">alert("Error deleting work history: '.$errors[2].'"); window.location="addCV.php"; </script>';   
        }
    }
    else{ /// return error
    }
}   


if(isset($_POST['clear_work'])){
        // see explanation of params in function declaration above for `deleteFromWhere()`
        deleteFromWhere($db,'work',$_SESSION['username'],'all');    
}

// add the below check 
if(isset($_POST['clear_this_work'])){
        // see explanination of params in function declaration above for `deleteFromWhere()`
        deleteFromWhere($db,'work',$_SESSION['username'],$_POST['clear_this_work']);    
}

HTML:

<form action="addCV.php" method="post">                         
    <input type="submit" value="Clear All Work History" name="clear_work" style="border: 1px solid #006; color:#F87F25; font: bold 16px Tahoma; border-radius:7px; padding:4px; background:#ffffff;"/>
</form> 
<!--  add the below -->
<form action="addCV.php" method="post"> 
    <input type="hidden" value="12345" name="clear_this_work" /><!--you'll need to set the value here to the database row id of the currently displayed record -->                  
    <input type="submit" value="Clear This Work Record" style="border: 1px solid #006; color:#F87F25; font: bold 16px Tahoma; border-radius:7px; padding:4px; background:#ffffff;"/>
</form>

The good part of all this is that I was close to the solution. I just forgot to use hidden type — Waaaaat, May 01 '15 at 22:20

PDO delete specified row from table

1 Answers1

Add a button or similar on your first page that will delete an individual record.

Add a check in the second page to see if this button was pressed and call the function passing in the supplied id.

Final amended php:

HTML:

Linked