MAX Date issue

[swatisonee]

Simplest of codes and simplest of errors. Darned if i can find it tho.

 

I simply need to select the Max of Dates in a table. This is my code. But I cannot get it to work.

 

$sqlb  = "SELECT MAX(QD)  FROM `Qts` WHERE  `OCID` = '$ocid' " ;

//QD is the date column in YYYY-MM-DD format;
$resultb = mysql_query($sqlb);
$num_rows = mysql_num_rows($resultb);  

for($i = 0; $i < $num_rows; $i++) {

$rowb = mysql_fetch_array($resultb);
$qd = $rowb["QD"];

echo $qd; // should echo out the most recent date if say there were 2 entries of 2011-07-19 and 2011-08-26, then the latter should be output. 
}

 

This doesnt happen. In fact the query doesnt run at all?  What am I missing ?

 

Thanks in advance for guidance !

 

Swati

[Pikachu2000]

Echo the query string along with mysql_error() and see what you get.

[swatisonee]

$resultb = mysql_query($sqlb) or die (mysql_error()); 

 

yields nothing. The query echoes correctly. No error but Max QD doesnt get output.

[PFMaBiSmAd]

Setting error_reporting to E_ALL and display_errors to ON would (definitely) help expose the problem. $rowb["QD"] doesn't exist and would be producing an undefined index error message because you are selecting MAX(QD). You would reference that using $rowb["MAX(QD)"], or more clearly, use an alias name for that select term in the query and reference the alias name or you could reference the value using $rowb[0] (when using mysql_fetch_array or mysql_retch_row)

[Pikachu2000]

Edit: What PFM said ^^^

[swatisonee]

Ok ...let me see if i understood that correctly. This is what i did

<?
error_reporting(E_ALL);
ini_set('display_errors','On');
>?

 

$sqlb  = "SELECT (`QD`)  FROM `Qts` WHERE  `OCID` = '$ocid' " ;

echo $sqlb; // echoes correctly

$resultb = mysql_query($sqlb) or die (mysql_error());

$num_rows = mysql_num_rows($resultb);  

for($i = 0; $i < $num_rows; $i++) {

$rowb = mysql_fetch_array($resultb);
$qd = $rowb["QD"];

echo $qd; // lists all  the dates matching the query.


$qd1 = $rowb["MAX(QD)"]; // Is this correct ? 

echo $qd1; // No output. No error display either .
  

[Pikachu2000]

You know what, since you're only returning one field, and only one record, using mysql_fetch_array, you can echo $rowb[0] and get the value of MAX(QD) that way. There's also no need for a loop since the query should only ever return one record.

[PFMaBiSmAd]

I'm going to guess that in your actual code, the code you have posted is actually inside of some other logic that is false and is being skipped over or you have a fatal parse error and are getting a blank page because the code never runs at all or you are using short open tags and that setting is not enabled on your server so all the code is seen as a HTML tag and not php code.

[swatisonee]

Heres the complete code

<?
error_reporting(E_ALL);
ini_set('display_errors','On');
include ("../include/header.php"); //db info 

include ("logindl.php"); //logging data // common to every scrip

$userid = $_SESSION['userid'];
$ocid = $_POST["ocid"]; // comes from the previous page

$sqlb  = "SELECT (`QD`)  FROM `Quotes` WHERE  `OCID` = '$ocid' " ;

echo $sqlb;

$resultb = mysql_query($sqlb) or die (mysql_error());

$num_rows = mysql_num_rows($resultb);  

for($i = 0; $i < $num_rows; $i++) {

$rowb = mysql_fetch_array($resultb);
$qd = $rowb["QD"];

echo $qd; // this gets echoed correctly - lists all the dates meeting the criteria. Dates are 2011-07-19 , 2011-08-26 etc 

$qd1 = $rowb["MAX(QD)"]; // this doesnt seem to get recognised.Ideally, it ought to output 2011-08-26

echo $qd1;
}


//The foll part can run only when the MAX date gets selected.

$sqlo = "UPDATE Ord SET `OCD` = '$qd1' WHERE `OCID` = '$ocid' " ;
$resulto = mysql_query($sqlo) or die (mysql_error());

?>

[swatisonee]

Pikachu, Doesnt the code need to run thru the entire table to check for all records where OCID = $ocid and then selecting the most recent date?

[swatisonee]

I gave up on MAX.

 

I converted the query as under :

 

$sqlb  = "SELECT (`QD`)  FROM `Qts` WHERE  `OCID` = '$ocid' ORDER BY  `QD` DESC LIMIT 1 " ;" ;

 

Worked. 

[Pikachu2000]

You were close before, and the solution was actually given to you by PFMaBiSmAd, but you changed your query string when that wasn't even necessary. You aren't still using a loop to retrieve one result are you?

[swatisonee]

Sorry Pikachu missed seeing this post. I kept looking at the code and just could not figure out what was wrong - hence posted the whole thing. Then decided to bypass MAX. FWIW, can you look at the code i posted in reply to PFMaBiSmAd . Thanks !

 

You were close before, and the solution was actually given to you by PFMaBiSmAd, but you changed your query string when that wasn't even necessary. You aren't still using a loop to retrieve one result are you?