db looks SOMETHING like this:
___________________________________
|id | author | title |
___________________________________
|1 | Bob | Bippety-bop |
___________________________________
|2 | John | Doe Tales |
___________________________________
|3 | Bill | Lew in the Sky |
___________________________________
|4 | Bob | More bopping |
I have been trying like mad to do a loop that outputs
Bill:
<ul>
<li>Lew in the Sky</li>
</ul>
Bob:
<ul>
<li>Bippety-bop</li>
<li>More bopping</li>
</ul>
John:
<ul>
<li>Doe Tales</li>
</ul>
The trick is to sort of group the titles belonging to each author.
I have a working model but it goes something like this:
$query_rs_distinct = "SELECT DISTINCT author from db_texts ORDER BY author ASC"
...
while ($row_rs_distinct = mysql_fetch_assoc($rs_distinct)){
echo $row_rs_distinct['dTTL'];
$query_rs_all = "SELECT * from db_texts ORDER BY author ASC";
....
do {
echo '<li>'.$row_rs_all['title'].'</li>';
} while ($row_rs_all = mysql_fetch_assoc($rs_all));
}
This gets me a satisfying result but with a large table I may be asking the server to do a new "secondary" query possibly hundreds of times every time I load a page (and at some point some other people will too).
So my question is - isn't there a better/cleaner way to do this in some while/if-else-looping? I have tried and spent way more hours (days) than I care to admit - but it just won't give. It seems impossible to me to loop thru all the titles in a secondary loop whithout running back thru the first - OR not being able to close the loop correctly...
Any points are welcome but I could really use some basic help here.
Please check this answer and made correction:-
<?php
error_reporting(E_ALL);// check all error including warning and notices too
ini_set('display_errors',1); // show the errors
$new_result = array(); // get all data
$conn = mysqli_connect('host name','user name','password','database name') or die(mysqli_connect_error());
if($conn){
$query_rs_distinct = "SELECT * from db_texts ORDER BY author ASC"; //query to fetch complete record of each author
$rs_distinct = mysqli_query($conn,$query_rs_distinct)or die(mysqli_error($conn));
$i = 0;
while ($row_rs_distinct = mysqli_fetch_assoc($rs_distinct)){
$new_result[$row_rs_distinct['author']][] = $row_rs_distinct['title'];
}
}else{
echo "database connection error";
}
foreach($new_result as $key=>$result){
echo $key.'<br/><ul>';
if(is_array($result)){
foreach($result as $res){
echo"<li>".$res."</li>";
}else{
echo"<li>".$res."</li>";
}
}
echo "</ul><br/>";
?>
