Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
1  
Your SQL query is not directly linked to your PHP errors. Show the PHP code that gives you the undefined index errors and show us the full error messages. –  Repox Jun 11 '12 at 9:24
    
It just says Undefined index: hit.id and then the same thing for all the columns –  user123_456 Jun 11 '12 at 9:27
1  
You forgot to paste your surrounding PHP code. –  Repox Jun 11 '12 at 9:28
1  
@denonth In PHP, you reference the column names via the column_name, not table_name.column_name. So you would do $id = $row['id']; instead of $id = $row['hit.id'];. If you need to select columns from multiple tables where the columns have the same name, you use SELECT column_name AS alias to distinguish between them in the result set. This is actually a function of MySQL no matter what the client language. –  DaveRandom Jun 11 '12 at 9:30
1  
@denonth In the query above you don't need to use an alias because you are not retrieving any columns with the same name in the field list. Where you would need it is if you where selecting e.g. config.id, then you would need to do something like SELECT hit.id, config.id AS config_id ..., and in PHP the value of config.id would be accessible through $row['config_id'] –  DaveRandom Jun 11 '12 at 9:36
show 8 more comments

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
this helped me... –  user123_456 Jun 15 '12 at 9:56
    
Dave do you have a minute? Meet me in the old chat room. –  user123_456 Jun 20 '12 at 9:17
    
Dave can you help me and give me an idea how can I do this operation so I can put it in the rows for certain column. I have tried this in the sql and it is working but not in this script. Can you give me idea of how to do it in the php or you can change regex? (wa_a.DTI / 100) * wa_b.INCR_PERCENT –  user123_456 Jun 21 '12 at 9:13
    
@denonth If you can get the raw data for the columns wa_a.DTI and wa_b.INCR_PERCENT by specifying them in the field list then yes, no problem, you can just do it in the final while loop - but I'm starting to think that a complete rewrite of the script might be in order to accomplish what you want. The bit that would need re-writing would be the upper part of the script that builds the query, I don't have time to look at it properly now (or, realistically, for the next week or so - I am very busy on a project at work) but I will help you if you are still unable to do it by then. –  DaveRandom Jun 21 '12 at 9:47
    
I'm can't really tell you where to go with it at the moment, for one thing because the table aliases wa_a and wa_b don't exist in the above query, so I don't know how they relate to the rest of the data. –  DaveRandom Jun 21 '12 at 9:49
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.