Trying to create a schedule of games...

[Jim R]

The code is meant to print a schedule of games. 

Eventually, it will produce games on the given day, but I'm just trying to get it to list any game I have the database.  I'm not getting any results.

 

This is a stripped down version of code I'm using on another part of my site that involves listing players from each team, player stats and team records.  It works just fine.  Not sure what I'm missing here. 

$query = "
SELECT *,s.id as sid, gamedate, s.home_id, s.away_id,h.id as hid, a.id as aid,
(CASE
		WHEN h.toggle = 1 THEN concat(h.city,' ',h.school)
		WHEN h.toggle IS NULL THEN h.school
		ELSE concat(h.school,' (',h.toggle,')')			
	END) as home,

(CASE
		WHEN a.toggle = 1 THEN concat(a.city,' ',a.school)
		WHEN a.toggle IS NULL THEN a.school
		ELSE concat(a.school,' (',a.toggle,')')			
	END) as away
FROM a_schedule s

JOIN a_schools h
	ON h.id = s.home_id
JOIN a_schools a
	ON a.id = s.away_id
	
";

		$schools = mysqli_query($con,$query);
		
		echo mysqli_error($con);
		while($row = mysqli_fetch_assoc($schools)) {
		
		echo $row['away']. ' at ' .$row['home'];
		}
		

 

a_schedule:

a_schools:

 

[Barand]

I ran your query (cleaning the SELECT clause up to remove duplicate output)

SELECT s.id as sid, gamedate, s.home_id, s.away_id,
(CASE
        WHEN h.toggle = 1 THEN concat(h.city,' ',h.school)
        WHEN h.toggle IS NULL THEN h.school
        ELSE concat(h.school,' (',h.toggle,')')            
        END) as home,
(CASE
        WHEN a.toggle = 1 THEN concat(a.city,' ',a.school)
        WHEN a.toggle IS NULL THEN a.school
        ELSE concat(a.school,' (',a.toggle,')')            
        END) as away
FROM a_schedule s

JOIN a_schools h
    ON h.id = s.home_id
JOIN a_schools a
    ON a.id = s.away_id;

on this data

a_schools                                  a_schedule
+----+-----------+--------+---------+      +----+------------+----------+---------+---------+
| id | school    | toggle | city    |      | id | gamedate   | gametime | home_id | away_id |
+----+-----------+--------+---------+      +----+------------+----------+---------+---------+
|  1 | School 1  |      1 | City 1  |      |  1 | 2023-01-04 | 19:30:00 |       1 |       2 |
|  2 | School 2  |   NULL | City 2  |      |  2 | 2023-01-11 | 19:30:00 |      10 |      11 |
|  3 | School 3  |   NULL | City 3  |      |  3 | 2023-01-18 | 19:30:00 |      15 |       3 |
|  4 | School 4  |   NULL | City 4  |      |  4 | 2023-01-25 | 19:30:00 |       4 |       5 |
|  5 | School 5  |      2 | City 5  |      |  5 | 2023-02-01 | 19:30:00 |       6 |       7 |
|  6 | School 6  |   NULL | City 6  |      |  6 | 2023-02-08 | 19:30:00 |       8 |       9 |
|  7 | School 7  |   NULL | City 7  |      +----+------------+----------+---------+---------+
|  8 | School 8  |   NULL | City 8  |
|  9 | School 9  |   NULL | City 9  |
| 10 | School 10 |      1 | City 10 |
| 11 | School 11 |   NULL | City 11 |
| 12 | School 12 |   NULL | City 12 |
| 13 | School 13 |   NULL | City 13 |
| 14 | School 14 |   NULL | City 14 |
| 15 | School 15 |      2 | City 15 |
| 16 | School 16 |   NULL | City 16 |
+----+-----------+--------+---------+

and got this

+-----+------------+---------+---------+-------------------+--------------+
| sid | gamedate   | home_id | away_id | home              | away         |
+-----+------------+---------+---------+-------------------+--------------+
|   1 | 2023-01-04 |       1 |       2 | City 1 School 1   | School 2     |
|   2 | 2023-01-11 |      10 |      11 | City 10 School 10 | School 11    |
|   3 | 2023-01-18 |      15 |       3 | School 15 (2)     | School 3     |
|   4 | 2023-01-25 |       4 |       5 | School 4          | School 5 (2) |
|   5 | 2023-02-01 |       6 |       7 | School 6          | School 7     |
|   6 | 2023-02-08 |       8 |       9 | School 8          | School 9     |
+-----+------------+---------+---------+-------------------+--------------+

 

[Jim R]

I cleaned up the SELECT.  I'm not getting any output out of that query on to my page, but it does work in my database software (Sequel Pro).  It should be printing 309 rows.  

 

I'm not getting any errors, and I don't see anything wrong with my loop.  I'm clearly missing something that my brain is blocking out.  

[mac_gyver]

what output are you getting on the web page? if it's a blank page, what does the 'view source' in your browser show? can you echo a string using php code? is your code doing any redirects that could discard any output from the web page if php's output_buffering is ON?

do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects? stop and start your web server to get any changes made to the php.ini to take effect and check that the settings actually got changed to those values by using a phpinfo(); statement in a .php script file.

do you have error handling for all the database statements that can fail - connection, query, prepare, execute, and a few others? the simplest way of adding error handling, without adding conditional logic at each statement, is to use exceptions for errors and in most cases simply let php catch and handle the exception, where php will use its error related settings (see the previous paragraph above) to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.)

to enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

 

[Jim R]

@mac_gyver your prompt made me check my connection, and I had the connection commented out.  

[mac_gyver]

you would have been getting php errors at the reference to the connection variable. php's error_reporting should always be set to E_ALL. when learning, developing, and debugging code/query(ies), display_errors should be set to ON. when on a live/public server, display_errors should be set to OFF, and log_errors should be set to ON.