Member Avatar for QWaz

Hi,

I am developing a website for a club and I am trying to get the next games to show up.

I am trying to get some info out of one database and then use that info as a key for the next query, then display all the results that correspond to those queries. Hope that makes sense.

The code I have done so far is:
Please help, I have never done this before..

<?php

	$upcomingGames=array();
	$teamsSql = 'SELECT * FROM teams';
	foreach ($conn->query($teamsSql) as $row) {
		$teamArray[]= $upcomingTeam = $row['team'] && $upcomingRound = $row['round'] &&	$league = $row['league'] &&	$leagueURL = urlencode($row['league']) && $teamURL = urlencode($row['team']);
		foreach ($conn->query("SELECT * FROM fixtures_results WHERE `team`='".$upcomingTeam."' AND `round`='".$upcomingRound."'") as $gameRow) {
			$upcomingGames[]=<<<END
			<tr>
			<td><div class="upcomingGamesLeague"><a href="fixtures.php?leagueName=$leagueURL&teamName=$teamURL">$league:</a></div></td>
			<td><div class="upcomingGamesTeams"><a href="" target="_blank">$gameRow[home_team]</a> V <a href="" target="_blank">$gameRow[away_team]</a></div></td>
			<td><div class="upcomingGamesDate">$gameRow[day] $gameRow[date] $gameRow[time]</a></div></td>
			</tr>
			
END;
		}
	}
	$upcomingGames=implode("\r\n",$upcomingGames);

?>

Thanks,

Cheers,

QWaz

  • 3 Contributors
  • 2 Replies
  • 94 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Member #334542

Dani AI

Generated

Short summary and recommended approach (addresses the code in the first post and the quick replies by and ):

The simplest, safest, and fastest solution is to fetch what you need with one properly written SQL query (avoid the per-team loop + query pattern), then render the results while escaping output and building URLs safely. Use parameterized queries or PDO to avoid SQL injection and set PDO to throw exceptions so errors are visible while developing.

Example pattern (PDO, single query + safe output):

<?php
// assume $pdo is a PDO instance with ERRMODE_EXCEPTION
$sql = "
SELECT f.home_team, f.away_team, f.day, f.date, f.time, t.league, t.team AS teamName
FROM teams AS t
INNER JOIN fixtures_results AS f
  ON t.team = f.team AND t.round = f.round
ORDER BY f.date, f.time
";
$stmt = $pdo->query($sql);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $leagueURL = urlencode($r['league']);
    $teamURL   = urlencode($r['teamName']);
    $home      = htmlspecialchars($r['home_team'], ENT_QUOTES, 'UTF-8');
    $away      = htmlspecialchars($r['away_team'], ENT_QUOTES, 'UTF-8');
    $homeHost  = 'http://' . rawurlencode($r['home_team']) . '.site.com.au';
    echo "<tr><td><div class=\"upcomingGamesLeague\"><a href=\"fixtures.php?leagueName={$leagueURL}&amp;teamName={$teamURL}\">" . htmlspecialchars($r['league']) . "</a></div></td>";
    echo "<td><div class=\"upcomingGamesTeams\"><a href=\"{$homeHost}\" target=\"_blank\">{$home}</a> V <a href=\"http://" . rawurlencode($r['away_team']) . ".site.com.au\" target=\"_blank\">{$away}</a></div></td>";
    echo "<td><div class=\"upcomingGamesDate\">{$r['day']} {$r['date']} {$r['time']}</div></td></tr>";
}
?>

Practical tips and troubleshooting

  • Avoid N+1 queries: fetching all matching fixtures with a JOIN (or a single fixtures query using an IN list of teams) reduces DB round trips. If using SQL Server, the JOIN semantics are the same; make sure the join keys are indexed.
  • Add an index on the columns used in the join/where, e.g. CREATE INDEX idx_fixtures_team_round ON fixtures_results(team, round);
  • Always escape HTML output with htmlspecialchars, and build external hostnames with rawurlencode to avoid broken or unsafe links.
  • Use prepared statements when incorporating user input and enable PDO exceptions during development: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION.
  • Watch for PHP mistakes like using logical operators (&&) where you meant assignment; that yields booleans and will break your values.

Further reading: PHP PDO prepared statements (https://www.php.net/manual/en/pdo.prepared-statements.php) and SQL JOINs on Microsoft Learn ().

Recommended Answers

All 2 Replies

Member Avatar for genevish

Select * from teams t join fixtures_results f on t.team = f.team and t.round = f.round

Member Avatar for Member #334542

If you are going to fetch a single field from first query or one more values?
If a single value:

Then store it in a $infokey="that value";

Then pass it to another query: "select * from nextQuery where field=$infokey"

This is somewhat I understand!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.