Regex date w/ array

[andremantovani]

Hi, all!

 

I've got a text field in my DB, and inside it i have several date formmated like "02/02/2001". I need to find, from all the found dates, which one is the newest. I thought using regex 'd be nice, am i right?

Googlin, i've found http://www.roscripts.com/PHP_regular_expressions_examples-136.html, which lead me using something like:

 

function do_reg($text, $regex)
{
preg_match_all($regex, $text, $result, PREG_PATTERN_ORDER);
for ($i = 0; $i < count($result[0]); $i++) {
 $result[0][$i];
}
}

 

But the given expressions are for cheking if there is any invalid date:

 

//Date yyyy-mm-dd
//1900-01-01 through 2099-12-31
//Matches invalid dates such as February 31st
'(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])'

 

So, anyone?

 

P.s.: this is because i'm importing a full-messed 'DB'.

 

 

Thanks!

[jcbones]

You really should have DB date columns structured as "date" or "datetime" or "timestamp".  This will solve these issues.  There is a reason DB's have date functions built into them.

 

So, If it were me, I would build a script, that would re-order the date, and insert it into a date field.  Making my future interactions with the DB, painless.

[silkfire]

To get the row with the most recent date you don't need PHP, PHP preg_match, nor MySQL Regex, just pure MySQL:

 

SELECT * from db ORDER BY date DESC LIMIT 1

[andremantovani]

Hey all!

 

Thanks for the replies; unfortunatelly, I think i was not so clear. Here's the deal: I'm organizing a messy DB, recreating all the structure and stuff. The mess comes in the text field, where are all the dates I need. For instance, it looks something like this:

modified: 12/12/2010. Text: blahblah; modified: 11/09/2009. Text: blahblah;

This text field doesn't have any pattern that allows me searching through it; and the last date that appears not always is the newest. So my thought is to first use a generic select; then, for each row returned, use regex to find all dates in the text field, and store them in a array. After, find from the array what's the newest date. And finally store it in the datetime column.

That's the steps... in fact all i need is the regex part, that i couldnt get to work.

 

Thanks!

[silkfire]

If you row look like this:

 

modified: 12/12/2010. Text: blahblah; modified: 11/09/2009. Text: blahblah;

 

Do you need both dates here and you'll compare them too?

 

Too capture them just use this regex:

 

'#\d{2}/\d{2}/\d{4}#'

 

You don't have to validate the dates they're probably correct.

[jcbones]

Of course, you don't need PHP for regex either.  You can use it in MySQL too.