像这样的一个数组的PHP GET结果 [英] Php get result of an array like this

问题描述

我有我的数据库这样的

listing_id  category_id
1            1,4,8,9
2            2,3,5,10
3            7,8,9,5,3

现在的类别表是这样的。

Now the category table is like this

id  category_name
1   apple
2   grapes
3   banana
4   pineapple
5   mango
6   strawberry
7   corn
8   leech
9   tomato
10  potato

我想打一个完整的数组，其中我会从这样的表

I want to make a complete array where I will get the results from the table like this

Array
(
    [0] => Array
        (
            [listing_id] => 1
            [category_name] => apple,pineapple,leeche,tomato
        )

    [1] => Array
        (
            [listing_id] => 2
            [category_name] => grapes,banana,mango,potato
        )

    [2] => Array
        (
            [listing_id] => 3
            [category_name] => corn,leeche,tomato,mango,banana
        )
)

所以为了这个，我有我的PHP code这样的

so for this I have made my php code like this

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "listings";
$mysqli = new mysqli($servername, $username, $password, $dbname);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$listing_query = "SELECT * FROM `listings` ORDER BY listing_id";
$listing_array = array();
$category_array = array();
if( $result = $mysqli->query($listing_query) ) {
    while( $obj = $result->fetch_object() ) {
        $categories = $obj->categories;
        $listing_id = $obj->listing_id;
        array_push($category_array, $categories);
        foreach( $category_array as $category_name ) {
          $category_names = explode(',',$category_name);
          $get_category_names = "SELECT * FROM `categories` WHERE `id` = $category_names";
          if( $sql = $mysqli->query($get_category_names) ) {
            while( $object = $sql->fetch_object() ) {
                $category_name = $object->category_name;
                $listing_row['category_name'] = $category_name;
            }
          }
        }
    $listing_row['listing_id'] = $obj->listing_id;   
    array_push($listing_array, $listing_row);
    }
}

print_r($listing_array);

$mysqli->close();

但它不工作。谁能告诉我怎么去的结果是这样排列

But its not working at all. Can someone tell me how to get the results like this array

Array
(
    [0] => Array
        (
            [listing_id] => 1
            [category_name] => apple,pineapple,leeche,tomato
        )

    [1] => Array
        (
            [listing_id] => 2
            [category_name] => grapes,banana,mango,potato
        )

    [2] => Array
        (
            [listing_id] => 3
            [category_name] => corn,leeche,tomato,mango,banana
        )
)

任何帮助和建议将成为真正的AP preciable。谢谢

Any help and suggestions will be really appreciable. Thanks

更新

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "listings";
$mysqli = new mysqli($servername, $username, $password, $dbname);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$listing_query = "SELECT * FROM `listings` ORDER BY `listing_id` LIMIT 5 ";
$listing_array = array();
if( $result = $mysqli->query($listing_query) ) {
    while( $obj = $result->fetch_object() ) {
        $categories = $obj->categories;
        $listing_id = $obj->listing_id;
        $listing_row['listing_id'] = $obj->listing_id;
        $Query = "SELECT listing_id, group_concat(Category.category_name) category_name FROM `listings` Listing LEFT JOIN `categories` Category ON FIND_IN_SET(Category.category_id, Listing.categories) group by Listing.listing_id LIMIT 5";
        if($sql = $mysqli->query($Query)){
        while ($object = $sql->fetch_object()) {
        $listing_row['category_name'] = $object->category_name;
        }
        }
        array_push($listing_array, $listing_row);
    }

}

print_r($listing_array);

$mysqli->close();
?>

据显示每个阵列块元素相同的类别名称。

It is showing the same category name in each array block element.

推荐答案

您可以简单地使用的 FIND_IN_SET 和的 GROUP_CONCAT ，见下面的查询我测试过

You can simply use FIND_IN_SET and GROUP_CONCAT , see query below I've tested

SELECT listing_id, group_concat(Category.category_name) FROM `listings` Listing LEFT JOIN `categories` Category ON FIND_IN_SET(Category.id, Listing.category_id) group by Listing.listing_id

尝试下面code在PHP

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "listings";
$mysqli = new mysqli($servername, $username, $password, $dbname);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$listing_array = array();


$Query = "SELECT listing_id, group_concat(Category.category_name) category_name FROM `listings` Listing LEFT JOIN `categories` Category ON FIND_IN_SET(Category.id, Listing.category_id) group by Listing.listing_id LIMIT 5";
if($sql = $mysqli->query($Query)){
    while ($arAssoc = $sql->fetch_assoc()) {
        $listing_array[] = $arAssoc;
    }
}

print_r($listing_array);

$mysqli->close();
?>

这篇关于像这样的一个数组的PHP GET结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！