Column in field list is ambiguous

jimbob72

How do I specify a table field in server side processing which has the same name as a field in a joined table?

I have two tables joined by a common field, and I want to output the 'id' field from one table (the other also has an 'id' field). I've tried:

[code]$aColumns = array('id','fact_code','name','createdby','published','editedby','modified');[/code]
but I get the 'Column 'id' in field list is ambiguous'

I've also tried
[code]$aColumns = array('library_fact.id','fact_code','name','createdby','published','editedby','modified');[/code]

The name of the table is 'library_fact', but this outputs NULL values.

Any help much appreciated...

jimbob72

Anyone?

allan

Hi jimbob72,

I've put together an example modification of my demo server-side processing script which considerers join's here: http://datatables.net/dev/server_processing.txt . It should just be that you need to fill in the variables at the top of the script, much like you have started doing above, including modifying the JOIN statement to whatever you want to do the join on.

Regards,
Allan

jimbob72

Thanks Allan. I've tried this but now I get NULL for all fields, not just for the library_fact.id field.

This is what i've used:

[code]
$aColumns = array('library_fact.id','library_fact.fact_code','library_fund.name','library_fact.createdby','library_fact.published','library_fact.editedby','library_fact.modified');

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

/* DB table to use */
$sTable = "library_fact, library_fund";

/* Join condition */
$sJoin = "library_fact.fact_code = library_fund.code";
...
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$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
WHERE $sJoin
";
[/code]

allan

Could you possibly echo out $sQuery just before line 19 in the above code, and we'll have a look at what the generated SQL looks like and where the issue might be.

Are you getting all the columns and rows you would expect? Its just that the data is null?

Allan

fbas

The issue is that the mysql returns just the end name in the result set (i.e. for library_fact.fact_code, the column name used is "fact_code").

The associative array that the php mysql extension returns thus only uses these names in the result set.

This is not an issue if you are joining (/matching) all columns of the same name because if you have a.id and b.id but a.id = b.id, then it doesn't matter that the associative array $row["id"] represents either/both columns. (and there will be no $row["a.id"] nor $row["b.id"] which is why you are getting NULL).

However it complicates the issue of using a single aColumns array to represent the columns both for the SELECT portion of the query (where you do want the table names qualifying the column) and any code that examines the column name (where you will not have the fully-qualified name).

Without seeing your server side code, I can't give specific recommendations to solve the issue for you.

jimbob72

Thanks Allan and fbas. This is the full server side coding:
[code]<?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('library_fact.id','library_fact.fact_code','library_fund.name','library_fact.createdby','library_fact.published','library_fact.editedby','library_fact.modified');

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

/* DB table to use */
$sTable = "library_fact, library_fund";
//$sTable2 = "library_fund";

/* Join condition */
$sJoin = "library_fact.fact_code = library_fund.code";

/* Database connection information */
include 'dbtable_connection.php';

/*get newsmanager status and username */

$manager = ($_GET['manager'] != "") ? $_GET['manager'] : '0';
$fundmanager = ($_GET['fundmanager'] != "") ? $_GET['fundmanager'] : '0';

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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 array()
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i[/code]

jimbob72

Allan - i echoed out as follows, but it just says 'Query was empty'
[code]/*
* SQL queries
* Get data to display
*/
//$sQuery = "
// SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
// FROM $sTable
// $sWhere
// $sOrder
// $sLimit
//";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";[/code]

allan

If you modify this bit:

[code]
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable 
        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
[/code]

to be:


[code]
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable 
        $sWhere
        $sOrder
        $sLimit
    ";
echo $sQuery;
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
[/code]

What do you get in the JSON return (DataTables will alert an error when you put this in btw).

Allan

jimbob72

Ah yes, echo, ahem.

I get
SELECT SQL_CALC_FOUND_ROWS library_fact.id, library_fact.fact_code, library_fund.name, library_fact.createdby, library_fact.published, library_fact.editedby, library_fact.modified
FROM library_fact, library_fund

ORDER BY library_fact.editedby
desc
LIMIT 0, 10
{"sEcho":1,"iTotalRecords":"130","iTotalDisplayRecords":"17688","aaData":[[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null],[null,null,null,null,null,null,null]]}

allan

Ah - okay thanks for that. I think I see what the problem is. Its trying to do a column name match on the query from the array of table.column_names to the column names returned by the SQL engine. However, by default MySQL will strip off the table names from the column...! Hence why you are getting null returns (e.g. its trying to look for "ajax.user", and returning "user").

So what you needs is the part from my uploaded script that deals with that - line 120:

[code]
/*
* Select list
*/
$sSelect = "";
for ( $i=0 ; $i

jimbob72

Yup. That did it. Many thanks again!

fbas

@allan Lol. clever. simple solution to the problem. I would have made a more convoluted situation.

jpaalmeida

I use postgresql, is returning null please for example by... thanks (i'm from Brazil)