MySQL Count Question...

[rackemup420]

First of all let me say im a somewhat noob lol 😃. been doin it for about 2 months now, but anyways i was trying to get the count of certain rows under a certain category, after searching for hours like a noob i cant find the right answer... maybe u guys can help... here is a snippet of my code where i want to get it to work:

<div id="systems" class="tabcontent">
<ul>
<?php
$console[1]= mysql_query("SELECT COUNT(*) FROM games WHERE system='PS3'");
?>
<?php
$id = $_GET['id'];

$result = mysql_query("SELECT * FROM systems ORDER BY title ASC");

while($row = mysql_fetch_array($result))
  {
  echo "<li><a href='./console.php?system=" . $row['sys'] . "'>" . $row['title'] . "</a> (" . $console["" . $row['id'] . ""] . " Games)</li>";
  }

?>
</ul>
</div>

Ok so what i want to do exactly is pull out "console" titles such as PS3 and Xbox360 from my "systems" table, and next to that name i want to display how many games reside under that specific system in my "games" table.

so it looks like:

Playstation 3 (11 Games)

[laserlight]

You may be able to do something like this:

<?php
$result = mysql_query("SELECT systems.*, COUNT(games.system) AS game_count
    FROM systems LEFT JOIN games ON systems.sys=games.system
    GROUP BY games.system
    ORDER BY systems.title ASC");

echo "<ul>\n";
while ($row = mysql_fetch_assoc($result))
{
    printf('    <li><a href="./console.php?system=%s">%s</a> (%d Games)</li>',
        urlencode($row['sys']),
        htmlspecialchars($row['title']),
        $row['game_count']);
}
echo "</ul>\n";

[rackemup420]

Works Like A Charm!!! Thank You... only problem im thinking im still seeing is not all rows are being pulled from the systems table, it only pulled about 5/15 systems and displayed them.

[rackemup420]

oops sorry bout that didnt see it doesnt pull out records that are at 0, once i added a game under that system it displayed correctly. although i would like to display all categories and counts 😃

[laserlight]

rackemup420 wrote:

only problem im thinking im still seeing is not all rows are being pulled from the systems table, it only pulled about 5/15 systems and displayed them.

Check that you really do have 15 rows in the systems table. If you do, check that you wrote LEFT JOIN instead of just JOIN, since an inner join would leave out all those systems that have no games, whereas a left join would include them.

[rackemup420]

<div id="systems" class="tabcontent">
<?php
$result = mysql_query("SELECT systems.*, COUNT(games.system) AS game_count FROM systems LEFT JOIN games ON systems.sys=games.system GROUP BY games.system ORDER BY systems.title ASC");
echo "<ul>\n";
while ($row = mysql_fetch_assoc($result))
{
    printf('    <li><a href="./console.php?system=%s">%s</a> (%d Games)</li>',
        urlencode($row['sys']),
        htmlspecialchars($row['title']),
        $row['game_count']);
}
echo "</ul>\n"; 
?>
</div>

[laserlight]

That looks correct to me. What is your relevant database schema?

[rackemup420]

hehe noob comin out 😃. i believe its utf8... not sure though how do i check?!? anyway to check in navicat?

[rackemup420]

found it and it is:

Character Set: utf8 -- UTF-8 Unicode
Collation: utf8_general_ci

[laserlight]

I actually wanted you to state the details of the tables involved. The character set and collation might be relevant, but with a left join even if nothing was matched the systems rows will still be displayed.

I tested on SQLite, so maybe my statement syntax is not doing what I expect it to on MySQL, even if SQLite confirms it as correct.

[rackemup420]

see told ya noob comin out lol...ok lemme lay it out for ya

systems Table:
id, sys, title, img

games Table:
id, title, system

need to grab system titles from the systems table and display how many games are relevant to taht system from the games table.

[laserlight]

It would be easier if you gave the exact CREATE TABLE statements. Also, give a couple of INSERT statements. I am going to test.

[rackemup420]

Games Table:

DROP TABLE IF EXISTS `games`;
CREATE TABLE `games` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `system` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

INSERT INTO `games` VALUES ('1', 'Dantes Inferno', 'PS3');
INSERT INTO `games` VALUES ('2', 'Assassins Creed', 'PS3');
INSERT INTO `games` VALUES ('3', 'Dragon Age Origins', 'PS3');

Systems Table:

DROP TABLE IF EXISTS `systems`;
CREATE TABLE `systems` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `sys` varchar(10) DEFAULT NULL,
  `title` varchar(20) DEFAULT NULL,
  `img` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

INSERT INTO `systems` VALUES ('1', 'PS3', 'Playstation 3', 'ps3.jpg');
INSERT INTO `systems` VALUES ('2', 'Xbox360', 'Xbox 360', 'xbox360.jpg');
INSERT INTO `systems` VALUES ('3', 'PS2', 'Playstation 2', 'ps2.jpg');

sorry 😃 you are the best!!!

[laserlight]

Okay, the correct query to use should be:

SELECT systems.*, COUNT(games.system) AS game_count FROM systems LEFT JOIN games ON systems.sys=games.system GROUP BY system.sys ORDER BY systems.title ASC

[rackemup420]

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in E:\xampp\htdocs\index.php on line 117

Line 117:

<div id="systems" class="tabcontent">
<?php
$result = mysql_query("SELECT systems.*, COUNT(games.system) AS game_count FROM systems LEFT JOIN games ON systems.sys=games.system GROUP BY system.sys ORDER BY systems.title ASC");
echo "<ul>\n";
while ($row = mysql_fetch_assoc($result))
{
    printf('    <li><a href="./console.php?system=%s">%s</a> (%d Games)</li>',
        urlencode($row['sys']),
        htmlspecialchars($row['title']),
        $row['game_count']);
}
echo "</ul>\n"; 
?>
</div>

[laserlight]

Oh, typo error. Take a look at what I wrote and you should be able to see why 🙂

[rackemup420]

lol, i give up. it looks good to me... i dont see any mispelling although i did just wake up not too long ago 😃

[rackemup420]

BAM!!! You rock!!! mispelled systems 😃 ftw. mad <3

[laserlight]

Heheh, and remember to mark this thread as resolved using the thread tools 🙂