[URGENT] JOIN two tables is working in SQL editors but not in Datables

Denonth

Hi all I have a problem with my query. It is working normally in SQL editor but Its not working in PHP.

[code]
SELECT
time1.time, time2.time, time1.Signal, v.Name, k.name , time1.reg
FROM
data time1
INNER JOIN data time2
ON time1.id != time2.id
AND time1.Serial = time2.Serial
INNER JOIN data_voz v
ON time1.FK_ID_voz=v.ID_voz
INNER JOIN data_vo k
ON time1.Reg=k.Reg

WHERE
TIMEDIFF(time2.time, time1.time) BETWEEN '00:15:00' AND '00:30:00';
[/code]

The first part of the code until WHERE clause is the same and it is in the $sTable variable, then we come to this WHERE clause, it look like this:
[code]$aWhereAnd[] = " TIMEDIFF(time2.time, time1.time) BETWEEN '".$_GET['stop1']."' AND '".$_GET['stop2']."'";[/code]

This should give me around 1000 records in the table and it is writing me 184000 records and none is shown??

allan

Sounds like the join is going bonkers. I'd suggest that you print out the SQL that is being generated in PHP an compare it to your static query. However, this isn't really a forum for SQL / PHP questions, so if you have an issue with the SQL, it might be an idea to ask in an SQL specialist forum.

Allan

Denonth

Allan can I post my whole PHP script here? I have tried my code in several sql editors and it is working perfectly. So it must be PHP and I have edited version from one guy Chris. I think he actually contacted you about the changes that he made to this script? He made so I can use a multiple tables in one join. So if you can please help me with that. I am sure that this is a PHP problem.

I will post the script when you approve that.

allan

> Allan can I post my whole PHP script here?

You can, but at the moment I don't have enough free time to look through hundreds of lines of code. So I think my previous suggestion stands - just print the SQL statement out and debug that.

Allan

Denonth

Alan,

I tried to use "echo" to print out a SQL query just before it is sent to a database. And when I use exactly the same query in mysql workbench it is working perfectly.

Please don't understand me wrong and I really think that you have more knowledge than me, but generated sql is really working.

That's why I wanted you to see the PHP, it is a 80% same as yours that I found on your website. It just has some additional stuff that we need when making a query.

If you still think that problem is in the SQL query, than I don't know who can really help me.

Denonth

I was searching for this problem but I only found that others were uploading whole scripts to resolve their problems so I'm gonna upload mine and guys please help me as this generated code is working perfectly in sql editors. I have cut some part of the script

[code]
<?php
$aColumns = array( 'time1.Time' ,'time2.Time' , 'time1.Signal' , 'v.Name' , 'v.Lastname' , 'k.Veh_name' , 'time1.Registration , 'time1.Latitude' , 'time1.Longitude' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "time1.id";

/* DB table to use */
$sTable = "{$_SESSION['SESS_MAIN_BASE']} as time1
INNER JOIN {$_SESSION['SESS_MAIN_BASE']} as time2
ON (time1.id != time2.id
AND time1.serial_number = time2.serial_number
AND time1.Latitude=time2.Latitude
AND time1.Longitude=time2.Longitude)
INNER JOIN {$_SESSION['SESS_DRIVER_BASE']} as v
ON time1.FK_ID_driver=v.ID_driver
INNER JOIN {$_SESSION['SESS_VEHICLE_BASE']} as k
ON time1.Registration=k.Registration";

function parse_mysql_field ($sField)
{
/* CW 06/2012
* Takes as string definition of a MySQL database field and return an array describing
* the column name, table name (if present) and alias name (if present)
*/
$sRegex =
// Delimiter
'/'
// Leading white space
. '^\s*'
// Table name
. '(?:(?P(?:`[^`]+`|[\w$]+))\.)?'
// Column name
. '(?P(?:`[^`]+`|[\w$]+))'
// Column alias
. '(?:\s+as\s+(?P(?:(?P[`\'"]?)[\w$]+\4|[\w$]+)))?'
// Delimiter/no case
. '/i';
preg_match($sRegex, $sField, $aMatches);

$aResult = array();

if ( $aMatches['table'] != '')
{
$aResult['table'] = trim( $aMatches['table'], '`' );
}
$aResult['column'] = trim( $aMatches['column'], '`' );
if ( !empty( $aMatches['alias'] ) )
{
if ( !empty( $aMatches['aliasquotetype'] ) )
{
$aResult['alias'] = trim( $aMatches['alias'], $aMatches['aliasquotetype'] );
}
else
{
$aResult['alias'] = $aMatches['alias'];
}
}

return $aResult;

}

if ( ! function_exists( 'json_encode' ) )
{
function json_encode ($mData)
{
/* CW 06/2012
* Replacement for native PHP json_enocde() when unavailable
*/
$bIsObject = FALSE;
switch (TRUE) {
case $mData === NULL: return 'null';
case is_bool($mData): return $mData ? 'true' : 'false';
case is_int($mData) || is_float($mData): return $mData;
case is_string($mData): return '"' . addcslashes($mData, '"\\') . '"';
case is_object($mData):
$bIsObject = TRUE;
$mData = get_object_vars($mData);
case is_array($mData):
$i = 0;
foreach ($mData as $mKey => $mValue)
{
if ($mKey !== $i++)
{
$bIsObject = TRUE;
break;
}
}
$iLength = count($mData);
if ($bIsObject)
{
foreach ($mData as $sKey => $mValue)
{
$mData[$sKey] = json_encode((string) $sKey) . ':' . json_encode($mValue);
}
return '{' . implode ( ',', $mData ) . '}';
}
else
{
foreach ($mData as $iKey => $mValue)
{
$mData[$iKey] = json_encode($mValue);
}
return '[' . implode ( ',', $mData ) . ']';
}
}
}
}


/* CW 06/2012
* Number of columns cached to prevent loop condition on count()
*/
$iNumCols = count($aColumns);

/* CW 06/2012
* Create list of columns for use outside field list
* This allows the use of identifier aliases in $aColumns, and quotes identifiers
* to avoid collisions with MySQL reserved words
*/
$aClauseColumns = array();
for ( $i = 0 ; $i < $iNumCols ; $i++ )
{
/* Parse column string */
$aField = parse_mysql_field( $aColumns[$i] );

/* Create properly quoted strings from parsed values */
$sClauseCol = $sFieldCol = $sTableName = $sColumnName = $sColumnAlias = '';
if ( !empty( $aField['table'] ) )
{
$sTableName = '`' . trim( $aField['table'], '`' ) . '`.';
$sClauseCol .= $sTableName;
$sFieldCol .= $sTableName;
}
$sColumnName = '`' . trim( $aField['column'], '`' ) . '`';
$sClauseCol .= $sColumnName;
$sFieldCol .= $sColumnName;
if ( !empty( $aField['alias'] ) )
{
$sFieldCol .= ' AS `' . $aMatches['alias'] . '`';
}
$aClauseColumns[$i] = $sClauseCol;
$aColumns[$i] = $sFieldCol;
}

/* Individual column filtering */
/* CW 06/2012
* Array use for same reasons as in Ordering: section
*/
$aWhereAnd = array();

if (isset($_GET['stop1'], $_GET['stop2'] ))
{
//$aWhereAnd[] = " TIMEDIFF(time2.Time, time1.Time) BETWEEN '".$_GET['stop1']."' AND '".$_GET['stop2']."'";
}
for ( $i = 0 ; $i < $iNumCols ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && isset( $_GET['sSearch_'.$i] ) && $_GET['sSearch_'.$i] !== '' )
{
$aWhereAnd[] = $aClauseColumns[$i]." LIKE '%".@mysql_real_escape_string($_GET['sSearch_'.$i])."%'";
}
}
if ( count($aWhereAnd) > 0 )
{
if ($sWhere == "")
{
$sWhere .= " WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= implode(' AND ', $aWhereAnd);
}


/*
* Output
*/
$aOutput = array
(
"sEcho" => isset($_GET['sEcho']) ? (int) $_GET['sEcho'] : 0,
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);

if ( mysql_num_rows( $rResult ) > 0 )
{

/* CW 06/2012
* Find the position of the 'version' column if one was defined
*/
$aCols = array_keys( mysql_fetch_assoc( $rResult ) );
mysql_data_seek($rResult, 0);

/* CW 06/2012
* Using _fetch_row() gives us the array in the format we want with no
* messing about. This will be faster and more memory efficient
*/
while ( $aRow = mysql_fetch_row( $rResult ) )
{

$aOutput['aaData'][] = $aRow;
}
}

echo json_encode( $aOutput );
?>

[/code]