List:General Discussion« Previous MessageNext Message »
From:John Hicks Date:April 29 2006 5:52am
Subject:Re: Matching fields from two different tables
View as plain text  
-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);
?>

Thread
Matching fields from two different tables-Patrick29 Apr
  • Re: Matching fields from two different tablesJohn Hicks29 Apr
    • Re: Matching fields from two different tables-Patrick29 Apr
      • Re: Matching fields from two different tablesJohn Hicks29 Apr