PHP delete not working and displaying resulting table

Question

I'm having an issue where I have a drop down list which has owner's last name and once I select it and press delete button, it should remove the owners name from the drop down along with any associated owner information and boat information in mySQL database. I have written the @sql query to perform the delete function but doesn't seem to delete it.

Also how can I print out the tables (owner table and MarinaSlip table, these are the names in the mySQL database) once user click delete button. I want it to display both tables underneath in the same page.

deletedowner.php:

<?php  #index.php for Assignment 10
$page_title = 'Assignment 10 for Marina Database';
include('header.html');
require('dbConn.php');

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{

    $id = $_POST['OwnerID'];
    try
    {
        $sql = "DELETE m, o
              FROM Owner AS o
              LEFT JOIN MarinaSlip AS m
              ON o.OwnerNum = m.OwnerNum
              WHERE o.OwnerNum = :ownerId";
        $stmt = $conn->prepare($sql);
        $stmt->execute(array(':ownerId' => $id));

    //include('DeletedUpdatedList.php'); when I put uncomment this line, it shows table but the delete button disappears

    } // end try

catch (PDOException $e)
    {

    echo 'Error: '.$e->getMessage();

    } //end catch

} //end if server
echo '<center>';
echo '<h3> Select the owners last name from drop down list to delete owner and their boats.</h3>';
$sql = "select OwnerNum, LastName from Owner"; //prints sql query
echo '<form action="Assignment10deleteowner.php" method="POST">';

echo "<select name='OwnerID' id=OwnerID'>";

foreach($conn->query($sql) as $row) 
{
    echo '<option value = "';
    echo $row['OwnerNum'];
    echo '"> ';
    echo $row['LastName'];
    echo '</option>';

} // end foreach
echo '</select>';

echo '<br><input type="submit" name="submit" value="Delete"> <br>';
echo '</form>'; //end form

 // now to check if the delete button has been clicked 

include('footer.html'); 
?>

DeletedUpdatedList.php

<?php  #index.php for Assignment 10
$page_title = 'Assignment 10 for AlexaMara Marina Database';
echo '<h2> Updated list of Owners and MarinaSlip:</h2>';
$stmt = $conn->prepare("select * from Owner");  //prepare statment to print all of the owners
$stmt->execute(); //excute the sql query

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt = $conn->prepare("select * from MarinaSlip");  //prepare statment to print all of the owners
$stmt->execute(); //excute the sql query

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 

echo "<table style='border: solid 1px black;'>"; //make table to display column headers
echo "<tr><th>OwnerNum</th><th>LastName</th><th>FirstName</th><th>Address</th><th>City</th><th>State</th><th>Zip</th></tr>";


class TableRows extends RecursiveIteratorIterator 
{ 
function __construct($it) { 
    parent::__construct($it, self::LEAVES_ONLY); 
}

function current() {
    return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
}

function beginChildren() { 
    echo "<tr>"; 
} 

function endChildren() { 
    echo "</tr>" . "\n";
} 

} 

foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) 
{ 
     echo $v;

}


$conn = null;
echo "</table>"; //end table

//$sql = 'select BoatName, m.MarinaNum, SlipID from MarinaSlip s, Marina m where s.MarinaNum //= m.MarinaNum';
//echo '<form action="Assignment9.php" method="POST">';
//echo '</form>';

?>


    [only prints 1 table and now formatting is messed up.  The drop down and delete button should be first and then should display both tables][1]

Any help to do this would be much appreciated, thanks in advance

Answer 1

Execute the DELETE query to perform the deletion.

try
{
    $sqlDel =  "DELETE o, m 
                FROM Owner o 
                left join MarinaSlip m 
                on o.OwnerNum = m.OwnerNum
                WHERE o.OwnerNum = '{$id}';" ;
    $conn->query($sqlDel);
} // end try
catch (PDOException $e)
{
    echo 'Error: '.$e->getMessage();
} //end catch

You can use inner join if both table will have data always

Answer 2

For numerous reasons, including protection from SQL injection, you should be using a prepared statement for your DELETE statement. You also must actually execute the statement for it to have any effect.

Here's the relevant portion of your code, modified to operate correctly:

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
    $id = $_POST['OwnerID'];
    try
    {
        $sql = "DELETE m, o
                  FROM Owner AS o
                  LEFT JOIN MarinaSlip AS m
                    ON o.OwnerNum = m.OwnerNum
                  WHERE o.OwnerNum = :ownerId";
        $stmt = $conn->prepare($sql);
        $stmt->execute(array(':ownerId' => $id));
    } // end try
    catch (PDOException $e)
    {
        echo 'Error: '.$e->getMessage();
    } //end catch
} //end if server

By using a prepared statement, you gain automagic escaping, quoting and type-matching of variables.

Also, don't overcomplicate things. To get your tables to display,

$stmt = $conn->query('SELECT * FROM Owner');
echo '<table>';
while ($row = $stmt->fetch(PDO::FETCH_NUM))
{
    echo '<tr>';
    foreach ($row as $value)
    {
        echo "<td>{$value}</td>";
    }
    echo '</tr>';
}
echo '</table>';

should suffice. Change the query string for the MarinaSlip table as appropriate. Once that's working, then you can play with fancy formatting.