WHERE DATE BETWEEN '$fromDate' AND '$toDate' query

[kalaban]

good day guys;
I hope somebody could help me again with my problem
I'm working with this for a month now and It seems I cant analyze what's wrong with my code.
now here's what I want to happen

I want to count and pull the records from a table called `medicalrecords` where the date is (exclusive) from $fromDate to $toDate.

here's my code:

<!------- DOWnloaded Date Picker --------------------->
 <form action="" method="POST" name="datepick">
        <label for="datefrom">From:</label>
        <input type="Text" id="datefrom" name="datefrom" value="<?php echo $_GET['datefrom']; ?>"  maxlength="25" size="25"/>
        <img src="images/cal.gif" onclick="javascript:NewCssCal('datefrom')" style="cursor:pointer"/>
        <label for="dateTo">To:</label>
        <input type="Text" id="dateto" name="dateto" value="<?php echo $_GET['dateto']; ?>" maxlength="25" size="25"/>
        <img src="images/cal.gif" onclick="javascript:NewCssCal('dateto')" style="cursor:pointer"/>
        <input type="submit" value="Go" />
</form>

</div>
<br />
<div id="countdept">
<!-------------------------------- the query ------------------------------->
<?php if (isset($_POST['datefrom']) && isset($_POST['dateto'])) {
	$datefrom = $_POST['datefrom'];
	$dateto = $_POST['dateto'];
	$fromDate = strtotime('Y-m-d', $datefrom);
	$toDate = strtotime('Y-m-d', $dateto);
	
		if (!empty($datefrom) && !empty($dateto)) {
			
			$queryOne = "SELECT diagnosisName, COUNT(1) as numberOfRecords FROM medicalrecords
							Inner Join diagnosis ON medicalrecords.diagnosisId = diagnosis.diagnosisId
								WHERE DATE BETWEEN  '$fromDate' AND '$toDate'
									GROUP BY diagnosisName";
									
			$queryOne_run = mysql_query($queryOne);
			$queryOne_num_rows = mysql_num_rows($queryOne_run);
			$rsReport = mysql_fetch_assoc($queryOne_run);
	?>
 <!------------ table --------------------->
 <table border="2" cellpadding="2" cellspacing="2" align="center">
   <tr bgcolor="#00ccff">
     <th>Sickness</th>
     <th>Number Of Patient</th>
   </tr>
   <?php do { ?>
     <tr>
       <td><?php echo $rsReport['diagnosisName']; ?></td>
       <td><?php echo $rsReport['numberOfRecords']; ?></td>
     </tr>
     <?php } while ($queryOne_num_rows = mysql_fetch_assoc($queryOne_run)); 
	 
		}
}
?>
 
 </table>

if you have a more suitable form for this, please share it to me.. it would be a greet help. I'm bloody stuck with this problem.

[diafol]

Where's the $_GET vars coming from? In what format are the $_GET and $_POST date variables?

[phper]

How are you dates being stored in the database?

[|-|x]

WHERE DATE BETWEEN

Correct me if I'm wrong but isn't DATE a reserved word?

If that's the name of your field in the `medicalrecords` table, you may need to use backticks around it, ie:

WHERE `DATE` BETWEEN

[kalaban]

@sir ardav;
awts sowe I didn't review my code
it should be a $_POST (I already change it) variable, not $_GET sorry for that..
the $datefrom and $dateto variables are from the datepick form...

@sir phper;
data type is dateTime the format is yyyy-mm-dd (2012-02-21), I hope it help.

@sir Hearth;
thank you for the tip I'll put backticks in `date`

[vaultdweller123]

awts sowe lol :D, btw so what's wrong? i mean is what is the problem? you have error? or it isn't displaying anything

[kalaban]

my problem is only the table header is showing up in the table not the query result itself.

[vaultdweller123]

have you tried echoing the $datefrom and $dateto results? they could be empty. Does your query work? try running your query on phpmyadmin SQL to test if it works

[|-|x]

actually I just noticed, strtotime is not the correct function to format your date string.

Try using date('Y-m-d', $datefrom) instead.

[kalaban]

@sir vaultdweller123;
I already tried echoing $datefrom and $dateto and it's working just fine..
I also run my query in Navicat it's running as it should.

@sir Hearth;
I get rid of the strtotime and revise my code into this

<?php if (isset($_POST['datefrom']) && isset($_POST['dateto'])) {
	
	$datefrom = $_POST['datefrom'];
	$dateto = $_POST['dateto'];

		if (!empty($datefrom) && !empty($dateto)) {
			
$superQuery = "SELECT diagnosis.diagnosisName, COUNT(*) as numberOfRecords FROM medicalrecords
Inner Join diagnosis ON medicalrecords.diagnosisId = diagnosis.diagnosisId
GROUP BY diagnosisName";
$run_superQuery = mysql_query($superQuery);
$superNumRows = mysql_num_rows($run_superQuery);
$SuperRecordSet = mysql_fetch_assoc($run_superQuery);
?>

<table width="200" border="1" cellspacing="1" cellpadding="1">
  <tr>
    <th scope="col">Diagnosis</th>
    <th scope="col">Number of Records</th>
  </tr>
  <?php do {?>
  <tr>
    <td><?php echo $SuperRecordSet[diagnosisName];?></td>
    <td><?php echo $SuperRecordSet[numberOfRecords]; ?></td>
  </tr>
  <?php } while ($superNumRows = mysql_fetch_assoc($run_superQuery)); ?>
</table>
<?php
		}
}
?>

but the result is look like this

and when I put

WHERE `date` BETWEEN $datefrom AND $dateto

in my $superQuery.
it became this

[diafol]

@sir phper;
data type is dateTime the format is yyyy-mm-dd (2012-02-21), I hope it help.

But your datepicker shows: dd-mm-yyyy or mm-dd-yyyy.

It seems you are trying to compare dates in different formats.

If this is the case, you could change the datepicker data ($_POST) to DB format like this::

function pick2db($date){
   $bits = explode("-",$date);
   return implode('-', array($bits[2],$bits[1],$bits[0]);
 //return implode('-', array($bits[2],$bits[0],$bits[1]); //if format was mm-dd-yyyy  
}

$datefrom = pick2db($_POST['datefrom']);
$dateto = pick2db($_POST['dateto']);

then use them in your SQL. However, you should validate the dates prior to insertion.

[|-|x]

2 things I see there...

don't forget to quote your array indexes echo $SuperRecordSet['diagnosisName']; your loop is incrementing to the wrong variable, while ($superNumRows = mysql_fetch_assoc($run_superQuery)) should be using $SuperRecordSet

[kalaban]

@sir ardav;
thank you for your precious time sir
but I have an error when I apply the function pick2db()

Parse error: syntax error, unexpected ';' in F:\xampp\htdocs\Fsite\reports.php on line 272

ScreenShot of code

I did also configure the date format directly from datetimepicker_css.js file but I wasn't so sure if I did it correctly.. I hope you would have a look the file for me.. I read the documentation but there's not instruction how to format the output date.

@sir Heart;
also thank you for your time sir;
I already did quoted my array indexes and change the mysql_fetch_assoc($run_superQuery)) into $SuperRecordSet
and it just show me the same result.

[diafol]

Yep: this line:

return implode('-', array($bits[2],$bits[1],$bits[0]);

should be:

return implode('-', array($bits[2],$bits[1],$bits[0]));

sorry. :(

[diafol]

but there's not instruction how to format the output date.

Yes there is:

http://www.rainforestnet.com/datetimepicker/datetimepicker-demo1.htm

[kalaban]

@sir ardav;
once again you solve my problem,
thank you so much for your kindness and time.^^v
have a good day.