PDO Deleting

[dean7]

Hey guys, I've recently started coding again after a long break away from it but I'm trying to learn PDO, so as a Boxing fan I thought I'll try to code my own Boxing Score card. I've not tested it all as of yet but I'm getting a problem when I go to delete a record from the database.

 

Here is the delete part of my code:

</tr>
<?php
 
$DoQuery = ("SELECT * FROM BoxingResults");
$query = $conn->query($DoQuery);
 
while ($r = $query->fetch(PDO::FETCH_OBJ)){
 
echo '
<tr>
<td align="center" width="">'.$r->FavBoxer.'</td><td align="center" width="">'.$r->R1.'</td><td align="center" width="">'.$r->R2.'</td><td align="center" width="">'.$r->R3.'</td><td align="center" width="">'.$r->R4.'</td><td align="center" width="">'.$r->R5.'</td><td align="center" width="">'.$r->R6.'</td><td align="center" width="">'.$r->R7.'</td><td align="center" width="">'.$r->R8.'</td><td align="center" width="">'.$r->R9.'</td><td align="center" width="">'.$r->R10.'</td><td align="center" width="">'.$r->R11.'</td><td align="center" width="">'.$r->R12.'</td><td align="center" width="" class="header">'.$r->FavPoints.'</td><td align="center" width="" rowspan="2" class="Delete"><a href="index.php?del='.$r->id.'">Delete (X)</a></td>
</tr>
<tr>
<td align="center" width="">'.$r->UnFavBoxer.'</td><td align="center" width="">'.$r->UR1.'</td><td align="center" width="">'.$r->UR2.'</td><td align="center" width="">'.$r->UR3.'</td><td align="center" width="">'.$r->UR4.'</td><td align="center" width="">'.$r->UR5.'</td><td align="center" width="">'.$r->UR6.'</td><td align="center" width="">'.$r->UR7.'</td><td align="center" width="">'.$r->UR8.'</td><td align="center" width="">'.$r->UR9.'</td><td align="center" width="">'.$r->UR10.'</td><td align="center" width="">'.$r->UR11.'</td><td align="center" width="">'.$r->UR12.'</td><td align="center" width="" class="header">'.$r->UnFavPoints.'</td>
</tr>
<tr>
<td colspan="15" class="Uuheader" align="center">Winner: '.$r->Winner.'</td>
</tr>
<tr>
<td colspan="15" class="Uuheader"> </td>
</tr>';
 
 
}
 
if (isset($_GET['del'])){
 
 
 
$DoQuery1 = ("SELECT * FROM BoxingResults");
$query1 = $conn->query($DoQuery1);
$Obj = $query1->fetch(PDO::FETCH_OBJ);
 
$PostIdThing = $_GET['del'];
 
$idnum = $Obj->id;
$delete = "DELETE FROM BoxingResults WHERE id = ':boxingid' LIMIT 1";
$Startoff = $conn->prepare($delete);
$Startoff->bindValue(':boxingid', $PostIdThing);
$Startoff->execute(array(':boxingid' => 1));
 
if (!$Startoff->execute()){
print_r ($Startoff->errorInfo());
} 
 
 
echo ("Result ".$PostIdThing." deleted!");
 
}
?>

When I click the delete button I'm wanting it to delete the record from the database although currently when I press the delete button its saying its deleted with the correct ID but not actually deleting. I've search over google and just cannot find something to help me.

 

Thanks for any help given.

[Zephni]

You are binding the delete statement twice. First you are binding the parameter ":boxingid" to $PostIdThing and then executing it with a forced ID of 1 in the next line.

 

Then you are executing within the if statement without any parameters. You would be better off deleting this line:

$Startoff->execute(array(':boxingid' => 1));

Then the bindTo line would sort out the parameter binding and the execute statement can still be tested by the if statement.

 

Also you don't want to be putting ' quotes inside the statement when using parameter bindings, remove them in this line:

$delete = "DELETE FROM BoxingResults WHERE id = :boxingid LIMIT 1";

Hope this helps

Edited December 16, 2015 by Zephni

[dean7]

You are binding the delete statement twice. First you are binding the parameter ":boxingid" to $PostIdThing and then executing it with a forced ID of 1 in the next line.

 

Then you are executing within the if statement without any parameters. You would be better off deleting this line:

$Startoff->execute(array(':boxingid' => 1));

Then the bindTo line would sort out the parameter binding and the execute statement can still be tested by the if statement.

 

Hope this helps

Hey thanks for your reply, I've tried removing that line but it still does the exact same thing

[ginerjm]

You are doing a bindvalue as well as including an array in the execute call.  More importantly you are assigning different values to your parm in each case.  I'm confused and I am sure the SQL Server is more confused.

 

Personally I stick to the array method and never use the bindvalue one.  That said - you should do ONLY this:

if (!$Startoff->execute(array('boxingid'=>$PostIdThing)))
{//show error}
else
{//show success msg}

and leave out the bindvalue as well as the first call to execute.

[Zephni]

Did you see the second part of my message?

[dean7]

You are doing a bindvalue as well as including an array in the execute call.  More importantly you are assigning different values to your parm in each case.  I'm confused and I am sure the SQL Server is more confused.

 

Personally I stick to the array method and never use the bindvalue one.  That said - you should do ONLY this:

if (!$Startoff->execute(array('boxingid'=>$PostIdThing)))
{//show error}
else
{//show success msg}

and leave out the bindvalue as well as the first call to execute.

I currently have:

 

Scrap that I have it working now thank you! Just had to put the : in the array

 
if (isset($_GET['del'])){
 
 
 
$DoQuery1 = ("SELECT * FROM BoxingResults");
$query1 = $conn->query($DoQuery1);
$Obj = $query1->fetch(PDO::FETCH_OBJ);
 
$PostIdThing = $_GET['del'];
 
$idnum = $Obj->id;
$delete = "DELETE FROM BoxingResults WHERE id = :boxingid LIMIT 1";
$Startoff = $conn->prepare($delete);
 
if (!$Startoff->execute(array('boxingid'=>$PostIdThing))){
print_r ($Startoff->errorInfo());
}else{
echo ("Result ".$PostIdThing." deleted!");
}
}

Edited December 16, 2015 by dean7

[ginerjm]

Happy to help!