Delete from MYSQL Table

Question

I have several forms that delete various rows from the table the user is viewing. I am using MYSQL and PHP. I have the same code below (As far as i can tell) on another page and it works fine. For some reason i cannot get my code to execute. It gives the update failed error.

try 
{
    $db = new PDO('mysql:host=$hname;dbname=' . $DBname, $DBusername, $DBpassword, array(PDO::ATTR_PERSISTENT => true));
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}

catch ( Exception $ex ) 
{
    die ( 'Cannot Connect to the Database.' );
}

$delete_equipment_single = "DELETE FROM EquipmentItem 
                              WHERE equipmentID = :equipmentID";    


equipment_delete.php
<?php require_once('php/security.php'); ?>
<?php

// Get the ID Tag
if( !isset($_GET['equipmentID'] ))
{
    echo "You Didn't Supply a equipmentID - (equipmentID) [ERROR x1]";
    exit();
}

require_once('php/database.php');

$equipmentID = htmlspecialchars($_GET['equipmentID']);

// Delete the Equipment
$statement = $db->prepare($delete_equipment_single);
$result = $statement -> execute(array(
    ':equipmentID' => $equipmentID
));

// Make Sure Everything Worked
    if( $result == false )
    {
        die('Update Failed, Please Check Your Database. [ERROR x2]');
    }

echo '<meta http-equiv="Refresh" Content="0; url=display_equipment.php?a=1" />'; 

?>

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in delete_equipment.php:16 Stack trace: #0 delete_equipment.php(16): PDO->prepare('') #1 {main} thrown indelete_equipment.php on line 16

VarDump for equipmentID string(8) "E1202612"

Is this using PDO? If so what does var_dump($statement->errorInfo()); produce if called above die()? — FoolishSeth
Why are you calling htmlspecialchars? That's used for rendering output, not for URIs (which use a different encoding entirely, which PHP decodes for you behind-the-scenes). You'd want to use intval instead (assuming equipmentID is an integer). — Dai
What is the type of your $statement ? If it is a mysqli_statement, the execute method does not take any parameter : php.net/manual/en/mysqli-stmt.execute.php. May be you should use the '?' character inside the query (instead of :yourId), and use $statement->bind_param("i", intval($your_id)); before calling execute() — MatRt
I am using pdo but i know its not a connection problem. EquipmentItem is the table and i havent had any other problems editing this data besides this delete. — John Ayers

peterm peterm · Accepted Answer · 2013-03-12T04:04:21

Change

$result = $statement -> execute(array('equipmentID' => $equipmentID));

to

$result = $statement -> execute(array(':equipmentID' => $equipmentID));
                                       ^

UPDATE: Turn on exception throwing and disable prepare emulation.

$db = new PDO('mysql:host=localhost;dbname=mydb;charset=UTF-8', 'user', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

UPDATE2: Change

$statement = $db->prepare($delete_equipment_single);

to

$statement = $db->prepare("DELETE FROM EquipmentItem WHERE equipmentID = :equipmentID");

Delete from MYSQL Table

2 Answers