FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


SQL query problem





Jamatu
I have 2 queries

Query 1
Code:
$query = "SELECT news.*, users.username
FROM news, users
WHERE news.news_posterid = users.id
ORDER BY news_id DESC
LIMIT 0,5";


Query 2
Code:
   $query = "SELECT COUNT(ncomment_newsid) AS num_coms
   FROM news_comments
   WHERE ncomment_newsid = $row[news_id]";


Here they are placed in my while loop
Code:
$query = "SELECT news.*, users.username
FROM news, users
WHERE news.news_posterid = users.id
ORDER BY news_id DESC
LIMIT 0,5";

$result=mysql_query($query) or die ( mysql_error( ) );
$numqueries++;
while($row = mysql_fetch_array($result))
{
   $query = "SELECT COUNT(ncomment_newsid) AS num_coms
   FROM news_comments
   WHERE ncomment_newsid = $row[news_id]";
   $numcom=mysql_query($query) or die ( mysql_error( ) );
   $com=mysql_fetch_array($numcom);
?>
         <span class="title"><? echo $row[news_title]; ?></span> - <span class="tiny"><a href="newsarchive.php?aid=<? echo $row[news_id]; ?>"><? echo $com[num_coms]; ?> Comments</a></span><br />
         <span class="tiny">Posted by <a href="./members.php?uid=<? echo $row[news_posterid]; ?>"><? echo $row[username]; ?></a> on <? echo date('D, M j Y @ H:i:s T', $row[news_date]); ?></span>
         <div class="greymo">
         <table>
            <tr>
               <td><p><? echo nl2br($row[news_text]); ?></p></td>
            </tr>
         </table>
         </div><br />
<?
}


Is their anyway to put both of the queries together instead of repeating the second query for every news article?

Hope someone can help!
jasperlevink
What version of MySQL are you using?
Jamatu
4.1.11

It doesn't matter if the query you give me needs a newer version though as I can easily upgrade. If it does need a newer version don't forget to tell me which! Laughing
Jamatu
This is the best I've come up with so far:
Code:
$query = "SELECT news.*, users.username, COUNT(ncomment_newsid) AS num_coms
FROM news, users, news_comments
WHERE news.news_posterid = users.id
AND news_comments.ncomment_newsid = news.news_id
GROUP BY news_id DESC
ORDER BY news_id DESC
LIMIT 0,5";

The problem with this query though is that it only shows the news articles if it has a comment...

These are the table layouts and how the query table layout should look like

News Table
Code:
+---------+------------+------------+-----------+---------------+
| news_id | news_date  | news_title | news_text | news_posterid |
+---------+------------+------------+-----------+---------------+
|       1 | 1144242032 | Test1      | Testing   |             1 |
|       2 | 1144254060 | Test2      | Testing   |             1 |
|       3 | 1144289523 | Test3      | Testing   |             1 |
+---------+------------+------------+-----------+---------------+

News_comments Table
Code:
+-------------+-----------------+-------------------+---------------+-------------------------+
| ncomment_id | ncomment_newsid | ncomment_posterid | ncomment_date | ncomment_comment        |
+-------------+-----------------+-------------------+---------------+-------------------------+
|           1 |               1 |                 1 |    1145718219 | Test comment            |
|           2 |               2 |                 1 |    1145729496 | YOYO                    |
|           3 |               2 |                 1 |    1145731224 | Another test comment    |
|           4 |               2 |                 1 |    1145747197 | And yet ANOTHER comment |
+-------------+-----------------+-------------------+---------------+-------------------------+

Query output
Code:
+---------+------------+------------+-----------+---------------+--------------+
| news_id | news_date  | news_title | news_text | news_posterid | num_comments |
+---------+------------+------------+-----------+---------------+--------------+
|       1 | 1144242032 | Test1      | Testing   |             1 |            1 |
|       2 | 1144254060 | Test2      | Testing   |             1 |            3 |
|       3 | 1144289523 | Test3      | Testing   |             1 |         NULL |
+---------+------------+------------+-----------+---------------+--------------+
Jamatu
GOT IT!
Code:
SELECT news.*,users.username, COUNT(news_comments.ncomment_newsid) AS num_coms
FROM news
INNER JOIN users ON users.id = news.news_posterid
LEFT JOIN news_comments ON news_comments.ncomment_newsid = news.news_id
GROUP BY news_id DESC
ORDER BY news_id DESC

This can be locked now Very Happy
Related topics
I cant install php chat tables nor set up diffrent subdomain
html form help needed
PHPBB MYSQL Database Question.
SQL to XML converter
sql with phpmyadmin
MySql to SQL
Can't create mySQL table
Database Privilege
need help restoring phpbb database from old webhost.
mySQL Query/PHP code - get the highest value...
phpmyadmin upload problem
database import
mySQL Query Problem
Strange SQL cursor problem. Pls do not ignore if can help.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.