PHP/MySQL Related

Anon

I want to list the records of last 7 days
using php/mysql. How do i do that.

Sonia

Anon

Have a look at the mysql manual section about "date calculations"

SELECT * FROM table
WHERE TO_DAYS(the_field_with_the_date)>(TO_DAYS(now())-7)
ORDER BY the_field_with_the_date;

Anon

This should work, too...

SELECT * FROM table WHERE your_date_column > NOW() - INTERVAL 7 DAY;

---John Holmes...

Anon

this is not working my date column is a varchar. Please tell me some other way.

sonia

Anon

The best solution would be to change your field from varchar to a date field.
If you have the correct information in your varchar field you can change the field type without losing the data, but I would recommend you first try it on a new column. you can simply update the table like this:

UPDATE my_table SET new_date_field=old_date_field;

where new_date_field is of the DATE type and the old_date_field is the varchar type;

If you decide to do this, make backups of your data, make a test database/test tables, don't blame me if it messes up etc...

Anon

thanx for ur reply but it will not work like that as there is a possibility that i may lose some of my records. i have around 1000 records in my table and morever the date is stored in a dd/mm/yy format as varchar.
so i cant change the field type everything will mess up.

sonia

Anon

Create another column like Vincent said. Create a PHP script that reads your varchar column, parses it into a correct MySQL date format, and then inputs it into your new column. Then delete your varchar date column and start doing things the right way....You're making way to much work for yourself by using varchar. Why do you think they have a date column to begin with??

---John Holmes...

Anon

hi
There is no way out? ,i have to do this only for one file.
So can u tell me some other way out.

sonia

Anon

Well, if you really don't want to change your table....

Proding your strings are all of the format dd/mm/yy
so january is stored as 01 and not as 1,
then you could convert the string to a date within your query:

to_days(concat(substring(d,7,2),"/",substring(d,4,2),"/",substring(d,1,2)))

Then you could select all items that have a "to_days" of seven days less than the current date:

SELECT * FROM yourtable
WHERE to_days(concat(substring(d,7,2),"/",substring(d,4,2),"/",substring(d,1,2)))

(to_days(now)-7);

This is about as complex as you can get if you don't want to use a date field where a date field is the way to go, but this could work.

Anon

this code is not working i also tried to convert my date column to date datatype using that query but it is also not working what happens is lets say date in varchar is
12-10-00
so when it converts into date data type it gives following:
2012-10-00

help me to solve this problem.

sonia

Anon

The example I posted did work perfectly for strings like "26/08/00".

If you tweak my example a little I'm sure it will work.

Anon

.oO(Can you spoonfeed me please?)Oo.

Anon

giggle

My hourly rate is $100,- 🙂

Anon

You want to subcontract your work to me for $50/hour????

Anon

giggle why not!