需要帮助理解MySQL左连接和数据表
我试图让我的头在MySQL。我从网上的信息中学到了很多东西,但是我有点不明白JOIN如何工作以及如何适应DataTables代码。需要帮助理解MySQL左连接和数据表
我有这样的表结构:
而这个MySQL的语句:
SELECT leads.lead_id, leads.date_time, clients.username, courses.course_type, courses.location_name, CONCAT(first_name,' ',surname)
FROM courses
LEFT JOIN leads ON leads.lead_get_course_id = courses.course_id
LEFT JOIN clients ON clients.client_id = courses.course_get_client_id
WHERE leads.checked_by_admin = 'No'
这一切工作正常,当我测试它在Dreamweaver,但是,当我尝试并将其应用于DataTables代码我在'字段列表'中获得未知列'leads.lead_id'
这里是我的DataTable代码:
<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array(
"leads.lead_id",
"leads.date_time",
"clients.username",
"courses.course_type",
"courses.location_name",
"CONCAT(first_name,' ',surname)"
);
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "leads.lead_id";
/* DB table to use */
$sTable = "courses";
$sJoin = 'LEFT JOIN leads ON leads.lead_get_course_id = courses.course_id';
$sJoin = 'LEFT JOIN clients ON clients.client_id = courses.course_get_client_id';
/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "tommy";
$gaSql['db'] = "testingsiasite";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect($gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die('Could not open connection to server');
mysql_select_db($gaSql['db'], $gaSql['link']) or
die('Could not select database '. $gaSql['db']);
/*
* Paging
*/
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1')
{
$sLimit = "LIMIT ".mysql_real_escape_string($_GET['iDisplayStart']).", ".
mysql_real_escape_string($_GET['iDisplayLength']);
}
/*
* Ordering
*/
if (isset($_GET['iSortCol_0']))
{
$sOrder = "ORDER BY ";
for ($i=0 ; $i<intval($_GET['iSortingCols']) ; $i++)
{
if ($_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true")
{
$sOrder .= $aColumns[ intval($_GET['iSortCol_'.$i]) ]."
".mysql_real_escape_string($_GET['sSortDir_'.$i]) .", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY")
{
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
/* */
/* */
$sWhere = "";
if (isset($_GET['sSearch']) && $_GET['sSearch'] != "")
{
$sWhere = "WHERE (";
for ($i=0 ; $i<count($aColumns) ; $i++)
{
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch'])."%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= " AND checked_by_admin='No'";
$sWhere .= ')';
}else{
$sWhere = "WHERE checked_by_admin='No'";
}
/* Individual column filtering */
for ($i=0 ; $i<count($aColumns) ; $i++)
{
if ($_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '')
{
if ($sWhere == "")
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
}
}
/*
* SQL queries
* Get data to display
*/
if($sWhere != ""):
$sWhere .= " AND checked_by_admin='No'";
endif;
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sJoin
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iFilteredTotal, // HERE FOR MASK TOTAL ENTRY not necerrary for others customers
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ($aRow = mysql_fetch_array($rResult))
{
$row = array();
for ($i=0 ; $i<count($aColumns) ; $i++)
{
if ($aColumns[$i] == "version")
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ($aColumns[$i] != ' ')
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$row[] = "<span style='cursor:pointer'><img src='../images/more-info.png' alt='more info' onClick='javascript:my_onclick({$row[0]});'/></span>";
$row[] = "<a href='../leads/actions/approve-lead.php?lead_id={$row[0]}'><img src='../images/approve.png' alt='' /></a>";
$row[] = "<a href='../leads/actions/delete-lead.php?lead_id={$row[0]}' class='delete_link'><img src='../images/delete.png' alt='' /></a>";
$output['aaData'][] = $row;
}
echo json_encode($output);
?>
为什么林收到此错误的任何光线将是巨大的!
好吧,连接将无法使用该PHP代码。即使我不得不与相同的情况作斗争,直到我最终决定创建我自己的联接类来处理数据表,我正在考虑修改它以供所有人使用并释放它。但既然你面对的问题是这个班,我想出了,并没有任何缺陷,虽然我目前没有完美的状态,但我给出的代码认为它可能会帮助你。
class DataTable {
protected $_sTable;
protected $_aColumns = array();
protected $_sJoin = '';
protected $_sWhere_0 = '';
protected $_sGroupBy = '';
protected $_sIndexColumn = 'id';
protected $_iFilteredTotal;
protected $_iTotal;
protected $_iResult;
protected static $_dbh;
public function __construct($sTable, array $aColumns, $sJoin = '', $sWhere_0 = '', $sGroupBy = '', $sIndexColumn = '') {
//This is PDO object for database connection.
if(!isset(self::$_dbh)) self::$_dbh = Config_Database::getInstance();
$this->_sTable = $sTable;
$this->_aColumns = $aColumns;
if($sJoin != '') $this->_sJoin = $sJoin;
if($sWhere_0 != '') $this->_sWhere_0 = $sWhere_0;
if($sGroupBy != '') $this->_sGroupBy = $sGroupBy;
if($sIndexColumn != '') $this->_sIndexColumn = $sIndexColumn;
$this->sQuery();
}
protected function sLimit() {
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = ' LIMIT ' . intval($_GET['iDisplayStart']) . ', ' . intval($_GET['iDisplayLength']);
} else {
$sLimit = '';
}
return $sLimit;
}
protected function sOrder() {
$sOrder = '';
if (isset($_GET['iSortCol_0'])) {
$sOrder = ' ORDER BY ';
for ($i=0 ; $i<intval($_GET['iSortingCols']) ; $i++) {
if ($_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == 'true') {
$sOrder .= $this->_aColumns[ intval($_GET['iSortCol_'.$i]) ].' '.$_GET['sSortDir_'.$i] .', ';
}
}
$sOrder = substr_replace($sOrder, '', -2);
if ($sOrder == ' ORDER BY') {
$sOrder = '';
}
}
return $sOrder;
}
protected function sWhere() {
$sWhere = '';
if ($_GET['sSearch'] != '') {
$sWhere = ' WHERE (';
for ($i=0 ; $i<count($this->_aColumns) ; $i++) {
if ($_GET['bSearchable_'.$i] == "true") {
$sWhere .= $this->_aColumns[$i]." LIKE '%". $_GET['sSearch'] ."%' OR ";
}
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}
/* Conditions */
if($this->_sWhere_0 != '') {
if($sWhere != '') {
$sWhere .= ' AND '.$this->_sWhere_0;
} else {
$sWhere .= ' WHERE '.$this->_sWhere_0;
}
}
/* Individual column filtering */
for ($i=0 ; $i<count($this->_aColumns) ; $i++) {
if ($_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '') {
if ($sWhere == '') {
$sWhere = 'WAHERE ';
} else {
$sWhere .= ' AND ';
}
$sWhere .= $this->_aColumns[$i]." LIKE '%".$_GET['sSearch_'.$i]."%' ";
}
}
return $sWhere;
}
protected function sQuery() {
$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $this->_aColumns)).' FROM '.
$this->_sTable.
$this->_sJoin.
$this->sWhere().
$this->_sGroupBy.
$this->sOrder().
$this->sLimit();
$sth = self::$_dbh->query($sQuery);
$this->_iResult = $sth->fetchAll(PDO::FETCH_NUM);
/* Data set length after filtering */
$sQuery = 'SELECT FOUND_ROWS()';
$sth = self::$_dbh->query($sQuery);
$aResultFilterTotal = $sth->fetchAll(PDO::FETCH_NUM);
$this->_iFilteredTotal = $aResultFilterTotal[0][0];
/* Total data set length */
$sQuery = 'SELECT COUNT('.$this->_sIndexColumn.') FROM '.$this->_sTable;
$sth = self::$_dbh->query($sQuery);
$aResultTotal = $sth->fetchAll(PDO::FETCH_NUM);
$this->_iTotal = $aResultTotal[0][0];
return $this;
}
public function aaData() {
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $this->_iTotal,
"iTotalDisplayRecords" => $this->_iFilteredTotal,
"aaData" => array()
);
return $output;
}
public function iResult() {
return $this->_iResult;
}
}
现在当我需要访问数据表时,它将通过创建上面的DataTable对象的实例来工作。
$sTable = 'table1';
$aColumns = array('table1.column1', 'table1.colmun2', 'table2.column1', 'table2.column2', 'table3.column1');
//Put your logic of join over here, if you don;t have join just leave it empty
$sJoin = '';
//Put your condition over here, if you don;t have any leave it empty.
$sWhere = '';
$dataTable = new DataTable($sTable, $aColumns, $sJoin, $sWhere);
$output = $dataTable->aaData();
$iResult = $dataTable->iResult();
//Change Columns attribute and values
foreach($iResult as $k => $v) {
$iResult[$k][3] = //Change 2nd row with whatever content you like.
}
$output['aaData'] = $iResult;
echo json_encode($output);
我的代码适用于任何数量的工作从任意数量的表连接,它仍然有一定的局限性,我需要的时候我得到的时间工作。
希望它来得有些你有帮助:),
更新:这里是你应该做的事情对你的代码。
//Create new PDO connection.
$host = 'localhoost';
$database = 'database';
$username = 'username';
$password = 'password';
$sDatabase = new PDO('mysql:host='.$host.';dbname='.$database,$username,$password);
//for the database to work with the class change the code in constructor with the below one.
public function __construct($sDatabase, $sTable, array $aColumns, $sJoin = '', $sWhere_0 = '', $sGroupBy = '', $sIndexColumn = '') {
if(!isset(self::$_dbh)) self::$_dbh = $sDatabase;
$this->_sTable = $sTable;
$this->_aColumns = $aColumns;
if($sJoin != '') $this->_sJoin = $sJoin;
if($sWhere_0 != '') $this->_sWhere_0 = $sWhere_0;
if($sGroupBy != '') $this->_sGroupBy = $sGroupBy;
if($sIndexColumn != '') $this->_sIndexColumn = $sIndexColumn;
$this->sQuery();
}
//Datatable config and instance.
$sTable = 'courses';
$aColumns = array(
"leads.lead_id",
"leads.date_time",
"clients.username",
"courses.course_type",
"courses.location_name",
"CONCAT(first_name,' ',surname) as full_name"
);
$sJoin = ' LEFT JOIN leads ON leads.lead_get_course_id = courses.course_id';
$sJoin .= ' LEFT JOIN clients ON clients.client_id = courses.course_get_client_id';
$sWhere = "checked_by_admin='No'";
$dataTable = new DataTable($sDatabase, $sTable, $aColumns, $sJoin, $sWhere);
$output = $dataTable->aaData();
$iResult = $dataTable->iResult();
$output['aaData'] = $iResult;
echo json_encode($output);
让我知道如果你还有其他问题
看起来不错,非常感谢分享它。我试图实现它,但是,努力与什么改变在这里:foreach($ iResult作为$ k => $ v){i = zh-参] $ iResult [$ k] [3] = //更改第二行喜欢。 } – monsterboy
也有点不确定如何将我的数据库连接细节添加到类,感谢您的时间。 – monsterboy
@masterboy,没有必要有任何改变,有时候,我们要显示的图像,或显示href属性或一些HTML代码,那就是的foreach是干什么用的,你可以让它空或删除的foreach,如果你不想改变任何东西。 –
用'SELECT leads.lead_id FROM leads'会发生什么? –
您是否尝试使用“lead_id”而不是“leads.lead_id”? – Arfeen
顺便提一下,外键通常被命名为“ _id”。所以'courses.course_get_client_id'可以简单地命名为'courses.client_id'。 –
halfer