Notice: Undefined index: MAX(Date)

[travelkind]

I am getting the following error when I try to run my php script:

 

Notice: Undefined index: MAX(Date) in D:\Pricing\EasyPHP-5.3.9\www\FT9 05-16-12.php on line 21

The most recent date is:

 

The most recent or MAX Date should be returned after the echo statement "The most recent date is:"

 

Here is my code:

 

<?php


        $conn=odbc_connect("XXX","xxx|xxx","xxx");
        if (!$conn)
        {	exit("Connection Failed: " . $conn);
}

// Get a specific result from the "FT9_FUEL_TAX_PRICE_LINES" table
$query =("SELECT ItemNumber,MAX(Date) FROM 'FT9_FUEL_TAX_PRICE_LINES'
         WHERE ItemNumber = 'E-10 NL GAS'AND TableCode = 'CITA'
         GROUP BY 'ItemNumber'");

$result = odbc_exec($conn, $query) or die(odbc_errormsg());


// keeps getting the next row until there are no more to get
while($row=odbc_fetch_array($result))
{
// Print out result
echo "The most recent date is: " .$row['MAX(Date)']; 
        echo "<br />";

} 

?>

 

Can anyone help me with this?  Any help would be much appreciated.

[mrMarcus]

That's because you're doing it wrong

 

Add an alias to the MAX(Date) in your query like so:

 

$query =("SELECT ItemNumber, MAX(`Date`) as `max_date` FROM 'FT9_FUEL_TAX_PRICE_LINES'
         WHERE ItemNumber = 'E-10 NL GAS'AND TableCode = 'CITA'
         GROUP BY 'ItemNumber'");

 

And change your echo result to reflect that alias:

 

echo "The most recent date is: " .$row['max_date'];

 

Get it?

 

Same as if you wanted to print `ItemNumber`, you do:

 

echo $row['ItemNumber'];

[Maq]

in the future, place

OR

tags around your code.

[mrMarcus]

Just noticed, too, that you have your table name encapsulated in single-quotes.  That will not work.  Same for 'ItemNumber' in your GROUP BY.  Either leave without, or use ticks (better practice is to use ticks ` as MySQL haa reserved keywords that will most likely creep in at some point in time):

 

$query ="SELECT ItemNumber, MAX(`Date`) as `max_date` FROM `FT9_FUEL_TAX_PRICE_LINES`
         WHERE ItemNumber = 'E-10 NL GAS' AND TableCode = 'CITA'
         GROUP BY `ItemNumber`";

 

Edit: table names, field/column names cannot be surrounding with quotes of any kind.  Leave them alone, or as recommended, use ticks.

[travelkind]

Hey guys thanks for the input but now I am getting the following Warning:

 

Warning: odbc_exec() [function.odbc-exec]: SQL error: [ProvideX][ODBC Driver][FILEIO]Table is not accessible, SQL state S0000 in SQLExecDirect in D:\Pricing\EasyPHP-5.3.9\www\FT9 05-16-12 Night Version.php on line 15

[ProvideX][ODBC Driver][FILEIO]Table is not accessible

 

Here is my revised code I must have missed something!

 

<?php


        $conn=odbc_connect("XXX","xxx|xxx","xxx");
        if (!$conn)
        {	exit("Connection Failed: " . $conn);
}

// Get a specific result from the "FT9_FUEL_TAX_PRICE_LINES" table
$query ="SELECT ItemNumber, MAX(`Date`) as `max_date` FROM `FT9_FUEL_TAX_PRICE_LINES`
         WHERE ItemNumber = 'E-10 NL GAS' AND TableCode = 'CITA'
         GROUP BY `ItemNumber`";


$result = odbc_exec($conn, $query) or die(odbc_errormsg());


// keeps getting the next row until there are no more to get
while($row=odbc_fetch_array($result))
{
// Print out result
echo "The most recent date is: " .$row['max_date']; 
        echo "<br />";

} 

?>

[mrMarcus]

Sounds like there's a permissions issue with accessing the table.  No longer a PHP issue, but an Access issue.

[Maq]

travelkind, in the future, place

OR

tags around your code.

[travelkind]

There is an issue with the ODBC connection so I created a local Mysql database and the code ran perfectly!  I will have to look into ODBC issue but I appreciate all the help you guys gave me on this!  Thanks again!