I was looking for help in fixing my code.
- First I have to insert a new breed, so I have created the form but when I enter data and click submit, the table doesn't update with changes (I think there might be something wrong with my INSERT sql statments).
- I have to delete a dog from a drop down list which I have it working but my drop down list contains the description of the dog and not the name of the dog to delete. When I click on delete it doesn't delete anything from table and table doesn't reflect the deleted item, so how can I fix this?
Any help would be great.
insert.php
<html>
<body>
<h1>Insert a new Breed</h1>
<form action="sort4.php" method="post">
Breed Name: <input type="text" name="breedname" /><br><br>
Phone: <input type="text" name="phone" /><br><br>
Description: <input type="text" name="descr" /><br><br>
Breeder Name: <input type="text" name="breedername" /><br><br>
<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
//declare variables used to insert into mySQL database
$breedname=$_POST['name'];
$phone=$_POST['phone'];
$description=$_POST['descr'];
$date=$_POST['date'];
$breedername=$_POST['name']; //this is from a different table
//echo test;
try
{
//sql query to insert form values into database
$sql = "INSERT INTO breed (id, name, descr, date) VALUES ``('".$breedname."', '".$description."', 'NOW()')";
$sql2 = "INSERT INTO breeder (id, phone, name) VALUES ('".$breedername."', '".$phone."')";
$conn->exec($sql); //execute the INSERT sql statment above
$conn->exec($sql2);
include('AddedOwnersList.php');
//echo $OwnerNum, $LastName, $FirstName, $Address, $City, $State, $Zip;
}
catch (PDOException $e)
{
echo 'Error: '.$e->getMessage();
} //end catch
}
?>
<input type="submit" value="Submit"/>
</form>
</body>
</html>
sort4.php
<?php
include ('includes/header.html');
require ('includes/conn.php');
$sort = (isset($_GET['sort']))?$_GET['sort']:'ph';
switch ($sort)
{
case 'dn': //dn is sorting on dog name
$order_by = 'dog.name';
break;
case 'bn': //bn is sorting on breed name
$order_by = 'breed.name';
break;
default:
$order_by = 'breeder.phone';
break;
} //end switch
$sql = "select dog.name as dogname, breed.name as breedname, breed.descr as description,
breeder.name as breedername, breeder.phone as phone from breeder, dog, breed, breed_breeder
where dog.breed_id = breed.id and breed.id = breed_breeder.breed_id and
breeder.id = breed_breeder.breeder_id order by ".$order_by;
$stmt = $conn->query($sql);
echo "<table border='1'>";
echo '<tr>
<td align="center"><a href="sort4.php?sort=dn"> Dog Name </a></td>
<td align="center"><a href="sort4.php?sort=bn"> Breed Name </a></td>
<td align="center"><a href="sort4.php?sort=ph"> Phone </a></td>
<td align="center"><a href="sort4.php?sort=ph"> Description </a></td>
<td align="center"><a href="sort4.php?sort=ph"> Breeder Name </a></td>
</tr>';
while($row = $stmt->fetch() )
{
echo '<tr><td>' .$row['dogname'].'</td><td>' .$row['breedname'].'</td>';
echo '<td>' .$row['phone'].'</td><td>' .$row['description'].'</td><td>' .$row['breedername'].'</td></tr>';
}
echo "</table>";
include ('includes/footer.html');
?>
delete.php
<?php
include ('includes/header.html');
require ('includes/conn.php');
//checks to see if delete was clicked
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
$id = $_POST['BreedID'];
try
{
$sql = "DELETE b
FROM breed AS b
WHERE b.id = :breedId";
$stmt = $conn->prepare($sql);
$stmt->execute(array(':breedId' => $id));
} // end try
catch (PDOException $e)
{
echo 'Error: '.$e->getMessage();
} //end catch
} //end if server
echo '<center>';
echo '<h2> Select the dog breed you want to delete.</h2>';
$sql = "select name, descr from breed"; //prints sql query
echo '<form action="delete.php" method="POST">';
echo "<select name='BreedID' id=BreedID'>";
foreach($conn->query($sql) as $row)
{
echo '<option value = "';
echo $row['name'];
echo '"> ';
echo $row['descr'];
echo '</option>';
} // end foreach
echo '</select>';
echo '<br><input type="submit" name="submit" value="Delete"> <br>';
echo '</form>'; //end form
$stmt = $conn->query('SELECT * FROM breed');
echo "<table style='border: solid 1px black;'>"; //make table to display column headers
echo "<tr><th>ID</th><th>Name</th><th>Description</th><th>Date</th></tr>";
while ($row = $stmt->fetch(PDO::FETCH_NUM))
{
echo '<tr>';
foreach ($row as $value)
{
echo "<td>{$value}</td>";
}
echo '</tr>';
}
echo '</table>';
include('includes/footer.html');
?>
