-Patrick wrote:
John Hicks wrote:
-Patrick wrote:
Folks, I could really use your assistance.
Take a look here: http://pastebin.com/687889
How can I manipulate totalRows_numberComments so that I get the number
of blg_comment_com.idart_com PER blg_article_art.id_art? Right now, it
produces every blg_comment_com.idart_com in existence, definately not
cool.
Thanks
-Patrick
You had:
SELECT COUNT(*) FROM blg_comment_com, blg_article_art WHERE
blg_article_art.id_art=blg_comment_com.idart_com
Try this:
SELECT blg_article_art.id_art, COUNT(*) as NumberOfComments
FROM blg_comment_com, blg_article_art
WHERE blg_article_art.id_art = blg_comment_com.idart_com
group by blg_article_art.id_art
Try it in the mysql console before you try to plug it into php.
(You can only learn so much at one time :)
--John
> That is fantastic John. Outputs the id along with respective count.
>
> However, it leads back to my original question... how do I coordinate
> the output from this query with php?
> I know php fairly well, but when
> mixes with mysql
> Im still new and I only want to call those particular
> values and have them respond appropriately.
> What do you suggest?
The wonderful thing about a computer is that you can make it do just
about anything that you want it to do.
But first you have to decide just what you want it to do.
You've seen the output from your SQL statement. Is there something you
would like to do with that? Do you want to print it out? Sort it? Select
from it?
No, no. Don't tell me. Let me see if I can read your mind. You are
writing your own blog software and you want to display the number of
comments following each post?
If that's the case you will want to select the post articles themselves
... and you might as well select the comment count at the same time.
So you can start with the same SQL statement and tweak it by just adding
a word or two. I'll let you figure that out.
But it looks like you have never done a query through PHP before, so
here is a quick template that should print out the above query results:
$Conn = mysql_pconnect("localhost", "MyUserName", "MyPassword")
or die("Unable to connect to database");
$Db = mysql_select_db("MyDatabaseName", $Conn)
or die("Unable to select database");
$Sql = " SELECT blg_article_art.id_art, COUNT(*) as NumberOfComments
FROM blg_comment_com, blg_article_art
WHERE blg_article_art.id_art = blg_comment_com.idart_com
group by blg_article_art.id_art ";
$Result = mysql_query($Sql, $Conn)
or die("Query failed with error " . mysql_error());
echo "<html><head></head><body>\n";
echo "<table><tr><th>Article ID</th><th>Count</th></tr>\n";
while ($Record = mysql_fetch_object($Result)) {
echo "<tr>
<td>$Record->id_art</td>
<td>$Record->NumberOfComments</td>
</tr>
";
}
echo "</table></body></html>";
Here's another example, this one taken from the PHP manual page on mysql
functions (http://us3.php.net/manual/en/ref.mysql.php):
Example 1. MySQL extension overview example
<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
