SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2007
    Location
    Melbourne, Australia
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying grouped results, nested loops maybe?

    Ok guys,

    How would you achieve the following output:

    State 1
    Name
    Mob
    Website etc

    State 2
    Name
    Mob
    website etc

    State 3
    Name
    Mob
    Website etc


    From this database:

    CREATE TABLE `affiliate` (
    `idaffiliate` int(11) NOT NULL auto_increment,
    `affName` varchar(100) collate latin1_general_ci NOT NULL,
    `affContact` varchar(100) collate latin1_general_ci NOT NULL,
    `affPhone` varchar(20) collate latin1_general_ci default NULL,
    `affMob` varchar(20) collate latin1_general_ci default NULL,
    `affSite` varchar(50) collate latin1_general_ci default NULL,
    `affEmail` varchar(50) collate latin1_general_ci default NULL,
    `affAddress1` varchar(100) collate latin1_general_ci default NULL,
    `affAddress2` varchar(100) collate latin1_general_ci default NULL,
    `country` varchar(20) collate latin1_general_ci NOT NULL,
    `state` varchar(15) collate latin1_general_ci NOT NULL,
    `affCode` varchar(6) collate latin1_general_ci default NULL,
    PRIMARY KEY (`idaffiliate`)
    )

    Thanks in advance for your help

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would fetch the records from the table row by row and display them accordingly. What have you tried so far?
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you want to display the records Statewise? (ie Heading State 1 followed by State 1 records )

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2007
    Location
    Melbourne, Australia
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would iterate through db records with an if statement that would search for a specific state. I would repeat that code for each state. What i was looking at doing is making the whole thing dynamic.

    Sorry if this is hard to follow I'll try and find the code piece I am talking about and post it.

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2007
    Location
    Melbourne, Australia
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you want to display the records Statewise? (ie Heading State 1 followed by State 1 records )
    Yep thats exactly right

  6. #6
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't quite see what the problem is. Are there multiple rows per state? Do you want the states to be ordered in a specific way? Else it's all in that table, so what about

    PHP Code:
    $sql 'SELECT state, affName, affMob, affSite FROM affiliate';
    $res mysql_query($sql);
    while(
    $arr mysql_fetch_assoc($res)){
      echo 
    "<b>"$arr['state'], "</b><br />";
      echo 
    $arr['affName'], "<br />";
      echo 
    $arr['affMob'], "<br />";
      echo 
    $arr['affSite'], "<br /><br />";

    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2007
    Location
    Melbourne, Australia
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its a club db and there are multiple clubs per state.

    So the output is:

    Heading - State 1
    -club1(name, contact, site, etc)
    -club2 etc...
    Heading - State 2
    -club 3
    -club 4
    -club 5
    So the above code wouldn't display correctly.

    Heres what I was using for something similar:
    Code PHP:
    include "admin/include/classes/ez_sql.php";
     
    				if ($locs=$db->get_results("SELECT idaffiliate, affName, affAddress1, affAddress2, state, country, affCode FROM affiliate WHERE state = 'VIC' ORDER BY state DESC")){
    					echo"<div class='state'>\n";
    					echo"<h1>Victoria</h1>\n";
    					foreach($locs as $loc){
    						echo"<div class='loc'>\n";
    						echo"<h3>$loc->affName</h2>\n";
    						echo"<p>\n";
    						echo"$loc->affAddress1<br />\n";
    				 		if($loc->affAddress2)		
    							echo"$loc->affAddress2<br />\n";
    						echo"$loc->state $loc->affCode<br />\n";
    						$var=$db->get_var("SELECT  * FROM affTimes WHERE idaffiliate = '$loc->idaffiliate'");
    						if($var > 0)
    							echo"<b><a href='class-times.php?aff=$loc->idaffiliate'>More Info...</a></b>\n";
    						echo"</p>\n";
    						echo"</div>\n";
    					}
    					echo"</div>\n";
    				}

    Keep in mind i want to make this dynamic by state.

    thanks heaps for helping me with this

  8. #8
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mate to solve ur problem we got to use two loops , one is to select the states alone and the next one is to select the rows listed under the state( I am not sure whether its the optimized code.. somebody can correct if i am wrong)
    The code might be like

    Quote Originally Posted by webberoo View Post
    Its a club db and there are multiple clubs per state.

    So the output is:

    Heading - State 1
    -club1(name, contact, site, etc)
    -club2 etc...
    Heading - State 2
    -club 3
    -club 4
    -club 5
    So the above code wouldn't display correctly.

    Heres what I was using for something similar:
    Code PHP:
    include "admin/include/classes/ez_sql.php";
    if ($rs_States = $db->get_results("SELECT DISTINCT(state) from affiliate"){
    foreach ($rs_States as $States){
     echo"<div class='state'>\n";
     echo"<h1>$States->state</h1>\n";
     if ($locs=$db->get_results("SELECT idaffiliate, affName, affAddress1, affAddress2,  country, affCode FROM affiliate WHERE state = "$States->state")){
     
    					foreach($locs as $loc){
    						echo"<div class='loc'>\n";
    						echo"<h3>$loc->affName</h2>\n";
    						echo"<p>\n";
    						echo"$loc->affAddress1<br />\n";
    				 		if($loc->affAddress2)		
    							echo"$loc->affAddress2<br />\n";
    						echo"$loc->state $loc->affCode<br />\n";
    						$var=$db->get_var("SELECT  * FROM affTimes WHERE idaffiliate = '$loc->idaffiliate'");
    						if($var > 0)
    							echo"<b><a href='class-times.php?aff=$loc->idaffiliate'>More Info...</a></b>\n";
    						echo"</p>\n";
    						echo"</div>\n";
    					}
    					echo"</div>\n";
    				}
     
     
    }
    }

    This might help i guess

  9. #9
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I don't think that you need a separate SQL to loop through the states:

    PHP Code:
    include "admin/include/classes/ez_sql.php";

    $sql '
    SELECT
      idaffiliate,
      affName,
      affAddress1,
      affAddress2,
      state,
      affCode
    FROM
      affiliate
    ORDER BY
      state DESC
    '
    ;

    if (
    $locs=$db->get_results($sql)){
      
    $state '';
      foreach(
    $locs as $loc){
        if(
    $loc->state != $state){
          echo 
    "<h1>"$loc->state"</h1>\n";
          
    $state $loc->state;
        }
        echo
    "<h2>$loc->affName</h2>\n";
        echo
    "<p>\n";
        echo
    "$loc->affAddress1<br />\n";
        if(
    $loc->affAddress2){
          echo
    "$loc->affAddress2<br />\n";
        }
        echo
    "$loc->state $loc->affCode<br />\n";
        
    $var=$db->get_var("SELECT  * FROM affTimes WHERE idaffiliate =  $loc->idaffiliate'");
        if(
    $var 0){
          echo
    "<b><a href='class-times.php?aff=$loc->idaffiliate'>More Info...</a></b>\n";
        }
        echo
    "</p>\n";

    I've left out the DIVs because I don't know how you want them to be nested - and because they are a bit trickier than I thought ;-)

    Another suggestion: if db->get_var just counts the entries in the affTimes table then you even can get rid of this additional SQL and do it in one go:

    PHP Code:
    include "admin/include/classes/ez_sql.php";

    $sql '
    SELECT
      a.idaffiliate,
      a.affName,
      a.affAddress1,
      a.affAddress2,
      a.state,
      a.affCode,
      COUNT(t.idaffiliate) AS cntTimes
    FROM
      affiliate AS a
    LEFT JOIN
      affTimes AS t
    ON
      t.idaffiliate = a.idaffiliate
    GROUP BY
      a.idaffiliate,
      a.affName,
      a.affAddress1,
      a.affAddress2,
      a.state,
      a.affCode
    ORDER BY
      a.state DESC
    '
    ;

    if (
    $locs=$db->get_results($sql)){
      
    $state '';
      foreach(
    $locs as $loc){
        if(
    $loc->state != $state){
          echo 
    "<h1>"$loc->state"</h1>\n";
          
    $state $loc->state;
        }
        echo
    "<h2>$loc->affName</h2>\n";
        echo
    "<p>\n";
        echo
    "$loc->affAddress1<br />\n";
        if(
    $loc->affAddress2){
          echo
    "$loc->affAddress2<br />\n";
        }
        echo
    "$loc->state $loc->affCode<br />\n";
        if(
    $loc->cntTimes 0){
          echo
    "<b><a href='class-times.php?aff=$loc->idaffiliate'>More Info...</a></b>\n";
        }
        echo
    "</p>\n";

    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  10. #10
    SitePoint Zealot
    Join Date
    Feb 2007
    Location
    Melbourne, Australia
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats exactly what I was after. I knew there would be a way to display results without resorting to multiple loops.

    Thank you soooo much kleineme.

    Your help is greatly appreciated.


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
  •