Welcome to WebmasterWorld Guest from 34.235.143.190

Forum Moderators: open

Message Too Old, No Replies

arrange by date data from MySQL

arrange by date data from MySQL

     
9:09 am on Aug 2, 2007 (gmt 0)

New User

10+ Year Member

joined:Aug 2, 2007
posts:3
votes: 0


I am using field "date" type "datetime" in format YYYY-MM-DD HH:MM:SS in MySQL db.
My fields contains date, name, description.
I want to create a dropdown menu that can query db and show field with Today, Yesterday, last month, all time.
I also need to put a form having Fields FROM: dd/mm/yy TO : dd/mm/yy that can display all fields ranging between To and From values.

I will be really thankful for any help in logic or code.

Note. All this thing will be displayed on password protected page accessible only to admin.

10:28 am on Aug 3, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 16, 2004
posts:96
votes: 0


You may use the DATEs functions in MySQL, such as:


$sql = "select * from table_name where date >= DATE_SUB(NOW(), INTERVAL 1 DAY)";

Here is your select menu:


<select name="selected_interval">
<option value="0 DAY">Today</option>
<option value="1 DAY">Yesterday</option>
<option value="1 MONTH">Last month</option>
<option value="30 YEAR">All time</option>
</select>

Than your query will be:


$sql = "select * from table_name where date >= DATE_SUB(NOW(), INTERVAL ".$_POST['selected_interval'].")";

I did not tested this code, but I think this is a good start point.

10:38 am on Aug 3, 2007 (gmt 0)

New User

10+ Year Member

joined:Aug 2, 2007
posts:3
votes: 0


I just cant find a way to pass values from php variables to sql where clause. All I manage to do it is made a form having fields "from_date" and "to_date" stored in php variables. How i should format the form input to pass in sql query? But i am not able to pass values in sql query.
what i am using is
Code:

$result = mysql_query('SELECT * FROM `table_name` WHERE 'date' >= $from_date AND 'date' <= $to_date ORDER BY `date` DESC');

so i just have to pass values of variables $to_date and $from _date.
but getting error, so just help me with these code.

5:09 am on Aug 4, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Apr 21, 2004
posts:306
votes: 0


Replace this:
$result = mysql_query('SELECT * FROM `table_name` WHERE 'date' >= $from_date AND 'date' <= $to_date ORDER BY `date` DESC');

With this:
$result = mysql_query("SELECT * FROM table_name WHERE date >= " . $from_date . " AND date <= " . $to_date . " ORDER BY date DESC");

I think your problems arise from your interchanging of ' and `. You can avoid those errors by concatenating strings as I did in the revised SQL statement.

8:33 am on Aug 6, 2007 (gmt 0)

New User

10+ Year Member

joined:Aug 2, 2007
posts:3
votes: 0


thanks buddy,
i have done the changes,
now its working fine.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members