Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Fetching problem between months and year

Codes here !

Moderators: macek, egami, gesf

Fetching problem between months and year

Postby amitsovainfotech » Fri Aug 24, 2012 5:32 am

Hi,

I have a database structure in which i have five fields called name, email, address, phone no and added_date. The added_date value is in time function ie time(); All the fields are inserted properly.
Now on the frontend I want to search by Month and year. Image attachedImage. Now how do i do it as the month value is coming 8 and year is 2012 and in database the added_date filed is something like '45345678'. Can you please help how should i write the query to get the result.

Thanks.
amitsovainfotech
New php-forum User
New php-forum User
 
Posts: 3
Joined: Fri Aug 24, 2012 5:07 am

Re: Fetching problem between months and year

Postby johnj » Fri Aug 24, 2012 7:55 am

The definition for time() is " Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)".

Are you sure that added_date is a value returned by time?. I think added_date is a timestamp value returned by strtotime().
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: Fetching problem between months and year

Postby amitsovainfotech » Mon Aug 27, 2012 2:01 am

Hi,
Thanks for the reply.

I made a query to search the above result

$check_attandance_history= mysql_query("SELECT * FROM `emp_attendance` WHERE date('n',attendance_start_time) between '$from_month' and '$to_month' and date('Y',attendance_start_time) between '$from_year' and '$to_year' and user_id='$user_id'")or die(mysql_error());

but it is showing following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'attendance_start_time) between '8' and '8' and date('Y',attendance_start_time) b' at line 1

Can you please help what syntax error i have made.

Thanks.
amitsovainfotech
New php-forum User
New php-forum User
 
Posts: 3
Joined: Fri Aug 24, 2012 5:07 am

Re: Fetching problem between months and year

Postby amitsovainfotech » Mon Aug 27, 2012 2:29 am

My database structure are as follows

Table name :emp_attendance

attendance_id user_id attendance_start_time attendance_close_time
1 5 1344946402 0
2 5 1345103382 0
3 6 1345531902 0

Insert Query
=========
$current_time=time();

$insert_commence= "INSERT INTO `emp_attendance` (`user_id`, `attendance_start_time`, `attendance_close_time`) VALUES ('$user_id', '$current_time','0')";
mysql_query($insert_commence) or die(mysql_error());

Now from the emp_attendance table i want to search Image. Only by month and year.

For that in have written the query

$check_attandance_history= mysql_query("SELECT * FROM `emp_attendance` WHERE date('n',attendance_start_time) between '$from_month' and '$to_month' and date('Y',attendance_start_time) between '$from_year' and '$to_year' and user_id='$user_id'")or die(mysql_error());

but it is showing following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'attendance_start_time) between '8' and '8' and date('Y',attendance_start_time) b' at line 1

Can you please help what syntax error i have made.

Thanks.
amitsovainfotech
New php-forum User
New php-forum User
 
Posts: 3
Joined: Fri Aug 24, 2012 5:07 am

Re: Fetching problem between months and year

Postby johnj » Tue Aug 28, 2012 8:06 am

date() is a php builtin function. You need to use the mysql date function if you want to use it within a sql. Please read the mysql manual.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 5 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.