Jump to content

Date problem in Select query

rocky48

By rocky48
in PHP Coding Help

 Share
Go to solution Solved by rocky48,

Recommended Posts

rocky48 Advanced Member

rocky48

Posted
Posted

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.

Link to comment
Share on other sites
Jessica Advanced Member

Jessica

Posted
Posted (edited)

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.

Edited by Jessica
Link to comment
Share on other sites
rocky48 Advanced Member

rocky48

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

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.

 

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().

Link to comment
Share on other sites
  • Solution
rocky48 Advanced Member

rocky48

Posted
  • Author
  • Solution
Posted

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.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.