user3325133 user3325133 - 7 months ago 10
PHP Question

PHP delete not working and displaying resulting table

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

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.