I have a situation where a person arrives on a page having clicked a theme on the previous page, the problem I am having is when they click 'Latest Additions' which should show the last 10 entires in the database, but rather than showing the hotels, I need to show the countries associated with those hotels.

So I have this for starters -

if ($selectCategory=="Latest Additions") {
$r=mysql_query("SELECT DISTINCT(Id_Cntry), Nom_Cntry FROM tbl_countries LEFT JOIN tbl_hotels ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE (tbl_hotels.Act_Hot='1') ORDER BY tbl_countries.Nom_Cntry");
}

It works fine in that it outputs all the countries, but what I need is to use this too -

$r=mysql_query("Select * from tbl_hotels WHERE Act_Hot=1 ORDER ORDER by tbl_hotels.Id_Hot LIMIT 0,10 DESC");

So basically I 'select' the countries associated with the hotels limited by 10 and order by Nom_Cntry.

Can you see my problem, I need to limit the hotels to 10 and show the countries associated with it, rather than limit the countries to 10.

cpradio
—
2013-05-29T11:09:35Z —
#2

Wouldn't you just limit your prior query?

$r=mysql_query("SELECT DISTINCT(Id_Cntry), Nom_Cntry FROM tbl_countries LEFT JOIN tbl_hotels ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE (tbl_hotels.Act_Hot='1') ORDER BY tbl_countries.Nom_Cntry LIMIT 0, 10");

Or does that not produce the wanted results?

multichild
—
2013-05-29T11:15:55Z —
#3

Hi cpradio,

No tried that and basically what happens there is that it LIMIT's the countries to 10, when basically what I need to happens is not LIMIT the countries, but LIMIT the hotels to 10 and it displays the countries associated with those hotels, regardless of how many countries there are.

It could only show 1 country if say the last 10 hotels uplaoded are all from Egypt, but if say the last 10 hotels are from Spain, Egypt & UK, then only 3 countries will show for the 10 hotels selected.

Does that make sense to you, as I cant work out how to do it code wise.

fretburner
—
2013-05-29T11:20:52Z —
#4

I think this might do it:

SELECT tbl_countries.Id_Cntry, tbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot
WHERE tbl_hotels.Act_Hot='1' ORDER ORDER by tbl_hotels.Id_Hot DESC LIMIT 0,10

What you're doing here is to select on the hotel table (even though we're not displaying any columns from it), and joining the countries table to get the country name and ID.

What you're doing here is to select on the hotel table (even though we're not displaying any columns from it), and joining the countries table to get the country name and ID.

That was going to be my next suggestion, change the order of the tables in your query. If that still fails, give me a sampling of your data (50 countries, and their respective hotels -- attach a CSV or something) and I'll play with it.

cpradio, that's what I thought too - hence the query we discussed in posts #4 and #5.. but I think multichild wants to remove duplicate countries from that list, so your result list should only be 5 items long.. at least, that's how I understand it.

multichild
—
2013-05-29T14:17:59Z —
#17

yes it worked and as fretburner pointed out it is outputting duplicate countries.