Menu
Community /Pin to ProfileBookmark
@NogDogFeb 12.2018 — #Seems like the simplest approach would be a couple counter variables you could increment in the while loop.
_@NogDogFeb 12.2018 — #Not knowing what you actually do with $artists later, it's difficult to say whether whatever problem you are having is how $artists is being populated by your DB query results, or whether it's an issue with your template code/format/whatever. @NogDogFeb 12.2018 — #Well, if I were going to do it, I'd just use something like this:
(I suggest using an alias for that count field, e.g. something like:
[FONT=Courier New]SELECT a.name, COUNT(s.artist_id)[COLOR="#FF0000"]as num_songs[/COLOR] FROM . . .[/FONT]
)
_
creating table using mysql
Hi,
I’m stuck with the logic. I have two tables. One is artist second songs. Artist table has 8 artist and song table has 14 songs.
For each artist, you must display their name and the number of songs accredited to them. This part i have done.
but i’m struggle get the logic for summary.
A summary of the songs and artists on the system should also be displayed on each view. The summary should inform the user of the total number of songs and the total number of active artists in the system.
I don’t know how to display the sumary in the table. Basically I don’t know how to construct the query for all the information to get that in one table.
this what I have so far but it only display artist with the song accredited to them plus is multiple table not in one table because of the while loop it return only one table at time.
[code=php]// Construct the query
$sql = “SELECT a.name, COUNT(s.artist_id) FROM artist a JOIN song s
ON (s.artist_id = a.id)
GROUP BY a.name ASC”;
// Execute the query, assigning the result to $result
$result = mysqli_query($link,$sql);
// If the query failed, $result will be “false”, so we test for this, and exit if it is
if ($result === false) {
exit($lang[‘mysqli_error’]);
}
// Gather the author HTML for later
$artists = ”;
// Check if the query returned anything
if (mysqli_num_rows($result) == 0) {
// Pass an error message to the error template which will appear in place of authors
$msg = $lang[’empty_result_error’];
$artists .= $msg;
} else {
// Loop through $result, converting each record from the result set to an array which we assign to $row
while ($row = mysqli_fetch_assoc($result)) {
// We can now access the values in $row using the database column names as array keys
// Echo the escaped string to the page
$pass1 = str_replace(‘[+name+]’, htmlentities($row[‘name’]), $tpl_c);
$pass2 = str_replace(‘[+songs+]’, htmlentities($row[‘COUNT(s.artist_id)’]), $pass1);
$artists .= $pass2;
}
}
// We are finished with the result set, so no point keeping it in memory
mysqli_free_result($result);
// As we are doing no more database querying, we can also close the connection now.
mysqli_close($link);
Hopefully it does make sence.
thanks.
Sign in
to post a comment14 Comments(s) ↴
0
@NogDogFeb 12.2018 — #Seems like the simplest approach would be a couple counter variables you could increment in the while loop.[code=php]
$num_artists = $num_songs = 0;
while(/* ... */) {
$num_artists++;
$num_songs += $row['COUNT(s.artist_id)'];
// rest of the stuff you do in the loop...
}
// output or save the numbers in $num_artists and $num_songs as desired
[/code]_
0
@NogDogFeb 12.2018 — #0
@NogDogFeb 12.2018 — #Well, if I were going to do it, I'd just use something like this:[code=php]
<?php
// bunch of code up to executing the query, then...
?>
<table>
<tr><th>Name of Artist</th><th>Number of Songs</th></tr>
<?php
while ($row = mysqli_fetch_assoc($result)) {
printf(
"<tr><td>%s</td><td>%d</td></tr>n",
htmlentities($row['name']),
$row['num_songs'] // or whatever alias you want to use for that count field
);
}
?>
</table>
<?php
// rest of script...
[/code](I suggest using an alias for that count field, e.g. something like:
[FONT=Courier New]SELECT a.name, COUNT(s.artist_id)
)
_