new to php and MYSQL,and would like some help, so please be gentle
I have some code which populates a HTML table using php echo.
The statement is:
$query= "SELECT Distinct AgeGrp, Gender,Dist, Stroke, First,
Family, Name, Year, Time, Event, Date
FROM JLSSWIMMING1
WHERE Year = '7'
and Stroke = 'Freestyle' order by Time Asc Limit 1" ;
I would like to add more select statements. The idea is that by using Order bt Time ASC and Limit 1 for each select statement, the single table will display the fastest entry for each style, e.g. one entry for Freestyle, one entry for Backstroke etc.
$query2= "SELECT Distinct AgeGrp, Gender,Dist, Stroke, First,
Family, Name, Year, Time, Event, Date
FROM JLSSWIMMING1
WHERE Year = '7'
and Stroke = 'Backstroke' order by Time Asc Limit 1" ;
Thanks in advance
From research it look like I may need an array, (not a join), but I am struggling at this point.
Here is my current code:
<?php
try {
$pdo = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query= "SELECT Distinct AgeGrp, Gender,Dist, Stroke, First,
Family, Name, Year, Time, Event, Date
FROM JLSSWIMMING1
WHERE Year = '7'
and Stroke = 'Freestyle' Order by Time ASC" ;
$stmt = $pdo->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($result) > 0) {
echo "<br>";
echo '<table class="special-table">';
echo '<thead class="fixed-header">';
echo '<tr>
<th>Pos.</th>
<th class="left-align">Name</th>
<th class="left-align">Stroke</th>
<th>Yr</th>
<th>Dist</th>
<th>Time</th>
<th>AgeGrp</th>
<th>Gen.</th>
<th>DAte</th>
<th>Event</th>
</tr></thead><tbody>';
foreach ($result as $row) {
echo "<tr>
<td >" . $cnt++ ."</td>
<td class='left-align'>" . $row["Name"] . "</td>
<td class='left-align'>" . $row["Stroke"] . "</td>
<td >" . $row["Year"] . "</td>
<td >" . $row["Dist"] . "</td>
<td >" . $row["Time"] . "</td>
<td >" . $row["AgeGrp"] . "</td>
<td >" . $row["Gender"] . "</td>
<td >" . Date('d-m-y', strtotime($row['Date'])) . "</td>
<td >" . $row["Event"] . "</td>
</tr>";
}
echo "</table></div>";
} else {
echo "<br>";
echo "<br>";
echo "<div style='font-size: 42px;'> No results found ";
}
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
// Close the database connection
$pdo = null;
?>
2
Answers
Use one query like this to get all the data you want in one select. You should always try to exercise the power of a query rather than treat a relational database like a flat file.
Or you could use the IN() clause like this
If there are no ties in Time per Stroke you can use this approach:
Or using the ROW_NUMBER() window function you could: