Server-side MYSQL/PHP, standard template returning 'null' on a column

organizer

Hello all, just started loving datatables :0)

I'm using 1.9 and the server-side script template but have an issue with one of the colmuns being parsed back JSON wise as NULL. If I execute the query myself the data is there, however somewhere in the very last bit of processing this is not what JSON sends back. I am fairly sure that in the last part of the script, the row value still holds my data, however in the $output... part its gone or the later json_ecode.

$row[] = $aRow[$aColumns[$i]];
}
}
$output['aaData'][] = $row;

echo json_encode( $output );

The only reason I know that likely can be associated is the columns I pick up data from, while all are unique one is not, and requires the name of the table in front of it to be valid. I'm guessing that somehow is connected but don't really see how. I'm no expert on this so I got no idea how to fix at this point. The below "gia_media.media_id" is the tricky column though (it contains a simple short digit number).

$aColumns = array( 'firstname', 'lastname', 'medianame', 'gia_media.media_id', 'printable_name' );

Happy to post the whole thing it it helps, but I've done echos of the results all down to the lines above, so eg. an echo $row[3] placed after the "$row[] = $aRow[$aColumns[$i]];" line above does show that the is there number. But when arrayed into $output['aaData'][] = $row; and later JSON_ENCODED its echoed back as a "NULL" field.

Any tips welcome :)

Chris

organizer

Still having the issue, let me know more details are needed... stuck on this :(

allan

I'd suggest showing out the generated SQL and running that directly against the DB. Does that show nulls? Presumably so, and thus you would want to have a little bit of code that tests for the null data and just make it an empty string is what is presented to the DataTable.

Allan

organizer

Thanks for your suggestion. However as already mentioned the query works just fine and is not the issue from a mysql point of view. I can replace the aColumn with another row that has a number value and the script works. For instance $aColumns = array( 'firstname', 'age'); works fine all the way as age is unique.

But i can't write it like that since I'm pulling from several tables and e.g. age would be ambiguous. So I instead do a $aColumns = array( 'firstname', 'table2.age'); which mysql wise works fine as I've checked he query part. But when going through that last output part of the template script and coverted to JSON it shows as NULL (any column with a table prefix does that to me).

Chris

organizer

Changed default loop that prepared the data from:

$row[] = $aRow[$aColumns[$i] ];

To this and seemingly that works:

$row[] = $aRow[$i];

Don't know if that messes something up yet and if this is a flaw in the original script but leaving that to you expert to correct and comment on.