PHP - Learning MySQL - page 439

// Run the query on the connection
if (!($result = @ mysql_query(
"SELECT * FROM artist, album WHERE artist.artist_id = album.artist_id",
$connection)))
die("Couldn't run query");
// Until there are no rows in the result set, fetch a row into
// the $row array.
while ($row = @ mysql_fetch_array($result))
{
print "\n";
// Print out the columns
print "{$row["artist_name"]} {$row["album_name"]}";
}
You can see that to access the artist_name column, you provide the column name as
an associative key into the $row array as $row["artist_name"] . As you'd expect, the code
outputs the following:
New Order Retro - John McCready FAN
New Order Substance (Disc 2)
New Order Retro - Miranda Sawyer POP
New Order Retro - New Order / Bobby Gillespie LIVE
New Order Power, Corruption & Lies
New Order Substance 1987 (Disc 1)
New Order Brotherhood
Nick Cave & The Bad Seeds Let Love In
Miles Davis Live Around The World
Miles Davis In A Silent Way
The Rolling Stones Exile On Main Street
The Stone Roses Second Coming
Kylie Minogue Light Years
This is a flexible, powerful method: you use the database column names directly, and
it makes your code readable and robust to most database and SQL query changes.
However, there are some cases where associative access is tricky. If both a table and
attribute name are used in a SELECT statement, only the attribute name is used to access
the data associatively. For example, if two or more tables contain columns with the
same name, only the one that occurs last in the query result can be accessed associa-
tively. You can get around this problem by using aliases as described in Chapter 7. For
example, to access the artist_id columns from both the artist and album tables, you'd
write:
// Run the query on the connection
if (!($result = @ mysql_query(
"SELECT artist.artist_id AS id1,
album.artist_id AS id2
FROM artist, album
WHERE artist.artist_id = album.artist_id",
$connection)))
die("Couldn't run query");
// Until there are no rows in the result set, fetch a row into