All geek questions in one place

How to logically use PHP to display all possible values ​​of a database table?

So, I created an interface that accepts input from a drop-down list. enter image description here

It then displays data from three different database tables in an HTML table with all elements matching the criteria. enter image description here

The table is shown at the bottom of the image.

My question is: how can I use PHP, using loops or otherwise, to reuse my code and create a huge HTML page that goes through every event? AKA I need to create 126 tables:

enter image description here

But I'm not sure how to approach this. My initial thought was to use a loop and just put the code to create one separate table inside it, but I would not know what to put a condition to stop it, and I do not know how to go to the various options in the drop-down lists. I don’t ask anyone to create code for me, but rather tell me which logic to use. After that, I can probably figure it out on my own. Thanks to all.:)

Below is my code that I use to create each table, with annotations in the form of a comment:

<?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>
+5
source share
2 answers

Since you already have code to create one table, you are right that you can use them to generate all of them.

All you have to do is to see all the possibilities that your form provides.

You should have a list of options for creating an HTML form, just use these lists of options 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 a complete 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>
+1
source

I recreated your db structure. Using the left join, I was able to get all the data you need to sort by event id.

My database:

Students table

 +------+---------+-----------+-------+ | stID | fName | lName | house | +------+---------+-----------+-------+ | 1 | Nadir | Roman | east | | 2 | Jesus | Lopez | west | | 3 | Ioannis | Chalkadis | west | | 4 | Adry | Pepes | east | | 5 | David | Caretas | west | +------+---------+-----------+-------+

Event table

 +----+-----------+---------------+ | ID | name | location | +----+-----------+---------------+ | 1 | 100m | Track | | 2 | 400m | Track | | 3 | High Jump | High Jump Pit | +----+-----------+---------------+

Score table

 +------+-------+-------+ | stID | ev1ID | ev2ID | +------+-------+-------+ | 1 | 1 | 2 | | 2 | 2 | 3 | | 3 | 1 | 3 | | 4 | 1 | 2 | | 5 | 1 | 3 | +------+-------+-------+

Request:

 mysql> SELECT Events.ID, Events.name, location, Scores.stID, fName, lName, house FROM Scores LEFT JOIN (Students, Events) ON (Students.stID = Scores.stID AND (Events.ID = Scores.ev1ID OR Events.ID = Scores.ev2ID)) ORDER BY ID;

And the result:

 +------+-----------+---------------+------+---------+-----------+-------+ | ID | name | location | stID | fName | lName | house | +------+-----------+---------------+------+---------+-----------+-------+ | 1 | 100m | Track | 1 | Nadir | Roman | east | | 1 | 100m | Track | 5 | David | Caretas | west | | 1 | 100m | Track | 4 | Adry | Pepes | east | | 1 | 100m | Track | 3 | Ioannis | Chalkadis | west | | 2 | 400m | Track | 2 | Jesus | Lopez | west | | 2 | 400m | Track | 1 | Nadir | Roman | east | | 2 | 400m | Track | 4 | Adry | Pepes | east | | 3 | High Jump | High Jump Pit | 2 | Jesus | Lopez | west | | 3 | High Jump | High Jump Pit | 5 | David | Caretas | west | | 3 | High Jump | High Jump Pit | 3 | Ioannis | Chalkadis | west | +------+-----------+---------------+------+---------+-----------+-------+

You can split this output into different arrays, 1 per event:

 $result = $stmt->fetchAll(); $mappedEvents = []; array_map(function($entry) { if(!isset($mappedEvents[$entry['ID']])) { $mappedEvents[$entry['ID']] = []; } $mappedEvents[$entry['ID'][] = $entry; }, $result); foreach($mappedEvents as $eventID => $data) { // $eventID = event ID // $data = Array with all info about the event (students, location, etc..) }
0
source

Source: https://habr.com/ru/post/1246874/

More articles:


All Articles