I Can't Delete record from mysql using php

Question

i have been creating a staff directory web aplication to learn and test what i have learnt, but im having issues deleteing records from my mysql database where the staff_id = staff_id from the previous page, it reconises the record and populates the fields but just does not delete the record when i select yes.

Any help or guidence would be great :D

<?php 
# display all php errors
error_reporting(E_ALL);
ini_set('display_errors', 1);

# include dbConnection details
require '/includes/dbconn.php';

# if $staff_id is not empty, GET the id
if (!empty($_GET['staff_id'])) {
    $staff_id = $_GET['staff_id'];
}

# check if data has been posted
if (!empty($_POST)) {
    # modify the variable request from GET to POST
    $staff_id = $_POST['staff_id'];

    # delete data from joke table
    $sql = "DELETE FROM staff WHERE staff_id = :staff_id";
    $stmt = $DB_con->prepare($sql);
    $stmt->bindValue(':staff_id', $staff_id);
    $stmt->execute();

    header("Location: Admin_View_Employee.php");
    exit();    
# display current record    
} else {
    $sql = "SELECT staff_id, forename, surname, job_role, joined, manager_id, extension, mobile, email, background_info, qualifications, achievements, username, password, level, dept_id
            FROM staff  
            WHERE staff_id = :staff_id";
    $stmt = $DB_con->prepare($sql);
    $stmt->bindValue(':staff_id', $staff_id);
    $stmt->execute();
    $data = $stmt->fetch();
    $staff_id = $data['staff_id'];
    $forename = $data['forename'];
    $surname = $data['surname'];
    $job_role = $data['job_role'];
    $manager_id = $data['manager_id'];
    $joined = $data['joined'];
    $extension = $data['extension'];
    $mobile = $data['mobile'];
    $email = $data['email'];
    $background_info = $data['background_info'];
    $qualifications = $data['qualifications'];
    $achievements = $data['achievements'];
    $dept_id = $data['dept_id'];
}


    # if data is not found
    if(!$data)
    {
        header("Location: Admin_View_Employee.php");
        exit();
    }

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Update Employee</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="css/style.css" type="text/css"  />
</head>


      <body>
    <div>
    <table width="100%" border="0">
    <tbody>
    <tr>
    <td width="25%"><img src="images/beacon_logo.png" width="240" height="168" align="left"></td>
    <td width="50%"><h1 style="text-align: center" border="0">Update Employee</h1></td>
    <td width="25%">&nbsp;</td>
    </tr>
    </tbody>
    </table>
</div>
    <?php include('Admin_Nav.php'); ?>



            </tbody>
      </table></p>
    <div>       
        <form action="Admin_Delete_Employee.php?staff_id=<?php echo $staff_id ?>" method="post">
        <input type="hidden" name="id" value="<?php echo $staff_id; ?>"/>
        <p>Are you sure to delete this record?</p>


 <table width="574">
            <tr>
                <th width="131">Staff ID</th>
                <td width="84"><?php echo $staff_id;?></td>
            </tr>
                <tr>
                <th>Forename</th>
                <td><?php echo $forename;?></td>              
            </tr>
            <tr>
                <th>Surname</th>
                <td><?php echo $surname;?></td>              
            </tr>
            <tr>
                <th>Job Role</th>
                <td><?php echo $job_role;?></td>
            </tr>   
        </tr>               
        </table>

            <div>
                <button type="submit">[Yes]</button>
                <a href="Admin_View_Employee.php">[No]</a>
            </div>
        </form>
    </div>
</body>
</html>

`name="id"` != `$_POST['staff_id']`...or use it as the `$_GET` because you also are sending it that way `?staff_id=` you just need to take off the other overwriting. — chris85, Apr 04 '17 at 00:19
What error message are you getting and what is the exact piece of code you are having trouble with? — Robert, Apr 04 '17 at 00:26

score 0 · Answer 1 · answered Apr 04 '17 at 00:23

0

as per

http://php.net/manual/en/pdo.prepared-statements.php

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

therefore use bindParam

answered Apr 04 '17 at 00:23

RoMEoMusTDiE

4,739
1
17
26

Why would this make a difference? The variable as overwritten to empty. – chris85 Apr 04 '17 at 00:24
you can check this case http://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue – RoMEoMusTDiE Apr 04 '17 at 00:25
I know what it does. Look at the code though. `$staff_id = $_POST['staff_id'];` and ``. – chris85 Apr 04 '17 at 00:26
check this post... to make sure your post is not empty http://stackoverflow.com/questions/12528524/if-empty-post-not-working – RoMEoMusTDiE Apr 04 '17 at 00:30

score 0 · Answer 2 · answered Apr 04 '17 at 00:27

0

Change your form method from post to get

<form action="Admin_Delete_Employee.php?staff_id=<?php echo $staff_id ?>" method="get">

answered Apr 04 '17 at 00:27

Niklesh Raut

34,013
16
75
109

It already is a `GET`. Per the string you posted `?staff_id=`. It overwrites in the `delete`. It is a typo and should be voted to close. – chris85 Apr 04 '17 at 00:29
Roughly the issue is what you said but the real is is just the `$staff_id = $_POST['staff_id'];` which overwrites the `GET` value the OP is already sending. If the OP changes method the other script values will need to change `if (!empty($_POST)) {`.. etc. – chris85 Apr 04 '17 at 00:32
In that case it should be `id` not `staff_id` right ? @chris85 – Niklesh Raut Apr 04 '17 at 00:34
Yea, that'd work too... as I read it. OP seems to have vanished though. – chris85 Apr 04 '17 at 00:35
Hi Chris, im just adjusting the code to what you said and by changeing it to $staff_id = $_POST['id']; that seems to have fixed it. Thanks you all for your help. – Luke Nicholls Apr 04 '17 at 00:46
@LukeNicholls You should have received an undefined index notice with that code. You should look into why your error reporting isn't working. – chris85 Apr 04 '17 at 00:53

score 0 · Answer 3 · answered Apr 04 '17 at 00:28

0

Could it be this easy?

Change:

$stmt->bindValue(':staff_id', $staff_id);

to

$stmt->bindValue(':staff_id', $staff_id, PDO::PARAM_INT);

That will work if the staff_id is an integer.

answered Apr 04 '17 at 00:28

StackSlave

10,613
2
18
35

It is a typo and should be voted to close. The variable is overwritten in the `delete` conditional. – chris85 Apr 04 '17 at 00:30
the staff_id is an INTeger and i have added ", PDO::PARAM_INT);" but the record is still displayed within the database. – Luke Nicholls Apr 04 '17 at 00:36

I Can't Delete record from mysql using php

3 Answers3