[SOLVED] Sort info by Icelandic ID number?

[egillsigurdur]

I have this signup form for a sports team, and I'd like to be able to sort them into an Excel file by age.  It is only requested that you give up your ID number, and I have no idea how I'm going to do that.

 

This is what the Icelandic ID number is like: 2306823269.

 

23:day of month.

06:month.

82:year born (1982)

3269:some number that doesn't matter.

 

 

 

So, how would I do this?  I also have to take in count that many of these children are born in 20--.

 

I already know how to put info in Excel, just not how to sort them from the MySQL database by this.

 

 

Thank you.

[trq]

Your not going to be able to sort them in a query. These numbers mean nothing to mysql.

 

You'll need to write you own parser of sorts, using strtotime, again, however, theres no way of handling the problem of people being born in 20** if the data isn't stored in the first place. Id say your pretty screwed.

[bundyxc]

How about this:

 

$id = 2306823269;
$values = array ( //Sorts all data out of ID into array.
		'day'   => substr( $id, 0, 2); //Returns '23'
		'month' => substr( $id, 2, 2); //Returns '06'
		'year'  => substr( $id, 4, 2); //Returns '82'
		'other' => substr( $id, 6, 4); //Returns '3269'
		);

$currentYear = date('y'); //Returns a two-digit representation of the current year.

if ($values['year'] <= $currentYear) { //Evaluates between a 1900 birthdate or a 2000 birthdate.
//Born in 2000's.
$values['year'] = '20' . $values['year']
} else {
//Born in 1900's.
$values['year'] = '19' . $values['year']
}

[trq]

What of someone born in 1907?

[egillsigurdur]

No one older than 90 will be entering this sport team.

 

Thanks bundyxc, but how will I make this to a "SELECT FROM 'blahblah' sort by 'icelandic_id_number'";

[bundyxc]

What are you trying to do?

 

The script I wrote will take any Icelandic ID and distinguish the four values from it (day/month/year/other). Are you trying to input the four values into a database, or do you have a full Icelandic ID in the database that you need seperated?

 

If you let me know what you're doing, I can help you out.

[egillsigurdur]

I have everything in a database, every row has an Icelandic ID number to it, 0311942919, 3108012140, 1212053280, you know.  They all include the birthday of a person.

 

I have the usual

mysql_connect("###", "###", "###") or die(mysql_error()); 
mysql_select_db("###") or die(mysql_error()); 
$data = mysql_query("SELECT * FROM $table") or die(mysql_error()); 

and then some complex while() to print it out to an excel file.

 

How could I do a

$data = mysql_query("SELECT * FROM $table ORDER BY 'idnumber' ASC")); 

 

yet let PHP know what to sort by?  First sort by year, then month, then day.

I got this working in a HTML table with some weird JavaScript, but don't know how in the world I'm going to do this.

[PFMaBiSmAd]

Untested but should work -

ORDER BY STR_TO_DATE(LEFT(idnumber,6),'%d%m%y')

[bundyxc]

See, my problem right now is that I don't know what you're trying to do, or what sort of table you're making in Excel...

[egillsigurdur]

Pretty much just printing every single row in a table.  Simply need to know how to sort them by ID number.

 

Thanks.

[PFMaBiSmAd]

The following takes into account the two digit year and will work until the year 2098, assuming you are not going to put in any values for people with a birth year greater than the current year (i.e. those yet to be born) -

SELECT *,CONCAT_WS('-',IF(SUBSTRING(idnumber,5,2) > SUBSTRING(YEAR(CURDATE()),3,2),CONCAT('19',SUBSTRING(idnumber,5,2)),CONCAT('20',SUBSTRING(idnumber,5,2))),SUBSTRING(idnumber,3,2),LEFT(idnumber,2)) as date FROM your_table ORDER BY date

[egillsigurdur]

Awesome!!! Thank you so much!