Cannot get my PHP code to remove row in mysql

Question

I am currently trying to make it so my PHP code can remove appointment records in mysql. I have been trying for quite some time without any luck.

Here is my code where you would select which appointment to remove. All of the appointments display correctly in a dropdown menu on this page.

<?php
session_start();

$db = mysqli_connect("localhost", "user", "pass", "database");
if (!$db) { die("Connection failed: " . mysqli_connect_error()); }

$sql2 = "SELECT a.appointmentID
       FROM AppointmentDetail AS a, Customer AS c
       WHERE a.customerID=c.customerID 
       AND a.appointmentStatus<>'completed' 
       AND emailAddress = '".$_SESSION['username']."';";

$result2 = mysqli_query($db, $sql2);

echo "<h2 class='ArticleHeader1'>Cancel one of your Upcoming Appointments</h2>";
echo "<form action='Example.php' method='post'>";
echo "<p> Select an AppointmentID from the list below </p>";
echo "<select type='text' name='appointmentCancel' required>";
while($row2 = mysqli_fetch_row($result2))
       {foreach($row2 as $cell2) 
         echo "<option value='".$cell2."'>$cell2</option>";}
echo "</select>";
echo "<input type='submit' name='formDelete' value='Cancel Appointment' class='button'/>";
echo "</form>";

mysqli_close($db);
?>

Here is the Example.php form that I would submit to where I always get the "Sorry! There has been an error in canceling your appointment. Please contact your Administrator"

<?php
session_start();

$db = mysqli_connect("localhost", "user", "pass", "database");
    if (!$db) { die("Connection failed: " . mysqli_connect_error()); }

if(isset($_POST['formDelete']))
{
    $appointmentDelete = mysqli_real_escape_string($db, $_POST['appointmentCancel']);
    $del_val = "DELETE FROM AppointmentDetail  
                WHERE appointmentID='".$appointmentDelete;."';";
    $saved = mysqli_query($db, $del_val);
    if($saved) {
        echo "Your Appointment Has Been Successfully Cancelled!";
    } else {
        echo "Sorry! There has been an error in canceling your appointment. 
        Please contact your Administrator";
    }
}
mysqli_close($db);
?>

I have tried using different SQL queries to remove records based on different fields other than appointmentID with no luck. But appointmentID is the simplest so since none of the fields are working, I must be doing something wrong.

I have also tried messing around with the quotes around $appointmentDelete and a few other variables with no luck.

I am pretty new to PHP and SQL so I really am just looking to get this basic functionality down.

I have cut out a lot of the additional code on my first PHP page to only include what I believe to be relevant.

In your delete query `WHERE appointmentID='".$appointmentDelete;."';";` remove `;` and your query will look like `WHERE appointmentID='".$appointmentDelete."';";`. — Vibha Chosla, Nov 25 '19 at 03:44
and also if your `appointmentID` is an `integer` then no need to compare as string, directly compare like `WHERE appointmentID=".$appointmentDelete.";` — Vibha Chosla, Nov 25 '19 at 03:47
Unless there are legal/business requirements, be wary of allowing non-administrators the DELETE privilege. A soft delete is often preferable. And note that you're presently vulnerable to sql injection — Strawberry, Nov 25 '19 at 05:39

score 0 · Answer 1 · answered Nov 25 '19 at 03:59

There's an concatenation error in your delete query. Change it as bellow,

$del_val = "DELETE FROM AppointmentDetail WHERE appointmentID=$appointmentDelete";

Please refer PHP - concatenate or directly insert variables in string for more details about concatenation.

Cannot get my PHP code to remove row in mysql

1 Answers1