Page 1 of 1

Array Sorting

Posted: Mon Mar 02, 2009 6:22 am
by sandstorm140
I'd like to be able to sort an array by one of the 3 values (username, joindate, or last_login) before it gets echod off. using ksort($value[2]); or something that would work.
How can I go about doing this?

Code: Select all

 
    while ($row[67] = mysql_fetch_assoc($result[67])) {
        $rowRe[] = array(1=>$row[67]['username'], $row[67]['joindate'], $row[67]['last_login']);
    }
    
    foreach ($rowRe as $value) {
        echo('
            <tr align="left" class="accountinfo">
                <td align="center">' . $iii++ . '</td>
                <td>' . $value[1] . '</td>
                <td align="center">' . substr($value[2], 0, -9) . '</td>
                <td align="left" colspan="7">' . substr($value[3], 0, -9) . '</td>
            </tr>');    
    }
    
 

Re: Array Sorting

Posted: Mon Mar 02, 2009 7:23 am
by Mark Baker
Why not put the ORDER BY in your database query

Re: Array Sorting

Posted: Mon Mar 02, 2009 7:38 am
by sandstorm140
Because i'm pulling information from multiple tables :?

Re: Array Sorting

Posted: Mon Mar 02, 2009 7:58 am
by Mark Baker
sandstorm140 wrote:Because i'm pulling information from multiple tables
Depending on what you're pulling from where and how, it could well still be possible to use ORDER BY.

Are you using a single query to retrieve the data from multiple tables, or the highly inefficient (but very commonplace) method of executing several queries (one against each table)?

Re: Array Sorting

Posted: Mon Mar 02, 2009 8:14 am
by sandstorm140
For example:

$result['characters'] = $connect->run_query("characters", "SELECT * FROM characters WHERE online='1'");
$row[1] = mysql_fetch_assoc($result['characters'])

This table has some IDs that I need and use to pull from another table:
example:
$result['accounts'] = $connect->run_query("accounts", "SELECT * FROM account WHERE id='".$row[1]['account']."'");
$row[2] = mysql_fetch_assoc($result['accounts']);

It's the easiest way I know.

Re: Array Sorting

Posted: Mon Mar 02, 2009 8:25 am
by Mark Baker
That query could probably be written with a join.
I'd use something like:

Code: Select all

 
SELECT C.*,
       A.account_name
  FROM characters C,
       account A
 WHERE C.online='1'
   AND A.id = C.account
 
though I tend to use Oracle rather than MySQL.
I'm sure some of the MySQL guys here on the forums could come up with a better alternative


This is a lot more efficient and faster too, because you only need to query the database once to retrieve all the data you need from the two tables, rather than once for characters and then once for each character to retrieve the account information

Then you can also add an

Code: Select all

 
ORDER BY A.account_name, C.character_name
 
or whatever sort order you need

Re: Array Sorting

Posted: Mon Mar 02, 2009 8:33 am
by sandstorm140
Ok thanks, I'll read up on some mysql join codes to understand it better.

Thanks

Re: Array Sorting

Posted: Mon Mar 02, 2009 4:02 pm
by sandstorm140
I just realized using Joins won't work. I failed to mention earlier that i'm also pulling data from different databases and it appears joining can't do that.