Please Help: Table Sorting

[mmpromo]

Hey Everyone,
I've been pulling my hair out on this one. I'm creating an online contact spreadsheet which allows users to view contact info (email, first name, last name, state, status, etc) as well as sort it by Name, state, etc (much like Microsoft Excel). I have to use this database structure (see below) and I'm having trouble with multiple sorting for the database. Here's the DB set up:

TABLE1
UserID, Email

TABLE 2
UserID, UserFields, UserValue
(UserFields = things like First Name, Last Name, State, etc....UserValue is the value of those fields)

EXAMPLE:
TABLE 1
1 [e-mail]
2 [e-mail]
3 [e-mail]

TABLE 2
1 - First Name - Joe
1 - Last Name - Smith
1 - Address - 555 Main St
1 - State - Alaska
2 - First Name - Bob
2 - Last Name - Harper
2 - Address - 255 Poplar Ave
2 - State - Pennsylvania
3 - First Name - Eric
3 - Last Name - Klausen
3 - Address - 111 Chestnut Ave
3 - State - Alaska

I'm going to first parse all the fields by USERID and initially sort by First Name:

<?php
$getmember = mysql_query("SELECT UserID, UserEmail, UserField, UserValues FROM Table1, Table2 WHERE table1.UserID = table2.UserID AND UserField='Last Name' ORDER BY UserValue ASC");

while ($row=mysql_fetch_array($getmember)) {

/ Now I grab all the UserFields & UserValues associated with the UserID and parse them in an associative array/
$getfields = mysql_query("SELECT UserField, UserValues FROM Table2 Where UserID = $row[UserID]");
while ($val = mysql_fetch_array($getfields)) {
$details[$val[UserField]] = $val[UserValue];
}

/ Now from here I can display the row for each user and it will be sorted by First Name already...example:
$display = "$details[First Name] $details[Last Name]<br>$details[City]
$details[State]...etc";/

} //end of initial while loop

Now here's where I'm stuck. I have all the values for each User sorted by First Name and I've retrieved all the FieldValues for each User....how do I now go back and sort not just by First Name but by (let's say) STATE and display that. I don't know the proper way of doing it because the FieldValues and FieldID's are on seperate rows in Table2.

Any help would be appreciated and thanks
mmpromo

[barand]

Why can't you make life easy for yourself and just have a simple conventional table?

userID | FirstName |  LastName |  Address         | State        |  Email          |
-------|-----------|-----------|------------------|--------------|-----------------|
   1   |    Joe    |  Smith    | 555 Main St      | Alaska       | [e-mail] |
   2   |    Bob    |  Harper   | 255 Poplar Ave   | Pennsylvania | [e-mail] |
   3   |    Eric   |  Klausen  | 111 Chestnut Ave | Alaska       | [e-mail] |

[mmpromo]

I would love to but I'm tying it all in with my email system so I have to do it this way. Trust me....if I had it my way, I'd do that exactly and I wouldn't be posting on this board. Thanks for your comment. If anyone can help me out...that would be great.

[barand]

OK, from your data construct an array like this

$data = array (
	1 => array (
	        'email' => '[e-mail]',
	        'FirstName' => 'Joe',
	        'LastName' => 'Smith',
	        'Address' => '555 Main St',
	        'State' => 'Alaska'
		),
	2 => array (
	        'email' => '[e-mail]',
	        'FirstName' => 'Bob',
	        'LastName' => 'Harper',
	        'Address' => '255 Poplar Ave',
	        'State' => 'Pennsylvania'
		),
	3 => array (
	        'email' => '[e-mail]',
	        'FirstName' => 'Eric',
	        'LastName' => 'Klausen',
	        'Address' => '111 Chestnut Ave',
	        'State' => 'Alaska'
		)
);

Now define a custom sort function

function sort_data($a, $b) {
	global $g_sort_key;
	return strcmp ($a[$g_sort_key], $b[$g_sort_key]);
}

To sort by LastName

$g_sort_key = 'LastName';
uasort($data, 'sort_data');
echo '<pre>', print_r($data, true), '</pre>';

Gives

Array
(
    [2] => Array
        (
            [email] => [e-mail]
            [FirstName] => Bob
            [LastName] => Harper
            [Address] => 255 Poplar Ave
            [State] => Pennsylvania
        )

[3] => Array
    (
        [email] => [e-mail]
        [FirstName] => Eric
        [LastName] => Klausen
        [Address] => 111 Chestnut Ave
        [State] => Alaska
    )

[1] => Array
    (
        [email] => [e-mail]
        [FirstName] => Joe
        [LastName] => Smith
        [Address] => 555 Main St
        [State] => Alaska
    )

)

To sort by state

$g_sort_key = 'State';
uasort($data, 'sort_data');
echo '<pre>', print_r($data, true), '</pre>';

Gives

Array
(
    [1] => Array
        (
            [email] => [e-mail]
            [FirstName] => Joe
            [LastName] => Smith
            [Address] => 555 Main St
            [State] => Alaska
        )

[3] => Array
    (
        [email] => [e-mail]
        [FirstName] => Eric
        [LastName] => Klausen
        [Address] => 111 Chestnut Ave
        [State] => Alaska
    )

[2] => Array
    (
        [email] => [e-mail]
        [FirstName] => Bob
        [LastName] => Harper
        [Address] => 255 Poplar Ave
        [State] => Pennsylvania
    )

)