Search Function stops when using GROUP BY in server processing
Search Function stops when using GROUP BY in server processing
ESHam
Posts: 14Questions: 0Answers: 0
Hello All..
I have a server processing table that queries from 2 tables and groups the results.. When searching the results are not working ERROR 1064 (MYSQL SYNTAX ERROR near WHERE) If i remove Group by from the code everything works fine but i get multiple results from the parent record. Any ideas? Thank you.
[code]
<?php
$aColumns = array( 'accountname', 'accountAddress1', 'accountAddress2', 'accountCity', 'accountState', 'accountZip', 'accountphone', 'accountemail', 'accountWebSite', 'accountFax','tasktitle');
$sIndexColumn = "accountname";
$sTable = "accounts";
$sJoin = "LEFT JOIN tasks on accounts.accountname = tasks.taskresource GROUP BY accounts.accountname";
$gaSql['user'] = "xx";
$gaSql['password'] = "";
$gaSql['db'] = "xx";
$gaSql['server'] = "localhost";
/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}
/*
* MySQL connection
*/
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );
}
if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );
}
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
//$row[] = $OpenAccountTasks;
$row[] = '';
for ( $i=0 ; $i
[/code]
I have a server processing table that queries from 2 tables and groups the results.. When searching the results are not working ERROR 1064 (MYSQL SYNTAX ERROR near WHERE) If i remove Group by from the code everything works fine but i get multiple results from the parent record. Any ideas? Thank you.
[code]
<?php
$aColumns = array( 'accountname', 'accountAddress1', 'accountAddress2', 'accountCity', 'accountState', 'accountZip', 'accountphone', 'accountemail', 'accountWebSite', 'accountFax','tasktitle');
$sIndexColumn = "accountname";
$sTable = "accounts";
$sJoin = "LEFT JOIN tasks on accounts.accountname = tasks.taskresource GROUP BY accounts.accountname";
$gaSql['user'] = "xx";
$gaSql['password'] = "";
$gaSql['db'] = "xx";
$gaSql['server'] = "localhost";
/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}
/*
* MySQL connection
*/
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );
}
if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );
}
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
//$row[] = $OpenAccountTasks;
$row[] = '';
for ( $i=0 ; $i
[/code]
This discussion has been closed.
Replies
$sGroup = "GROUP BY accounts.accountname";
Added $sGroup after $sWhere
Thank you