Mysql多表数据整合问题实例

问题描述:

多表数据整合

 

创建数据库代码:

 

/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Version : 50162
Source Host : localhost:3306
Source Database : hetong

Target Server Type : MYSQL
Target Server Version : 50162
File Encoding : 65001

Date: 2015-06-30 18:48:32
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `t1`
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`dk` int(11) DEFAULT NULL,
`db` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES ('1', '1000', '1000');
INSERT INTO `t1` VALUES ('2', '2000', null);
INSERT INTO `t1` VALUES ('3', '3000', null);

-- ----------------------------
-- Table structure for `t2`
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`db` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES ('2', '1000');
INSERT INTO `t2` VALUES ('3', '3000');
INSERT INTO `t2` VALUES ('4', '1000');

-- ----------------------------
-- Table structure for `t3`
-- ----------------------------
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`dk` int(11) DEFAULT NULL,
`dy` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t3
-- ----------------------------
INSERT INTO `t3` VALUES ('2', null, '1000');
INSERT INTO `t3` VALUES ('4', '4000', '3000');

数据库类创建:

<?php
class hetong {
private $id;
private $dk;
private $db;
private $dy;

public function __get($name){
return $this->$name;
}
public function __set($name, $value){
$this->$name = $value;
}
}

test.php代码实例:

<?php
header("content-type:text/html;charset=utf-8");
require_once('hetong.php');

$dsn="mysql:dbname=hetong;host=localhost";
$db_user='root';
$db_pass='admin123';

try{
$pdo=new PDO($dsn,$db_user,$db_pass);
}catch(PDOException $e){
echo '数据库连接失败'.$e->getMessage();
}
//查询t1
$sql="select * from t1";
$res=$pdo->query($sql);
$data = array();

foreach($res as $row){
$data[$row[id]]->id = $row[id];
$data[$row[id]]->dk = $row[dk];
$data[$row[id]]->db = $row[db];
$data[$row[id]]->dy=null;
}

//查询t2
$sql="select * from t2";
$res=$pdo->query($sql);
foreach($res as $row){
$data[$row[id]]->id = $row[id];
if(!$data[$row[id]]->db){
$data[$row[id]]->db=$row['db'];
}
}

//查询t3
$sql="select * from t3";
$res=$pdo->query($sql);
foreach($res as $row){
$data[$row[id]]->id = $row[id];
if(!$data[$row[id]]->dk){
$data[$row[id]]->dk = $row[dk];
}
if(!$data[$row[id]]->dy){
$data[$row[id]]->dy=$row['dy'];
}
}
var_dump($data);

foreach($data as $row){
echo $row->id.' '.$row->dk.' '.$row->db.' '.$row->dy.'<br>';
}

运行效果图:

多表整合效果

 

上班时间仓促,花了几分钟研究了一下,肯定有更好的解决方法,后续研究!

打赏此文

如果您觉得本站的内容对您有所帮助,您可以扫描下面的二维码小额支付请我喝杯茶,感谢!打赏记录
支付宝
微信
承诺:凡打赏捐助的朋友,留言备注自己的邮箱,在打赏捐助时间点的6个月内,本站会每周邮件推送原创专业技术博文,供大家学习和参考!

1 条评论

  1. yun说道:

    学习学习,研究研究,呵呵

留下评论

All fields marked (*) are required