Page 1 of 1

Pick month from date

Posted: Wed Dec 30, 2009 5:16 pm
by mudasir
Hi,

I made a script few days back which saves DATE/TIME in following PHP format
$dte=date("l F d, Y, h:i A");
$query = "insert into abc (name, date) values ('$name', '$dte')";
mysql_query ($query);

which saves datetime similar to
"Friday December 25, 2009, 11:58 PM"

Now i am learning to make a script to show monthly data present against a particular name in the database.
I would like to know how can i get only month from the above code
If possible please show practical example of php with mysql, as i dont have much experience in PHP.

Looking forward for a quick and positive reply.



Kind Regards

Re: Pick month from date

Posted: Wed Dec 30, 2009 6:17 pm
by requinix

Code: Select all

$string = "Friday December 25, 2009, 11:58 PM";
 
strtok($string, " ");
$month = strtok(" ");

Re: Pick month from date

Posted: Wed Dec 30, 2009 11:23 pm
by mudasir
Hi,

Thanks allot. This works for me.
WIll it be possible to run a MySQL query with this string on date column for specific month.
Like in the above example the month is december, so is it possible to get all the records for the month of december.
I tried it, but not getting successful result.
I really Appreciate your guidance.

Re: Pick month from date

Posted: Wed Dec 30, 2009 11:32 pm
by pbs
What data type you are using?

Re: Pick month from date

Posted: Wed Dec 30, 2009 11:35 pm
by mudasir
Data type used here for date column is varchar, because DATETIME was not working with this format of date.
I can change it anytime, if it requires to be changed.

Re: Pick month from date

Posted: Wed Dec 30, 2009 11:38 pm
by pbs
You can try this SQL and try the month condition in your query.

Code: Select all

 
select MONTH(STR_TO_DATE('Friday December 25, 2009, 11:58 PM', "%W %M %d, %Y, %l:%i %p")) as mon;
 

Re: Pick month from date

Posted: Wed Dec 30, 2009 11:46 pm
by mudasir
This is not working, it is giving error "unexpected %" at line number.

Re: Pick month from date

Posted: Wed Dec 30, 2009 11:50 pm
by pbs
The % character is required before format specifier characters in date functions, it should not give error.

Re: Pick month from date

Posted: Thu Dec 31, 2009 12:04 am
by pbs

Re: Pick month from date

Posted: Thu Dec 31, 2009 2:25 am
by requinix
pbs wrote:The % character is required before format specifier characters in date functions, it should not give error.
...unless OP copy/pasted the code without escaping the quotes ;)

Code: Select all

$query = "select MONTH(STR_TO_DATE('Friday December 25, 2009, 11:58 PM', "%W %M %d, %Y, %l:%i %p")) as mon;";
Booyah! Win!

Re: Pick month from date

Posted: Thu Dec 31, 2009 7:51 am
by mudasir
Here is the complete PHP code where i am implementing this, everything else is working fine.
It is basically a complain system that i am working on to implement in my office, i thought that it would be great to know complains on monthly basis against single dealer.
This is a code to view complain by the complain ID.

Code: Select all

 
$id = stripslashes($_POST['number']);
$username = $_SESSION['SESS_LOGIN'];
if($_SESSION['SESS_RIGHTS'] == "admin"){
        query = mysql_query("select * from complains where id='$id'");
} else{
        $query = mysql_query("select * from complains where username='$username' AND id='$id'");
}
if (mysql_num_rows($query)) {
                if (mysql_num_rows($query)) {
                        $run = query_assoc("select * from complains where ID='$id'");
                        $query1 = "select MONTH(STR_TO_DATE($date ,"%W %M %d, %Y, %l:%i %p")) as mon from complains where ID='$id';";
                        $mth = mysql_query($query1);
                        $cnum = $run['id'];
                        $fileby = $run['username'];
                        $date = $run['date'];
                        $status = $run['status'];
                        $dealername = $run['dealerid'];
                        $description = $run['description'];
                        echo "Complain Filed By:<b> ". $fileby . "</b><br>";
                        echo "Dealer Name: <b> ". $dealername . "</b><br>";
                        echo "Status: <b> ". $status . "</b><br>";
                        echo "Date / Time:<b> ". $date . "</b><br>";
                        echo "Description:<b> ". $description . "</b><br><br>";
                        echo "Test:<b> ". $mth['mon']. " </b><br><br>";
                        if($status == "complete"){
                                $admin_desc = $run['admin_description'];
                                $admin_date = $run['admin_date'];
                                $admin_name = $run['admin_name'];
                                echo "Completed By: <b> ". $admin_name. "</b><br>";
                                echo "Admin Description: <b> ". $admin_desc. "</b><br>";
                                echo "Admin Date: <b> ". $admin_date. "</b><br>";
                        }
 
The above code is giving me following error
"Parse error: syntax error, unexpected ',' in /var/www/cms/view.php on line 118"
Line 118 is:
query1 = "select MONTH(STR_TO_DATE($date ,"%W %M %d, %Y, %l:%i %p")) as mon from complains where ID='$id'";

When i put "%W %M %d, %Y, %l:%i %p" like '%W %M %d, %Y, %l:%i %p' replacing double quotes with single quotes, then i get no error however i dont get any results from this query.
Here i am just testing to check how to retrieve the month from the date field. If successful i will make a seperate page to see the monthly report of a particular dealer.

Please help me out in this one.


Kind Regards

Re: Pick month from date

Posted: Fri Jan 01, 2010 7:02 am
by mudasir
Hi,

I need help in this. Still unable to figure out the method. Please help

Re: Pick month from date

Posted: Fri Jan 01, 2010 8:18 am
by requinix
Let me rephrase what I said 30 hours ago:
tasairis wrote:[You] copy/pasted the code without escaping the quotes
The solution is to escape the quotes.

This is a basic PHP skill - nay, a basic programming skill. You need to be able to understand what's going on, what the problem is, and how to fix it.
And, of course, how to use Google.