Menu
So I have created an interface that accepts input from a drop-down box..
Then displays the data from three different database tables in an HTML table with all entries that match the criteria.
The table is shown at the bottom of the above image.
My question is how do I use PHP, using loops or otherwise, to re-work my code and create a huge HTML page that goes through every single event? AKA I need to generate 126 tables:
But I'm not sure how to approach this. My initial thought was to use a loop and just put the code to generate one single table inside it, but I wouldn't know what to put the condition for it to stop, nor would I know how to cycle through the different options in the drop-down lists. I'm not asking anyone to create the code for me but rather point me in a direction of what logic to use.. after that, I can probably figure it out on my own. Thank you, everyone. :)
Below is my code, that I use to generate each table, with annotations in comment form:
<?php
error_reporting(E_ALL);
$dbhost = "localhost"; //logs into my localhost server
$dbname = "sportsDay";
$dbuser = "root";
$dbpass = "...";
$year=$_POST['Year']; //gets variables from the drop-downs in the form displayed above
$gender=$_POST['Gender'];
$event=$_POST['Event'];
$result[]=0;
try
{
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("SET CHARACTER SET utf8mb4");
$sql = "SELECT Students.lName, Students.fName, Students.house
FROM Entries INNER JOIN Events ON Entries.ev1ID = Events.ID
JOIN Students ON Students.stID = Entries.stID
WHERE (Entries.ev1ID = :event or Entries.ev2ID = :event2) and (Students.year = :year)
AND (Students.gender = :gender)
ORDER BY Students.house ASC";
//my SQL code that matches up the values from the drop-downs to the values in the database tables
$stmt = $conn->prepare($sql);
$stmt->bindValue(':event', $event);
$stmt->bindValue(':event2', $event);
$stmt->bindValue(':year', $year);
$stmt->bindValue(':gender', $gender);
$stmt->execute();
$result = $stmt->fetchAll();
$count = $stmt->rowCount();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
<html>
<body>
<?php if ($count > 0): ?> //checks to see if there are results. if there are results, it displays them:
<table border="1" >
<tr>
<th>Name</th>
<th>House</th>
<th>Score</th>
</tr>
<?php foreach ($result as $row) {
?>
<tr>
<td><?php echo $row['fName']. ' '.$row['lName'] ?></td>
<td><?php echo $row['house'] ?></td> <td></td>
</tr>
<?php } ?>
</table>
<?php else: echo "No results." ?> //if not, it displays that there are no results.
<?php endif ?>
</body>
</html>
643
Since you already have your code to generate one table, you are correct that you can use that to generate all of them.
All you need to do is to cycle through all possibilities your form provides.
You must have a list of possible options in order to build the HTML form, just use these option lists in a nested foreach loop.
foreach ($event as $e) {
foreach ($gender as $g) {
foreach ($year as $y) {
// Use $e, $g and $y for your query and table construction.
$sql = ...; // Query stays the same.
$stmt->bindValue(':event', $e);
$stmt->bindValue(':event2', $e);
$stmt->bindValue(':year', $y);
$stmt->bindValue(':gender', $g);
}
}
}
Here is the full example according to the code you provided:
<?php
error_reporting(E_ALL);
$dbhost = "localhost"; //logs into my localhost server
$dbname = "sportsDay";
$dbuser = "root";
$dbpass = "...";
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("SET CHARACTER SET utf8mb4");
?>
<html>
<body>
<?php
// Lists of possible DB values
$event = array("100m", "100m relay", "High Jump", ...); // a list of all events
$gender = array("F", "M"); // a list of all genders
$year = array(7, 8, 9, 10, 11, 12); // a list of all classes
foreach ($event as $e) {
foreach ($gender as $g) {
foreach ($year as $y) {
$result[] = 0;
try {
$sql = "SELECT Students.lName, Students.fName, Students.house
FROM Entries INNER JOIN Events ON Entries.ev1ID = Events.ID
JOIN Students ON Students.stID = Entries.stID
WHERE (Entries.ev1ID = :event or Entries.ev2ID = :event2) and (Students.year = :year)
AND (Students.gender = :gender)
ORDER BY Students.house ASC";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':event', $e);
$stmt->bindValue(':event2', $e);
$stmt->bindValue(':year', $y);
$stmt->bindValue(':gender', $g);
$stmt->execute();
$result = $stmt->fetchAll();
$count = $stmt->rowCount();
}
catch (PDOException $e) {
echo $e->getMessage();
}
if ($count > 0) {
?>
<table border="1" >
<tr>
<th>Name</th>
<th>House</th>
<th>Score</th>
</tr>
<?php foreach ($result as $row) {
?>
<tr>
<td><?php echo $row['fName']. ' '.$row['lName'] ?></td>
<td><?php echo $row['house'] ?></td> <td></td>
</tr>
<?php } ?>
</table>
<?php
}
else {
echo "No results for $e ($g, $y).";
}
}
}
}
?>
</body>
</html>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
