select inside a while loop
| From: | Mike Baerwolf | Date: | Fri, 28 Nov 2003 05:05:56 +0000 |
| Subject: | select inside a while loop | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-31800@lists.php.net to get a copy of this message | ||
Hello,
I have two mysql tables songs and artists. They look like this:
CREATE TABLE
artists (
artist_id int(10) unsigned NOT NULL auto_increment,
artist_name varchar(100) default NULL,
artist_img varchar(50) default NULL,
PRIMARY KEY (artist_id),
UNIQUE KEY artist_name (artist_name),
KEY artist_id (artist_id)
) TYPE=MyISAM;
CREATE TABLE songs (
song_id int(11) NOT NULL auto_increment,
song_title tinytext,
artist_id tinytext,
PRIMARY KEY (song_id)
) TYPE=MyISAM;
Currently I have the artist_id in the songs table setup has a text field with artist names in them temporarily. First I want to select all the artist_ids(with the names) and find the artist_id for that name in the artist table. Then update the artist_id in the song table with the artist_id in the artist table. Then convert the artist_id in the song table to int.
So with all that said here is what i have done that doesn't work,
$result = mysql_query("SELECT artist_id FROM songs",$db) or die(mysql_error());
if ($row = mysql_fetch_row($result)){
do {
$artist_name = $row["artist_id"];
$result_1 = mysql_query("SELECT artist_id,artist_name FROM
artists WHERE artist_name = '$artist_name'",$db);
$row_1 = mysql_fetch_array($result_1);
print "$row_1[artist_id]-$row_1[artist_name]";
}while ($row = mysql_fetch_array($result));
}
I haven't even been able to get to the update part. I'm pretty sure the above fails because of the var $artist_name after the first run through.
Any help would be appreciated.
Thanks,
Mike