[SOLVED] Mysql Count

[dropfaith]

My Code is below im having issues with counting the amount of records per row

http://www.lawrenceguide.org/literature/genre.php theres currently two blogs in the database  i want to display that on this page

Blogs (displays number of stories on db) 

Adventure (displays number of stories on db)

 

<?php
			// includes
			include("../template/conf.php");
			// open database connection
			$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
			// select database
			mysql_select_db($db) or die ("Unable to select database!");
			// generate and execute query
			$query = "SELECT * FROM story";
			$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
			// if records present
			if (mysql_num_rows($result) > 0)
			{
			// iterate through resultset
			// print article titles
			while($row = mysql_fetch_object($result))
			{
			?><tr>
		<td><a href="bygenre.php?Genre=<? echo $row->Genre; ?>"><? echo $row->Genre; ?>
</a></td>
</tr>
			<?
			}
			}
			// if no records present
			// display message
else
{
			?>
			<li>No users</li>
			<?php
			}
			// close database connection
mysql_close($connection);
			?>

[Andy-H]

try

<?php
include("../template/conf.php");

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

mysql_select_db($db) or die ("Unable to select database!");


$query = "SELECT Genre FROM story";

$result = mysql_query($query)or trigger_error('Error in query: ' . $query . '<br>On line: ' . __LINE__ . '<br>Error: ' . mysql_error());

$num = mysql_numrows($result);

if ($num == 0){
?>

<li>No users found.</li>

<?php

}else{

while ( $row = mysql_fetch_row($result) ){
?>
<tr>
<td><a href="bygenre.php?Genre=<?php echo $row[0]; ?>"><?php echo $row[0]; ?></a></td>
</tr>
<?php
}}

mysql_close($connection);
?>

[Andy-H]

Also I did a W3 validation and got this - Errors found while checking this document as XHTML 1.0 Strict!Result: 14 Errors

[dropfaith]

http://www.lawrenceguide.org/literature/genre.php nope it didnt error but it didnt do what i need it just looped that there was two blogs

 

there check the link i set it up as a diplay using html hopefully that makes sense

 

the code you gave did

 

blogs

blogs (as theres two blogs in the db)

[Andy-H]

What was the error?

[dropfaith]

it didnt have a php error

 

i fixed the html validation

 

http://www.lawrenceguide.org/literature/genre.php is h0w i want it displayed done with html so it isnt currently dynamic

 

the code i tried that you gave me displayed  as if it looped throught the db and pulled them all out but it didnt give a count of the records from each type

 

blogs

blogs

 

 

[Andy-H]

kk which table holds the data from each genre? You would need to run a query inside the loop for each genre, run a mysql_numrows function on it and display it in brackets

[dropfaith]

i know this requires something with

 

the function count but im clueless as to using it

story holds all storys and a varchar named Genre

 

 

Table structure for table `story`

--

 

CREATE TABLE `story` (

  `Id` int(5) NOT NULL auto_increment,

  `Title` varchar(150) NOT NULL,

  `Article` text NOT NULL,

  `Genre` varchar(100) NOT NULL,

  `Author` varchar(100) NOT NULL,

  `loginid` varchar(5) NOT NULL,

  `pubDate` timestamp NOT NULL default CURRENT_TIMESTAMP,

  `Date` varchar(250) NOT NULL,

  PRIMARY KEY  (`Id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

[Andy-H]

add me on msn if you have it, then you can explain what your trying to to do properly,

 

[email protected]

[dropfaith]

i dont have msn im at work(not doing my job) im making a side project

 

but

 

say theres 2 blogs and 39 storys under songs

 

i want it to display

 

 

Blogs (2)

Songs (39)

 

from the php i know it needs to pull all from the db and then loop the different genres  and count the amount of records that belong in each genre and display that number

 

[dropfaith]

i know its this that i need but i cant get it working

 

http://www.plus2net.com/sql_tutorial/sql_group_by.php

[Andy-H]

<?php

$genres = array("Blogs", "etc", "etc");

$n = count($genres);

for ($i = 0; $i < $n; $i++):


$genre = $genres[$i];

$query  = "SELECT id FROM story WHERE Genre='$genre' ORDER BY id DESC";
$result = mysql_query($query);

$num    = mysql_num_rows($result);

echo '<tr><td><a href="bygenre.php?Genre=' . $genre . '">' . $genre . '</a> ( ' . $num . ' )</td></tr>';


Endfor;

?>

[dropfaith]

awesome that works and validates still now i just gotta find how how to use php to populate that array

 

$genres = array("Blogs", "etc", "etc"); so i wont need to get in and edit things each time

[dropfaith]

below is the current genre list but users can submit new genres from the add story form

 

 

CREATE TABLE `genre` (

  `Genre` text NOT NULL,

  `Gid` int(5) NOT NULL auto_increment,

  `loginid` varchar(5) NOT NULL,

  PRIMARY KEY  (`Gid`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

 

--

-- Dumping data for table `genre`

--

 

INSERT INTO `genre` (`Genre`, `Gid`, `loginid`) VALUES

('Adventure', 1, ''),

('Autobiography', 2, ''),

('Fantasy', 3, ''),

('Fiction', 4, ''),

('Mystery', 5, ''),

('Non-fiction', 6, ''),

('Horror', 7, ''),

('Blogs', 8, ''),

('Drama', 9, ''),

('Crime', 10, ''),

('Humor', 11, ''),

('Philosophy', 12, ''),

('Religion', 13, ''),

('Social Science', 14, ''),

('The Arts and Recreation', 15, ''),

('Poetry, Plays, Literature', 16, ''),

('Geography and History', 17, ''),

('Travel', 18, ''),

('Songs', 19, ''),

('Plays', 20, '');

[Andy-H]

<?php

$genres = array();

$q = "SELECT Genre FROM genre";
$r = mysql_query($r);

while ( $row = mysql_fetch_row($r) ):

$add = $row[0];

if ( !in_array($genres, $add) ):

$genres[] = $add;

Endif;

Endwhile;

$n = count($genres);

for ($i = 0; $i < $n; $i++):


$genre = $genres[$i];

$query  = "SELECT id FROM story WHERE Genre='$genre' ORDER BY id DESC";
$result = mysql_query($query);

$num    = mysql_num_rows($result);

echo '<tr><td><a href="bygenre.php?Genre=' . $genre . '">' . $genre . '</a> ( ' . $num . ' )</td></tr>';


Endfor;

?>

 

Sorry about long reply ma m8 was on computer

[Andy-H]

Saw the error, my bad lol

 

$r = mysql_query($q);

 

if ( !in_array($add, $genres) ){

[dropfaith]

Warning: in_array() [function.in-array]: Wrong datatype for second argument in /home/www/lawrenceguide.org/www/literature/genre.php on line 61

 

??

 

i fixed the error i noticed it too hah

[Andy-H]

if ( !in_array($add, $genres) ){

 

lol

 

BTW the error there was the parenthesis

 

if ( !in_array($add, $genres) ):

[Barand]

$res = mysql_query("SELECT genre, COUNT(*) as total 
                          FROM story
                          GROUP BY genre");
while ($row = mysql_fetch_row($res))
{
    vprintf('%s (%d)<br/>', $row);
}

[Andy-H]

$res = mysql_query("SELECT genre, COUNT(*) as total 
                          FROM story
                          GROUP BY genre");
while ($row = mysql_fetch_row($res))
{
    vprintf('%s (%d)<br/>', $row);
}

 

omg where were you 2 hours ago lol

[Andy-H]

<?php

$genres = array();

$q = "SELECT Genre FROM genre";
$r = mysql_query($q);

while ( $row = mysql_fetch_row($r) ):

$add = $row[0];

if ( !in_array($add, $genres) ):

$genres[] = $add;

Endif;

Endwhile;

$n = count($genres);

for ($i = 0; $i < $n; $i++):


$genre = $genres[$i];

$query  = "SELECT id FROM story WHERE Genre='$genre' ORDER BY id DESC";
$result = mysql_query($query);

$num    = mysql_num_rows($result);

echo '<tr><td><a href="bygenre.php?Genre=' . $genre . '">' . $genre . '</a> ( ' . $num . ' )</td></tr>';


Endfor;

?>

 

 

Felt like I had to fix it to save further embarasment lol

[Barand]

10/10 for effort

[Andy-H]

lol thanks