[SOLVED] Date Questions

[dvdflashbacks]

Hi,

 

I am trying to setup a date in MySQL and PHP.  Basically what I am trying to do is create two entries in a form; one for the Month and then one for the Year.  I then want to be able to search for any submissions based on the month and the year.

 

Can someone give me a little guidance on how I can go about doing this?

 

Thanks.

[dclamp]

Are you wanting to get rows from a table based on the date?

[dvdflashbacks]

hi dclamp,

 

Basically I have a basic blog type system that I built.  I have a database that stores journal entries and they are setup with different fields in the DB such as blog_id, title, author, picture, and then body(text).  I also have a timestamp entry in mysql to help me sort by the most recent in the browser, but I have hidden this field in PHP because it is a long unix timestamp and looks ugly.  I really only want to be able to display the month and year and then be able to sort/search by a given month and year.

 

Does that make sense?

[RichardRotterdam]

Hi,

 

you can use the month() and year() functions in mysql

 

example

SELECT date_field from message where month(date_field)=11 and year(date_field)=2008

[dvdflashbacks]

Hey Dj Kat,

 

I had a chance to talk with one of my coworker's who knows a little more about sql than me.  He suggested parsing out the month and year from the timestamp field in mysql through PHP.  He is not familiar with PHP though so he had no suggestions on how to write this query for the browser. 

 

So does anyone have any info on how I can write this statement so that I can display just the month and year instead of the full timestamp.

 

As an example:  I currently have in mysql db a "datetime" field, type=timestamp, attrib=on update current_timestamp, default=current_timestamp

 

I am not displaying this field on my site because it is a very long string, but I am using the timestamp to sort entries for most current.

[dclamp]

well if your column has a unix time stamp you can format that using the date() function:

 

$timestamp = "1228437087"; // For example

$formated = date("F Y"); // will format like this: "December 2008"

[PFMaBiSmAd]

You can use the mysql EXTRACT() function with a YEAR_MONTH unit specifier in your query to just get the year and month for either comparison purposes or to just SELECT that part of that DATETIME -

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_extract

 

 

[dvdflashbacks]

well if your column has a unix time stamp you can format that using the date() function:

 

$timestamp = "1228437087"; // For example

$formated = date("F Y"); // will format like this: "December 2008"

 

Sorry for being such a noob on this.  Currently I have a recordset (I use DW alot since I am not an expert handcoder) to pull the items I need from the MySql DB.

 

The sql statement is:

SELECT * FROM blog_items WHERE blog_category = 1 ORDER by blog_item_datetime DESC

 

I then am using a dynamic text statement to display the date and time as follows:

 

echo $row_rsDisplayFeatures['blog_item_datetime'];

 

The date and time displays like this currently on the site:

2008-12-03 21:44:46

 

Is there a way that I can simply rewrite my echo statement for the blog_item_datetime to display just the month and year?

 

Thanks and sorry for my lack of understanding.

[gevans]

try;

 

SELECT * FROM blog_items, DATE_FORMAT(blog_item_datetime, '%M %Y') as month_year WHERE blog_category = 1 ORDER by blog_item_datetime DESC

 

 

 

 

echo $row_rsDisplayFeatures['month_year'];

[dvdflashbacks]

try;

 

SELECT * FROM blog_items, DATE_FORMAT(blog_item_datetime, '%M %Y') as month_year WHERE blog_category = 1 ORDER by blog_item_datetime DESC

 

 

 

 

echo $row_rsDisplayFeatures['month_year'];

 

Hey gevans,

 

I tried entering this exactly as you have written and I received an SQL syntax error.  Any ideas?

 

Thanks again.

[gevans]

Can you post all your code? That should work by my reconning.

[PFMaBiSmAd]

And post the error message as that indicates where in the query the problem is occurring.

[premiso]

SELECT *, DATE_FORMAT(blog_item_datetime, '%M %Y') as month_year FROM blog_items WHERE blog_category = 1 ORDER by blog_item_datetime DESC

 

I think that should work.

[dvdflashbacks]

Ok, so I changed the code a little bit and I was able to test it successfully under DW's Server Behaviors applet.  Here is the modified code I used that seems to work.

 

SELECT *, DATE_FORMAT(blog_item_datetime, "%b %Y") AS blog_item_datetime FROM blog_items WHERE blog_category = 1 ORDER BY blog_item_datetime DESC

 

Now the problem is that when I load this page in the browser the whole thing is blank.  Almost like it never loaded.

[gevans]

If you put all your code in we wouldn't have issues with double and single quotes

 

Give us something to work with in the [ code ] [ /code ] tags!!

[PFMaBiSmAd]

Blank php pages are usually caused by fatal parse or fatal runtime errors (they can also be caused by code that outputs nothing.) My standard blurb in this situation -

 

When learning php, developing php code, or debugging php code, do it on a development system with error_reporting set to E_ALL and display_errors set to ON in your php.ini to get php to help you. Stop and start your web server to get any changes made to php.ini to take effect. Turning these two settings on in your script won't work for parse errors because your script is never executed when there is a parse error.

[dvdflashbacks]

If you put all your code in we wouldn't have issues with double and single quotes

 

Give us something to work with in the [ code ] [ /code ] tags!!

 

Sorry.  I thought I had put everything in the

 tags.  What else should I include?

[gevans]

paste all your php code.

 

Whatever page your getting this code from, copy and paste it!!

[dvdflashbacks]

paste all your php code.

 

Whatever page your getting this code from, copy and paste it!!

 

I couldn't paste the entire page as it exceeded the allowed characters, but here is more code for you to see from the beginning of the page.

 

Here is the exact error I got:

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home2/theheal7/public_html/index.php on line 49

 

And here is the code for lines 48-52

 

mysql_select_db($database_conndb, $conndb);
$query_rsDisplayFeatures = "SELECT *, DATE_FORMAT(blog_item_datetime, "%b %Y") AS blog_item_datetime FROM blog_items WHERE blog_category = 1 ORDER BY blog_item_datetime DESC";
$query_limit_rsDisplayFeatures = sprintf("%s LIMIT %d, %d", $query_rsDisplayFeatures, $startRow_rsDisplayFeatures, $maxRows_rsDisplayFeatures);
$rsDisplayFeatures = mysql_query($query_limit_rsDisplayFeatures, $conndb) or die(mysql_error());
$row_rsDisplayFeatures = mysql_fetch_assoc($rsDisplayFeatures);

 

This is my code for the echo statement on the page where the date and time should appear:

 

<?php echo $row_rsDisplayFeatures['blog_item_datetime']; ?>

 

Hope this helps.

[gevans]

What happens when you replace this;

 

$query_rsDisplayFeatures = "SELECT *, DATE_FORMAT(blog_item_datetime, "%b %Y") AS blog_item_datetime FROM blog_items WHERE blog_category = 1 ORDER BY blog_item_datetime DESC";

 

with this;

 

$query_rsDisplayFeatures = "SELECT *, DATE_FORMAT(blog_item_datetime, '%b %Y') AS blog_item_datetime FROM blog_items WHERE blog_category = 1 ORDER BY blog_item_datetime DESC";

[dvdflashbacks]

Hey gevans,

 

That seemed to work!  The page can be viewed and it shows only the month and year.  But oddly, the sorting is wrong now.  I just upadated two records and the dates changed from "Sep 2008" to "DEC 2008" but the record order didn't change.

 

Any thoughts?

[gevans]

I'm not sure I understand

 

Surely if you've added a new record DEC 2008 would be right?

[PFMaBiSmAd]

You need to change the alias name. When you do -

 

AS blog_item_datetime

 

and then ORDER BY blog_item_datetime

 

it will use the formatted blog_item_datetime in the ORDER BY.

[dvdflashbacks]

You need to change the alias name. When you do -

 

AS blog_item_datetime

 

and then ORDER BY blog_item_datetime

 

it will use the formatted blog_item_datetime in the ORDER BY.

 

Thank You PFMaBiSmAd!  And thank you to gevans and everyone else involved in this mess I created!  Everything is now working as it should!  The display shows just the Month and Year but the entries are still sorted by the original timestamp!

 

You guys rock!