php script question

[Anon]

Hi Guys,
How do i do this:
i've got this script on a page:

<?
$link = mysql_connect('localhost', 'username', 'password');
mysql_select_db('typhoon');
$query = <<< EOF
SELECT band_name, picture, biography, profile, FROM bands
WHERE band_id = $id
EOF;
$row = mysql_fetch_array(mysql_query($query));
?>

Further down the page i have this script: <?=$row['genre']?> This is suppose to insert the value from the field "genre" in the table "genre" from a MySQL database.

Will i have to perform an INNER JOIN with the "bands" table? If so, what would i need to change in the above script?

The database has the following tables:

CREATE TABLE IF NOT EXISTS bands (
band_id int(10) unsigned NOT NULL auto_increment,
genre_id int(10) unsigned NOT NULL DEFAULT '0' ,
band_name varchar(100) NOT NULL DEFAULT '' ,
picture varchar(100) ,
picture_thumb varchar(100) ,
members text ,
biography text ,
profile text ,
PRIMARY KEY (band_id),
UNIQUE KEY band_id (band_id),
KEY band_id_2 (band_id)
);

CREATE TABLE genre (
genre_id int(10) unsigned NOT NULL auto_increment,
band_id int(10) unsigned NOT NULL DEFAULT '0' ,
genre varchar(50) NOT NULL DEFAULT '' ,
PRIMARY KEY (genre_id),
UNIQUE KEY album_id (genre_id),
KEY genre_id_2 (genre_id)
);

Cheers,
Andrea

[Anon]

Im not 100% sure on what you mean by "insert the value from the field 'genre' in the table 'genre'", but im guessing that you just want to get the genre for the current band you have selected.

If so, then you will need to modify your SQL so that it joins with the genre table. At the moment, nothing is returned because the 'genre' field does not exist in the $row array.

I have also noticed that your MySQL tables are slightly confusing in that you have far too many id columns in them with too many potential joins. I suggest redefining your tables to something like the following swince you dont need band_id and genre_id in both tables: -

CREATE TABLE IF NOT EXISTS bands (
band_id int(10) unsigned NOT NULL auto_increment,
genre_id int(10) unsigned NOT NULL DEFAULT '0' ,
band_name varchar(100) NOT NULL DEFAULT '' ,
picture varchar(100) ,
picture_thumb varchar(100) ,
members text ,
biography text ,
profile text ,
PRIMARY KEY (band_id)
);

CREATE TABLE genre (
genre_id int(10) unsigned NOT NULL auto_increment,
genre varchar(50) NOT NULL DEFAULT '' ,
PRIMARY KEY (genre_id)
);

I have also removed the UNIQUE KEY and KEY fields because i am not convinced you actually need them.

Then the SQL should be: -

SELECT band_name, picture, biography, profile, genre FROM bands LEFT JOIN genre ON genre.genre_id=bands.genre_id
WHERE band_id = $id

I havent tested this, but it looks ok to me.
Hope this helps.

Carl