Mysql - I need the results from more than one select statement, (using Limit (x) displayed in a table created by php echo

bgarcher
March 4, 2024
137 views
1 vote
2 Answers

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;
?>

Answers

- RiggsFolly
- March 2, 2024 at 11:22 am
- 0 votes
0
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.
```
$query= "SELECT Distinct AgeGrp, Gender,Dist, Stroke, First, 
                Family, Name, Year, Time, Event, Date 
        FROM JLSSWIMMING1 
        WHERE Year = '7'  
        and ( Stroke = 'Freestyle' OR Stroke = 'Backstroke')";  
```
Or you could use the IN() clause like this
```
$query= "SELECT Distinct AgeGrp, Gender,Dist, Stroke, First, 
                Family, Name, Year, Time, Event, Date 
        FROM JLSSWIMMING1 
        WHERE Year = '7'  
        and Stroke IN('Freestyle', 'Backstroke')";  
```
Login or Signup to reply.

- user1191247
- March 4, 2024 at 2:10 am
- 0 votes
0
If there are no ties in Time per Stroke you can use this approach:
```
SELECT *
FROM JLSSWIMMING1
WHERE (Year, Stroke, Time) IN (
    SELECT Year, Stroke, MIN(Time)
    FROM JLSSWIMMING1
    WHERE Year = 7
    GROUP BY Stroke
);
```
Or using the ROW_NUMBER() window function you could:
```
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Stroke ORDER BY Time) AS rn
    FROM JLSSWIMMING1
    WHERE Year = 7
) AS tmp
WHERE rn = 1;
```
Login or Signup to reply.