[PHP-DB] RE: select inside a while loop

[Rolf van de Krol]

Hello,

Your code looks well. But is the variable $db the name of your database or
your link-identifier. When it is the name of your database i'm not really
surpised your code wouldn't work. mysql_query requires as second argument a
link identifier.

Rolf van de Krol

-Oorspronkelijk bericht-
Van: Mike Baerwolf [mailto:[EMAIL PROTECTED]
Verzonden: vrijdag 28 november 2003 6:06
Aan: [EMAIL PROTECTED]
Onderwerp: select inside a while loop


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

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] RE: select inside a while loop

[Martin Marques]

Sorry, just say the message.

 Your code looks well. But is the variable $db the name of your database or
 your link-identifier. When it is the name of your database i'm not really
 surpised your code wouldn't work. mysql_query requires as second argument a
 link identifier.

Read what Rolf says. Very wise words here. :-)

 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);

Why don't you make one query with a union between the 2 tables and then pass 
through the results with a loop and maybe an if inside it? Ypou are making to 
many connections to the DB.

P.D.: Any way, your problem will be solved with Rolfs advice.

-- 
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-
Martín Marqués  |[EMAIL PROTECTED]
Programador, Administrador, DBA |   Centro de Telemática
   Universidad Nacional
del Litoral
-

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php