I'm trying to write a function that will display the credit information for a song in the database, using the credits that are linked to an artist or an artist_group (which is two or more artists that are linked together under a different name).
(I previously had help writing this loop in the following questions which also has more information on my table layout if that helps: PHP/MYSQL: how to loop matching results from a query already inside a while loop)
I have a database with tables similar to this:
Artist | artist_id, artist_name
Artist_Group | group_id, group_name
Credits | credit_id, credit_name
Artist_To_Group | artist_id, group_id
Song | song_id, song_name
Credit_To_Artist | credit_id, artist_id, group_id, song_id
Below are the two separate functions I've made to try and get the results for both artist and artist_group separately (I'm sure there's probably a way to do it at the same time, but I don't understand php enough to try). The reason for this is because I want to check that there isn't any credits for either of them before echoing the results, and if there isn't post a message: "There are currently no artists linked to this song."
$id = mysql_real_escape_string($_GET['id']);
if (!$id) {
die('Please provide an id!');
}
function getArtistsBySongId($id)
{
$query = "SELECT * FROM `Credit_To_Artist` AS c2a
INNER JOIN `Credits` AS cr ON cr.credit_id = c2a.credit_id
INNER JOIN `Artist` AS a ON a.artist_id = c2a.artist_id
LEFT OUTER JOIN `Song` AS s ON s.song_id = c2a.song_id
LEFT OUTER JOIN `Project` AS p ON p.project_id = s.project_id
WHERE c2a.song_id = $id";
$res = mysql_query($query);
$artists = Array();
$artisttoid = Array();
$songtoid = Array();
while( $row = mysql_fetch_array($res) ) {
$artist = $row[artist_name];
$credit = $row[credit_name];
$songcr = $row[song_id];
if(!array_key_exists($artist, $artists) ) {
$artists[$artist] = Array();
$artisttoid[$artist] = $row[artist_id];
$songtoid[$songcr] = $row[song_id];
}
$artists[$artist][] = $credit;
}
return $getArtists;
}
function getGroupsBySongId($id)
{
$query = "SELECT * FROM `Credit_To_Artist` AS c2a
INNER JOIN `Credits` AS cr ON cr.credit_id = c2a.credit_id
INNER JOIN `Artist_Group` AS ag ON ag.group_id = c2a.group_id
LEFT OUTER JOIN `Song` AS s ON s.song_id = c2a.song_id
LEFT OUTER JOIN `Project` AS p ON p.project_id = s.project_id
WHERE c2a.song_id = $id";
$res = mysql_query($query);
$groups = Array();
$grouptoid = Array();
$song2id = Array();
while( $row = mysql_fetch_array($res) ) {
$group = $row[group_name];
$credits = $row[credit_name];
$songcred = $row[song_id];
if(!array_key_exists($group, $groups) ) {
$groups[$group] = Array();
$grouptoid[$group] = $row[group_id];
$song2id[$songcred] = $row[song_id];
}
$groups[$group][] = $credits;
}
return getGroups;
}
On the same page, I've then attempted the following code to display the information. It's probably not the best method but I've used includes because I find it easier to separate the different codes as I'm still very green to PHP.
<table border="0" cellspacing="5" cellpadding="5" class="cdinfo" width="100%;">
<tr>
<?php
if (getArtistsBySongId($id) == NULL AND getGroupsBySongId($id) == NULL) {
echo "<th style='font-size: 13px'>Credits:</th>";
echo "<td style='font-size: 13px'>There are currently no artists linked to this song.</td>";
} else {
include 'songs/getsongcredits.php';
}
?>
</tr>
</table>
file: songs/getsongcredits.php
<th valign="top" style="font-size: 13px">Credits:</th>
<td valign="top" style="font-size: 13px">
<?php if (getArtistsBySongId($id) == NULL) {
} else {
include 'getartists.php';
}
?>
<?php if (getGroupsBySongId($id) == NULL) {
} else {
include 'getgroups.php';
}
?>
</td>
file: songs/getartists.php
<?php foreach (getArtistsBySongId($id) as $artist => $creditarr) {
$credits = implode( ", ", $creditarr );
echo "<a href='star.php?id={$artisttoid[$artist]}'>{$artist}</a> ({$credits})<br />";
} ?>
file: songs/getgroups.php
<?php foreach (getGroupsBySongId($id) as $group => $creditgr) {
$credits = implode( ", ", $creditgr );
echo "<a href='group.php?id={$grouptoid[$group]}'>{$group}</a> ({$credits})<br />";
} ?>
There's probably a very simple solution to all of this, but once again PHP has stumped me. Any help would be highly appreciated.
Aucun commentaire:
Enregistrer un commentaire