drpogkqqi536984960 2013-08-01 06:31
浏览 93
已采纳

如何在zf2中使用外键从表中获取数据?

Please help me!I need fetch field from table 'restaurant' - restaurant_longitude.How to do this in zf2 using tableGateway or Zend\Db\Select when i have one table('user_favorite') with foreign key to table 'restaurant'-restaurant_id.For example:return field with expression

 SELECT restaurant_longitude from restaurant,user_favorite where user_favorite.restaurant_id = restaurant.restaurant_id

my clearly sql statement

 select restaurant_longitude,restaurant_latitude from user_favorite join restaurant on user_favorite.restaurant_id = restaurant.restaurant_id where user_favorite.display_name = 'admin'

user_favorite.sql;

CREATE TABLE IF NOT EXISTS `user_favorite` (
  `user_favorite_id` int(11) NOT NULL AUTO_INCREMENT,
  `display_name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `restaurant_id` int(11) DEFAULT NULL,
  `attraction_id` int(11) DEFAULT NULL,
  `user_favorite_timestamp` date NOT NULL,
  PRIMARY KEY (`user_favorite_id`),
  KEY `display_name` (`display_name`),
  KEY `restaurant_id` (`restaurant_id`),
  KEY `attraction_id` (`attraction_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=42 ;

ALTER TABLE `user_favorite`
  ADD CONSTRAINT `user_favorite_ibfk_1` FOREIGN KEY (`display_name`) REFERENCES `user` (`display_name`),
  ADD CONSTRAINT `user_favorite_ibfk_3` FOREIGN KEY (`attraction_id`) REFERENCES `attraction` (`attraction_id`),
  ADD CONSTRAINT `user_favorite_ibfk_4` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant` (`restaurant_id`);

restaurant.sql

CREATE TABLE IF NOT EXISTS `restaurant` (
  `restaurant_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Уникальный идентификатор ресторана',
  `restaurant_id_name` varchar(100) NOT NULL COMMENT 'Идентификатор ресторана для маршутизации',
  `restaurant_name` varchar(100) NOT NULL COMMENT 'Имя ресторана',
  `restaurant_mode` varchar(100) NOT NULL COMMENT 'Тип ресторана',
  `restaurant_description` varchar(1000) NOT NULL COMMENT 'Описание ресторана',
  `restaurant_thumbnail` varchar(100) NOT NULL COMMENT 'Главный рисунок ресторана',
  `restaurant_image_1` varchar(100) NOT NULL,
  `restaurant_image_2` varchar(100) NOT NULL,
  `restaurant_image_3` varchar(100) NOT NULL,
  `restaurant_features` varchar(200) NOT NULL COMMENT 'Возможности ресторана',
  `restaurant_dj` varchar(20) NOT NULL,
  `restaurant_wifi` varchar(20) NOT NULL,
  `restaurant_karaoke` int(5) NOT NULL,
  `restaurant_kalian` int(5) NOT NULL,
  `restaurant_chill_out` int(5) NOT NULL,
  `restaurant_sigarette_room` int(5) NOT NULL,
  `restaurant_live_music` int(5) NOT NULL,
  `restaurant_veranda` int(5) NOT NULL,
  PRIMARY KEY (`restaurant_id`),
  KEY `restaurant_id_name` (`restaurant_id_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Таблица для описания ресторанов в городе Ялта' AUTO_INCREMENT=95 ;

in UserFavoriteTable.php

 public  function getFavoriteByUsername($display_name){

        $resultSet = $this->tableGateway->select(function (Select $select) use($display_name){   
            $select->where('user_favorite.display_name = ' . $display_name)
                ->join('restaurant', 'user_favorite.restaurant_id = restaurant.restaurant_id',array('restaurant_longitude'));
        });
        return $resultSet;
     }

in controller

 //username from route array in module.config.php
 $display_name = (string)$this->params()->fromRoute('username','');
 $favorite = $this->getFavoriteTable()->getFavoriteByUsername($display_name);
 $view->setVariable('favorite',$favorite);

but it not works fine returns only nulls( favorite.phml

 <? foreach($favorite as $fav): {?>
                            <? echo var_dump($fav->restaurant_longitude); ?>
                        <? }endforeach; ?>
  • 写回答

1条回答 默认 最新

  • duanfuxing2212 2013-08-01 07:53
    关注

    EDIT: okay, so apparently you don't need to make the join with the users table, you just go by display name. So this is how it's done.

    public  function getFavoriteByUsername($username){
         $resultSet = $this->tableGateway->select(function (Select $select) use($username){    // Here's how you can pass params to a select statement!
         $select->where('display_name = ' . $username)
             ->join('restaurant', 'user_favorite.restaurant_id = restaurant.restaurant_id',array('restaurant_longitude', 'restaurant_latitude');
         });
    return $resultSet;
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥30 vb net 使用 sendMessage 如何输入鼠标坐标
  • ¥200 求能开发抖音自动回复卡片的软件
  • ¥15 关于freesurfer使用freeview可视化的问题
  • ¥100 谁能在荣耀自带系统MagicOS版本下,隐藏手机桌面图标?
  • ¥15 求SC-LIWC词典!
  • ¥20 有关esp8266连接阿里云
  • ¥15 C# 调用Bartender打印机打印
  • ¥15 我这个代码哪里有问题 acm 平台上显示错误 90%,我自己运行好像没什么问题
  • ¥50 C#编程中使用printDocument类实现文字排版打印问题
  • ¥15 找会编程的帅哥美女 可以用MATLAB里面的simulink编程,用Keil5编也可以。