BongSoo
asked on
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_sea rch, MYSQL_BOTH)) {
echo '<p><strong>';
echo $count;
echo ') ';
$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?
// 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_sea
echo '<p><strong>';
echo $count;
echo ') ';
$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?
Oops - left a semi-colon where it didn't belong
while ($row = mysql_fetch_array($res_sea rch, MYSQL_BOTH)) {
$concerts[$row[1]][] = array("Date"=>$row[2], "Source"=>$row[3], "Notes"=>$row[4]);
}
foreach($concerts AS $band => $listings)
{
echo '<p><strong>'.$band.'</str ong></p>';
foreach($listings AS $show)
{
echo '<p>'.$count.') ';
$count++ ;
echo $show['Date'].'<br />';
echo $show['Source'].<br />;
echo $show['Notes'].'</p>';
}
}
while ($row = mysql_fetch_array($res_sea
$concerts[$row[1]][] = array("Date"=>$row[2], "Source"=>$row[3], "Notes"=>$row[4]);
}
foreach($concerts AS $band => $listings)
{
echo '<p><strong>'.$band.'</str
foreach($listings AS $show)
{
echo '<p>'.$count.') ';
$count++ ;
echo $show['Date'].'<br />';
echo $show['Source'].<br />;
echo $show['Notes'].'</p>';
}
}
ASKER
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.
or you can do it with the query and have something like this
$res_search = mysql_query("SELECT bandname,GROUP_CONCAT(CONC AT(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_sea rch, 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 ') ';
echo $date; // Date
echo '</strong><br />';
echo $source; // Source
echo '<br />';
echo $notes; // Notes
echo '</p>';
$count++ ;
}
}
$res_search = mysql_query("SELECT bandname,GROUP_CONCAT(CONC
$count =1;
while ($row = mysql_fetch_array($res_sea
//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
echo '<p><strong>';
echo $count;
echo ') ';
echo $date; // Date
echo '</strong><br />';
echo $source; // Source
echo '<br />';
echo $notes; // Notes
echo '</p>';
$count++ ;
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked. Thanks Masonwolf! steelseth12, I didn't have time to test yours, but I really do appreciate the attempt!!!
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
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
ASKER
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.
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
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
TYPO
on retrieving 1250 results
PHP SOLUTION : 0.023 secs
MYSQL SOLUTION : 0.020 secs
on retrieving 1250 results
PHP SOLUTION : 0.023 secs
MYSQL SOLUTION : 0.020 secs
ASKER
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...
Wow! Was that difficult to put together, steelseth?
what the results ?
In this case, try:
while ($row = mysql_fetch_array($res_sea
$concerts[$row[1]][] = array("Date"=>$row[2], "Source"=>$row[3], "Notes"=>$row[4]);
}
foreach($concerts AS $band => $listings)
{
echo '<p><strong>'.$band.'</str
foreach($listings AS $show)
{
echo '<p>'.$count;.') ';
$count++ ;
echo $show['Date'].'<br />';
echo $show['Source'].<br />;
echo $show['Notes'].'</p>';
}
}