Undefined index in php when executing sql statement

Question

I am getting an error when I execute this query in php:

SELECT hit.timestamp, 
       hit.id, 
       config.Name, 
       hit.meter_id, 
       levels.LevelName, 
       pos.sm_pos, 
       hit.hit_value 
FROM   hit 
       INNER JOIN config 
               ON hit.id = config.id 
       INNER JOIN levels 
               ON hit.meter_id = levels.id 
       INNER JOIN POS 
               ON pos.id = hit.id 
       INNER JOIN controllers 
               ON pos.controller_id = controllers.id; 

Problem is that I am getting undefined index on each column in my select statement except the first one. Fist one is not giving me error and it gives me data normally. Other are giving errors.

Any idea what is wrong? And what I need to change?

EDIT: This query is used when using DataTables script. So fully php code is this when using this script:

<?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( 'hit.timestamp','hit.id', 'config.Name', 'hit.meter_id','levels.LevelName','pos.sm_pos','hit.hit_value'  );

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

    /* DB table to use */
    $sTable = "hit 
       INNER JOIN config 
               ON hit.id = config.id 
       INNER JOIN levels 
               ON hit.meter_id = levels.id 
       INNER JOIN POS 
               ON pos.id = hit.id 
       INNER JOIN controllers 
               ON pos.controller_id = controllers.id";

    /* Database connection information */
    $gaSql['user']       = "";
    $gaSql['password']   = "";
    $gaSql['db']         = "";
    $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
     */
    $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 .= $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++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_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
     */
    $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
    ";
    $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" => $iTotal,
        "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] ];
            }
        }
        $output['aaData'][] = $row;
    }

    echo json_encode( $output );
?>

This last part when outputing is using $row[] = $aRow[ $aColumns[$i] ]; and I think this is where problem is as I am using table.column type in the select statement. And funny thing is that my first column from select statement is going trough and I am getting the values. And others are giving me undefined index.

Answer 1

This problem has been resolved by using a modified version of the DataTables example script and further discussion on SO chat.

I will give a full write up in the form of a proper answer for future visitors as soon as I have time.

---

In a nutshell:

The DataTables sample PHP script does not accommodate the possibility of using column aliases or MySQL function calls in its field list mechanism. This makes any query that performs JOINs or applies some form of data transformation to the results impossible to accomplish without some modification.

The modified version linked above uses regex to safely use field aliases but still does not allow for function calls, which is what was really required to produce the desired result in this instance. This has been worked around by subbing some of the transformation work out to PHP, and a few additional modifications to apply a user defined date range to the returned results.

Full details of exactly what was done and why can be seen by viewing the links above.

---

If there's one thing I have taken away from the process that is potentially useful to future visitors, it is this:

If you need anything more than the very simplest query for use with DataTables, you'd best make damn sure you understand every last line of the back end code and how it interfaces with the front end.

The supplied example scripts do make a good starting point but they are by no means exhaustive in terms of their capabilities, and you need to be prepared to modify or even completely rewrite them in order to achieve your desired result.