Problem with Left Join
14 posts by 4 authors in: Forums > CMS Builder
Last Post: September 20, 2013 (RSS)
Hi and thanks in advance for your help.
I'm building a soccer stats site and i have two tables
Teams (num, title, logo)
Fixtures multirecords (game, team_home1, teamaway1, result1, team_home2, team_away2, result2 and so on)
In the fixtures list i would have
GAME 1
TEam_home 1 - TEam_away1 - result1
TEam_home 2 - TEam_away2 - result2
etc
But for each team i would recall the correct logo from teams table. Is it possible with leftjoin?
:-)
Hum, maybe is a better solution to have two tables
Teams (num, title, logo)
Matches (num, round_num, team_home, team_away, result)
Team_home and team_away values are SELECT num, title FROM `<?php echo $TABLE_PREFIX ?>teams`
My questions are:
Is it possibile in the list page to have one table for each round with all matches grouped by round?
Is it possibile to link team_away and team_home with cms_teams and put each teams.logo near matches.team_away and matches.team_home?
Hi,
I think you can solve both of these problems using the array_groupBy function. To list the logo of each home and away team I would retrieve all of the teams in a seperate variable, and then sort them by their num value using array_groupBy. Then you can easily find each teams details, something like this would work:
<?php
// load records from 'members'
list($teams, $teamsMetaData) = getRecords(array(
'tableName' => 'teams',
'loadUploads' => true,
'allowSearch' => false,
));
//Sort the teams by there num value
$teams = array_groupBy($teams, 'num', false);
// load records from 'members'
list($matches, $matchesMetaData) = getRecords(array(
'tableName' => 'matches',
'loadUploads' => true,
'allowSearch' => false,
));
//Sort and group the matches by the round number
$matches = array_groupBy($matches, 'round_num', true);
?>
then you'd use the following code to display the entries on the page:
<h1>Matches</h1>
<!-- matches have been grouped by round, so cycle through the rounds -->
<?php foreach($matches as $round): ?>
<ul>
<!-- cycle through the matches in each round -->
<?php foreach($round as $match): ?>
<li>
Result: <?php echo $match['result']; ?><br>
<!-- display the match home teams title, teams have been sorted by num, so we can use the num value to find the team in the array -->
Home team: <?php echo $teams[$match['team_home']]['title']; ?><br>
Away team: <?php echo $teams[$match['team_away']]['title']; ?>
</li>
<?php endforeach; ?>
</ul>
<?php endforeach; ?>
This is just example code, so you'll probably need to make changes to get it working.
So the array_groupBy function requires the array you want to sort, the field you want to sort by, and a Boolean for if you want to group by the field or not. So I've sorted the teams by their num value, then you can use the team num value to find the correct home and away team in the array.
I've used the array_groupBy function on the matches as well, but grouped them by the round_num, this will create an array where all of the items with the same round_num will be grouped together, then you can cycle through each item.
I'd recommend using the showme function on matches and team variables so you can see the structure the array_groubBy function creates:
showme($matches);
echo "<hr>";
showme($teams);
Let me know if you have any questions.
Thanks!
Greg
PHP Programmer - interactivetools.com
Thanks greg!
I have another question: i would count matches in wich team_home_score > team_away_score where team_home_id = xxxx
Is it possibile?
Orazio
By Jason - September 6, 2013
Hi Orazio,
You can do this using the mysql_count() function. This function takes in a table name (ie, matches) and a where clause. It will return a count of the records that match that where clause.
For example, if we assume the table is called "matches" and we have fields called team-home_score, team_away_score (both numbers) and team_home_id, we can get that count like this:
<?php $homeTeamWins = mysql_count("matches", "team_home_score > team_away_score AND team_home_id = 'xxx'");?>
You would replace xxx with the num of the home team you were looking for.
Hope this helps,
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
mmm i have this code in the list page
// load records
list($teamsRecords, $teamsMetaData) = getRecords(array(
'tableName' => 'teams',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$teamsRecord = @$teamsRecords[0]; // get first record
// load records
list($matchesRecords, $matchesMetaData) = getRecords(array(
'tableName' => 'matches',
'where' => "team__home =".intval($squadreRecord['num']),
'orWhere' => "team_away =".intval($squadreRecord['num']),
'orderBy' => 'date_match ASC',
));
// show error message if no matching record is found
if (!$matchesRecord) {
header("HTTP/1.0 404 Not Found");
print "Record not found!";
exit;
}
so i try to put
<?php $homeTeamWins = mysql_count("matches", "team_home_score > team_away_score AND team_home = '1'");?>
but it does'nt work...
By Jason - September 6, 2013
Hi,
Does the code return errors? What is the structure of the matches table?
Let me know and we'll see what we can work out.
Thanks!
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
By Jason - September 19, 2013
Hi,
Yes, the code in the example will calculate the total and store it in a variable called $homeTeamWins. You can output that value using <?php echo $homeTeamWins;?> where ever it's needed.
Hope this helps
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Thank you for your reply Jason!
I have this code:
// load records
list($matchesRecords, $matchesMetaData) = getRecords(array(
'tableName' => 'matches',
'where' => "home_team =".intval($squadreRecord['num']),
'orderBy' => 'data_incontro ASC',
));
so how could i select only matches where home_team num is the final num of url? i try this
<?php $homeTeamWins = mysql_count("incontri", "reti_casa > reti_trasferta AND squadra_casa = '.intval($squadreRecord['num'])'");?>
but it doesn.'t work