Using array_unique on MSQL while loop result

[punstc]

I'm having a problem getting rid of duplicates when selecting things from my mysql database and returning them in a while loop.

I've been trying to use array_unique but I can't find any good information about it in use with a while loop when selecting from a database.

here is what I have. Everything works but it returns duplicates.

 <?php

	$select = "SELECT post_id, post_title, post_time FROM user_posts WHERE user_email='$user_email' ORDER BY post_time DESC LIMIT 10 ";
	
		$results = mysql_query($select);
				
		while($row = mysql_fetch_assoc($results)) {
			$post_id = $row['post_id'];
			$post_title = $row['post_title'];
			$post_time = $row['post_time'];
			
			$post_time = date('D, M d g:i a',strtotime($post_time));
?>
	<div class="recent"><h1 class="recent_name"><a href="user/discussion.php?post_id=<?=$post_id ?>&post_title=<?=$post_title ?>"><?=$post_title ?></a></h1> <span class="date"><?=$post_time ?></span></div>

<?php } ?>

I took out the array_uniques that I was trying because it would make it just error out.

Any help would be appreciated. Thanks

[JasonDFR]

<?php

$select = "SELECT post_id, post_title, post_time FROM user_posts WHERE user_email='$user_email' ORDER BY post_time DESC LIMIT 10 ";

$results = mysql_query($select);

while($row = mysql_fetch_assoc($results)) {

    echo '<div class="recent">' . $row['post_id'] . ' ' . $row['post_title'] . ' ' . $row['post_time'] . '</div>' . "\n";
    
}

Run that and tell me what happens.

[punstc]

this is what it out putted

21 punstc@gmail.com Questionaire 2009-02-10 21:22:52
20 punstc@gmail.com Questionaire 2009-02-10 21:15:52
17 asdfasdfsadfsafasdfsadfsadfsafsa 2009-02-10 17:41:19
15 I have to write a long long long title so i can see if it repeats or drops to a second line instead of repeating 2009-01-30 11:14:51
14 twertlyltkajhdkasjhdlfiuasldkjfhaslkdjfhlasdfhkajsdhfkajshdf 2009-01-17 22:43:58
13 sdfkjgasdkfjsadklfhaslkdfhlaisuehfksnflkjas 2009-01-17 22:43:37
12 sdfgdsfgdsfgsdfg 2009-01-17 22:43:20
11 I have to write a long long long title so i can see if it repeats or drops to a second line instead of repeating 2009-01-17 22:42:46
10 asdfasdfsadfsafasdfsadfsadfsafsa 2009-01-17 20:20:38
9 sadfsadfasdf 2009-01-17 20:20:38

which is the results of my database that I was getting without any styling applied to them and the id number in front.

I'm trying to get it to not pull back duplicate titles.

[JasonDFR]

You'll have to change your query. I don't know SQL off the top of my head, but I think there is something like SELECT DISTINCT somewhere. Check out the MySQL website or search for distinct.

Or just don't have duplicate titles in the first place.

Bottom line is you have an SQL / Database issue, not PHP.

Good Luck.

[punstc]

I did a search and it worked perfectly for what I needed. I'm pretty new to the whole php mysql thing. I appreciate the help thanks

[Twey]

Don't do this sort of manipulation with PHP. Use your database.

The correct way to do it in PHP would be to store the results in an array:

<?php
  $query = sprintf('SELECT post_id AS id, post_title AS title, post_time AS time
                      FROM user_posts
                      WHERE user_email = \'%s\'
                      ORDER BY post_time DESC
                      LIMIT 10',
                    $user_email);
  $posts = array();

  $rs = mysql_query($query);

  while ($row = mysql_fetch_array($rs, MYSQL_ASSOC))
    $posts[] = (object)$row;

  array_unique($posts);
?>

<?php foreach ($posts as $post): ?>
  <div class="recent">
    <h1 class="recent_name">
      <a href="user/discussion.php?post_id=<?=$post->id ?>">
        <?php echo $post->title; ?>
      </a>
    </h1>

    <span class="date">
      <?php echo $post->time; ?>
    </span>
  </div>
<?php endforeach; ?>

However, this is wasteful of both CPU time and memory. Better is to have the database do it:

<?php
  $query = sprintf('SELECT DISTINCT post_id AS id, post_title AS title, post_time AS time
                      FROM user_posts
                      WHERE user_email = \'%s\'
                      ORDER BY post_time DESC
                      LIMIT 10',
                    $user_email);

  $rs = mysql_query($query);
?>

<?php while (($row = mysql_fetch_array($rs, MYSQL_ASSOC)) && ($post = (object)$row)): ?>
  <div class="recent">
    <h1 class="recent_name">
      <a href="user/discussion.php?post_id=<?=$post->id ?>">
        <?php echo $post->title; ?>
      </a>
    </h1>

    <span class="date">
      <?php echo $post->time; ?>
    </span>
  </div>
<?php endwhile; ?>

[punstc]

thanks a lot twey I appreciate the info on both ways to do. Even if one is less efficient. learning both helps me not mistakes in the future.

Thanks again I appreciate it.

[Twey]

Sorry, I had some mismatched quotation marks in there I just spotted.

You will also note the sane indentation and lack of one huge long line of HTML in my script. I'm assuming that $user_email is sanely escaped; if it isn't, you need to escape it. An alternative would be to use PDO:

<?php
  $dbh = new PDO('mysql:dbname=some_database;host=localhost',
                 'some_username',
                 'some_password');

  $query = $dbh->prepare('SELECT DISTINCT post_id AS id, post_title AS title, post_time AS time
                            FROM user_posts
                            WHERE user_email = :email
                            ORDER BY post_time DESC
                            LIMIT 10');
  $query->bindValue(':email', $user_email);
  $query->execute();
?>

<?php while ($post = $query->fetch(PDO::FETCH_OBJ)): ?>
  <div class="recent">
    <h1 class="recent_name">
      <a href="user/discussion.php?post_id=<?=$post->id ?>">
        <?php echo $post->title; ?>
      </a>
    </h1>

    <span class="date">
      <?php echo $post->time; ?>
    </span>
  </div>
<?php endwhile; ?>

Note that PDO includes a fetchAll() method which allows us to more easily fetch everything into an array, should we choose to do so.

Nowadays you should probably use PDO wherever possible. It makes many tasks involving databases much easier, and also bypasses common causes of errors or security issues entirely (like using prepared statements to avoid having to escape input manually).

[punstc]

Once again thanks for the info right now I just have it escaped but i'll look up some more information on PDO.

Thanks again