Re: Help with MySQL SELECT loop

no need to create a temporary table Check out aliases and the mysql count function query will be something like $sql = "SELECT count(song_id) as how_many from table WHERE genre = '$genre'"; olinux --- Kenneth Brill <kbrill@multi.com> wrote: > The code below is my first attempt at a multiple > SELECT. Is there a better way? > > Thanks > Ken Brill > kbrill@multi.com > > -------------------------------------------------------------------------------- > > > > function TotalCountSongs($channel) { > // create a temp table to fill > > a.. $sql = "CREATE TABLE templistcount {TABLE > STUFF DELETED}; > b.. $result = mysql_query($sql) or die > ("\n\nCannot read from database\n" . mysql_errno(). > "\n" . mysql_error()); > // get the allable genres from the channels > database, these are MP3 ID3v2 genres tags > > a.. $sql = "SELECT * FROM channels WHERE > channel=\"$channel\""; > b.. $result = mysql_query($sql) or die > ("</select>\n\nCannot read from database\n" . > mysql_errno(). "\n" . mysql_error()); > c.. $row = mysql_fetch_object ($result); > // At this point $t="Rock, Country, Jazz" > a.. $t=$row->types; > b.. $a=explode(", ", $t); > // Now that I have a list of genres (MP3 ID3 genre > tags) in $a, I want to pull all the > // songs that match those 3 genres out of the table > 'list' and count them > // and to do this I move them one genre at a time to > a temp database and > // then select them all and count that. The table > 'list' has several thousand song titles > // from hundreds of genres > a.. foreach ($a as $Genres) { > a.. $Genres=trim($Genres); > b.. $sql = "INSERT INTO templistcount (number, > filename, filesize, seconds, bitrate, sample, > copyright, ID3songtitle, ID3artist, ID3album, year, > comment , genre) SELECT number, filename, filesize, > seconds, bitrate, sample, copyright, ID3songtitle, > ID3artist, ID3album, year, comment, genre FROM list > WHERE genre=\"$Genres\""; > c.. $result = mysql_query($sql) or die > ("</select>\n\nCannot copy from database\n" . > mysql_errno(). "\n" . mysql_error()); > b.. } > c.. $sql = "SELECT * FROM templistcount"; > d.. $result = mysql_query($sql) or die > ("</select>\n\nCannot count items in database\n" . > mysql_errno(). "\n" . mysql_error()); > e.. $num_rows = mysql_num_rows($result); > f.. $sql = "DROP TABLE templistcount"; > g.. $result = mysql_query($sql) or die > ("</select>\n\nCannot count items in database\n" . > mysql_errno(). "\n" . mysql_error()); > h.. return $num_rows; > } > > __________________________________________________ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/