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" />
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:
'all' as the $rowId parameter would delete all records ( which is wha the application needed)$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:
<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>
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']);
}
// 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']);
}
<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>