Problem with Left Join

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

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

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,

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... 

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!

hum Jason maybe you've forgotten "echo" in your code?

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

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