Random Query??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jwerre
Forum Newbie
Posts: 18
Joined: Thu Feb 05, 2009 5:06 pm

Random Query??

Post 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.
Last edited by jwerre on Sun Feb 08, 2009 11:19 am, edited 1 time in total.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Query

Post 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
jwerre
Forum Newbie
Posts: 18
Joined: Thu Feb 05, 2009 5:06 pm

Re: Random Query??

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Random Query??

Post by John Cartwright »

Code: Select all

GROUP BY genre
jwerre
Forum Newbie
Posts: 18
Joined: Thu Feb 05, 2009 5:06 pm

Re: Random Query??

Post 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?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Random Query??

Post 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.
jwerre
Forum Newbie
Posts: 18
Joined: Thu Feb 05, 2009 5:06 pm

Re: Random Query??

Post 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?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Random Query??

Post 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.
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Random Query??

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Random Query??

Post 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.
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Random Query??

Post 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.
jwerre
Forum Newbie
Posts: 18
Joined: Thu Feb 05, 2009 5:06 pm

Re: Random Query??

Post 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.
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Random Query??

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Random Query??

Post 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!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Random Query??

Post 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.
Post Reply