CODEWITHSUNDEEP
phpmysqlmysqli
Ayoub
Ayoub

Reputation: 41

How to stop ORDER BY RAND() from duplicating results?

I am trying get 5 random places from the table places and adding a random photo (linked to it by id) to it. I have done that and it works fine using the ORDER BY RAND() but most of the times results get duplicated. I would see the same "Place" but with another random photo I put an image under to explain what I see.

Place 1 then another Place 1

Has anyone seen something like this before and can help me? I would really appreciate it.

enter image description here

My PHP code: 

/// 5 random places + 1 random photo for each place

$sqlCommand2 = "SELECT DISTINCT(places.id), places.id AS id, places.title AS title, places.latitude AS latitude , places.longitude AS longitude, places_photos.place_id, places_photos.url AS image FROM places INNER JOIN places_photos ON places.id = places_photos.place_id ORDER BY RAND() LIMIT 5";

  $query2 = mysql_query($sqlCommand2) or die(mysql_error());
  $count2 = mysql_num_rows($query2);
  if($count2 > 1){
    $places .= "";
    while($row = mysql_fetch_array($query2)){

        $places .= "<a href=\"places/p/?id=".$row['id']."\"><div class=\"main-place-item\" style=\"background-image: url('http://Ayoubsi.com/places/p/images/".$row['image']."');\"><div id=\"title\">".$row['title']."</div></div></a>";
            } // close while

  } else {
    $places = "No places!";
  }

And this is the HTML

<div id="main-places">
      <?php echo $places; ?>
</div>

Upvotes: 0

Views: 1216

Answers (4)

Dylan James McGannon
Dylan James McGannon

Reputation: 944

From your query I'm guessing you have multiple photos per place.

For each place, do you want to show a random one of its photos?

If not, then you can use the query

$sqlCommand2 = "SELECT 
                    `places`.`id`,
                    `places`.`title`,
                    `places`.`latitude`,
                    `places`.`longitude`,
                    `places_photos`.`place_id`, 
                    `places_photos`.`url` AS `image`
                FROM 
                    `places`
                        JOIN 
                            `places_photos`
                        ON 
                            `places`.`id` = `places_photos`.`place_id`
                GROUP BY `places`.`id`
                ORDER BY
                    RAND()
                LIMIT 5";

If you do want to select a random photo you'll need to use a subquery like so

$sqlCommand2 = "SELECT
                    `x`.`id`,
                    `x`.`title`,
                    `x`.`latitude`,
                    `x`.`longitude`,
                    `x`.`place_id`, 
                    `x`.`url` AS `image`
                FROM (
                    SELECT
                        *
                    FROM 
                        `places`
                            JOIN 
                                `places_photos`
                            ON 
                                `places`.`id` = `places_photos`.`place_id`
                    ORDER BY
                        RAND()
                    ) AS 'x'
                GROUP BY
                    `x`.`id`
                LIMIT 5";

Upvotes: 0

Jalpa
Jalpa

Reputation: 720

Please use "seed" in RAND() function. Like below.

$seed=time();

$query="SELECT * FROM my_table ORDER BY RAND($seed) LIMIT $amount OFFSET $offset";

Upvotes: 0

spencer7593
spencer7593

Reputation: 108460

I think you are not understanding the SQL keyword DISTINCT. That's a keyword. It's not a function.

SELECT DISTINCT applies to all of the expressions in the select list, not just some column(s) wrapped in unnecessary parens.

When you write a statement like this:

  SELECT DISTINCT(a), b, c FROM ...

those parens around that first expression a don't do anything. That's equivalent to writing: 

  SELECT DISTINCT a, b, c FROM ...

The point is that the DISTINCT keyword is going to eliminate the duplicate rows. In this example, where the values of a, b, and c (taken together) duplicate the values of a, b and c returned from another row.

If you want distinct values of just the expression a, then use a GROUP BY clause.

  SELECT a, b, c
    FROM ...
   GROUP BY a

(Note that MySQL has a non-standard extension to GROUP BY that allows the expressions b and c to be non-aggregates, where other databases will throw an error. MySQL will return a value for b and c that is from one of the rows that was collapsed ("grouped") together, but which of those rows the values will come from is not deterministic.)

Upvotes: 1

Pupil
Pupil

Reputation: 23958

You need to group by your results instead of using DISTINCT.

Change your query to:

$sqlCommand2 = "SELECT places.id, places.id AS id, 
places.title AS title, places.latitude AS latitude ,
 places.longitude AS longitude, places_photos.place_id, 
places_photos.url AS image FROM places 
INNER JOIN places_photos ON places.id = places_photos.place_id 
GROUP BY places.id
ORDER BY RAND() LIMIT 5";

Because even if you are using DISTINCT, you are using JOIN.

And in JOIN, duplicate results are coming.

Upvotes: 2

Related Questions