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.

Member Avatar

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

How are you dates being stored in the database?

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
commented: Yes +7
commented: good spot +15

@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`

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

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

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

@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

Member Avatar

@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.

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

@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.

Member Avatar

Yep: this line:

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

should be:

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

sorry. :(

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