如果多个表的结构不一样,想要在一起查询出来多个表的结果,这样子以前好像不可能的事情!
比如A表:
a_id | title | is_open | last_update |
2 | 测试1 | 1 | 130225252 |
B表:
b_id | name | is_check | last_update |
2 | b测试2 | 0 | 156523322 |
这样两个结构不同的表,如果能够让他数据能够一个列表显示出来。
首先,我们创建一个变量:$moretable
$moretable = array( 'a表'=>array( 'where'=>'is_open', 'zone'=>'title as name,a_id as checkid ,last_update', 'url'=>'a1.php', 'keyword'=>'title' ), 'b表'=>array( 'where'=>'is_check', 'zone'=>'name as name,b_id as checkid,last_update', 'url'=>'b1.php', 'keyword'=>'name' ) )
然后sql.获取数据的总数:
先在php中建立函数
function get_alltable_count($table=array(),$type=0,$user_id,$keywords='') { $info=0; if (is_array($table)) { foreach ($table as $key=>$val) { $sql ="SELECT COUNT(*) FROM ".$GLOBALS['sdk']->table($key)." WHERE $val[where] = '$type' "; if (!empty($keywords)) { $sql.="AND $val[keyword] LIKE '%" . mysql_like_quote($keywords) . "%' "; } $sql .="AND user_id = '$user_id' "; $info += $GLOBALS['db']->getOne($sql); } return $info; } else { return $info; } }
然后,获取数据总数:
$record_count = get_alltable_count($GLOBALS['moretable'],0,$user_id,$filter['keyword']);
然后,获取数据:
function get_alltable_infos($table=array(),$type=0,$user_id,$num = 10, $start = 0, $keywords='') { $arr=array(); if (is_array($table)) { foreach ($table as $key=>$val) { $sql = "SELECT $val[zone] FROM ".$GLOBALS['sdk']->table($key)." WHERE $val[where] = '$type' "; if (!empty($keywords)) { $sql.="AND $val[keyword] LIKE '%" . mysql_like_quote($keywords) . "%' "; } $sql.="AND user_id = '$user_id' ORDER BY last_update DESC"; $res=$GLOBALS['db']->query($sql); while ($row = $GLOBALS['db']->fetchRow($res)) { $row['url'] = $val['url']."?act=edit&id=".$row['checkid']; $row['last_update'] = local_date($GLOBALS['_CFG']['time_format'],$row['last_update']);; $arr[]=$row; } } $arr=array_slice($arr,$start,$num); } return $arr; }
分页函数可以自己写,有了数据总数分页就很好写了。