[SOLVED] Combining results from 2 arrays

[padams]

I've got a query that retrieves data and groups it, then counts how many entries fall into each group.

 

$tries_sql = "SELECT playerID, COUNT(playerID) FROM tries GROUP BY playerID";

$tries_query = mysql_query($tries_sql) or die(mysql_error());

 

However, I need to put this info in a table where the players' names appear next to the number of tries they have scored. The players' names are in another table called 'players', and their names appear in two columns, called firstName and lastName. 'playerID' is the primary key in the players table, and I've put a column with the same name in the tries table.

 

Is there any way that I can gather the results I have retrieved with the above query, then print them out next to the correct player's names?

[redarrow]

join mate.

[padams]

I understand how to use join to join up within one array, but how would I join between two arrays? And, how do I make sure I am joining the correct elements?

 

Should I run two separate queries, one to get the try data, and one to get the player names, or try to incorporate both queries into one?

a.

$tries_sql = "SELECT playerID, COUNT(playerID) FROM tries GROUP BY playerID";

$tries_query = mysql_query($tries_sql) or die(mysql_error());

$players_sql = "SELECT playerID, playerFirstName, playerLastName FROM players";

$players_query = mysql_query($players_sql) or die(mysql_error());

and then try to join the results up.

 

OR

 

b.

$tries_sql = "SELECT playerID, COUNT(playerID) FROM tries GROUP BY playerID";  <---find some way to also select playerID, playerFirstName and playerLastName from the players table in this query.

$tries_query = mysql_query($tries_sql) or die(mysql_error());

[php_dave]

Is this what you mean?

 

select p.PlayerFirstName, p.playerLastname, count(t.playerID)

from players p, tries t

where p.playerid = t.playerid

group by p.PlayerFirstName, p.playerLastname

 

 

edit - bug!! Squashed!

[redarrow]

Try this please as a test always backup your current code.

 

tell me what happens intresting might work.

<?php
$sql = "SELECT playerID.tries, playersFiirstname.players,
playersLastname.players COUNT(playerID) as num FROM tries,players where 
join players.playersID='$trie.playerID'";
$result = mysql_query($sql) or die(mysql_error());

while($rec=mysql_fetch_assoc($result)){

echo " Name: ".$rec['playersFirstname']." <br> Surname: ".$rec['playersLastname']." <br>Score: $rec['num'] ";
}
?>

 

[Barand]

Nice one, redarrow. You've managed to take the previous poster's code and introduce about eight errors into it.

[redarrow]

well can you correct my code so i no where i went wrong please cheers.

 

[redarrow]

is that better

 

if it wrong then please correct it, as i am learning aswell thank you anyone cheers.

<?php
$sql = "SELECT tries.playerID, players.playersFiirstname,
players.playersLastname COUNT(tries.playerID) as num FROM tries,players where 
join players.playersID='$trie.playerID'";
$result = mysql_query($sql) or die(mysql_error());

while($rec=mysql_fetch_assoc($result)){

echo " Name: ".$rec['playersFirstname']." <br> Surname: ".$rec['playersLastname']." <br>Score: $rec['num'] ";
}
?>

[Barand]

1 ) Needs comma before COUNT

2 ) Don't put ' around column names ( as in '$tries.playerID') and it doesn't want the "$" either

 

3 ) join syntax should be either

"FROM tries,players WHERE players.playersID = tries.playerID" 

or

FROM tries JOIN players ON players.playersID = tries.playerID

 

4 ) if you use an aggregation function, such as count() in conjunction with other columns in the SELECT clause, you need a GROUP BY clause

 

$sql = "SELECT tries.playerID, players.playerFirstname,
            players.playerLastname, COUNT(tries.playerID) as num 
        FROM tries 
            JOIN players ON players.playersID = tries.playerID 
        GROUP BY tries.playerID";

[redarrow]

ok thank you going to study that now cheers.

[padams]

This is getting way above my level, but I've tried what you suggested and got the following error:

 

You have an error in your SQL syntax near 'ON players.playerID = tries.playerID GROUP BY tries.playerID'

[BlueSkyIS]

you may need to reverse these:

 

ON players.playerID = tries.playerID

 

becomes

 

ON tries.playerID = players.playerID

 

I have noticed MySQL seems pickier on JOIN orders lately.

[padams]

Same error unfortunately, just reflects the change you suggested.

 

You have an error in your SQL syntax near 'ON tries.playerID = players.playerID GROUP BY tries.playerID'

 

Do I have to do this all in one query or could I do 2 separate queries and them merge them using PHP?

[Barand]

What do you get if you echo the query string that you are passing to mysql_query();

[padams]

SELECT tries.playerID, players.playerFirstname, players.playerLastname, COUNT(tries.playerID) as num FROM tries JOIN players ON tries.playerID = players.playerID GROUP BY tries.playerID

[Barand]

Hmm, I set up players and tries tables and that runs OK

[padams]

When it works for you, do the playerID and the group match up in the joined data? I need to make sure that I get the correct name next to the number of tries.

 

This is the code I have, anything I may have done wrong?

 

$sql = "SELECT tries.playerID, players.playerFirstname,

            players.playerLastname, COUNT(tries.playerID) as num

        FROM tries

            JOIN players ON tries.playerID = players.playerID

        GROUP BY tries.playerID";

echo $sql;

$sql_query = mysql_query($sql) or die(mysql_error());

[Barand]

Tables

CREATE TABLE `players` (
  `playerID` int(10) unsigned NOT NULL auto_increment,
  `playerFirstname` varchar(45) NOT NULL,
  `playerLastname` varchar(45) NOT NULL,
  PRIMARY KEY  (`playerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tries` (
  `idtries` int(10) unsigned NOT NULL auto_increment,
  `playerID` int(10) unsigned NOT NULL,
  `tstamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`idtries`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Data

playerID, playerFirstname, playerLastname
1       , 'aaa'          , 'bbbbbbbb'
2       , 'ccc'          , 'dddddddd'
3       , 'eee'          , 'ggggggg'

idtries, playerID, tstamp
1      , 1       , '2007-09-24 09:54:37'
2      , 2       , '2007-09-24 09:54:37'
3      , 1       , '2007-09-24 09:54:37'
4      , 2       , '2007-09-24 09:54:37'
5      , 3       , '2007-09-24 09:54:37'
6      , 1       , '2007-09-24 09:54:37'
7      , 2       , '2007-09-24 09:54:37'
8      , 1       , '2007-09-24 09:54:37'
9      , 2       , '2007-09-24 09:54:37'
10     , 3       , '2007-09-24 09:54:37'
11     , 1       , '2007-09-24 09:54:37'
12     , 2       , '2007-09-24 09:54:37'
13     , 1       , '2007-09-24 09:54:37'
14     , 2       , '2007-09-24 09:54:37'
15     , 3       , '2007-09-24 09:54:37'

 

Results

playerID  playerFirstname  playerLastname  num
--------+----------------+---------------+------
   1          aaa            bbbbbbbb        6
   2          ccc            dddddddd        6
   3          eee            ggggggg         3

[padams]

How frustrating, I tried to do exactly as you showed but still got the same error. I had to drop the line ENGINE=MyISAM DEFAULT CHARSET=latin1; - does that affect it at all? Couldn't create the tables while that was there.

 

Thanks for the help, I've no idea why this wouldn't be working if the same stuff works perfectly for you.

[padams]

I set up the site on another server running a different version of mysql (5.0.18) and your solution works. Unfortunately this is a local network server only so I can't use it for the live site. The server hosting our site is running version 3.23.49 (or at least that's the number on the main page), would that be causing the problems?

 

Additionally, how do I output the results of the query? I've tried the following code but it doesn't output names or even the first players try count, only the 2nd and 3rd players counts. How exactly are the query results outputted?

Query is:

$sql = "SELECT tries2.playerID, players2.playerFirstname, players2.playerLastname, COUNT(tries2.playerID) as num FROM tries2

JOIN players2 ON tries2.playerID = players2.playerID GROUP BY tries2.playerID";

echo $sql;

$sql_query = mysql_query($sql) or die(mysql_error());

$rsSql = mysql_fetch_assoc($sql_query);

 

I've tried to output results using:

<?php do { ?>

  <?php echo $rsSQL['playerFirstName']; ?> <?php echo $rsSQL['playerLastName']; ?> scored <?php echo $rsSQL['num']; ?> tries<br />

      <?php } while ($rsSQL = mysql_fetch_assoc($sql_query)) ?>

[Barand]

Your output problem seems to be case-sensitive variable names

 

$rsSql = mysql_fetch_assoc($sql_query);

 

<?php echo $rsSQL['playerFirstName']; ?>

 

As far as I know, that query contains nothing that is v5.0 only.

[padams]

Doh! Very obvious. That has cleared up the problem of showing every players try count, however it still doesn't output their names.

[Barand]

Same problem

 

playerFirstname and playerFirstName

[padams]

Man, I'm getting too close to the problem, keep making stupid mistakes. Thanks for pointing that out, it is outputting the results now.

 

However, I still can't get the same stuff to work on my live server. I've created the tables, although I had to change the sql statement from ENGINE=MyISAM to TYPE=MyISAM and dropped the DEFAULT CHARSET stuff in order to get it to work. I put data in the tables and ran the query, but get this error:

You have an error in your SQL syntax near 'ON tries2.playerID = players2.playerID GROUP BY tries2.playerID LIMIT 0, 30' at line 2

 

 

[Barand]

Grasping at straws - does "INNER JOIN" instead of just "JOIN" make any difference?