Date problem in Select query

rocky48 · May 22, 2013

Hi

I am trying to code, to select for a particular month events that are happening during the chosen month. The month is chosen using a HTML drop-down list and POSTing the result in the PHP file. I first tried without the select parameter using NOW() and it worked fine.

This the code for this:

<?php
include("RelHol_connect.php");
doDB();
$get_Event_sql  = "SELECT Events.ID, Events.Event_Date, Events.Event_Description, Religion.ID, Religion.Religion
	FROM Events
	LEFT JOIN Religion
	ON Religion.ID = Faith_ID
	WHERE MONTHNAME(Events.Event_Date)= MONTHNAME(Now()) And YEAR(Now())
	ORDER BY Events.Event_Date ASC";
$get_Event_res = mysqli_query($mysqli, $get_Event_sql) or die(mysqli_error($mysqli));	
//create the display string
	$display_block = "<h2>
	<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"#87CEEB\" >
	<tr>
	<th>Date</th>
	<th>Event</th>
	<th>Faith</th>
	</tr>";

	while ($Event_info = mysqli_fetch_array($get_Event_res)) {
		$Event_Date = $Event_info['Event_Date'];
		$Event_text = nl2br(stripslashes($Event_info['Event_Description']));
		$Faith = $Event_info['Religion'];
		
		//add to display
	 	$display_block .= "
		<tr>
		<td width=\"5%\" valign=\"top\">DATE FORMAT(".$Event_Date.",%d %m %Y)</td>
		<td width=\"30%\" valign=\"top\">".$Event_text."</td>
		<td width=\"15%\" valign=\"top\">" .$Faith."</td>
		</tr>";
		
	}

	//free results
	mysqli_free_result($get_Event_res);


	//close connection to MySQL
	mysqli_close($mysqli);

	//close up the table
	$display_block .= "</table>";


?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
	<meta charset="utf-8">
	<meta name="Description" content="Ecologic Theme">
	<meta name="author" content="CoffeeCup Software, Inc.">
	<meta name="Copyright" content="Copyright (c) 2011 CoffeeCup, all rights reserved.">
    <title>1066 Cards 4U - Religious Holidays</title>
    <link rel="stylesheet" href="stylesheets/default.css" />
    <!--[if IE]>
    <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]-->
	
</head>

<body>
 <header>
        <h1><a href="index.html"> ..............</a></h1></br></br></br></br>
		<div><img src="images/1066Green.jpg" width="600" height="80" alt="" title="" border="0" /></div>
        
    </header>	

<section id="body">		
<h1>Religious Holidays & Festivals</h1>

<?php echo $display_block; ?>
</section>


</body>
</html>

When I modified the code to introduce the html selection, all I get are the headers displayed. There are no errors reported, so I guess that the query format is wrong!

Here is the modified code:

<?php
include("RelHol_connect.php");
doDB();

echo $_POST["Month"];

$get_Event_sql  = "SELECT Events.ID, Events.Event_Date, Events.Event_Description, Religion.ID, Religion.Religion
	FROM Events
	LEFT JOIN Religion
	ON Religion.ID = Faith_ID
	WHERE MONTHNAME(Events.Event_Date) = MONTHNAME('".$_POST["Month"]."') And YEAR(Now())
	ORDER BY Events.Event_Date ASC";
$get_Event_res = mysqli_query($mysqli, $get_Event_sql) or die(mysqli_error($mysqli));	
//create the display string
	$display_block = "<h2>
	<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"#87CEEB\" >
	<tr>
	<th>Date</th>
	<th>Event</th>
	<th>Faith</th>
	</tr>";

	while ($Event_info = mysqli_fetch_array($get_Event_res)) {
		$Event_Date = $Event_info['Event_Date'];
		$Event_text = nl2br(stripslashes($Event_info['Event_Description']));
		$Faith = $Event_info['Religion'];
		
		//add to display
	 	$display_block .= "
		<tr>
		<td width=\"5%\" valign=\"top\">DATE FORMAT(".$Event_Date.",%d %m %Y)</td>
		<td width=\"30%\" valign=\"top\">".$Event_text."</td>
		<td width=\"15%\" valign=\"top\">" .$Faith."</td>
		</tr>";
		
	}

	//free results
	mysqli_free_result($get_Event_res);


	//close connection to MySQL
	mysqli_close($mysqli);

	//close up the table
	$display_block .= "</table>";


?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
	<meta charset="utf-8">
	<meta name="Description" content="Ecologic Theme">
	<meta name="author" content="CoffeeCup Software, Inc.">
	<meta name="Copyright" content="Copyright (c) 2011 CoffeeCup, all rights reserved.">
    <title>1066 Cards 4U - Religious Holidays</title>
    <link rel="stylesheet" href="stylesheets/default.css" />
    <!--[if IE]>
    <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]-->
	
</head>

<body>
 <header>
        <h1><a href="index.html"> ..............</a></h1></br></br></br></br>
		<div><img src="images/1066Green.jpg" width="600" height="80" alt="" title="" border="0" /></div>
        
    </header>	

<section id="body">		
<h1>Religious Holidays & Festivals</h1>

<?php echo $display_block; ?>
</section>


</body>
</html>

Can anyone spot where I am going wrong? I a novice programmer.

Jessica · May 22, 2013

If there was an actual error with the query, you'd get an error. You may have a LOGIC error with it. Put the query into a string, echo it out, and try running it directly in MySQL or phpMyAdmin. See what is returned.

This part:

WHERE MONTHNAME(Events.Event_Date) = MONTHNAME('".$_POST["Month"]."') And YEAR(Now())

Makes no sense to me.

rocky48 · May 22, 2013

Hi Jessica

Thanks solved it! Realised that the $_POST was a string not a date, so removed the MONTHNAME(). Not sure that the YEAR(Now()) was doing anything, so I removed it and it still works! If I wanted to include the year would this work after the And: YEAR(Events.Event_Date) = '".$_POST["Year"]."'

Also How do I get the date to display in European format i.e. dd:mm:yyyy?

Psycho · May 22, 2013

On 5/22/2013 at 3:43 PM, rocky48 said:
Not sure that the YEAR(Now()) was doing anything, so I removed it and it still works! If I wanted to include the year would this work after the And: YEAR(Events.Event_Date) = '".$_POST["Year"]."'

If you don't include year in the query,then you would get all results for the specified month - across ALL years. So, if you selected "May" you would get results from May 2013, May 2012, etc, You are probably getting the correct results now because you don't have data that spans multiple years. So, I would create the process to auto-determine the year to use.

On 5/22/2013 at 3:43 PM, rocky48 said:
Also How do I get the date to display in European format i.e. dd:mm:yyyy?

You can format the date either in your select query or in the PHP code to produce the output using date().

rocky48 · May 22, 2013

Hi

I had deduced that what you have said was happening, but what about the code I had suggested! Will it work?

Thanks for the sugestion on how to format the date.

Jessica · May 22, 2013

On 5/22/2013 at 3:43 PM, rocky48 said:
If I wanted to include the year would this work after the And: YEAR(Events.Event_Date) = '".$_POST["Year"]."'

Did you try it?

Barand · May 22, 2013

You could try

WHERE EXTRACT(YEAR_MONTH FROM event_date) = EXTRACT(YEAR_MONTH FROM CURDATE())

rocky48 · May 23, 2013

Hi Jessica

I have tried it and it works! Thanks for your help!

Barand

Can't understand why I would want to compare the YEAR_MONTH FROM Event_Date to the Current date. The idea is to be able to list events from next year by month.

Sign In

Date problem in Select query

Recommended Posts

rocky48

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

rocky48

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

rocky48

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

rocky48

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information