mySQL count function

A

Anonymous

Guest
I was reading about the count function at this web site.

http://www.webdevelopersnotes.com/tutorials/sql/20.php3

I have a list of books and the date that I read them. I would like to give a summary of how many books, I read for each year.

Here is the PHP code that I wrote. It never gets past the query, stating "Couldn't execute Query." What am I doing wrong?

<?php

$user="cheng_rob";
$host="localhost";
$user="chengrob_rob";
$password="";
$database = "chengrob_book";
$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db = mysql_select_db($database)
or die ("Couldn't select database");
$query = "SELECT Year(Rob), COUNT(*) as Number FROM book;
$result = mysql_query($query)
or die ("Couldn't execute query.");

/* Display results in a table */
echo "<center><h1>Results</h1></center>";
echo "<center><table cellPadding=2>";
echo "<tr><td><b>Year</b></td><td><b>Books Count</b></td></tr>";
while ($row = mysql_fetch_array($result))
{
extract($row);
echo "<tr><td>$Rob</td>";
echo "<td>$Number</td>";
}
echo "</table>\n";
?>
 
This is you problem: Year(Rob)!
MySQL YEAR(date) function, returns the year for date, in the range 1000 to 9999.
Example:
Code: 
mysql> SELECT YEAR('04-21-05');  // Output: 2004
See more here!

Ok, i don´t know your tables field and it types.
In case of a Distinct period data retrieving, i suggest you MySQL DISTINCT() function!

Example:
Code: 
// Let´s say this are your field/value par in your table:
name  -   date
------------------
John   -    2001
Mary   -   2002
Mary   -   2003
John   -   1980
John   -   2004
John   -   2001
------------------

// And the query:   
mysql> SELECT DISTINCT date, COUNT(*) FROM table WHERE name='John' ORDER BY date;

// Outputs:
1980  - 1
2001  - 1
2001  - 2
 
I tried this again with no luck. Here is what the code that I got. I still get "Couldn't Execute Query". What do you think that I am doing wrong? I created a new field in the database called Ano which is a 4 digit integer to represent the year same as your example.

<?php
/* Program: Year.php
* Desc: Displays a count by year
*/
?>
<html>
<head><title>Books Read by Year</title></head>
<body>
<?php

$user="cheng_rob";
$host="localhost";
$user="chengrob_rob";
$password="";
$database = "chengrob_book";
$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db = mysql_select_db($database)
or die ("Couldn't select database");
$query = "SELECT DISTINCT Ano, COUNT(*) FROM book";
$result = mysql_query($query)
or die ("Couldn't execute query.");

/* Display results in a table */
echo "<center><h1>$Result</h1></center>";
echo "<center><table cellPadding=2>";
echo "<tr><td><b>Year</b></td><td><b>Books Count</b></td></tr>";
while ($row = mysql_fetch_array($result))
{
extract($row);
echo "<tr><td>$Ano</td>";
echo "<td>$Numero</td>";
}
echo "</table>\n";
?>
</body></html>
 
Ah ok! Sorry!
You need to use GROUP BY function with COUNT() here!

So, try:
Code: 
mysql> SELECT DISTINCT Ano, COUNT(*) AS num FROM book GROUP BY Ano ORDER BY num

This will work!
Btw: You have a bug here:
Code: 
$user="cheng_rob"; 
$host="localhost"; 
$user="chengrob_rob";

// $user twice !!

Do you speak portuguese!?
 
Thank you very much. I figured out that I was missing the GROUP by statement. It is now working correctly.

Sim, eu posso falar português.
 
I´m glad you made it :-D

Private:
Portuguese:
Ok! Se precisar de algo, já sabe... escreva-me via email ou pm! Cumprimentos,
gesf

English:
Ok! If you ever need something, just email me or pm me!
Regards,
gesf
 
You must log in or register to reply here.
Back
Top