Retrieve data in the core php in the format of cakephp group by table

Question

I want to retrieve data in the format of cakephp like group by table (model)

Like:

select user.*,category.*,area.* from user 
left join category on user.cat_id=category.id
left join area on user.area_id=area.id

array(
     [0] => Array(
               [user]=>array(
                    [user_id] => 1
                    [user_name] => test user
                )
               [category]=>array(
                    [category_id] => 1
                    [category_name] => test cat
                )
               [area]=>array(
                    [area_id] => 1
                    [area_name] => area1
                )
     )
    [1] => Array(
               [user]=>array(
                    [user_id] => 2
                    [user_name] => test user
                )
               [category]=>array(
                    [category_id] => 2
                    [category_name] => test cat
                )
               [area]=>array(
                    [area_id] => 2
                    [area_name] => area2
                )
    )
)

Whenever we fire join query retrieves all data group by table.

So how to do that?

Table structure and values for table area:

CREATE TABLE IF NOT EXISTS `area` (
  `id` int(11) NOT NULL,
  `a_name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `area` (`id`, `a_name`) VALUES
(1, 'bapunagar'),
(2, 'bopal');

Table structure and values for table category

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `category` (`id`, `c_name`) VALUES
(1, 'food');

Table structure and values for table user:

CREATE TABLE IF NOT EXISTS `user` (
  `u_id` int(11) NOT NULL AUTO_INCREMENT,
  `u_name` varchar(20) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `area_id` int(11) NOT NULL,
  PRIMARY KEY (`u_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `user` (`u_id`, `u_name`, `cat_id`, `area_id`) VALUES
(1, 'pragnesh', 1, 1),
(2, 'tejash', 1, 2);

Answer 1

Below is the code to achieve your desired array:

$sql = "select user.*,category.*,area.* from user left join category on user.cat_id=category.id left join area on user.area_id=area.id";
$res = mysql_query ($sql);

$finalArray = array();

while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
    $array = array();
    for ($i = 0; $i < mysql_num_fields($res); ++$i) {
        $table = mysql_field_table($res, $i);
        $field = mysql_field_name($res, $i);

        $array[$table][$field] = $row[$field];
    }

    $finalArray[] = $array;
}
print_r($finalArray);    



Outputs as:

Array
(
    [0] => Array
        (
            [user] => Array
                (
                    [u_id] => 1
                    [u_name] => pragnesh
                    [cat_id] => 1
                    [area_id] => 1
                )

            [category] => Array
                (
                    [id] => 1
                    [c_name] => food
                )

            [area] => Array
                (
                    [id] => 1
                    [a_name] => bapunagar
                )

        )

    [1] => Array
        (
            [user] => Array
                (
                    [u_id] => 1
                    [u_name] => pragnesh
                    [cat_id] => 1
                    [area_id] => 1
                )

            [category] => Array
                (
                    [id] => 1
                    [c_name] => food
                )

            [area] => Array
                (
                    [id] => 1
                    [a_name] => bapunagar
                )

        )

    ...

)

Hope this would help you.