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!
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.
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.
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;
}
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.
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?
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.
$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.
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.
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 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.