php - 从 3 个不同的 mysql 表中选择数据

Question

我有 3 个表，我试图通过从每个表中放入一些特定数据来获得最终表。表格如下:

第一个表名为tbl_category，如下所示:

+------+---------------+----------------+
| cid  | category_name | category_image |
+------+---------------+----------------+
| 1    | cars          |  1.jpg         |
+------+---------------+----------------+

名为 tbl_wallpaper 的第二个表是这样的:

+------+---------------+----------------+-----------+
| id   | cat_id        | scat_name      | image     |
+------+---------------+----------------+-----------+
| 33   | 1             |  blue cars     | 001.png   |
+------+---------------+----------------+-----------+

第三个表叫做tbl_scategory，它是这样的:

+------+---------------+
| scid |scategory_name |
+------+---------------+
| 1    | blue cars     |
+------+---------------+

我试图达到的目标是得到这样的东西

"HD_WALLPAPER": [
    {
        "cid": "1",
        "scat_name": "blue cars",
        "category_name": "cars",
        "category_image": "1.jpg",
        "category_image_thumb": "001.png",
        "total_wallpaper": "1"
    },

表示数据来自表tbl_category和tbl_wallpaper和tbl_scategory。我正在尝试获取存储在表 tbl_scategory(tbl_category 的子类别)

中的壁纸总数

我正在使用这段代码，但它带来了整个壁纸列

if(isset($_GET['cat_list']))
{
    $jsonObj= array();
    $cat_order=API_CAT_ORDER_BY;
    $query="SELECT tbl_category.cid, tbl_category.category_name, tbl_category.category_image, tbl_scategory.scid, tbl_wallpaper.scat_name FROM tbl_category ,
    tbl_scategory, tbl_wallpaper
    ORDER BY tbl_category.".$cat_order."";
    $sql = mysqli_query($mysqli,$query)or die(mysql_error());


    while($data = mysqli_fetch_assoc($sql))
    {
        //Wallpaper count
        $query_wall = "SELECT COUNT(*) as num FROM tbl_wallpaper WHERE scat_name='".$data['cid']."'";
        $total_wall = mysqli_fetch_array(mysqli_query($mysqli,$query_wall));
        $total_wall = $total_wall['num'];   
        $row['cid'] = $data['cid'];
        $row['scat_name'] = $data['scat_name'];
        $row['category_name'] = $data['category_name'];
        $row['category_image'] = $file_path.'images/'.$data['category_image'];
        $row['category_image_thumb'] = $file_path.'images/thumbs/'.$data['category_image'];

        $row['total_wallpaper'] = $total_wall;


        array_push($jsonObj,$row);

    }

    $set['HD_WALLPAPER'] = $jsonObj;

    header( 'Content-Type: application/json; charset=utf-8' );
    echo $val= str_replace('\\/', '/', json_encode($set,JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
    die();
}

不知道是什么问题

注意:表格有更多行，我只是把这些部分放在示例中

Answer 1

试试这个:

SELECT t1.cid, t1.category_name, t1.category_image, t2.scat_name, t2.image
FROM tbl_category AS t1, tbl_wallpaper AS t2, tbl_scategory AS t3
WHERE t1.id = t2.cat_id
AND t2.scat_name = t3.scategory_name

希望能解决你的问题