Help with Mysql syntax

[Wilson]

Sorry if this is terribly basic.

Writing code such as this is easy

$sql2 = 'SELECT COUNT( DISTINCT `Author` ) FROM `books` LIMIT 0, 100';

but the $64 question is, whats next to make the result show up on a web page? I have this code, please refer to the above code at the bottom of the page

<?
$dbh=mysql_connect ("localhost", "XXXX", "XXXXX") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("lunar_effort", $dbh);
$sql = ("INSERT INTO `books`(`Book`, `Year`, `Author`, `Genre`, `Number`, `Series`, `Complete`,`Recommendation`, `Status`, `Key`)
VALUES ('$_POST[Book]', '$_POST[Year]', '$_POST[Author]', '$_POST[Genre]', '$_POST[Number]', '$_POST[Series]', '$_POST[Complete]', '$_POST[Recommendation]', '$_POST[Status]', NULL)");
if (!mysql_query($sql,$dbh))
  {
  die('Error: ' . mysql_error());
}
echo $_POST ["Book"],"<br />";
echo $_POST ["Year"],"<br />";
echo $_POST ["Author"],"<br />";
echo $_POST ["Genre"],"<br />";
echo $_POST ["Number"],"<br />";
echo $_POST ["Series"],"<br />";
echo $_POST ["Complete"],"<br />";
echo $_POST ["Recommendation"],"<br />";
echo $_POST ["Status"],"<br />";
echo "1 record added","<br />";
echo "The number of records is ";
$sql1 = "SELECT * FROM books";
$result = mysql_query($sql1,$dbh);
echo mysql_num_rows($result);
 echo "<br />";
echo "The number of Authors is ";
$sql2 = 'SELECT COUNT( DISTINCT `Author` ) FROM `books` LIMIT 0, 100';
$result = mysql_query($sql2,$dbh);
echo mysql_num_fields($result);
 mysql_close($dbh);
?>

Everything works perfectly till I get to this code

echo "The number of Authors is ";
$sql2 = 'SELECT COUNT( DISTINCT `Author` ) FROM `books` LIMIT 0, 100';
$result = mysql_query($sql2,$dbh);
echo mysql_num_fields($result);

At the $result part, I have no clue what I am doing to make it appear when called. Can anyone point at a tutorial that isnt just about how to write the Select Count part and how to echo the results?
(or help with the code as to what should be there?)

[Xnuiem]

ok, first your query is wrong.

$sql2 = 'SELECT COUNT( DISTINCT `Author` ) FROM `books` LIMIT 0, 100';
should be
$sql2 = "SELECT COUNT(DISTINCT(`Author`)) FROM `books`";

No point in using a LIMIT clause when only a single row is returned due to the select clause.

Next, you need to go and read the documentation on the MySQL functions, the example you gave isnt even close. I understand how you got there, but make sure to read the docs.

Here is the correct way to do it:

$sql2 = "SELECT COUNT(DISTINCT(`Author`)) FROM `books`";
$result = mysql_query($sql2); <-- Dont need the DB connection unless you have more than one.
$count = mysql_fetch_row($result);
print("The number of Authors is " . $count); <-- echo is fine too, I just the implicit print, a throwback to Jython/JAVA/Perl

[Wilson]

Thanks, I knew I was no wheres close to being right. Thats what i get for relying on MyPhpAdmin for scripts!

I used your example and the result was "The number of Authors is array"

So I did a little browsing and changed your example very very slightly
Original

print("The number of Authors is " . $count);

New

print("The number of Authors is " . $count[0]);

Dont really understand why it worked, but it did.

Unfortunately, 99% of the mysql tutorials give you mysql> type information. But I guess I should be looking in the PHP tutorials to find the information I need as far as expressing the results of queries in HTML pages

Thank you much for your help, is much appreciated.

[Xnuiem]

oops.

You can also try:

list($count) = mysql_fetch_row($result);

which will make $count work without messing with it as an array.

[Wilson]

Thanks again, I have a feeling your oops are much fewer than mine!

[Xnuiem]

we all start somewhere. Glad to help.