SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 47
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Want to use LIMIT but its not appropriate for my needs

    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 -

    PHP Code:
    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 -

    PHP Code:
    $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.

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Wouldn't you just limit your prior query?
    PHP Code:
    $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?
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,387
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    I think this might do it:
    Code SQL:
    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.

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    I think this might do it:
    Code SQL:
    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.
    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.

    In the meantime, I'm moving this to the DB forum.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  6. #6
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used this:

    [SQL]
    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 BY tbl_hotels.Id_Hot DESC LIMIT 0,10
    [/SQL]

    It seems to have worked yes thank you cpradio, but it has outputted some of the countries a few times, so its fine then to use GROUP instead of ORDER isnt it.

    But thanks cpradio, there no way on Earth I would have got that.

    Cheers

  7. #7
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,387
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    If you only want each country to appear once in the result (even if multiple hotels in the top 10 are from that country) then yes, you can add GROUP BY tbl_countries.Nom_Cntry to your query.

  8. #8
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No didnt seem to work that -

    PHP Code:
    SELECT tbl_countries.Id_Cntrytbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_HotWHERE tbl_hotels.Act_Hot='1' GROUP BY tbl_hotels.Id_Hot DESC LIMIT 0,10 
    That didnt change the country output, so changed it too

    PHP Code:
    SELECT tbl_countries.Id_Cntrytbl_countries.Nom_Cntry FROM tbl_hotels JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_HotWHERE tbl_hotels.Act_Hot='1' GROUP BY tbl_countries.Nom_Cntry DESC LIMIT 0,10 
    and it returned to outputting 10 countries

    working on 'Latest Additions' at the mo from the browse by themes, but the all inclusive works too, just not connected the results up properly yet

    http://devchecksafetyfirst.csf.dcman...ategory=Latest Additions

  9. #9
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just tried to JOIN another group on, but thats not working - but I might be completely wrong as it was based on a guess -

    Code SQL:
    $r=mysql_query("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' JOIN (GROUP BY tbl_countries.Nom_Cntry) GROUP BY tbl_hotels.Id_Hot DESC LIMIT 0,10");

  10. #10
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry cpradio, i missed your reply, will get to it now.

  11. #11
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I reply and attach files, or shall I send them to your email address.

    This is where I am at at the mo -

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

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by multichild View Post
    How do I reply and attach files, or shall I send them to your email address.

    This is where I am at at the mo -

    Code:
    $r=mysql_query("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' JOIN (GROUP BY tbl_countries.Nom_Cntry) ORDER BY tbl_hotels.Id_Hot DESC LIMIT 0,10");
    Click on Go Advanced and you can add an attachment.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  13. #13
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hopefully this is what you want.

    Tricky one this isnt it.

    Cheers cpradio

    tbl_countries.zip

    tbl_hotels.zip

  14. #14
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Would something like this work (I limited the returned values from tbl_Hotels in a sub-query).

    Code:
    SELECT Nom_Cntry
      FROM tbl_countries c
      JOIN (SELECT IdCntry_Hot
              FROM tbl_hotels
             WHERE Act_Hot = '1'
    		 LIMIT 0, 10) sq ON sq.IdCntry_Hot = c.Id_Cntry
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  15. #15
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, so let's start back at the beginning. You want exactly 10 records. Those 10 records need to be the latest hotels and include the country the hotel resides in. Right?

    Code SQL:
    SELECT
      tbl_hotels.*,
      tbl_countries.*
    FROM tbl_hotels
      LEFT JOIN tbl_countries ON (tbl_countries.Id_Cntry = tbl_hotels.IdCntry_Hot)
    ORDER BY tbl_hotels.Id_Hot DESC
    LIMIT 0, 10

    A hotel should only exist in 1 country. So unless your data is bad, the above should return what you are asking for.

    Edit:

    My Proof of Concept in SQL Server:
    Code:
    CREATE TABLE #tbl_countries
    (
    	Id_Cntry INT,
    	Nom_Cntry VARCHAR(50),
    	Region VARCHAR(100),
    	Misc INT
    )
    
    INSERT INTO #tbl_countries VALUES ('1', 'Spain', 'EU', '1')
    INSERT INTO #tbl_countries VALUES ('2', 'Dominican Republic', 'Caribbean Island', '3')
    INSERT INTO #tbl_countries VALUES ('3', 'Mexico', 'Central America', '5')
    INSERT INTO #tbl_countries VALUES ('4', 'Tunisia', 'North Africa', '2')
    INSERT INTO #tbl_countries VALUES ('5', 'Egypt', 'Middle East / North Africa', '2')
    
    CREATE TABLE #tbl_hotels
    (
    	Id_Hot INT,
    	IdCntry_Hot INT,
    	HotelName VARCHAR(100)
    )
    
    INSERT INTO #tbl_hotels VALUES ('1', '1', 'Apts. Tamaimo Tropical')
    INSERT INTO #tbl_hotels VALUES ('2', '2', 'Aparthotel Be Live La Calita')
    INSERT INTO #tbl_hotels VALUES ('3', '3', 'Apartments Be Smart Madrid Albufera')
    INSERT INTO #tbl_hotels VALUES ('4', '4', 'M/S Semiramis III')
    INSERT INTO #tbl_hotels VALUES ('5', '5', 'The Grand Bliss')
    INSERT INTO #tbl_hotels VALUES ('6', '1', 'Pez Espada')
    INSERT INTO #tbl_hotels VALUES ('7', '2', 'Eurostars Rembrandt Classic - Amsterdam')
    INSERT INTO #tbl_hotels VALUES ('8', '3', 'Occidental Abou Sofiane')
    INSERT INTO #tbl_hotels VALUES ('9', '4', 'Eurostars Das Letras')
    INSERT INTO #tbl_hotels VALUES ('10', '5', 'Hotel Eurostars Astoria')
    INSERT INTO #tbl_hotels VALUES ('11', '1', 'Sentido M/S Nile Saray')
    INSERT INTO #tbl_hotels VALUES ('12', '2', 'Majesty Marina Vista')
    INSERT INTO #tbl_hotels VALUES ('13', '3', 'Sofitel Karnak Hotel')
    INSERT INTO #tbl_hotels VALUES ('14', '4', 'Hilton Alexandria Green Plaza')
    INSERT INTO #tbl_hotels VALUES ('15', '5', 'Playa Mia Grand Beach Park')
    INSERT INTO #tbl_hotels VALUES ('16', '1', 'Tiran Island Hotel')
    INSERT INTO #tbl_hotels VALUES ('17', '2', 'H10 Conquistador')
    INSERT INTO #tbl_hotels VALUES ('18', '3', 'Parador de Baiona')
    INSERT INTO #tbl_hotels VALUES ('19', '4', 'Barceló Isla Canela')
    INSERT INTO #tbl_hotels VALUES ('20', '5', 'Iberostar Founty Beach')
    
    SELECT * FROM #tbl_countries
    SELECT * FROM #tbl_hotels
    
    SELECT 
    	TOP 10
    	#tbl_hotels.Id_Hot,
    	#tbl_hotels.HotelName,
    	#tbl_countries.Nom_Cntry
    FROM #tbl_hotels 
    	LEFT JOIN #tbl_countries ON (#tbl_countries.Id_Cntry = #tbl_hotels.IdCntry_Hot) 
    ORDER BY #tbl_hotels.Id_Hot DESC
    
    DROP TABLE #tbl_countries
    DROP TABLE #tbl_hotels
    Produced:
    Code:
    20	Iberostar Founty Beach	Egypt
    19	Barceló Isla Canela	Tunisia
    18	Parador de Baiona	Mexico
    17	H10 Conquistador	Dominican Republic
    16	Tiran Island Hotel	Spain
    15	Playa Mia Grand Beach Park	Egypt
    14	Hilton Alexandria Green Plaza	Tunisia
    13	Sofitel Karnak Hotel	Mexico
    12	Majesty Marina Vista	Dominican Republic
    11	Sentido M/S Nile Saray	Spain
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  16. #16
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,387
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    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.

  17. #17
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes it worked and as fretburner pointed out it is outputting duplicate countries.

    http://devchecksafetyfirst.csf.dcman...ategory=Latest Additions

    So what it needs to do is group those countries somehow

  18. #18
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, so in SQL (SQL Server), the following produces the distinct 5 countries from the last 10 hotels.
    Code SQL:
    SELECT
    	DISTINCT(Nom_Cntry)
    FROM (
    	SELECT 
    		TOP 10
    		#tbl_hotels.Id_Hot,
    		#tbl_hotels.HotelName,
    		#tbl_countries.Nom_Cntry
    	FROM #tbl_hotels 
    		LEFT JOIN #tbl_countries ON (#tbl_countries.Id_Cntry = #tbl_hotels.IdCntry_Hot) 
    	ORDER BY #tbl_hotels.Id_Hot DESC
    ) q

    So I think Mitt Dave is on the right track here, so something like:
    Code SQL:
    SELECT DISTINCT Nom_Cntry
      FROM tbl_countries c
      JOIN (SELECT IdCntry_Hot
              FROM tbl_hotels
             WHERE Act_Hot = '1'
             ORDER BY Id_Hot DESC
    		 LIMIT 0, 10) sq ON sq.IdCntry_Hot = c.Id_Cntry

    Or

    Code SQL:
    SELECT
    	DISTINCT(Nom_Cntry)
    FROM (
    	SELECT 
    		tbl_countries.Nom_Cntry
    	FROM tbl_hotels 
    		LEFT JOIN tbl_countries ON (tbl_countries.Id_Cntry = tbl_hotels.IdCntry_Hot) 
    	ORDER BY tbl_hotels.Id_Hot DESC
    	LIMIT 0, 10
    ) q
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  19. #19
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats done it by the looks, thanks guys, i in honesty would never have got it.

    It never ceases to amaze me that the better I get there is always another level.

    Thank you

  20. #20
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by cpradio View Post
    So I think Mitt is on the right track here, so something like:
    Wrong blue boy, but I'll take the compliment

    multichild, glad you got it working
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  21. #21
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Wrong blue boy, but I'll take the compliment

    multichild, glad you got it working
    Off Topic:

    Dope! I'm a bit off today
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  22. #22
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting a Country is checking all Countries

    It seems I'm having a little bit of a problem with the code below which is working in that the correct country names return and in also the way i want them to as in the hotels using LIMIT.

    This is what I got so far:

    Code:
    $r=mysql_query("SELECT DISTINCT Nom_Cntry FROM tbl_countries c JOIN (SELECT IdCntry_Hot FROM tbl_hotels WHERE Act_Hot = '1' ORDER BY Id_Hot DESC LIMIT 0, 10) sq ON sq.IdCntry_Hot = c.Id_Cntry");
     
    while($q=mysql_fetch_assoc($r)){ ?> 
    <input type="checkbox" name="countries[]" value="<?=$q['Id_Cntry']?>" onClick="javascript:checkRefresh()" <?=((in_array($q['Id_Cntry'], $_REQUEST['countries'])) ? "checked=\"checked\" " : "")?> class="inline" /><?=$q['Nom_Cntry']?>&nbsp;<?=$q['IdCntry_Hot']?>
    What Im trying to do now is create the count, and the first thing I noticed was that although <?=$q['Nom_Cntry']?>, <?=$q['IdCntry_Hot']?> doesnt, so if a user selects one of the countries they all become checked.

    http://devchecksafetyfirst.csf.dcman...ategory=Latest Additions

  23. #23
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, so here is your problem:

    There is no Id_Cntry or IdCntry_Hot returned from your query. Nom_Cntry is the only column being returned. Since this is MySQL, I think you can do this: SELECT Id_Cntry, IdCntry_Hot, DISTINCT Nom_Cntry .... rest of query ....

    That would at least get you the columns you need for your while loop. You are seeing all checkboxes being checked because each of them have a value of "", and since they all have the same value, when you check one, it checks them all.

    We'll need to get your query fixed to be able to get this operating correctly.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  24. #24
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right of course yes, will have a look now.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    I think you can do this: SELECT Id_Cntry, IdCntry_Hot, DISTINCT Nom_Cntry ....
    no, you can't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •