Fetching problem between months and year

[amitsovainfotech]

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 attached. 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.

[johnj]

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

[amitsovainfotech]

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]

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

[johnj]

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.