SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 47 of 47
  1. #26
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, you can't
    Maybe when I wake up I'll see the actual solution Anyone else have kids that get you up at 4:30 in the morning because they don't want to sleep anymore? Anyone?

  2. #27
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL, thats mad...

    Yes I got kids, but the game they play is during any school holidays its up at 6.30 - 7am, then when school starts we cant get them out of bed at 8, how does that work!

    No worries cpradio

  3. #28
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is that whole section, I havent properly had a go at the count bit yet, as couldnt get the ID out as you know.

    Code:
    if ($selectCategory=="Latest Additions") {
    	
    $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']?><?
    $query1 = mysql_query("select count(Id_Hot) as total1 FROM tbl_hotels WHERE (IdCntry_Hot=". $q['Id_Cntry'] .")");
    $result1 = mysql_fetch_array($query1); 
    ?>
    <span class="result_Number_Count">[<? echo $result1['total1'];?>]</span>
    <br/>
    }

  4. #29
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,400
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    You should be able to do this:
    Code SQL:
    SELECT DISTINCT Id_Cntry, Nom_Cntry
    You don't actually need to get IdCntry_Hot anyway, as it's the same value as IdCntry

  5. #30
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by multichild View Post
    LOL, thats mad...

    Yes I got kids, but the game they play is during any school holidays its up at 6.30 - 7am, then when school starts we cant get them out of bed at 8, how does that work!

    No worries cpradio
    Off Topic:

    Mine are ages 1 and 3, so no school yet, but I bet they start to sleep in when school does come around *sigh*


    @fretburner ;, well spotted

  6. #31
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes great that worked, so I got the Id I needed now, see about the rest of it now.

    Cheers

  7. #32
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    6 and 8 mine are so we just coming out of the baby part, so they sleeping better, but they still hard work, and yes I dont get the holiday thing...

    very early when its holiday time, seemingly very tired during school time and cant get them up.

    I got that id woring now thanks, so am going to test it to make sure I can select a country and also need to put the count to it.

    Cheers

  8. #33
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK Im nearly there, I got a count working, but its counting too much, cant seem to keep it to the 10 limit.

    Code:
    <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;<?
    $query1 = mysql_query("select count(Id_Hot) as total1 FROM tbl_hotels WHERE (IdCntry_Hot=". $q['Id_Cntry'] .") AND Act_Hot = '1' ORDER BY Id_Hot DESC LIMIT 0, 10");
    $result1 = mysql_fetch_array($query1);
    Tried this also with no results.

    PHP Code:
    $query1 mysql_query("select count(Id_Hot) as total1 FROM tbl_hotels WHERE (IdCntry_Hot="$q['Id_Cntry'] .") AND Act_Hot = '1' 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 = "$q['Id_Cntry'] .""); 

  9. #34
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    Code SQL:
    SELECT DISTINCT Id_Cntry, Nom_Cntry, COUNT(*) AS total1 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 GROUP BY Id_Cntry, Nom_Cntry

  10. #35
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No didnt output anything cpradio, but shouldnt something in that = to an ID or something from the select query for the country, for example, but this didnt work either.

    Code SQL:
    $query1 = mysql_query("select count(Id_Hot) as total1 FROM tbl_hotels WHERE Act_Hot = '1' 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 = ". $q['Id_Cntry'] ."");

    p.s how do you get the code sql to show correctly in the posts, im using [sql] [/sql] and its not right

  11. #36
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    [highlight=SQL][/highlight]

    I took a different approach, so instead of two queries, you have 1.

    PHP Code:
    if ($selectCategory=="Latest Additions") {
        
    $r=mysql_query("SELECT DISTINCT Id_Cntry, Nom_Cntry, COUNT(*) AS total1 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 GROUP BY Id_Cntry, Nom_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']?>
    <span class="result_Number_Count">[<? echo $q['total1'];?>]</span>
    <br/>

  12. #37
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right I see, that works thank you cpradio, looking good.

  13. #38
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    Try this:
    Code SQL:
    SELECT DISTINCT Id_Cntry, Nom_Cntry, COUNT(*) AS total1 
     ... GROUP BY Id_Cntry, Nom_Cntry
    now the DISTINCT is completely redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #39
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    now the DISTINCT is completely redundant
    Yes, yes it is... At least it doesn't bomb... (or maybe it'd be nice if it did...)

  15. #40
    Non-Member wap247.mobi's Avatar
    Join Date
    Jan 2013
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT Id_Cntry, IdCntry_Hot, Nom_Cntry from table "insign".... You can't???

  16. #41
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cheers, corrected that and works perfectly.

    I'm sorry to ask again guys, but this joining on of different tables and all is a new but everything is working fine in that the countries that are supposed to show, sho, the count is correct and the correct hotels show when the country is selected but its outputting 10 of the hotels, rathen than the say 1 or 5 that it should.

    http://devchecksafetyfirst.csf.dcman...test+Additions

    In the link above egypt is selected and only 3 should show, but 10 does.

    I understand what is happening with the code below and the LIMIT and all, but dont know how to sort this bit out.

    this is what I got.

    Code SQL:
    IF (is_array($_GET['countries'])) {
    	 $countriesArray = array();
         foreach($_GET['countries'] AS $countries) {	 
    	 $countriesArray[] = '\''.$countries.'\'';
         }
     
    	 $countriesData = implode(',', $countriesArray);	 
    	 $sqlcountries =  ' AND tbl_hotels.IdCntry_Hot IN ('. $countriesData .' )';
    }
     
    //echo $selectCountry;
     
    IF ($selectCategory=="Latest Additions") {
     
    	 $sqlcategory =  ' Select * from tbl_hotels ';	 
    }
     
    <?
    	IF ($selectCategory=="Latest Additions") {
    	$sql.= "".$sqlcategory."" ;
    	} ELSE {
    	$sql.= "".$sqlcategory."" ;
    	$sql.= "".$sqlcategory2."" ;
    	}
     
     
    IF ($selectCategory=="Latest Additions") {
    $sql.= " WHERE (tbl_hotels.Act_Hot=1) ". ((!empty($sqlcountries)) ? $sqlcountries : "") ." ORDER by tbl_hotels.Id_Hot DESC LIMIT 0, 10 ";
    } ELSE {
    $sql.= " AND (tbl_hotels.Act_Hot=1) ". ((!empty($sqlcountries)) ? $sqlcountries : "") ." ORDER by tbl_hotels.Id_Hot DESC";	
    }
     
    $result = mysql_query($sql);
    Last edited by cpradio; Jun 4, 2013 at 09:03. Reason: Fixed typo in highlight

  17. #42
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Can you place a var_dump($sql) before $result = mysql_query($sql); and then just copy and paste that query in a reply for us, so we can see the entire query in one shot (and not have to figure out the PHP implications.

  18. #43
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Morning guys,

    Sorry about the delay, end of day and had to leave the office.

    But have put the vardrump in place and this is the result:

    Code:
    string(101) " Select * from tbl_hotels WHERE (tbl_hotels.Act_Hot=1) ORDER by tbl_hotels.Id_Hot DESC LIMIT 0, 10 "
    And this is the output when a country is selected

    Code:
    string(138) " Select * from tbl_hotels WHERE (tbl_hotels.Act_Hot=1) AND tbl_hotels.IdCntry_Hot IN ('7' ) ORDER by tbl_hotels.Id_Hot DESC LIMIT 0, 10 "

  19. #44
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I think you want something like so:
    Code SQL:
    SELECT
      sq.*
    FROM tbl_countries c JOIN (SELECT * FROM tbl_hotels WHERE (tbl_hotels.Act_Hot=1) ORDER BY Id_Hot DESC LIMIT 0, 10) sq ON sq.IdCntry_Hot = c.Id_Cntry
    WHERE Id_Cntry IN ('7')

  20. #45
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi cpradio,

    I have been meaning to ask but what are the 'sq' and 'c' bits.

    Will put this to the test now

  21. #46
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,126
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    the c following tbl_countries and the sq following the sub-query are simply aliases to refer to the tables without using the whole table name.

  22. #47
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right I see now, and thanks cpradio, that worked perfectly...


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
  •