Am I using too many Queries in this code?

PHP
1

Hello Sitepoint PHP forum!

I hope this question is in the right place.

I have been working on this Mysql and PHP code for about a month now and I am curious if it is okay?

I wanted to gather the teamname in the second larger $query2 but I am at a standstill. The code works just as I want it to, meaning I would like to get rid of the smaller $query1 but if I have done it correctly, I would be happy to hear it.

I know I can get the teamname in $query2 BUT I can’t figure out how to print out the name of the team and then print all the games the team has as an array below it because of the WHILE condition.

Any help or advice would be appreciated. Thanks.

<?php
	$mysqli = NEW MySQLi('localhost','root','','sports');

	$year = '2020';
	$team = '7';

	echo "Year: " . $year . "<BR />";
	echo "Team: " . $team . "<BR /><BR />";

// First Query to get name of team equal to $team value
$query1 = "SELECT 
				team.id AS teamid,
				team.team_name AS teamname
			FROM
				teams AS team
			WHERE
				$team = team.id";

	$resultSet1 = $mysqli->query($query1);

	if ($resultSet1->num_rows !=0){
		while ($rows = $resultSet1->fetch_assoc()){
			$tid = $rows['teamid'];
			$tname = $rows['teamname'];
			echo "Team: " . $tid . " - " . $tname . "<BR /><BR />";
		}
	}else{
			echo $mysqli->error;
			echo "No team found matching that criteria.";
			die;
	}

// Second Query to check for games by the team in first query
$query2 = "SELECT 
				game.id AS gameid,
				game.home_team,
				game.guest_team,
				home.id,
				guest.id,
				home.team_name AS hteam,
				guest.team_name AS gteam,
				game.game_date AS gamedate
			FROM
				games AS game
			LEFT JOIN
				teams AS home ON game.home_team = home.id
			LEFT JOIN
				teams AS guest ON game.guest_team = guest.id
			WHERE
				DATE_FORMAT(game.game_date, '%Y') = $year AND (home.id = $team OR guest.id = $team)";

	$resultSet2 = $mysqli->query($query2);

	if ($resultSet2->num_rows !=0){
		while ($rows = $resultSet2->fetch_assoc()){
			$id = $rows['gameid'];
			$home = $rows['hteam'];
			$guest = $rows['gteam'];
			$gdate = $rows['gamedate'];
			echo "ID: $id $guest at $home - $gdate<BR />";
		}
	}else{
			echo $mysqli->error;
			echo "No games found matching that criteria.";
	}
?>

This is how it prints out on my localhost for an idea of what I am looking for.

Year: 2020
Team: 7

Team: 7 - Memphis State

ID: 3 Arkansas State at Memphis State - 2020-09-01
ID: 4 Memphis State at LSU - 2020-09-08
2

I usually set a flag for something like that, either to detect that it’s the first pass through the loop, or when something changes.

$first = true;
-- your query here --
while ($rows = $resultSet2->fetch_assoc()) { 
  if ($first == true) { 
    //display the header information
    echo "Team " . $rows[whatever];
    $first = false;
    }
  // do the rest of the loop
  }

Same principle as displaying a header when a sorted column changes.

3

I think two queries are fine here. If you tried to reduce it to one and the team in question has no scheduled games them you would never get the team’s name with just a game query.

Off topic but I would try for a more consistent naming convention and to use proper data types when applicable:

$teamId = 7; // Makes it clear that it contains a numeric team id.
CREATE TABLE games
    int id 
    int home_team_id
    int away_team_id

Eventually you may want objects (or just arrays) to hold $game and $team information. You don’t want to confuse yourself with $game vs $gameId.

I might add that you might eventually want to store more data about your game teams. For example you might want to track goals scored by each time. You can start adding things like home_team_score and away_team_score columns to game but that will quickly get messy. Instead you might want to add a new table:

CREATE TABLE game_team
    int id
    int game_id
    int team_id
    char(x) type # 'home' or 'away'
    int score

Bit more work up front but possibly worth it in the end

1 Like
4

Oh yes, good point.

5

There is CTE in SQL that combines two queries. Pseudocode:

WITH list1stquery AS (SELECT first query)
second query from y based on values in (SELECT * FROM list1stquery)

Works on modern MySQL and all Postgresql.

6

Gonna be honest, I would probably run two queries here.
Especially as query2’s result set gets larger and larger, the overhead of having 2 queries becomes less relevant to the weight of carrying the excess information repeated so many times in a singular query.

That said, query2 i feel can be optimized a bit (though i’m probably going to get told i’m wrong here… c’est la vie)…

#1: Parameterize your queries. This isn’t a streamline so much as it is a security thing once you put this somewhere where you’re going to want to let people choose the year and team.
#2: Don’t use the root login for your database connection. This one should go without saying, really.
#3: Add the WHERE conditions to your JOIN conditions. Fewer rows in the join makes for faster database workings.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.