Page 1 of 2

Random Query??

Posted: Sun Feb 08, 2009 10:58 am
by jwerre
Is there a way to select a random array of rows without duplication. Something like:

Code: Select all

 
public function getThreeRandomBooksFromEachGenre(){
    return mysql_query("SELECT * FROM tb_books WHERE genre = 'fiction' AND genre = 'non-fiction' AND genre = 'mystery' AND LIMIT 3  ");
}
 
I like the query to be random though, not just the first three that it finds.

Thanks.

Re: Query

Posted: Sun Feb 08, 2009 11:19 am
by jayshields
I don't understand your query but to randomly order something and choose 3 records use

Code: Select all

SELECT * FROM `table` ORDER BY RAND() LIMIT 3

Re: Random Query??

Posted: Sun Feb 08, 2009 11:23 am
by jwerre
Thanks Jayshields. That sounds like what I need but i want to make sure there's no duplicates. I need three random rows where no "genre" columns are the same. So if I have 100 books in each genre I only want one from each.

Re: Random Query??

Posted: Sun Feb 08, 2009 11:32 am
by John Cartwright

Code: Select all

GROUP BY genre

Re: Random Query??

Posted: Sun Feb 08, 2009 5:42 pm
by jwerre
I don't think this is giving me a random query. It's just returning the first one it finds from each genre.

Code: Select all

 
    public function getRandomBooks($total = 5){
        $resource = mysql_query("SELECT title FROM tb_book GROUP BY genre ORDER BY RAND() LIMIT $total ");
        $data = array();
        while($x = mysql_fetch_assoc($resource)){
            $data[$x['title']] = $x;
        }
        return $data;
    }
 
Does this look right?

Re: Random Query??

Posted: Sun Feb 08, 2009 6:06 pm
by jayshields
Oh, I see. That makes it more tricky. Tricky enough so that I don't know how to do it.

I've you've got a fixed number of genres you can use a subquery for each genre to product the desired output. If you haven't got a fixed number of genres, then I'm not sure. Try DISTINCT although I think that will just grab the first one it finds too.

Re: Random Query??

Posted: Mon Feb 09, 2009 11:05 am
by jwerre
I'm tying to select 5 random items from each of the five categories. I tried this but it just grabs the first five items it can find from each category.

SELECT title FROM tb_book GROUP BY genre ORDER BY RAND() LIMIT 5

If I have a table of 1000 books, each book being assigned one of five genres. How do i randomly get one book from each genre?

Re: Random Query??

Posted: Mon Feb 09, 2009 11:12 am
by jayshields
Like I said, if you've got a fixed number of categories you can do it with subqueries, but with an unfixed number of categories I don't know, you'll have to wait for a reply from someone else or do some more searching.

Re: Random Query??

Posted: Mon Feb 09, 2009 11:33 am
by Skoalbasher
If you have a table for categories like

Id Genre
1 Horror
2 Romance
3 Suspense
4 Crime
5 Adult

Code: Select all

 
$q = "SELECT * FROM genre";
$r = mysql_query($query);
 
while($row = mysql_fetch_array($r))
{
    $genreid = $row['id'];
    $q2 = "SELECT * FROM books WHERE genre_id='$genreid' ORDER BY RAND() LIMIT 1";
    $r2 = mysql_query($q2) or die(mysql_error());
    $row2 = mysql_fetch_array($r2);
  
    echo $row2['bookname']." is under the category ".$row['genre_name'];
}
 
 
I think that should work for you. And what's great about that is, if you add a genre, you don't have to change your code.

I did this real fast at work so it's untested.

Edit: If you want 3 books, you'll probably have another while loop nested in there.

Re: Random Query??

Posted: Mon Feb 09, 2009 4:05 pm
by jayshields
If he wants 3 books, he should just change the LIMIT from 1 to 3. By the way, I knew it could be done like that, but it's surely not the best way to do it. I think someone here will be able to put that into one query.

Re: Random Query??

Posted: Mon Feb 09, 2009 4:30 pm
by Skoalbasher
jayshields wrote:If he wants 3 books, he should just change the LIMIT from 1 to 3. By the way, I knew it could be done like that, but it's surely not the best way to do it. I think someone here will be able to put that into one query.
Well, it has to either be 3 different queries or one long one. That's how I would think. I could be wrong, and sometimes I am, so if there's anything better, I'd like to know as well.

But the genre should be an ID in the book table anyway, that way you're not duplicating large amounts of data. You're probably right though, probably a better way, but since no one responded I figure that would do him some good.

Re: Random Query??

Posted: Mon Feb 09, 2009 5:48 pm
by jwerre
I agree that genre should be a different table but for this example it's not :( I also thing we should be able to pull this off in one query.

Re: Random Query??

Posted: Mon Feb 09, 2009 5:54 pm
by Skoalbasher
jwerre wrote:I agree that genre should be a different table but for this example it's not :( I also thing we should be able to pull this off in one query.
Yeah, like jay said, that's the only way I know how to do it. You know, creating a new table wouldn't be hard. Then convert all the genres in your books table to a genre ID. Maybe in the DB section someone would be able to help you.

Re: Random Query??

Posted: Mon Feb 09, 2009 6:38 pm
by VladSun
[sql]SET @num := 0, @genre := ''; SELECT       genre,       title,      @num := IF(@genre = genre, @num + 1, 1) AS row_number,      @genre := genre AS __genreFROM      (            SELECT                  genre,                   title                              FROM                  tb_book             ORDER BY                  genre, rand()      ) AS __tb_bookGROUP BY       genre,       titleHAVING       row_number <= 5;[/sql]

Untested!

Re: Random Query??

Posted: Tue Feb 10, 2009 8:32 am
by Skoalbasher
VladSun wrote:[sql]SET @num := 0, @genre := ''; SELECT       genre,       title,      @num := IF(@genre = genre, @num + 1, 1) AS row_number,      @genre := genre AS __genreFROM      (            SELECT                  genre,                   title                              FROM                  tb_book             ORDER BY                  genre, rand()      ) AS __tb_bookGROUP BY       genre,       titleHAVING       row_number <= 5;[/sql]

Untested!
i've dabbled with using php and mysql for a little while now, and this makes no sense at all to me. wow.