Duplicate Items from query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Duvalfan23
Forum Newbie
Posts: 3
Joined: Sun Sep 28, 2008 5:35 pm

Duplicate Items from query

Post by Duvalfan23 »

I am new to PHP(only a couple of weeks) and I am trying to make a forum based website with other features. I am wanting to make a query that gets the last 10 topics, and also the 5 most popular topics. Whenever I display the items on the webpage, I get the correct number of results, but they are all duplicates of the last item inserted into the database. The code I have for the last 10 topics is as follows: (The 5 most popular is the same kind of query, so fixing this one wil fix the other one)


the query:

$getPosts = "SELECT Topics.TopicID,Topics.TopicName,Topics.ForumID,Topics.UserID,Topics.PostDate,Topics.PostTime,
Topics.Views,Topics.Replies,Topics.ODBCTime,Users.UserID,Users.User_name FROM Topics INNER JOIN Users ON Topics.UserID = Users.UserID ORDER BY Topics.ODBCTime DESC LIMIT 10";

$retrievePosts = mysql_query($getPosts,$con) or die(mysql_error());

$postRows = mysql_num_rows($retrievePosts);
$postArray = mysql_fetch_array($retrievePosts);

The Display Code

for ($i = 0; $i < $postRows; $i++) {
echo '
<tr>
<td class="tdLeft"><a href="viewtopic.php?forum=' . $postArray["ForumID"] . '&topic=' . $postArray["TopicID"] . '">' . $postArray["TopicName"] . '</a></td>
<td class="tdCenter"><a href="viewprofile.php?user=' . $postArray["UserID"] . '">' . $postArray["User_name"] . '</a></td>
<td class="tdCenter" style="font-size:80%;">' . $postArray["PostDate"] . '<br /> @ ' . $postArray["PostTime"] . '</td>
<td class="tdCenter">' . $postArray["Views"] . '</td>
<td class="tdCenter">' . $postArray["Replies"] . '</td>
</tr>';
}


If anyone could let me know how to make each row display each unique entry, please let me know! Thanks!!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Duplicate Items from query

Post by VladSun »

0. Please, use [ sql ] [ /sql ] BBcode (without spaces)

1. Why do you join with users table?
2. Are you sure ODBCTime column has correct values in it?
There are 10 types of people in this world, those who understand binary and those who don't
Duvalfan23
Forum Newbie
Posts: 3
Joined: Sun Sep 28, 2008 5:35 pm

Re: Duplicate Items from query

Post by Duvalfan23 »

Yes the ODBCTime is correct as I actually entered it directly into phpMyAdmin, by the "Default" for it. And I am joining the users table so that I can display the username of the person that posted it.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Duplicate Items from query

Post by VladSun »

:) :) :)
I should have read your post to the end ;)

mysql_fetch_array will fetch only one row! - read the manual about it and use a while loop to fetch all rows
There are 10 types of people in this world, those who understand binary and those who don't
Duvalfan23
Forum Newbie
Posts: 3
Joined: Sun Sep 28, 2008 5:35 pm

Re: Duplicate Items from query

Post by Duvalfan23 »

Thanks a lot for your help! I got it fixed now after 2 days of screwing around with this. Thanks a lot!
Post Reply