PHP PDO prepared delete. Why does this fail?

Question

I've got a small bit of code that I'm trying to execute via an ajax response. I've got the ID passing, but for some reason my delete statement fails(doesn't delete record, hence add to $err array). I'm sure it is something stupid, but it isn't jumping out at me right now.

PHP CODE

<?php
define('INCLUDE_CHECK',true);

require '../../connect.php';
require '../../functions.php';
// Those two files can be included only if INCLUDE_CHECK is defined
// sets site to require https
echo redirectToHTTPS();
session_name('tzLogin');
session_set_cookie_params(2*7*24*60*60);
session_start();



if (isset($_POST['id']) && $_SESSION['id'] && $_SESSION['permlvl']==3 )
{
    $id = is_numeric($_POST['id']);
    $err = array();
            $query = "DELETE FROM employees WHERE id = :id";
            $statement = $db -> prepare($query);
            $statement -> BindParam('id', $id, PDO::PARAM_INT);
            $result = $statement -> execute();
            $statement -> closecursor();
            if ($result === true){
            }
                else{
                    $err[] = "error";
            }
}   
//check for error messages
if(!count($err))
{
   echo 'success';
}
    else{
        //on failure, future will include logging either by sending email or writing to a log file
    }
?>

UPDATE I've changed the db error mode and can get this to display. So it has to be something with how my database is designed.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (login.thrives, CONSTRAINT thrives_ibfk_1 FOREIGN KEY (n_emp) REFERENCES employees (ID))' in /Applications/XAMPP/xamppfiles/htdocs/jen/maintabs/Employees/delete.php:23 Stack trace: #0 /Applications/XAMPP/xamppfiles/htdocs/jen/maintabs/Employees/delete.php(23): PDOStatement->execute() #1 {main} thrown in /Applications/XAMPP/xamppfiles/htdocs/jen/maintabs/Employees/delete.php on line 23

What `fails` means? Only 6 lines of presented code are related to the problem, the other ones make no sense for the question — zerkms, Dec 27 '11 at 03:20
Don't do this: `$db -> prepare()`, the spaces are silly and possibly confusing. Just: `$db->prepare()`. (In general.) — Jared Farrish, Dec 27 '11 at 03:25
The lines are related because if the prepared statement fails it adds to the $err array. If the statement executes successfully without error 'success' is passed to ajax html var. — atrueresistance, Dec 27 '11 at 03:25
@atrueresistance: do we need `echo redirectToHTTPS()`?? Do we need `if (isset($_POST['id']) && $_SESSION['id'] && $_SESSION['permlvl']==3 )`?? Just remove all pointless lines and give us definition of `fail`. We have no idea what you are in stuck with. This is how to handle errors with PDO: http://www.php.net/manual/en/pdo.errorinfo.php — zerkms, Dec 27 '11 at 03:28
`Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails` --- is not it informative enough? — zerkms, Dec 27 '11 at 03:35

score 6 · Answer 1 · answered Dec 27 '11 at 03:27

6

$id = is_numeric($_POST['id']);

...

$statement -> BindParam('id', $id, PDO::PARAM_INT);

is_numeric returns a boolean. The value of $id is true or false, not a number.

answered Dec 27 '11 at 03:27

deceze

510,633
85
743
889

"*The value of `$id` **at this point** is `true` or `false`, not a number.*" – Jared Farrish Dec 27 '11 at 03:28
1

Sure, but since there's no other use of `$id`... :) – deceze Dec 27 '11 at 03:30
Yep, just my own comment. `:)` – Jared Farrish Dec 27 '11 at 03:31

score 4 · Accepted Answer · edited May 23 '17 at 10:34

4

I think your problem is in this line:

$id = is_numeric($_POST['id']);

is_numeric will return a bool. I think what you want to use something like ~~intval~~ (int)$_POST['id'] instead. (Edit: @zerkms correctly points out that an explicit cast is better than intval(). In this case they're equivalent in functionality, but the cast is faster.)

edited May 23 '17 at 10:34

Community

1
1

answered Dec 27 '11 at 03:30

AgentConundrum

20,288
6
64
99

3

@atrueresistance - What was it about this answer that was any more attractive than deceze's? The `intval` reference? – Jared Farrish Dec 27 '11 at 03:36
@Jared Farrish: obviously less words to read. Reading is boring ;-) – zerkms Dec 27 '11 at 03:38
1

@atrueresistance I agree with Jared. I considered deleting this answer when I saw that deceze had beaten me to the punch, but left it because I thought the `intval` thing might give some extra context. – AgentConundrum Dec 27 '11 at 03:40
@zerkms - deceze's is actually shorter. `:o` – Jared Farrish Dec 27 '11 at 03:41
@zerkms Brevity is something for which I've seldom been accused. – AgentConundrum Dec 27 '11 at 03:41
@AgentConundrum - No offense, I was just wondering why one upvoted answer was completely passed over for another which is nearly identical. – Jared Farrish Dec 27 '11 at 03:43
2

@AgentConundrum: actually `(int)` cast is better than `intval()` – zerkms Dec 27 '11 at 03:43
@JaredFarrish None taken. As I said, I considered deleting the answer when I saw deceze's answer. – AgentConundrum Dec 27 '11 at 03:46
@zerkms Ironically, my answer was late specifically because I was debating which of the two to reference. I ultimately decided that they were equivalent in this case, but apparently `(int)` is faster, so.. yeah. Oops. – AgentConundrum Dec 27 '11 at 03:48

PHP PDO prepared delete. Why does this fail?

2 Answers2