[SOLVED] between dates logic returning '00'...

[rsammy]

i looked around and found a few threads on how to use "between dates". i used it exactly the same way as suggested in some of those threads. but, i still get a '00' for the num_rows returned.

 

thsi is my query...

$queryexec="SELECT DISTINCT admission.pat_id, pat_dgraphics.pat_first_name, pat_dgraphics.pat_last_name, admission.admit_phy_id, admission.admit_loc,
		admission.admit_encounter, admission.admit_date, DATE_FORMAT(admission.admit_date, '%m/%d/%Y') as admit_dates,
		DATE_FORMAT(admission.discharge_date, '%m/%d/%Y') as discharge_date, DATEDIFF(admission.discharge_date, admission.admit_date) + 1 as days
		FROM admission, pat_dgraphics
		WHERE admission.discharge_date != '0000-00-00' AND admission.admit_date <= '$dates' AND admission.admit_date >= '$datez'
		AND admission.pat_id=pat_dgraphics.pat_ID  AND pat_dgraphics.pat_first_name like '$patfname%'
		AND pat_dgraphics.pat_last_name like '$patlname%' AND admission.admit_loc like '$location%' AND admission.admission_client_id like '$client_id'
		AND DATE_FORMAT(pat_dgraphics.pat_dob, '%m/%d/%Y') like '$patdob'
		UNION
		SELECT admission.pat_id, pat_dgraphics.pat_first_name,
		pat_dgraphics.pat_last_name, admission.admit_phy_id, admission.admit_loc, admission.admit_encounter, admission.admit_date,
		DATE_FORMAT(admission.admit_date, '%m/%d/%Y') as admit_dates, DATE_FORMAT(admission.discharge_date, '%m/%d/%Y') as discharge_date,
		CONCAT(DATEDIFF(curdate(), admission.admit_date) + 1, ' <b><font color=green>ACTIVE</font></b>') as days
		FROM admission, pat_dgraphics
		WHERE admission.discharge_date = '0000-00-00' AND admission.admit_date <= '$dates' AND admission.admit_date >= '$datez'
		AND admission.pat_id=pat_dgraphics.pat_ID  AND pat_dgraphics.pat_first_name like '$patfname%' AND pat_dgraphics.pat_last_name like '$patlname%'
		AND admission.admit_loc like '$location%' AND admission.admission_client_id like '$client_id'
		AND DATE_FORMAT(pat_dgraphics.pat_dob, '%m/%d/%Y') like '$patdob' ORDER BY admit_date DESC, pat_last_name ASC, pat_first_name ASC";

 

i display all the rows for this query and for each row, i want to display the details (name patient#, location, activate date, # of days and # of encounters) like this...

				if ($result=mysql_query($queryexec))
				//print "$queryexec";
				{
				$num_rows=mysql_num_rows($result);

				$num_results=mysql_num_rows($result);

 

and then i print the details for each row here...

 

				$row = mysql_fetch_object($result);
				$pat_id=($row->pat_id);
				$admit_dates=($row->admit_dates);
				$discharge_date=($row->discharge_date);
				$admit_date=($row->admit_date);
				$queryz="select pat_first_name, pat_last_name, pat_ssn from pat_dgraphics where pat_ID='$pat_id' ";
				//echo "$queryz";
				$resultz=mysql_query($queryz);
				$rowz=mysql_fetch_object($resultz);

				$pat_ssn=($rowz->pat_ssn);


				print("<td height=\"15\"> <div align=\"left\">");

				print($rowz->pat_last_name);

				print(", ");
				print($rowz->pat_first_name);

				print("</div></td>");

				print("<td height=\"15\"> <div align=\"left\">");

				print($pat_ssn);

				print("<td height=\"15\"> <div align=\"left\">");

 

now, i want to print the # of enconters as the num_rows of the following query...

				print("<td height=\"15\"> <div align=\"center\">");

				//Number of Encounters

				$encounterquery="SELECT distinct visit_mgr.visit_pat_id, DATE_FORMAT(visit_mgr.visit_date, '%m/%d/%Y') as visit_date
								 FROM visit_mgr, admission, pat_dgraphics
								 WHERE visit_mgr.visit_pat_id=pat_dgraphics.pat_ssn
								 AND pat_dgraphics.pat_ID=admission.pat_id
								 AND admission.pat_id='$pat_id'
								 AND visit_mgr.visit_date between '$row->admit_dates' AND '$row->discharge_date'
								 ";


				$resultencounterquery=mysql_query($encounterquery);
				$num_rowsencounterquery = mysql_num_rows($resultencounterquery);


				print($num_rowsencounterquery);

				print("</div></td>");

 

but this ($num_rowsencounterquery) comes up as '00'. 

 

help please

[per1os]

if the field is date in mysql the date value should be surrounded by #'s

 

IE:

 

WHERE mydate = '#01/10/2007#'

 

Maybe that is where the issue lies?

[rsammy]

thanx for ur reply! still not clear. how do i use the '#' in the comparision variables...say '$admit_dates' and '$discharge_date'

[per1os]

'#".$admit_dates."#'

[Barand]

if the field is date in mysql the date value should be surrounded by #'s

 

IE:

 

WHERE mydate = '#01/10/2007#'

 

Maybe that is where the issue lies?

Sorry, but what a load of crap! You're using mysql here, not Access.

 

If it's a date field with mysql then

 

WHERE mydate = '2007-01-10'

[per1os]

Is that so, than I guess I am a load of crap, I usually don't use the date/time column attiribute int(11) with the unix time stamp is soo much easier =)

 

My bad for leading this person astray.

 

 

[rsammy]

thanx barand and frost for taking time to reply! issues resolved