按照cakephp group by table的格式在核心php中检索数据

我想以cakePHP的格式检索数据,如group by table(model)

喜欢：

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

每当我们触发连接查询时,按表检索所有数据组.

那怎么办呢？

表格区域的表结构和值：

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

表类别的表结构和值

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

表用户的表结构和值：

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),'tejash',2);

最佳答案

下面是实现所需数组的代码：

$sql = "select user.*,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
                )

        )

    ...

)

希望这会对你有所帮助.

按照cakephp group by table的格式在核心php中检索数据

猜你在找的MySQL相关文章