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
", 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
", 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 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";
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));
}
}
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
", 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();
?>