Php get result of an array like this

Question

I have my database 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
        )
)

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

Any help and suggestions will be really appreciable. Thanks

Updated

<?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.

You need to restructure listings table so that there is only one category_id per row. — AbraCadaver
– AbraCadaver, Commented Jun 1, 2015 at 15:36
Good luck, this will suck for you and anyone who comes after. Better to fix it now. — AbraCadaver
– AbraCadaver, Commented Jun 1, 2015 at 15:49

qcsites · Accepted Answer · 2015-06-01 15:29:47Z

1

You should be able to use group_concat to get the comma separated list like this

$get_category_names = "SELECT GROUP_CONCAT(category_name) as category_name  FROM `categories` WHERE `id` = $category_names";

answered Jun 1, 2015 at 15:29

qcsites

513 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pratik Joshi Over a year ago

Can you please elaborate use of Group concat?

qcsites Over a year ago

the mysql function group_concat function returns a string with concatenated values instead of a set of records. So you should be able to pull the list as comma separated values using it.

Friso Kluitenberg · Accepted Answer · 2015-06-01 15:50:33Z

You need to sort your SQL Query on the keys and you can use this custom function I wrote a while back

/**
         * Converts an mysql result query to a associative subarray
         * @param $arr the array container a sql result set, make sure that it is sorted
         *          on FIXED keys , so f.e (a1;b1;b3),(a1;b2;b4),(a2;b1;b3)
         */
        public function arr_common_to_sub($arr,$fixed,$set_name="data"){
            if (!isset($arr[0]))//no elements
                return array();

            $a_ret=array();


            $current_arr = array(); 

            foreach ($arr as $k => $v) {
                $key_check=array_intersect_assoc(self::get_ins_inc($v,$fixed), 
                    $current_arr);

                if (empty($key_check)){ //start of a new combination of fixed with the rest of the array

                    if (!empty($current_arr))//if it's not the first item ever, push!
                        array_push($a_ret, $current_arr); 

                    $current_arr=self::get_ins_inc($v,$fixed);
                    $current_arr[$set_name]=array();
                }

                array_push($current_arr[$set_name],self::get_ins_ex($v,$fixed) );
            }
            if (!empty($current_arr))//push the last item if not empty (TODO: check if 'check' is necessary)
                        array_push($a_ret, $current_arr); 

            return $a_ret;
        }



/**
* Remove all elements execpt those specified in $art
* @param $art the values of this array are the keys to keep in $arp
* @param $arp the initial array to apply the filter function too
*/
public static function get_ins_inc($arp,$art /*array to kep*/,$multiple=false){
    if (!is_array($arp))
        return array(); //return empty array since shit is not valid to begin with

    if ($multiple){ //apply $arp to all elements and then return it
        foreach ($arp as $k => &$v) {
            $v = self::get_ins_inc($v,$art,false);
        }
        return $arp;

    } else {
        return array_intersect_key($arp,array_flip($art));
    }
}

kamal pal · Accepted Answer · 2015-06-01 18:53:30Z

0

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

Try below code in 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();
?>

edited Jun 1, 2015 at 18:53

answered Jun 1, 2015 at 16:08

kamal pal

4,1975 gold badges27 silver badges40 bronze badges

5 Comments

NewUser Over a year ago

Can you give some code example in php as well. How to make that array?

NewUser Over a year ago

no it doesn't work for me. Check my updated question

kamal pal Over a year ago

see my updated answer, you don't need the upper loop, I've used the same column names as you mentioned in your question .. hope it works this time for you too!

NewUser Over a year ago

thanks for the answer but I want to push other data to the array which belongs to that lisiting_id from other table then what should I do?

kamal pal Over a year ago

you can create an another join on other table, or update the $listing_array[] inside while loop, (if not sql, you need to take a key counter like $listing_array[$index] to make the associated values on same index.

Collectives™ on Stack Overflow

Php get result of an array like this

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related