Ok so here is my problem.

TABLE:
ID BLA
1 1
1 1
2 2
3 3
4 4



What I want my SQL statment to echo.

1 (only once not the second row)
2
3
4



Heres what my current statement looks like.

PHP Code:

<?
$swq = false;
$posts5 = mysql_query("SELECT * FROM posts WHERE creator = '".$viewUid."' ORDER BY id DESC") or die (mysql_error());
while($posts = mysql_fetch_array($posts5)){
if($swq == false){
    if(returnCatStatus(returnCatForThread($posts['thread'])) == 0 || returnRights(loggedUser()) >= 2){
$time = compileTimestamp($posts['createdMonth'],$posts['createdDate'],$posts['createdYear'],$posts['createdHour'],$posts['createdMinute'],$posts['createdSecond']);
$title = returnThreadTitle($posts['thread']);
$forum = returnForumTitle(returnForumForPost($posts['id']));
?>
<tr>
<td class="thread_status"></td>
<td width="40%"><a href="thread.php?t=<? echo $posts['thread']; ?>"><? echo $title; ?></a></td>
<td><a href="forums.php?f=<? echo returnForumForPost($posts['id']); ?>"><? echo $forum; ?></a></td>
<td class="last_posted" width="17%"> <? echo $time; ?></td>
<td class="posts_by" align="center"><a href="#<? echo $posts['id']; ?>" class="show_posts_by">show</a></td>
</tr>
<? } } } ?>

What that does it echo your posts and it is echoing more then once if you have more then one post in a topic, I want it to only echo once per topic you've posted in.

How about

SELECT DISTINCT * FROM posts

Nope, didn't work but also didn't change anything.

I am thinking about having 2 querys one to get the ID's and then another to phase the ID's threw the DB and in between have a function to get rid of #'s like
1,1,2,3,4,4
and then get rid of one 1 and one 4

I have another idea, but post the table structure so that I can provide a better query.

id|creator|thread|ugrade|content|status|createdHou r|createdMinute|createdSecond|createdYear|createdM onth|createdDate|hour|minute|second|year|month|dat e|editor



or if you need

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(255) default NULL,
  `creator` text,
  `thread` text,
  `ugrade` text NOT NULL,
  `content` text,
  `status` text,
  `createdHour` text,
  `createdMinute` text,
  `createdSecond` text,
  `createdYear` text,
  `createdMonth` text,
  `createdDate` text,
  `hour` text,
  `minute` text,
  `second` text,
  `year` text,
  `month` text,
  `date` text,
  `editor` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Not my preferred solution, but this is the best I can come up with off the cuff:

PHP Code:

<?php
$swq = false;
$posts5 = mysql_query("SELECT * FROM posts WHERE creator = '".$viewUid."' ORDER BY id DESC") or die (mysql_error());
$active_id = -1;
while($posts = mysql_fetch_array($posts5)){
  if ($active_id == $posts['id']) continue;
  if($swq == false){
    $active_id = $posts['id'];
    if(returnCatStatus(returnCatForThread($posts['thread'])) == 0 || returnRights(loggedUser()) >= 2){
      $time = compileTimestamp($posts['createdMonth'],$posts['createdDate'],$posts['createdYear'],$posts['createdHour'],$posts['createdMinute'],$posts['createdSecond']);
      $title = returnThreadTitle($posts['thread']);
      $forum = returnForumTitle(returnForumForPost($posts['id']));
?>
<tr>
<td class="thread_status"></td>
<td width="40%"><a href="thread.php?t=<?php echo $posts['thread']; ?>"><?php echo $title; ?></a></td>
<td><a href="forums.php?f=<?php echo returnForumForPost($posts['id']); ?>"><?php echo $forum; ?></a></td>
<td class="last_posted" width="17%"> <?php echo $time; ?></td>
<td class="posts_by" align="center"><a href="#<?php echo $posts['id']; ?>" class="show_posts_by">show</a></td>
</tr>
<?php
    }
  }
}
?>

I think I didn't explain enough of what I wanted.

Here's what in my profile it looks like.


I want my script to get rid of the multiple's. Like so.



(BTW your edit didn't change anything.)

Try this, but I have the suspicion it will fail.

SELECT * 
FROM posts
WHERE id IN(
  SELECT DISTINCT id
  FROM posts 
  WHERE creator = 'xyz' 
)
ORDER BY id DESC

This is the result when you don't enforce a primary key.
Now, to be really sure that you don't have duplicate fields, I think you'll need to scan the messages body and date (that should be enough) to find duplicate.

This query should work, but it will be really slow, as it'll force the db to make a index scan and compare the content of every posts and createdDate of a given editor between each others.
The more posts, the more time this query will take to run.

SELECT * 
FROM posts
WHERE id IN(
  SELECT MAX(id)
  FROM posts 
  WHERE creator = 'xyz' 
  GROUP BY content, createdDate
)
ORDER BY id DESC

By the way, there are many more datatypes that "text" in a db, and I seriously enjoin you to read about them.
http://dev.mysql.com/doc/refman/5.1/en/data-types.html
The db engine is highly optimized for specific datatypes, and using text restrict you a lot, both on enhancement the db can use to speed up his work, and functions you have at hand to use the datas in the tables.
If you had used an time format for your dates, for instance, you would not have needed to decompose them in 6 fields.
You can use sql functions to easily extract any part of a date.
http://dev.mysql.com/doc/refman/5.1/...functions.html

The database isn't originally mine, and I don't have time to optimize. Nether work.

I think the problem was described wrong in terms of the database fields. Try this:

PHP Code:

<?php
$swq = false;
$posts5 = mysql_query("SELECT * FROM posts WHERE creator = '".$viewUid."' ORDER BY id DESC") or die (mysql_error());
$threads_shown = array();
while($posts = mysql_fetch_array($posts5)){
  if (isset($threads_shown[$posts['thread']])) continue;
  if($swq == false){
    $threads_shown[$posts['thread']] = true;
    if(returnCatStatus(returnCatForThread($posts['thread'])) == 0 || returnRights(loggedUser()) >= 2){
      $time = compileTimestamp($posts['createdMonth'],$posts['createdDate'],$posts['createdYear'],$posts['createdHour'],$posts['createdMinute'],$posts['createdSecond']);
      $title = returnThreadTitle($posts['thread']);
      $forum = returnForumTitle(returnForumForPost($posts['id']));
?>
<tr>
<td class="thread_status"></td>
<td width="40%"><a href="thread.php?t=<?php echo $posts['thread']; ?>"><?php echo $title; ?></a></td>
<td><a href="forums.php?f=<?php echo returnForumForPost($posts['id']); ?>"><?php echo $forum; ?></a></td>
<td class="last_posted" width="17%"> <?php echo $time; ?></td>
<td class="posts_by" align="center"><a href="#<?php echo $posts['id']; ?>" class="show_posts_by">show</a></td>
</tr>
<?php
    }
  }
}
?>

Thank you Jeremy, works exactly like I wanted it to.

No problem. It's still not my preferred choice because you're getting too much data back from the SQL server, but it does work. The more efficient ways just escape me right now.

Decided to post the end result, As you can see the user has 33 posts with multiple topics and it is only showing that he posted once in each of said topics.

I admire your guys, handling those boring codes and work it out to feed the need of common people like me!
Great work!