Help constructing a query

boboli

Hi all...
I have 2 tables

Struttura della tabella `gare`
CREATE TABLE `gare` (
  `race_id` tinyint(3) NOT NULL default '0',
  `pos_gara` tinyint(2) NOT NULL default '0',
  `nome_pilota` varchar(50) NOT NULL default '',
  `auto` varchar(50) NOT NULL default '',
  `giri_gara` tinyint(3) NOT NULL default '0',
  `tempo_gara` varchar(20) NOT NULL default '',
  `gap_gara` varchar(20) NOT NULL default '',
  `fastest_laptime` varchar(20) NOT NULL default '',
  `fastest_lapnr` tinyint(3) NOT NULL default '0',
  `pos_quali` tinyint(2) NOT NULL default '0',
  `tempo_quali` varchar(20) NOT NULL default '',
  `gap_quali` varchar(20) NOT NULL default ''
) TYPE=MyISAM;

  Struttura della tabella `drivers`
CREATE TABLE `drivers` (
  `driver_id` int(3) unsigned NOT NULL auto_increment,
  `nome` varchar(40) NOT NULL default '',
  `numero` int(3) NOT NULL default '0',
  `nick` varchar(40) NOT NULL default '',
  `auto` varchar(150) NOT NULL default '',
  `team` varchar(40) NOT NULL default '',
  `logo` varchar(150) NOT NULL default '',
  KEY `driver_id` (`driver_id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

where the first table has all the results of every single race with the following format
RACE_ID - DRIVER POSITION - DRIVER NAME - CAR - LAPS and so on...
the second table is a list of all the drivers of the league
DRIVER_ID - NAME - NUMBER - NICK and so on...

"DRIVER NAME" of TAB1 has the same value of "NAME" of TAB2

now I need to make a query to COUNT how many time every driver appear in the RACES (gare) TABLE in order to have a list of ALL THE DRIVERS showing

DRIVER NAME - TOTAL RACES

I tryed some JOIN but I really can't have more than a driver listed 🙁

hope someone will help me

Roberto

Lars_Berg

select  nome,count(nome_pilota) as races
from drivers
 left join gare on nome = nome_pilota
group by nome

boboli

Lars Berg wrote:

select  nome,count(nome_pilota) as races
from drivers
 left join gare on nome = nome_pilota
group by nome

thank you Lars but for some reasons it does not work...
the page take loooong to execute, and I receive the following error

Fatal error: Maximum execution time of 15 seconds exceeded in /membri2/boboli/gtr/drivers2.php on line 86

this is the link
http://boboli.altervista.org/gtr/drivers2.php

and as you can see you have the first driver (and there are no entries in GARE for this driver) and than it starts a loop for the second driver...

this is the entire code

<?

include("config.inc.php");


$db = mysql_connect($db_host, $db_user, $db_password);
if ($db == FALSE)
die ("Errore nella connessione. Verificare i parametri nel file config.inc.php");
mysql_select_db($db_name, $db)
or die ("Errore nella selezione del database. Verificare i parametri nel file config.inc.php");
if ($sortby == "")
$query = "SELECT * FROM drivers order by driver_id DESC";
else
$query = "SELECT * FROM drivers order by $sortby $order";
$result = mysql_query($query, $db);

$flag = 0;
$colore1 = "#000000"; // nero
$colore2 = "#000000"; // nero
$colore3 = "#315984"; // azzurro

$colore_riga = $colore1; // assegno ad una nuova variabile il colore che desidero sia il primo



while ($row = mysql_fetch_array($result)) {
$driver_id = $row["driver_id"];
$numero = $row["numero"]; 
$nome = $row["nome"]; 
$nick = $row["nick"]; 
$auto = $row["auto"]; 
$team = $row["team"]; 

$query = "select  nome,count(nome_pilota) as tot_gare from drivers left join gare on nome = nome_pilota group by nome_pilota";
$result = mysql_query($query, $db);
$row = mysql_fetch_array($result);
{
$tot_gare = $row[tot_gare]; 
}


if($flag == 0) {
$colore_riga = $colore2;
$flag = 1;
}else if ($flag == 1){
$colore_riga = $colore2;
$flag = 2;
}else {
$colore_riga = $colore3;
$flag = 0;
}

echo "<tr bgcolor=\"$colore_riga\"><td class='riga'><a href=\"view_driver.php?driver_id=$driver_id\"> $nome</a></td><td class='riga'>$nick</td><td class='riga'>$tot_gare</td><td class='riga'>$auto</td><td class='riga'>$team</td></tr>";  

}
mysql_close($db);
?>

any idea ??? thanx
Roberto

cornercuttinjoe

if you are using a current enough version of php, you can do this

SELECT COUNT(nome_pilota) FROM gare WHERE nome_pilota = some (SELECT DISTINCT nome FROM drivers);

at least i think this is what youre asking. Basically a count of the intersection.

boboli

cornercuttinjoe wrote:
if you are using a current enough version of php, you can do this

SELECT COUNT(nome_pilota) FROM gare WHERE nome_pilota = some (SELECT DISTINCT nome FROM drivers);

at least i think this is what youre asking. Basically a count of the intersection.

now I only get the first driver 😕