多个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.

我正在重构我的代码,只是遇到了麻烦。下面代码中的第一个查询获取最新提交的选项卡的tab_id。第二个查询获取每个选项卡的详细信息。按照我以前的方式,我嵌入了php和html,这真的是一个彻头彻尾的混乱。现在我想将2个查询合并为1和/或将其加载到数组中。

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

            }
        }
    }
}

3 个解决方案

#1

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

我建议使用JOIN而不是从多个表中选择。您也可以加入选项卡表。

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.

只要每个标签有一个用户/歌曲/艺术家,就可以获得一系列数据。

#2

Why not use sql:

为什么不使用sql:

WHERE tabs.tab_id IN (1,2,3,4)

or event

WHERE tabs.tab_id in (select tab_id from tabs ... )

I think it's rather SQL related question.

我认为这是与SQL相关的问题。

#3

I'm not sure how to read hegemon's suggestions, but AGREED it's an SQL question. That means you should do all your work in a MySQL interface without PHP in the way, then bring the finished SQL strings in. I use phpMyAdmin, which has a convenient feature that formats the SQL for use in PHP.

我不确定如何阅读霸权的建议,但同意这是一个SQL问题。这意味着你应该在没有PHP的情况下在MySQL接口中完成所有工作,然后将完成的SQL字符串带入。我使用phpMyAdmin,它具有格式化SQL以便在PHP中使用的便捷功能。

#1