Hello,
I have two arrays, one array contains a list of all the genre's in the database (eg: Rock & Roll, Classical etc) and the other array contains a list of all the genre's that a particual CD is in.
I want to code a script that will construct a multiple select list box containing a list of all the genre's, but with all the genre's that the CD belongs to selected.
This is what I have so far:
$AllGenres = mysql_query("select name from genre order by name", $connection);
//Retrive all the genre's ID that the CD belongs to.
$GenreResult = mysql_query("select genre_id from genre_cd where cd_id = '" . $cdID . "'", $connection);
//Build the multiple select list
echo "Genre(s):<br><select name='genre' multiple>";
while($row = mysql_fetch_array($GenreResult))
{
//I got all the CDs genre's ID above, but now I need the name of the
//genre so I can compare it with the genre in the all genres array.
$CDGenreName = mysql_query("select name from genre where id = " . $row["genre_id"] . "", $connection);
$NameRow = mysql_fetch_array($CDGenreName);
while($AllRow = mysql_fetch_array($AllGenres))
{
//need to compare this to all the genres in selected buffer
if ($AllRow["name"] == $NameRow["name"])
{
//display as selected
echo "\n\t<option value='" . $AllRow["name"]. "' selected>" . $AllRow["name"] . "";
}
else
{
echo "\n\t<option value='" . $AllRow["name"] . "' >" . $AllRow["name"] . "";
}
}//end inside while
}//end main while
If anyone can manage to get their heads around this they must be geniuses.
Best Regards
John
Problem building a multiple select list box from arrays.
Moderator: General Moderators
I'm not a sql-expert, so there might be (probably is) a better way to perform the query
and maybe I made some misstakes with the tablenames/fields
(script/query tested not even by compiler
)
Code: Select all
<html><body>
<?php
$dbConn = mysql_connect(...);
mysql_select_db(...);
$cdId = (int)...
$query = 'SELECT gc.id,g.name from cds as c left join genre_cd as gc on c.id=gc.cd_id right join genre as g on g.id=gc.genre_id where c.id='.$cdId;
$result = mysql_query($query, $dbConn) or die(mysql_error());
?>
<select name="genre" multiple="multiple">
<?php
while($row = mysql_fetch_row($result))
echo ' <option value="', $row[1], '" ', ($row[0]) ? 'selected="selected" >':'>', $row[1], '</option>';
?>
</select>
</html></body>(script/query tested not even by compiler