PDO removes the specified row from the table
I have a little problem ... I am using PDO and part of my code is to delete a specific row from a table in my database. My code is 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 statement is if
used to delete ALL data from the table and else
which I want to use to delete a specific row, but that doesn't work, what am I doing wrong?
It's a button ...
<input type="submit" value="Clear Selected Work History" name="clear_selected_work" />
source to share
In fact, no one here could answer this question with just the code you show here. But @ultranaut and @devJunk are both very nailed down. When I originally wrote the function for you, your form allowed the user to add records to the database and had a Clear All Work History button but did not have a method to delete individual records.
I wrote the function so that:
- passing a string value
'all'
as a parameter$rowId
will delete all entries (which are required for the application) - passing a database row id as a parameter
$rowId
will only remove that specific row (at that time it is not needed, but it makes sense to add it).
Since you only had one button at a time to delete everything, I only implemented it 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 entry, you need to do two things:
Add a button or similar on the first page that will delete an individual entry.
<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 on the second page to see if that button was pressed and call the function passed 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 php corrected:
// 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>
source to share