Counting some values

[Aravinthan]

Hi guys,

 

I have a question.

 

Ok so I have a table named boxscore.

 

Here is the table's structure:

`periode` int(5) NOT NULL,

  `temps` varchar(50) NOT NULL,

  `but` varchar(100) NOT NULL,

  `assist1` varchar(100) NOT NULL,

  `assist2` varchar(100) NOT NULL,

  `extra` varchar(5) NOT NULL,

  `gameid` int(10) NOT NULL

 

And some records:

(2, '06:32', 'John Ioannis', 'Sevag', 'Alex', '', 3),

(2, '14:21', 'Karl', 'Sevag', 'John Ioannis', '', 3),

(2, '06:32', 'John Ioannis', 'Sevag', 'Alex', '', 3),

(2, '14:21', 'Karl', 'Sevag', 'John Ioannis', '', 3),

(1, '08:32', 'Alex', '', '', '', 4),

(2, '06:37', 'Nicolas', 'Karl', 'Gino', '', 4),

(1, '07:32', 'Alexis', 'Aravinthan', '', '', 1),

(2, '11:38', 'Nicolas', 'John Ioannis', '', '', 1),

(3, '01:15', 'Alexis', 'Aravinthan', '', '', 1),

(3, '01:28', 'Alex', '', '', '', 1),

(3, '05:36', 'Alex', 'Nicolas', 'Gino', '', 1),

(3, '09:30', 'Alex', '', '', '', 1),

(1, '00:54', 'Nicolas', '', '', '', 2),

(1, '02:40', 'Nicolas', 'Alex', 'Jonathan', '', 2),

(1, '09:49', 'Nicolas', 'Gino', 'Steve', '', 2),

(1, '10:36', 'Nicolas', '', '', '', 2),

(2, '01:40', 'Laurent', 'Alexis', 'Aravinthan', '', 2),

(2, '03:04', 'Nicolas', 'Alex', 'Steve', '', 2),

(1, '13:09', 'Alex', 'Laurent', 'Alexis', '', 5),

(2, '07:56', 'Nicolas', 'Aravinthan', '', '', 5),

(2, '12:30', 'Nicolas', 'Karl', '', '', 5),

(3, '03:35', 'Jonathan', 'Alexis', '', '', 5);

 

So What I want to do, is count how many times a player has scored in the last 3 games. So the last three games id's are 5,4,3.

 

I have this:

SELECT * FROM boxscore WHERE `but` = '$nom' OR `assist1` = '$nom' OR `assist2` = '$nom' ORDER BY `gameid` LIMIT 0,3

 

But it selects only the rows where the player has a goal, or an assist....

 

Thank you for your help,

Ara

[Aravinthan]

UP

[Aravinthan]

So I have changed it a bit and here is what I have:

SELECT SUM(but) AS but, SUM(assist1) AS assist1, SUM(assist2) AS assist2 FROM boxscore WHERE `but` = '$nom' OR `assist1` = '$nom' OR `assist2` = '$nom' GROUP BY `gameid` ORDER BY `gameid` LIMIT 0,3

 

But it shows the 3 last games when the player had a point, I want it to show 0, if a player didnt a goal or an assist in the last game....

 

 

[fenway]

To do this properly, you'll need to LEFT JOIN in the games table with the required three game rows...