Using the DATETIME format will be very beneficial when you are searching by date, because if you are storing it as plain text, you will have to break apart the values in your column into the date and time format before you can perform your date-based search. That means more work when selecting the data (per-use basis) vs one-time work when parsing and inserting your data.
MySQL (or rather, SQL) has many date-related functions you could use. For example, here's one from the MySQL manual on Date and Time Functions to select data from the last 30 days:
SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
As for the milisecond part, I think storing it in a separate column (of numeric type) will be better than trying to mash things (date and time) in string-type columns. Searching for numeric values in string columns is a mess.
You may need to change the PHP script a little bit. For one, you will need to convert the date and time to SQL format.
No need to use another regex (although you could). Use
PHP's split() to accomplish that. If you are using PHP 5, you may even use
strptime () to help you out. After you split the year, month, date, hour, minute, second, milisecond, you simply concatenate them as one string:
PHP:
$datetime = "20{$y}-{$m}-{$d} {$H}:{$M}:{$S}"; # Remember to prefix the '20' for your year to avoid problems
$milisecond = $MS;
Actually, here's a modified regex to parse the date and time parts on their own:
PHP:
foreach ($myText as $val)
{
preg_match ('/^(\d+)\/(\d+)\/(\d+)\s+(\d+):(\d+):(\d+)\.(\d+)\s+(.*?)\s+(Servlet.Engine.Transports : \d+)\s+(.*)$/i', $val, $myResult);
print_r ($myResult);
}
Would I need to manually create a new table each day, or could I automatically create a table before the info is parsed each night?
Only once when you initially create the database. Once it is created, you simply add more data into it on nightly basis. Or, you could be fancy and perform a check and have your script create the table if it doesn't exist. But I don't think it's necessary and it's too much work.
Find out the SQL query to insert the data manually. Don't try to build upon the PHP script provided to you just yet.
By this I mean, manually try to figure out the correct SQL query that will insert your data into your database. This means, from your MySQL client (phpMyAdmin is a good one), type your SQL INSERT query manually to perform the data entry. Once you find out the correct syntax for entering your data, then continue with the next step. This will save you frustration when your script doesn't work as expected, as you may be wondering whether the problem lies with the PHP script or the SQL query.
For step 4, sorry if it was confusing. What I meant was skip the parsing of your log file, and not skip the parsing of the input texts. So, instead of running the full script in post #50, try running the partial script in post #46 first. That is actually a trial and debugging step, where you try your completed PHP script to see whether it will work as expected. That is, check whether your PHP script can parse the data and insert it correctly. There's no point in running 5 hours of processing just to try out your script, right?
Step 3 and 4 should be repeated when you are doing your select script. So, try to come up with the correct SQL SELECT statement first, then apply it to your PHP script.
Btw, I'm assuming this is only for internal use, right? Because if it's for public use, then there's other things to worry about, such as security and all that.
-stndn.
