SQL and PHP : Select time from datetime when date is current date

[mastubbs]

Hi all,

 

Im new to php and im trying to do something a bit fiddly. I was wondering if anyone knows how...

 

I have a table 'obs' with columns '_sfm_form_submision_time_' (date and time of submission of dataset in format YYYY-MM-DD HH:MM:SS), 'mrn', 'sbp' and a few other variables.

 

I want to echo the sbp and TIME (in the format HH:MM from _sfm_form_submision_time_), as long as the DATE of submission was today (ie date in _sfm_form_submision_time_ is current date) for a given value for mrn (passed from previous page). Ie, if 3 data sets were entered today for mrn 001 then i want to display the times these were entered, and the sbp entered.

 

I have this so far but it keeps telling me no results for mrns that have datasets enetered today, so somewhere the code must be wrong but i can't figure out where! Any help to find the problem would be amazing, thanks!

<?php

//STEP 1 Connect To Database
$connect = mysql_connect("localhost","jasperss_par1","password");
if (!$connect)
{
die("MySQL could not connect!");
}

$DB = mysql_select_db('jasperss_par1pats');

if(!$DB)
{
die("MySQL could not select Database!");
}

//STEP 2 Check Valid Information
if(isset($_GET['mrn']))
{
//STEP 3 Declair Variables
$Search = $_GET['mrn'];
$Find_Query1 = mysql_query("SELECT DATE_FORMAT(_sfm_form_submision_time_,'%H:%i') TIMEONLY, SBP FROM obs WHERE mrn='$Search' AND _sfm_form_submision_time_=CURRENT_DATE()");
if(!$Find_Query1)
{
die(mysql_error());
}

// STEP 4 Get results

while($row = mysql_fetch_assoc($Find_Query1))
{
    echo '<br/> TIME: '.$row['_sfm_form_submision_time_'];
    echo '<br/> SBP: '.$row['SBP'];
	echo '<br/>';
	
}
$numCount = mysql_num_rows($Find_Query1);

// STEP 5 error message if no results

if ($numCount < 1) 
{
print("no sbp found for that mrn today");
}
}

?>

Thanks again!

 

M

Edited May 22, 2013 by mastubbs

[kicken]

Make your where condition AND CONVERT(_sfm_form_submission_time,DATE)=CURRENT_DATE(). Then it will only compare the date portion and ignore the time.

[Barand]

Simpler just to use the DATE function to return the date element of the datetime field

 

WHERE DATE(datefield) = CURDATE()

[mastubbs]

Simpler just to use the DATE function to return the date element of the datetime field

 

WHERE DATE(datefield) = CURDATE()

 

 

Make your where condition AND CONVERT(_sfm_form_submission_time,DATE)=CURRENT_DATE(). Then it will only compare the date portion and ignore the time.

 

 

Perfect, both worked. Thanks.