question for php/sql wizards
 

what's the best format for me to save dates in my database. i'm still a newb to a lot of this. for what i am working on, i need to reference back upon dates frequently. what is the best format for me to keep thing sin to make things easier on myself down the line?

You might as well get used with timestamp since you're just beginning.
You can convert it in any format you like when you need to.

Y-M-D works for us.

AJC,

depends on how the data is entered, do you want to tag rows you insert with current time? Or do you want to set the specific time you want for the row when you create it?
Do you plan to do calculations on the date? IE one row's date minus another row's date? Or just "get anything since day XYZ"?

According to your post's topic, I imagine you use php to do the actual site?

I want to tag rows with a date, the date being tagged is not the current date, but a set date of choice. I will need to perform calculations on these dates. Say I want to perform an action on all rows with a date value stretching back 2 weeks from current, or whatever.

DATE or DATETIME if you need to store the time as well. Allows for all kinds of good date operations. For example, that two week thing:

SELECT * FROM table_name WHERE date_added >= date_sub(now(), interval 2 week);

While I've got you types in here, I might as well ask another question that has been picking at me. Say I'm loading a large text file into an array, just how many elements can an array handle? If I need to load 100,000 lines in, is this going to cause my script to shit the bed, or is that a reasonable number for a single array to handle? Say the text file is upwards of 20mb, like a large datafeed or CSV...

That sounds like what I need to be looking towards. I'm just starting to get comfortable with SQL. Been kind of quietly dabbling more in programming lately, things like SQL databases and regular expressions are suddenly starting to be a lot less intimidating now. Still got a lot of learning to do, but things are opening up now. In the past my biggest hindrance and expense has been my inability to afford programmers or to build out ideas myself. Or affording the programmer only to have him screw me over in the end. Gotta get that shit taken care of. :1orglaugh

Find a better way to do it. Load all that info into a mySQL database, open the file and read it line by line, or something. You shouldn't load a 20MB file into memory if you can avoid it. Script will probably still work, but no point in straining your server resources like that.

How would I go about loading the entire text file line by line straight into the database? That would certainly make things a lot easier.

What I was aiming for was loading the file into an array and then looping through the array and entering all the values into the database, parsing data out of each line in the text and then putting it into the database in the specified columns for each item.

Hitting Google now, and looking at BULK INSERT for importing to SQL. The format of the lines I'm inserting isn't that of a typical CSV, there's several values delimited by different characters. Like one value sperated from the next by a period, then that one sperated from the next by a comma, and then by a space, yadda yadda. So I guess I would have to modify the text file beforehand, and the BULK INSERT?

Yep, but it's best to do it line-by-line, instead loading the entire file into memory at once. Create mySQL table,then for example:

That way you're only loading one line into memory each time, instead of the entire 20MB of data.

Thnx :thumbsup I'll follow up on this a little more and hopefully get a good solution worked out.

You seem to be in good hands with kiopa....

I'd say so, that solution works perfectly. 318,000 records parsed and imported in no time. :thumbsup

Fuuuuuuuuuuuuck why didn't I put the effort in to learn some code ages ago.

As for what format you choose. I MUCH prefer a timestamp over datetime (timestamp is a ten char numerical time value. and date time is like YYYY-MM-DD). I actually loathe datetime for whatever reason. Probably because I always feel it's better to have my hard data in the database be as raw as possible and convert it when parsing it to nicer values once it hits the page.

I think the DATE data type best covers what I need to do for now. I have an input list with my date values in MM/DD/YYYY format though. Am I wrong in assuming I have to have my DATE date entries in YYYY-DD-MM format? Or is there a way I can set the column to accept that format of DATE value? Can I convert this quickly on the fly as I enter the values into my table? Or do I have to strip all the day/month/year values out with regex and piece it back together as variables to enter it in the proper date format?

bump for that ^^^^^^ question

Yep. Strip it out, piece it back together.

Ok, I just used a regular expression this morning and got it done. I just wanted to be sure there wasn't a more efficient way to go about it. It wouldn't be the first time I spent an hour referencing, playing around, and writing out all this code; only to find out I could have accomplished the exact same thing with 1 line. :1orglaugh

Thanks again for the tips. Appreciated.

datetime? depend how you are wanting to use it.... but most often i use datetime

What Barry said... :thumbsup LOAD DATA INFILE is very quick - but you have to have shell access.

With regards to the date splitting, using a regexp is ok - however I think just using explode() will/can be quicker. Especially for something this trivial, it doesn't really require a regexp.

That works really well too. From what I understand it's much quicker to use PHP native functions than a regex anyhow? This is what I came up with...

Does the trick. Thnx. :thumbsup

I need this to run automatically on my server from a cron job...

The code is perfect for what you want to do, except that it's YYYY-MM-DD in mysql, not YYYY-DD-MM ( http://dev.mysql.com/doc/refman/5.1/en/datetime.html )

so it should be

You can do it in mysql directly btw using STR_TO_DATE()

http://dev.mysql.com/doc/refman/5.5/...on_str-to-date

Even better. That's more what I was looking for. I knew there had to be some way to handle it as a part of the insertion process. Would there be any reason to lean one way or the either between STR_TO_DATE in the insertion vs explode() and then inserting the value?

Always let mysql do the job for you, reading a line and splitting into a variable and then put that variable directly into mysql with str_to_date is better than you are doing it,
even if you can ofcus, why reinventing the wheel ? :)

if you feel unsure about the functions, simple queries against mysql as example:
mysql> SELECT STR_TO_DATE('1/12/2011', '%e/%m/%Y');
+--------------------------------------+
| STR_TO_DATE('1/12/2011', '%e/%m/%Y') |
+--------------------------------------+
| 2011-12-01 |
+--------------------------------------+
1 row in set (0.00 sec)

Then you always can see how things will look like and you will learn a lot more about
mysql functions.

We always store php timestamp to the database, can then display it how you want using php date function

Agree, DATE storage is 3 bytes, TIME is 3 aswell, and DATETIME is 8 and an INT is 4 bytes so using one more byte for knowing the exact second is best of all, and doing some extra programming for converting the seconds to whatever you want is worth it.

Down the line, i mean ;)

Ya, it makes sense looking at it, and I knew there had to be something like it kicking around. I just didn't know what to look for, heh. Thanks for the explanation. I'm still soaking up tons of new information learning my way around, but it's definitely worth the time invested.

Time serves no purpose to me, only the date. So as far as I can figure, DATE is the data type which suits my needs the best for this application.

Got root? bash
cron a bash script to do this?

I have never done this but I found this as a protype ...