[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Looping/Sorting Question PHP/MySQL

Using PHP/MySQL, I  have a query

// Query database to get all sources that match search criteria
$res_search = mysql_query("SELECT showID, bandname, date, source, notes FROM music WHERE ('$SearchBandName' = bandname) OR ('$SearchShowDate' = date) OR ('$SearchSource' = source) ORDER BY date") or die("SQL error: " . mysql_error());

When I search by source, it can return results from multiple bands. Right now, I have it set up so that it loops through and lists each show including the band's name:

            while ($row = mysql_fetch_array($res_search, MYSQL_BOTH)) {            
            
            echo '<p><strong>';
            echo $count;
            echo ')&nbsp;';
            $count++ ;
            echo $row[1]; // Band name
            echo '</strong><br />';
            echo $row[2];  // Date
            echo '<br />';
            echo $row[3];  // Source
            echo '<br />';
            echo $row[4]; // Notes
            echo '</p>';
            }

Which might return results like this:

1) Grateful Dead
1966-07-16
Audience

2) Grateful Dead
1968-01-22
Audience

3) Grateful Dead
1968-03-03
Audience

4) Phil Lesh & Friends
1994-09-24
Audience

5) Phil Lesh & Friends
1998-04-20
Audience

6) Phil Lesh & Friends
1999-07-02
Audience

But, what I really want would to have it list the band name as a heading, and then list that band's shows, and then the next band name, and then so on like this:

Grateful Dead

1) 1966-07-16
Audience

2) 1968-01-22
Audience

3) 1968-03-03
Audience

Phil Lesh & Friends

4) 1994-09-24
Audience

5) 1998-04-20
Audience

6) 1999-07-02
Audience

My attempts at creating a loop to do this have fallen flat. Hopefully this is a quick and easy sort?
0
BongSoo
Asked:
BongSoo
  • 5
  • 4
  • 4
1 Solution
 
MasonWolfCommented:
I don't doubt that MySQL has a query of some sort that would do this, but I often find it much simpler to just use php arrays.

In this case, try:

            while ($row = mysql_fetch_array($res_search, MYSQL_BOTH)) {            
            $concerts[$row[1]][] = array("Date"=>$row[2], "Source"=>$row[3], "Notes"=>$row[4]);
            }
            foreach($concerts AS $band => $listings)
            {
                   echo '<p><strong>'.$band.'</strong></p>';
                   foreach($listings AS $show)
                   {
                          echo '<p>'.$count;.')&nbsp;';
                          $count++ ;
                          echo $show['Date'].'<br />';
                          echo $show['Source'].<br />;
                          echo $show['Notes'].'</p>';
                   }
            }
0
 
MasonWolfCommented:
Oops - left a semi-colon where it didn't belong

            while ($row = mysql_fetch_array($res_search, MYSQL_BOTH)) {            
            $concerts[$row[1]][] = array("Date"=>$row[2], "Source"=>$row[3], "Notes"=>$row[4]);
            }
            foreach($concerts AS $band => $listings)
            {
                   echo '<p><strong>'.$band.'</strong></p>';
                   foreach($listings AS $show)
                   {
                          echo '<p>'.$count.')&nbsp;';
                          $count++ ;
                          echo $show['Date'].'<br />';
                          echo $show['Source'].<br />;
                          echo $show['Notes'].'</p>';
                   }
            }
0
 
BongSooAuthor Commented:
My server doesn't like something about the code - it just shows a blank page when I test it using your code for the action page code.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
steelseth12Commented:
or you can do it with the query and have something like this

$res_search = mysql_query("SELECT bandname,GROUP_CONCAT(CONCAT(showID,';;'), CONCAT(`date`,';;'), CONCAT(source,';;'), notes SEPARATOR '||') as value FROM music WHERE ('$SearchBandName' = bandname) OR ('$SearchShowDate' = date) OR ('$SearchSource' = source) GROUP BY bandname ORDER BY date") or die("SQL error: " . mysql_error());





$count =1;

while ($row = mysql_fetch_array($res_search, MYSQL_BOTH)) {            

      //print_r($row);

      echo '<p><strong>';
      
                  
      echo $row["bandname"]; // Band name
      
      echo '</strong><br />';
      
      
      $string = explode("||",$row["value"]);
      
      foreach($string as $value) {
      
      list($showID,$date,$source,$notes) = explode(";;",$value);
      
      echo '<p><strong>';
      
      echo $count;
      
      echo ')&nbsp;';
      
      echo $date;  // Date
      
      echo '</strong><br />';
      
      echo $source;  // Source
      
      echo '<br />';
      
      echo $notes; // Notes
      
      echo '</p>';
      
      $count++ ;
      
      }
}
0
 
MasonWolfCommented:
Sorry - my bad. I left out the ' around one of the <br /> tags
I tested this one and it seemed to work fine.

while ($row = mysql_fetch_array($res_search, MYSQL_BOTH)) {            
            $concerts[$row[1]][] = array("Date"=>$row[2], "Source"=>$row[3], "Notes"=>$row[4]);
            }
                  $count = 1;
            foreach($concerts AS $band => $listings)
            {
                   echo '<p><strong>'.$band.'</strong></p>';
                   foreach($listings AS $show)
                   {
                          echo '<p>'.$count.')&nbsp;';
                          $count++ ;
                          echo $show['Date'].'<br />';
                          echo $show['Source'].'<br />';
                          echo $show['Notes'].'</p>';
                   }
            }
0
 
BongSooAuthor Commented:
That worked. Thanks Masonwolf! steelseth12, I didn't have time to test yours, but I really do appreciate the attempt!!!
0
 
steelseth12Commented:
It wasnt an attemp it was an answer!!!!
Its really intersting that I have time to help you but you have no time to test.
I would expect as much from a normal member but from an another expert .......
Ohhh well next time ill know better
0
 
BongSooAuthor Commented:
If there is a way I can make it up to you I will, but I was in a hurry and Masonwolf had the fastest response that was closest to using my original query. Your answer, which I am sure would probably be just as valid, would have required altering my query. Not necessarily a bad thing, but in this instance there can only be one right answer; since you both went in different directions, it wouldn't have been possible to split the points.
0
 
steelseth12Commented:
There is no need to make it up to me.
Its not a matter of points,  Masonwolf deserves the points.
It a matter of you not bothering testing my solution.

>>but in this instance there can only be one right answer

No there can be many solutions to a single question deppending on the nature and needs of the application.

For example although Masonwolf works well on a smaller result set it performes poor on large ones.
Thats why RDBMS's are for ... or we would all be using a flatline file.

For example

on retrieving 10 results

PHP SOLUTION : 0.0025 secs
MYSQL SOLUTION : 0.0041 secs

on retrieving 1250 results

PHP SOLUTION : 0.023 secs
MYSQL SOLUTION : 0.040 secs

on retrieving 2500 results

PHP SOLUTION : 0.045 secs
MYSQL SOLUTION : 0.035 secs

on retrieving 5500 results

PHP SOLUTION : 0.11 secs
MYSQL SOLUTION : 0.065 secs
0
 
steelseth12Commented:
TYPO
on retrieving 1250 results

PHP SOLUTION : 0.023 secs
MYSQL SOLUTION : 0.020 secs
0
 
BongSooAuthor Commented:
Cool, I stand corrected. I was in a hurry and should have been more open. I apologize and will do better next time! Please don't hold it against me...
0
 
MasonWolfCommented:
Wow! Was that difficult to put together, steelseth?
0
 
steelseth12Commented:
what the results ?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now