Query statistics

[mrt003003]

Hi there if you could point me in the right direction that would be ace.

 

I have a query that selects Ship records which includes fields such as Class and ShipName, :

 

<?php $colname_Ships = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Ships = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_swb, $swb);
$query_Ships = sprintf("SELECT * FROM ship WHERE PlayerName = %s", GetSQLValueString($colname_Ships, "text"));
$Ships = mysql_query($query_Ships, $swb) or die(mysql_error());
$row_Ships = mysql_fetch_assoc($Ships);
$totalRows_Ships = mysql_num_rows($Ships);?>

 

I want to be able to output the amount of Ships that have the same ShipName and Class.

 

For example: ShipName1 - 10, ShipName2 - 0, ShipName3 - 2 etc..

                    Class 2 - 0, Class3 - 3, Class 4 - 2.

 

The only way I can think it can be done would be by creating seperate queries for each ShipName and Class:

<?php $colname_Ships = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Ships = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_swb, $swb);
$query_Ships = sprintf("SELECT * FROM ship WHERE ShipName = 'ShipName1' AND PlayerName = %s", GetSQLValueString($colname_Ships, "text"));
$Ships = mysql_query($query_Ships, $swb) or die(mysql_error());
$row_Ships = mysql_fetch_assoc($Ships);
$totalRows_Ships = mysql_num_rows($Ships);?>

<?php $colname_Ships = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Ships = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_swb, $swb);
$query_Ships = sprintf("SELECT * FROM ship WHERE ShipName = 'ShipName2' ANDPlayerName = %s", GetSQLValueString($colname_Ships, "text"));
$Ships = mysql_query($query_Ships, $swb) or die(mysql_error());
$row_Ships = mysql_fetch_assoc($Ships);
$totalRows_Ships = mysql_num_rows($Ships);?>

 

etc...

 

This seems very wasteful so is there away i can do this using a single query.??

 

Thanks

 

 

[mikosiko]

COUNT() and GROUP BY

[mrt003003]

Thanks for the reply, wouldnt using COUNT() simply count the number ?? If i test it with:

$query_Ships = sprintf("SELECT COUNT(ShipName), COUNT(Class) FROM ship WHERE PlayerName = %s", GetSQLValueString($colname_Ships, "text"));

 

Then my outputted results are: ShipName = 14, Class = 14

 

I need to know how many occurances of Ships there are with the same name. The same applies for Class as it says there are 14 records counted. I need to know how many of them are either Class 1, 2, 3, or 4 out of the 14 counted records. Perhaps im confused with the count function?

 

How ever if i used:

SELECT COUNT(ShipName), ShipName FROM ship WHERE PlayerName = 'Empire' AND Template='0' GROUP BY ShipName[code/]

Its gives the the count of the ships and the ship name! I can write another for Class independantly. 

Thanks 

[mikosiko]

COUNT (or any aggregation function) AND GROUP BY work together... please read the info in the provided links

 

SELECT shipname, COUNT(shipname) FROM ship GROUP BY shipname;

 

 

SELECT class, COUNT(class) FROM ship GROUP BY shipname;