[SOLVED] Just need the latest extension to be shown

[suttercain]

Hi everyone,

 

Not sure if this is a MySQL or PHP question, but I think I'll be able to do it in PHP easier... I could be wrong.

 

Anyway, I have a varchar column that contains the following string format.

 

Examples:

DE-04-005

DE-04-005-01

DE-04-005-02

DE-04-006

DE-04-006-01

DE-05-004

 

The first two three characters will always be DE-. The last three characters may be NULL or contain - then two digits.  So from the above example to show the latest extension only I would need to only see:

 

DE-04-005-02

DE-04-006-01

DE-05-004

 

The last two digits, if any, would be the extension. I need the most recent, which would be the highest number from the last two.

 

Any one know of a way to do this? If not I was just going to add a column and add 1 to the latest and pull a WHERE latest = 1, but I'd rather not have to do that with every new extension.

 

Thanks in advance.

 

SC

[efficacious]

have you tried sorting the results?

 

ORDER BY column_name ASC

[suttercain]

Sorting will still display all of the records... just in order. I want only the latest to be shown.

[efficacious]

correct you will pull * of the extensions but then you need to use php to 'show' just the latest few.

[suttercain]

Yes... that is true.. that is why I am asking if anyone knows HOW TO do it as opposed to asking if it is possible.

[efficacious]

you mean you want someone to write the code for you....

 

$Amount='5' //SHOW THE 5 LATEST

$Extension_SQL="SELECT column_name FROM database ORDER BY column_name ASC";
$Extension_Result='mysql_query($Extension_SQL) or die (mysql_error());
$x=0;
while($row=mysql_fetch_array($Extension_Result))
{
$Extensionz[$x] = $row[0];
$x++;
}

for($x=0;$x<=$Amount)
{
echo($Extensionz[$x]."<br />")
}

[efficacious]

forgot a piece of that for loop

 

..

for($x=0;$x<=$Amount;$x++)
{
echo($Extensionz[$x]."<br />")
}

[suttercain]

Wouldn't this do the same thing?

 

$Extension_SQL="SELECT column_name FROM database ORDER BY column_name ASC LIMIT 0, 4";

 

Then loop through the five results?

 

The thing is I know how to limit and order, I need to only show the last extension to the records, there may be 20 or there may be 100. The code above would require me to count and limit it manually. I am looking for a way to only show the latest extensions for that number (record).

[efficacious]

well what criteria are you using to say its 20 or 100?

thats the kind of thing you'll need to use in code to get it to work..

 

if you do it by a date range then use a date range in an if statement to say if its within this date echo it if not doing nothing with it.

 

also yes limit does work but not in all SQL databases, it would work for your case tho yes.