Soldato
Hi All,
Hit a bit of a snag with some University work I'm completing, we need to design a animal adoption website with various functionality for both a staff member and a normal user.
So far I have setup the index page, ability to login, register etc.
One of the things we need to setup is the staff member needs to be able to see which animals are available for adoption and who owns them. When a staff member uploads a new animal it automatically updates the "owns" table with the userid of the member of staff and animal id of the new animal (auto-increments).
I have a PHP page that pulls the information into a table which is working perfectly fine, it runs a query to select * from the animal table where available = YES, however it's missing the owner details as that needs a JOIN to the user, animal and owns tables.
My database looks like this:
4 Tables - user, animal, owns, adoptionrequest (irrelevant atm)
user = userid, username, staff, password
animal = animalid, name, dateofbirth, description, type, photo, available
owns = animalid, userid
Using this SQL I can select my user from the user table successfully joining owns and animal.
However in PHP it throws me an error to do with there being no query buffer which I assume is because of how it's using a while loop to populate the table and then I'm trying to run another SQL query to populate just the owner column of the table on the webpage?
Here's my code for the page which will explain better with what I'm trying to achieve:
I'm very far from a decent PHP developer and a lot of this is loosely learned so expect it to be incorrectly used.
Here's how the webpage looks when running the above code:
Here's with that code commented out and "" used instead.
Hope I've explained myself well enough, but any guidance would be appreciated.
Hit a bit of a snag with some University work I'm completing, we need to design a animal adoption website with various functionality for both a staff member and a normal user.
So far I have setup the index page, ability to login, register etc.
One of the things we need to setup is the staff member needs to be able to see which animals are available for adoption and who owns them. When a staff member uploads a new animal it automatically updates the "owns" table with the userid of the member of staff and animal id of the new animal (auto-increments).
I have a PHP page that pulls the information into a table which is working perfectly fine, it runs a query to select * from the animal table where available = YES, however it's missing the owner details as that needs a JOIN to the user, animal and owns tables.
My database looks like this:
4 Tables - user, animal, owns, adoptionrequest (irrelevant atm)
user = userid, username, staff, password
animal = animalid, name, dateofbirth, description, type, photo, available
owns = animalid, userid
Using this SQL I can select my user from the user table successfully joining owns and animal.
Code:
SELECT username FROM user
INNER JOIN owns
ON user.userid = owns.userid
INNER JOIN animal
ON animal.animalid = owns.animalid
where animal.name = '15';However in PHP it throws me an error to do with there being no query buffer which I assume is because of how it's using a while loop to populate the table and then I'm trying to run another SQL query to populate just the owner column of the table on the webpage?
Here's my code for the page which will explain better with what I'm trying to achieve:
Code:
<?php
session_start();
if (!isset($_SESSION['username']) and ($_SESSION['usertype']!=1)){
header("Location: index.php");
}
$husername=$_SESSION['username'];
// Database Connection
require_once ('connectdb.php');
$getinfo="SELECT * FROM animal WHERE available = 'Yes'";
$rows = $db->query($getinfo);
if ( $rows && $rows->rowCount()> 0) {
include("header.php")
?>
<!-- Start HTML Form -->
<!-- Create Animal table -->
<table border="1px" cellpadding="5px">
<tr>
<th align='left'><b>Photograph</b></th>
<th align='left'><b>ID</b></th>
<th align='left'><b>Name</b></th>
<th align='left'><b>DOB</b></th>
<th align='left'><b>Description</b></th>
<th align='left'><b>Type</b></th>
<th align='left'><b>Available</b></th>
<th align='left'><b>Owner</b></th>
<th align='left'><b>Click to Edit</b></th>
</tr>
<!-- Use PHP to grab information from the database, while loop -->
<?php while ($row = $rows->fetch()) { ?>
<!-- Trying to populate the owner column of the table -->
<?php $getanimalid = $row['animalid'] ?>
<?php $owner = $db->exec("SELECT username FROM user INNER JOIN owns ON user.userid = owns.userid INNER JOIN animal ON animal.animalid = owns.animalid where owns.animalid = '$getanimalid'");
?>
<tr>
<!-- Display Image & Details of animal stored in DB -->
<td><?php echo '<img src="' . $row['photo'] . '" />'; ?> </td>
<td><?php echo $row['animalid'] ?> </td>
<td><?php echo $row['name'] ?> </td>
<td><?php echo $row['dateofbirth'] ?> </td>
<td><?php echo $row['description'] ?> </td>
<td><?php echo $row['type'] ?> </td>
<td><?php echo $row['available'] ?> </td>
<td><?php echo $owner ?> </td>
<!-- Edit Button & Values are brought into the page -->
<td><a href = "updateanimal.php?animalid=<?= $row['animalid']?>&name= <?= $row['name']?>&dateofbirth='<?= $row['dateofbirth']?>'&available=<?= $row['available']?>">Edit</a></td>
</tr>
<?php
}
echo '</table>';
}
else {
echo "<p>No animals to display.</p>\n";
}
?>
<html>
<form action ="staff.php" method="post">
<p><input type="submit" name="Register" value ="Go Back" /></p>
</form>
</html>
<?php
include ('footer.php')
?>I'm very far from a decent PHP developer and a lot of this is loosely learned so expect it to be incorrectly used.
Here's how the webpage looks when running the above code:
Here's with that code commented out and "" used instead.
Hope I've explained myself well enough, but any guidance would be appreciated.
Last edited:
