sql help

[vj5]

Below is my sql statement:

$sql = "SELECT UCASE(ph.groupno) as groupno, ph.name, ph.asof , p.managing_broker_id, 				 b.FirstName, b.Lastname,				 ph.base, ph.current, ph.`gro/decline`				FROM `dbBkrPol`.Policies p INNER JOIN `dbBkrPol`.persHorizon ph ON p.GroupNumber = ph.groupno				INNER JOIN dbBkrPol.Brokers b ON p.managing_broker_id = b.PersonID where 1";   if (strlen($groupno)!=0) {$sql = $sql." AND `groupno` LIKE '%".$groupno."%'";}   if (strlen($groupname)!=0) {$sql = $sql." AND `name` LIKE '%".$groupname."%'";}   if (strlen($asofstart)!=0) {$sql = $sql."AND `asof` BETWEEN '".$asofstart."' and '".$asofend."'";}   $sql = $sql." ORDER BY `asof` DESC";   $RESULT = mysql_query($sql, $objConn);   $CNT1 = mysql_num_rows($RESULT);

I am able to search based on the between two dates. $asofstart and $asofend comes from the same field and it is varchar data type. If I search just on $asofstart, it gives an empty result set, even though I have data in the table. How can I rectify this issue?When echoed, got this:

SELECT UCASE(ph.groupno) as groupno, ph.name, ph.asof , p.managing_broker_id, b.FirstName, b.Lastname, ph.base, ph.current, ph.`gro/decline` FROM `dbBkrPol`.Policies p INNER JOIN `dbBkrPol`.persHorizon ph ON p.GroupNumber = ph.groupno INNER JOIN dbBkrPol.Brokers b ON p.managing_broker_id = b.PersonID where 1 AND `groupno` LIKE '%243y0%' AND `name` LIKE '%scarrone%'AND `asof` BETWEEN '04/2007' and '' ORDER BY `asof` DESC

[justsomeguy]

You're only checking for $asofstart, you're not even checking $asofend. Instead of this:if (strlen($asofstart)!=0) {$sql = $sql."AND `asof` BETWEEN '".$asofstart."' and '".$asofend."'";}You should do something like this:

if (strlen($asofstart)!=0) {  if (strlen($asofend))	$sql .= " AND `asof` BETWEEN '".$asofstart."' and '".$asofend."'";  else	$sql .= " AND `asof` > '{$asofstart}'";}elseif (strlen($asofend)){  $sql .= " AND `asof` < '{$asofend}'";}

[vj5]

You're only checking for $asofstart, you're not even checking $asofend. Instead of this:if (strlen($asofstart)!=0) {$sql = $sql."AND `asof` BETWEEN '".$asofstart."' and '".$asofend."'";}You should do something like this:

if (strlen($asofstart)!=0) {  if (strlen($asofend))	$sql .= " AND `asof` BETWEEN '".$asofstart."' and '".$asofend."'";  else	$sql .= " AND `asof` > '{$asofstart}'";}elseif (strlen($asofend)){  $sql .= " AND `asof` < '{$asofend}'";}

Thank you.

[vj5]

Only problem I have is when I search from 04/2007 to 12/2007, I am getting 2006 also in the result. How can I fix this?

[justsomeguy]

What data type is asof?

[vj5]

What data type is asof?

varchar[15]. I fixed it. Thanks.