mysql 查询二维数组,多个mysql查询成一个php二维数组

I'm refactoring my code and just hit a snag. The first query in the code below gets the tab_id of the latest submitted tabs. The second Query gets the detail of each tab.

In my old way of doing it, i embedded php and html and it was truly an utter mess. Right now I'd like to merge the 2 queries into 1 and/or load it into an array.

feel free to ask and/or butcher

function get_newest_tabs()

{

$db_open;

$sql = "SELECT tab_id, song_id, user_id FROM tabs ORDER BY time_added DESC ". "LIMIT 15";

$result = mysql_query($sql) or die("ERROR - newest tabs function: ".mysql_error());

if (mysql_num_rows($result) > 0)

{

for($i = 0; $i < mysql_num_rows($result); $i++)

{

$tab_id = mysql_result($result, $i, "tab_id");

$db_open;

$sql =

"SELECT tabs.tab_id, tabs.tab_version, tabs.number_of_hits, artist.artist_name, users.user_alias, songs.song_name, tabs.time_added

FROM tabs, users, artist, songs

WHERE tabs.tab_id ='".$tab_id."' AND tabs.user_id = users.user_id AND tabs.song_id = songs.song_id AND songs.artist_id = artist.artist_id";

$result2 = mysql_query($sql) or die("ERROR - i3p mysql - 4: ".mysql_error());

if(mysql_num_rows($result2) == 1)

{

$song_name = mysql_result($result2, 0, "songs.song_name");

$artist_name = mysql_result($result2, 0, "artist.artist_name");

$user_alias = mysql_result($result2, 0, "users.user_alias");

$tab_version = mysql_result($result2, 0, "tabs.tab_version");

$number_of_hits = mysql_result($result2, 0, "tabs.number_of_hits");

$time_added = mysql_result($result2, 0, "tabs.time_added");

}

}

}

}

解决方案

I'd suggest using JOIN instead of selecting from multiple tables. You can also join the tabs table.

SELECT tabs.tab_id, tabs.song_id, tabs.user_id, tabs.tab_version, tabs.number_of_hits, artist.artist_name, users.user_alias, songs.song_name, tabs.time_added

FROM tabs

LEFT JOIN users ON users.user_id = tabs.user_id

LEFT JOIN songs ON songs.song_id = tabs.song_id

LEFT JOIN artist ON artist.artist_id = songs.artist_id

ORDER BY tabs.time_added DESC LIMIT 15

Then you could do a loop like:

while($row = mysql_fetch_array($result)) {

$tabs[$row['tab_id']] = $row;

}

As long as you have one user/song/artist per tab that will get you an array of your data.