[SOLVED] Mysql Count

dropfaith · October 4, 2008

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 · October 4, 2008

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 · October 4, 2008

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

dropfaith · October 4, 2008

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 · October 4, 2008

What was the error?

dropfaith · October 4, 2008

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

Andy-H · October 4, 2008

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 · October 4, 2008

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 · October 4, 2008

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

andy-holland@live.co.uk

dropfaith · October 4, 2008

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 · October 4, 2008

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 · October 4, 2008

<?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 · October 4, 2008

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 · October 4, 2008

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 · October 4, 2008

<?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 · October 4, 2008

Saw the error, my bad lol

$r = mysql_query($q);

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

dropfaith · October 4, 2008

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 · October 4, 2008

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

lol

BTW the error there was the parenthesis

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

Barand · October 4, 2008

$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 · October 4, 2008

$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 · October 4, 2008

<?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 · October 4, 2008

10/10 for effort

Andy-H · October 4, 2008

lol thanks

Sign In

[SOLVED] Mysql Count

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information