Mysql query help needed

Question

Hi,

i have two tables category and hotels where category.id should be equal to hotels.catid. Now how do i select 3 rows from each different category from the hotels table.

I have this query:

select h.* from hotels h inner join category c on h.catid = c.id
order by h.catid, h.hid

this selects all records, but i want to select three rows per different category so in all it should return 9 rows with 3 rows for each category.

If this can not be done in mysql, you could also suggest php code please.

Thanks

Answer 1

You should be able to use DISTINCT(category) and limit query to 3 results

Answer 2

So you want to find three hotels per category... there are options!

The easiest method I can think of is to perform the query per category.

SELECT
    h.* 
FROM
    hotels h
WHERE
    h.catid = 1
LIMIT 0, 3        

Answer 3

hi i am not sure about the query but i have a alternate way.first find all the category array and the find the 3 record from the hotels tables.

step I: $sql="SELECT * FROM Table category ";
execute the query and place results in category array

Step II: Make a loop For($i=0;isset(category array [$i]);$i++) { query for 3 record from hotel tabel Execute this and store all data in array } In this way you can also get the record.This is not the exact solution of your problem but you can use this for resolving your problem

Answer 4

You could do it in the following way.

select 
    h.* 
from 
    hotels h 
where 
    h.catid IN (
        select 
            c.catid 
        from 
            category c 
        order by
            RAND() 
        limit 1
    ) 
order by 
    h.catid, h.hid 
limit 3

This should give you 3 hotels in one random category.

With php you could do it like this

$i = 0;
$sql = "select catid from category order by rand()";
$query = mysql_query($sql);
while($row = mysql_fetch_object($query))
{
    if($i < 3)
    {
        $i++;
        $categories[] = $row->catid;
    }
    else
    {
        break;
    }
}

foreach($categories as $catid)
{
    $i = 0;
    $sql = "select * from hotels where catid = $catid";
    $query = mysql_query($sql);
    while($row = mysql_fetch_object($query))
    {
        if($i < 3)
        {
            $i++;
            $hotels[] = $row;
        }
        else
        {
            break;
        }
    }
}