Date / URL /SQL question

Pilly

Hi,

I have a url:
http://www.xyz.com/name_month.php?month=01

The Date is stored in MYSQL is in the format "02/01/2004 - 12:30" and is a varchar(30)

The name_month.php script grabs the month variable no problem. using $_GET['month'] but how can i get the query to search the database for all records with the month 01 using the time format above..

$sql = "SELECT field1,datestamp, count(*) AS cnt
FROM phplog
WHERE phplog.(where im stuck)
GROUP BY field1";

Ive searched and tested different possiblities but have become stuck and and advice would be greatful.

AstroTeg

Um, if you made that a datetime field, you could use some of the many date functions available to MySQL such as MONTH() - so you could do:

$sql = "SELECT field1,datestamp, count(*) AS cnt
FROM phplog
WHERE MONTH(phplog.datestamp) = 1
GROUP BY field1";

Granted, this will mean you'll have to convert all the dates into a form MySQL will like, but you should be able to whip up a quick script to do that and then change the field to datetime. But doing all of this will mean you can do some nifty tricks like use BETWEEN date1 and date2 or YEAR(). Also remember there's DATE_FORMAT() available to MySQL which allows you to convert that datetime field into any format you could wish for.

dotbob

My advice would be to overhaul your database but if you don't want to do that, try this:

$month = "01";
$sql = "SELECT field1,datestamp, count(*) AS cnt
FROM phplog
WHERE phplog.datestamp LIKE '%/$month/%'
GROUP BY field1";

Because the month is encapsulated by / / - the above query should only return dates that match /01/ or whatever month you provide.

PLEASE NOTE:

This method is extremely slow so if you have a large database, or a large user base - overhaul your database and change the way that you query it.

Pilly

Ive just created a copy of the DB to test on. Ive changed the type to DATETIME and its zero'd all my records in this field. What kind of script were you refering to to help me convert them to the correct format?

mtimdog

He was saying write your own custom script to change the dates over.

Another way would be to say in sql

where left(date,2) = "$month"

you'll have to check mysql.com for exact way to use Left though.

AstroTeg

You'll need to do some coding to actually create the script. Actually, if you're really smooth with SQL, you may find you just need to write one update SQL query, but you'll need to be very comfy with MySQL string functions since you would have to parse the date and convert into a datetime value (which off the top of my head is formated as: 2004-01-21 15:01:01).

Or you could create a PHP script that loops through all your records and gets the date value. Then use PHP's [man]date[/man] function and maybe even [man]strtotime[/man] to convert the date into "Y-m-d H:i:s" (I believe this is the format you'll want to use to insert the time into the datetime field.

Granted, the PHP method is the slower of the two, but since you only need to do this once (versus as a regular feature of your app), speed isn't that much of an issue unless you have a gazillion records.

If you go with the PHP route, only test with 1 record until you get it right. Then unleash your recordset loop to handle all the records. Same may go with doing the SQL - unless you keep backups and don't mind restoring the data...

Pilly

what i was thinking of is creating a copy of the table, then run a script that goes through each row of the original table, each time stripping the day, month, year and time as variables, then formating the variables before updating the copy of the table.

Would this work?
If so any chance of giving me the php commands i may need for stripping the data from the varchar field, i dont mind doing the groundwork.

AstroTeg

As mentioned in my last post, check out strtotime() and its partner in crime, date() (both PHP functions). You might be able to get away with using strtotime on your date string. Its like a Swiss Army knife on date and time strings and will convert it into a time stamp, which is exactly what date() likes to work with:

$mysqlfriendly = date('Y-m-d H:i:s', strtotime($yourdateinfo));

But you may want to echo out the results before going too far just to make sure it is converting the time properly...

Pilly

I have to admit i cheated. I took the easy way out instead of programming a script. sorry guys

I exported the data in the DB to a csv, edited the date time columns format in excel, exported as a csv, then imported back into the DB, then changed the original script to reflect the new insert date format...

Thanks for all your help.

AstroTeg

That works too!