Question 243827 in Mysql Question posted in
The official MySQL documentation can be found here.

Ignore a MySQL query if no record(s) found

I have a database with a list of soccer fixtures for the year. Fixtures are contested on most weekends – but, notably, not all.

The website currently displays fixtures in this style/format, with separate MySQL statements running for each weekend:

Weekend 1

Team A vs Team B

Team C vs Team D

Weekend 2

No Fixtures

Weekend 3

Team B vs Team D

Team C v Team A

Weekend 4

No Fixtures

How can I get a MySQL statement to completely ignore any dates that do not have any fixtures? That is to say, in the above example, I would not want Weekends 2 or 4 to show at all.

However, should a fixture be unexpectedly rearranged to those weekends, I WOULD want it to show. Currently, I am removing/inserting new MySQL statements when such rearrangements occur, which is time consuming.

It would be less work for me if I could avoid this. Thanks in advance for any advice!

<?php
$servername = "#";
$username = "#";
$password = "#";
$dbname = "#";
    
            
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}


$sql = "
SELECT * 
FROM fixtures
WHERE rel_date 
BETWEEN '2023-09-02' AND '2023-09-03' ";

$result = $conn->query($sql);


echo "<h3>Weekend 2</h3>";              

                    
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
                    
                
echo "<div class='fixture-strip'>";
echo "" .$row["fixtures"]. "";
echo "</div>";
                
}

} else {
echo "<p>No fixtures on this date</p>";
}
$conn->close();

?>
rel_date fixtures
2023-03-09 Team A vs Team B
2023-03-09 Team C vs Team D
2023-03-17 Team B vs Team D
2023-03-17 Team C vs Team A
2023-10-01 Team D vs Team A
2023-10-01 Team B vs Team C
Tags:

2

Answers


  1. 0

    You can add additional where clauses to your query
    in your case AND fixtures != NULL 

    $sql = "
SELECT * 
FROM fixtures
WHERE rel_date BETWEEN '2023-09-02' AND '2023-09-03' AND
    fixtures != NULL";
    Login or Signup to reply.
  2. 0

    Use a query like this:

    SELECT *, DENSE_RANK() OVER (ORDER BY YEAR(rel_date), WEEK(rel_date, 1)) AS weekend_no
FROM fixtures
WHERE rel_date BETWEEN '2023-10-01' AND '2024-03-17'
ORDER BY rel_date;

    to retrieve all the fixtures for the time period you are interested in. The use of DENSE_RANK() is giving us the weekend number. This can then be used in your loop to decide whether to display your weekend header or not:

    <?php
$servername = '#';
$username = '#';
$password = '#';
$dbname = '#';
$charset = 'utf8mb4';

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset($charset);
$conn->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

$sql = <<<'SQL'
    SELECT *, DENSE_RANK() OVER (ORDER BY YEAR(rel_date), WEEK(rel_date, 1)) AS weekend_no
    FROM fixtures
    WHERE rel_date BETWEEN '2023-10-01' AND '2024-03-17'
    ORDER BY rel_date;
    SQL;

$result = $conn->query($sql);

$prev_weekend = null;

while ($row = $result->fetch_assoc()) {

    if ($prev_weekend != $row['weekend_no']) {
        echo "<h3>Weekend {$row['weekend_no']}</h3>";
        $prev_weekend = $row['weekend_no'];
    }

    echo "<div class='fixture-strip'>{$row['fixtures']}</div>";

}

    If the "weekend number" is the count from the first weekend of the season you could use something like:

    SELECT *, TIMESTAMPDIFF(WEEK, '2023-09-30', rel_date) + 1 AS weekend_no
FROM fixtures
WHERE rel_date BETWEEN '2023-10-01' AND '2024-03-17';

    where '2023-09-30' represents the first Saturday of the season. This could be stored in a seasons table with the id of the season added to your fixtures table.

    You should read Mysqli tutorial (how to use it properly) before going any further.

    Here’s a db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search