question for php/sql wizards

[Angry Jew Cat - Banned for Life]

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?

[darksoul]

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.

[DHDChris]

Y-M-D works for us.

[Nathan]

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?

[Angry Jew Cat - Banned for Life]

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.

[Kiopa_Matt]

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);

[Angry Jew Cat - Banned for Life]

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

[Angry Jew Cat - Banned for Life]

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);

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.

[Kiopa_Matt]

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

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.

[Angry Jew Cat - Banned for Life]

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?

[Kiopa_Matt]

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:

$file = fopen('myfile.txt', 'r');
while ($line = fgets($file)) { 
     // Parse data, and insert into mySQL table
}
fclose($file);

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

[Angry Jew Cat - Banned for Life]

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:

$file = fopen('myfile.txt', 'r');
while ($line = fgets($file)) { 
     // Parse data, and insert into mySQL table
}
fclose($file);

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

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

[Nathan]

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

[Angry Jew Cat - Banned for Life]

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.

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

[potter]

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.

[Angry Jew Cat - Banned for Life]

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?

[Angry Jew Cat - Banned for Life]

bump for that ^^^^^^ question

[Kiopa_Matt]

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?

Yep. Strip it out, piece it back together.

[Angry Jew Cat - Banned for Life]

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.

Thanks again for the tips. Appreciated.

[Barry-xlovecam]

In SSH or terminal if you can;

mysql> load data LOCAL infile '/home/user/path to/file.csv' into table name fields terminated by ',' lines terminated by '\n' (zip, city, state, lat, lng, county);

Query OK, 41755 rows affected (0.13 sec)
Records: 41755 Deleted: 0 Skipped: 0 Warnings: 0

This was for a database of all the postal zip codes in the United States . PHP is way too slow to load databases.

I like epoch time myself ...

[bigmacandcheese]

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

[Dido]

In SSH or terminal if you can;

mysql> load data LOCAL infile '/home/user/path to/file.csv' into table name fields terminated by ',' lines terminated by '\n' (zip, city, state, lat, lng, county);

Query OK, 41755 rows affected (0.13 sec)
Records: 41755 Deleted: 0 Skipped: 0 Warnings: 0

This was for a database of all the postal zip codes in the United States . PHP is way too slow to load databases.

I like epoch time myself ...

What Barry said... 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.

[Angry Jew Cat - Banned for Life]

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

$oldDate = "1/12/2011";
list ($day, $month, $year) = explode("/", $oldDate); 
$newDate = "$year-$day-$month";

Does the trick. Thnx.

[Angry Jew Cat - Banned for Life]

In SSH or terminal if you can;

This was for a database of all the postal zip codes in the United States . PHP is way too slow to load databases.

I like epoch time myself ...

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

[StariaDaniel]

$oldDate = "1/12/2011";
list ($day, $month, $year) = explode("/", $oldDate); 
$newDate = "$year-$day-$month";

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

$oldDate = "1/12/2011";
list ($day, $month, $year) = explode("/", $oldDate); 
$newDate = "$year-$month-$day";

[StariaDaniel]

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

INSERT INTO ... ( ... ) VALUES ( ... , STR_TO_DATE('1/12/2011', '%e/%m/%Y') , ... )

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

[Angry Jew Cat - Banned for Life]

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

INSERT INTO ... ( ... ) VALUES ( ... , STR_TO_DATE('1/12/2011', '%e/%m/%Y') , ... )

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?

[Traxman]

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.

[Wilsy]

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

[Traxman]

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 ;)

[Angry Jew Cat - Banned for Life]

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.

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.

[Angry Jew Cat - Banned for Life]

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 ;)

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.

[Barry-xlovecam]

What Barry said... 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.

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

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

#!/bin/bash
P=password
D=database
U=username
mysql -u$U -p$P <<< 'alter database '$D' default character set utf8;'
mysql -u$U -p$P -D$D <<< 'show tables;' | while read x; do mysql -u$U -p$P\
-D$D <<< 'alter table '$x' convert to character set utf8;'; done

[Barry-xlovecam]

#!/usr/bin/perl
use strict;

$date= s/\//-/g;

#perl substitute s/$z/$x/;

I don't get paid by the word
TIMTOWTDI