can't figure out why record is not showing up

[kirks]

I have a MySQL statement that seems to malfunction.

All records contain two date fields: details_date and details_end_date

details_end_date can contain NULL values while details_date is a required field

My query below only shows records whose detail_date is greater than or equal to currentDate. However, I also need any records whose details_end_date is greater than or equal to currentDate even if its details_date is less than currentDate.  Can anyone tell me what's wrong with my query?

 

		$sql2="SELECT  *, 
		   DATE_FORMAT(`details_date`, '%M %d') AS start_date,
		   DATE_FORMAT(`details_end_date`, '%d') AS end_date,
		   DATE_FORMAT(`details_date`, '%Y') AS start_year
		   FROM itn_details 			   
		   WHERE details_date >='$currentDate' OR details_end_date >= '$currentDate'
		   and DATE_FORMAT( `details_date` , '%Y' )='".$row['years']."'
		   and 	DATE_FORMAT( `details_date` , '%M' )='".$row1['Month']."'";

 

Thanks! 

[zsedc]

I would compose a small MySql query and than check if works. If there is everything fine than try to add next piece and run again and again. Following this way you will easy find where the problem is.

[Barand]

Seems like you just need

 

WHERE details_end_date > CURDATE()

[kirks]

WHERE details_end_date > CURDATE() won't work because details_end_date is an optional field that may contain NULL values for some records.

[Barand]

sorry, missed that.

 

WHERE (details_end_date IS NULL AND details_date >= CURDATE() )

OR (details_end_date >= CURDATE())

[kirks]

That should have worked but for some reason.  :-\ It still doesn't work. I'll have to go back and strip down all of the code until I get it working. Thanks a lot though.

[Barand]

worked ok with my test data **

[pre]

mysql> SELECT * FROM datetest;

+---------+-------------+-----+-----------------+

| idtuple | detail_date | num | detail_end_date |

+---------+-------------+-----+-----------------+

|      1 | 2008-09-01  |  25 | NULL            |

|      2 | 2008-09-07  |  14 | 2008-11-01      |

|      3 | 2008-09-10  |  17 | NULL            |

|      4 | 2008-10-08  |  2 | NULL            |

+---------+-------------+-----+-----------------+

4 rows in set (0.00 sec)

 

mysql> select CURDATE();

+------------+

| CURDATE()  |

+------------+

| 2008-10-07 |

+------------+

1 row in set (0.00 sec)

 

mysql> SELECT * FROM `datetest`

    -> WHERE (detail_end_date IS NULL AND detail_date >= CURDATE() )

    -> OR (detail_end_date >= CURDATE());

+---------+-------------+-----+-----------------+

| idtuple | detail_date | num | detail_end_date |

+---------+-------------+-----+-----------------+

|      2 | 2008-09-07  |  14 | 2008-11-01      |

|      4 | 2008-10-08  |  2 | NULL            |

+---------+-------------+-----+-----------------+

2 rows in set (0.00 sec)

[/pre]

 

** test data - that data for which the program works.

[kirks]

Yeah, at first, I didn't think the other code mattered, but it obviously does. Here's the entire script that outputs data for input into a flash file:

 

<?php
include("itn.globals.php");
include("itn.opendb.php");
$currentDate=date("Y-m-d"); 
echo ("itncontent=");

$query="SELECT distinct(DATE_FORMAT( `details_date` , '%Y' )) AS
years from itn_details  group by details_date" ;


$Res=mysql_query($query, $connection);

while($row=mysql_fetch_assoc($Res))
{
	echo ("<br><br><font size='20' color='#F9D9A3'> " . $row['years'] . " Itinerary</font>");		
	echo ("<br>\n");

	$sql1="SELECT  Distinct(DATE_FORMAT( `details_date` , '%M' )) AS
		  Month from itn_details WHERE details_date >='$currentDate' 
		  and DATE_FORMAT( `details_date` , '%Y' )='".$row['years']."' 	
		  group by Month  order by details_date";

	$Res1=mysql_query($sql1, $connection);

	while($row1=mysql_fetch_assoc($Res1))
	{
	echo ("<img src='images/hr.png' height='2' width='400' align='left'><br>\n");
	echo ("<font size='16' color='#F9D9A3'> " . $row1['Month'] . "</font>");
	echo ("<br>\n");

	$sql2="SELECT  *, 
		   DATE_FORMAT(`details_date`, '%M %d') AS start_date,
		   DATE_FORMAT(`details_end_date`, '%d') AS end_date,
		   DATE_FORMAT(`details_date`, '%Y') AS start_year
		   FROM itn_details 			   
		   WHERE (details_end_date IS NULL AND details_date >='$currentDate') OR (details_end_date >= '$currentDate')
		   and DATE_FORMAT( `details_date` , '%Y' )='".$row['years']."'
		   and 	DATE_FORMAT( `details_date` , '%M' )='".$row1['Month']."'";

	$Res2=mysql_query($sql2, $connection);

	while($getRecord=mysql_fetch_assoc($Res2))
	{
		$getRecord['details_date'];
		$breakDate=explode("-", $getRecord['details_date']);
		echo ("<br><font size='11'><font color='#F9D9A3'> " . $getRecord['start_date']); 
		if($getRecord['end_date'] > "00") {
			echo (" - " . $getRecord['end_date']);}; 
		echo (", " . $getRecord['start_year'] . "</font><br>");
		echo (" " . $getRecord['details_church']); echo "<br>";
		echo (" " . $getRecord['details_city_state']); echo "<br>";
		if($getRecord['details_pastor'] > " ") {
			echo (" " . $getRecord['details_pastor']); echo "<br>";};
		if($getRecord['details_phone'] > " ") {
			echo (" " . $getRecord['details_phone']); echo "<br>";};
		if($getRecord['details_link'] > " ") {
			echo (" <a href='".$getRecord['details_link']."' target='_blank'><u><font color='#378ABC'>" . $getRecord['details_link']); echo "</font></u></a><br>";};
		echo ("</font><br>");

	} 

}		

}		






include("itn.closedb.php");
?>

 

Something in there is causing the problem.

[Barand]

I wonder if it needs extra ()

 

WHERE ((details_end_date IS NULL AND details_date >='$currentDate') OR (details_end_date >= '$currentDate'))

            and DATE_FORMAT( `details_date` , '%Y' )='".$row['years']."'

            and    DATE_FORMAT( `details_date` , '%M' )='".$row1['Month']."'";

 

 

BTW, Instead of those DATE_FORMAT()s you could use

 

MONTH(details_date)

YEAR(details_date)

[kirks]

H-m-m-m, I tried the extra parenthesis but didn't change the results. I also tried the MONTH(details_date) and YEAR(details_date) but output failed altogether. 

 

Thanks though.

[Barand]

echo $sql2;

 

Let's see what is actually being sent

[kirks]

Okay!  Yeah, echoing out $sql2 worked.

[Barand]

Does that mean the echo command worked or, having echoed it, you were able to identify the problem and fix it?

 

If it's the latter, please click "topic solved" button

[kirks]

The echo command worked but I don't know why. I'm thinking it may be the $getRecord['details_date']; but I'm not sure nor how/what to replace it with.  ???