sorting tables

[B34ST]

I have the following script which first gets a list of users out of one table, it then uses this list to get the stats for each user out of another table I would like to know how I can sort the table by $cmb DESC (or even let the user decide how to sort it via a dropdown menu but this would affect $rank?). I dont think i can use sql as there is 26 entries per user in the stats table. also is there an easier way of doing this? I always seem to do everything the long way.

 

<?php

//get a list of registered users
$userlist_sql = "SELECT username
	 FROM users";
$userlist_result = mysql_query($userlist_sql) or die("Could not query: " . mysql_error());

//make an array of usernames
$userlist = array();
$i = 0;
while ($userlist_row = mysql_fetch_assoc($userlist_result))
{
$userlist[$i]['username'] = $userlist_row['username'];
$i++;
}

//start to output table
echo '<table border="0" cellpadding="2" cellspacing="1">
<tr>
  <th class="titlemedium">RANK</th>
  <th class="titlemedium">RSN</th>
  <th class="titlemedium">CMB</th>
  <th class="titlemedium">HP</th>
  <th class="titlemedium">Overall</th>
  <th class="titlemedium">HIGHEST</th>
</tr>';

//Get stats for each user
$rank = 1;
foreach ($userlist as $r => $value)
{
//get data
$rsn = $userlist[$r]['username'];
$stats_sql = "SELECT *
	       FROM stats
	       WHERE userID='$rsn'";
$stats_result = mysql_query($stats_sql) or die("Could not query: " . mysql_error());

$cmb = '';
while ($stats_row = mysql_fetch_assoc($stats_result))
{
	//get users best skill (sort by experience not level)
	$highest_sql = "SELECT cat, statB
			FROM stats
			WHERE userID='$rsn'
			AND cat!='Overall'
			AND cat!='Combat'
			ORDER BY statC DESC
			LIMIT 1";
	$highest_result = mysql_query($highest_sql) or die("Could not query: " . mysql_error());
	$highest_row = mysql_fetch_assoc($highest_result);
	$highest = $highest_row['statB'] . ' ' . $highest_row['cat'];

	//get users combat level
	if ($stats_row['cat'] =='Combat')
	{
		$cmb = $stats_row['statB'];
	}
	//get users hp level
	if ($stats_row['cat'] =='Hitpoints')
	{
		$hp = $stats_row['statB'];
	}
	//get users total lvl
	if ($stats_row['cat'] =='Overall')
	{
		$overall = number_format($stats_row['statB'],0);
	}
}

//output this data
echo '<tr class="row4">
	<td>' . $rank . '</td>
	<td>' . $rsn . '</td>
	<td>' . $cmb . '</td>
	<td>' . $hp . '</td>
	<td>' . $overall . '</td>
	<td>' . $highest . '</td>
      </tr>';
//use foreach count to look like clan rank
$rank++;
}

echo '</table>';
?>

 

Any help would be greatfully appreciated

 

Thanks

[B34ST]

I have used something like this in the past:

 

function desc($a, $b)
{
   if($a['combat'] == $b['combat'])
      return 0;
   else
      return ($a['combat'] > $b['combat']) ? -1 : 1;
}

usort($stats_row, 'desc');

 

but it doesnt work with the script in question I think this is because its embedded in a foreach loop

[B34ST]

hmm this seems to have ppl stumped? or has it just been overlooked? If this problem cant be solved can anyone direct me on how data is usually sorted?

[boo_lolly]

this can be done in many ways. you can dynamically alter your sql query by allowing the user to specify how they want the results to be displayed. this can be done via $_POST method. for example:

<?php
        $sql = "your query here";
        if(isset($_POST['order_by'])){
                $sql .= " ORDER BY ". $_POST['order_by'];
        }

        echo "<form action=\"\" method=\"post\">\n";
        echo "<select name=\"order_by\">\n";
        echo "<option value=\"DESC\">descending order\n";
        echo "<option value=\"ASC\">ascending order\n";
        echo "</select>\n";
        echo "<input type=\"submit\">\n";
        echo "</form>\n";
?>

 

something like that would work.

[B34ST]

ok thanks I'll have a play with that code and see if there is any way I can fit it into my script

[B34ST]

I just realised I was forgetting my own problem, I cant use sql for this particualar script (I dont think i can anyway) because its not as simple as ordering one column. my db has 5 column: userid, cat, stata, statb, and statc. in this db there is 26 entries per user because there is 26 categories(cat) if I display a table of each category seperately I have no problem with sorting the data but for the main memberlist I want to have a selection of categories displayed combat being the one that determines the users clan rank.

<?php

//get a list of registered users
$userlist_sql = "SELECT username
	 FROM users";
$userlist_result = mysql_query($userlist_sql) or die("Could not query: " . mysql_error());

//make an array of usernames
$userlist = array();
$i = 0;
while ($userlist_row = mysql_fetch_assoc($userlist_result))
{
$userlist[$i]['username'] = $userlist_row['username'];
$i++;
}

//start to output table
echo '<table border="0" cellpadding="2" cellspacing="1">
<tr>
  <th class="titlemedium">RANK</th>
  <th class="titlemedium">RSN</th>
  <th class="titlemedium">CMB</th>
  <th class="titlemedium">HP</th>
  <th class="titlemedium">Overall</th>
  <th class="titlemedium">HIGHEST</th>
</tr>';

//Get stats for each user
$rank = 1;
foreach ($userlist as $r => $value)
{
//get data
$rsn = $userlist[$r]['username'];
$stats_sql = "SELECT *
	       FROM stats
	       WHERE userID='$rsn'";
$stats_result = mysql_query($stats_sql) or die("Could not query: " . mysql_error());

$cmb = '';
while ($stats_row = mysql_fetch_assoc($stats_result))
{
	//get users best skill (sort by experience not level)
	$highest_sql = "SELECT cat, statB
			FROM stats
			WHERE userID='$rsn'
			AND cat!='Overall'
			AND cat!='Combat'
			ORDER BY statC DESC
			LIMIT 1";
	$highest_result = mysql_query($highest_sql) or die("Could not query: " . mysql_error());
	$highest_row = mysql_fetch_assoc($highest_result);
	$highest = $highest_row['statB'] . ' ' . $highest_row['cat'];

	//get users combat level
	if ($stats_row['cat'] =='Combat')
	{
		$cmb = $stats_row['statB'];
	}
	//get users hp level
	if ($stats_row['cat'] =='Hitpoints')
	{
		$hp = $stats_row['statB'];
	}
	//get users total lvl
	if ($stats_row['cat'] =='Overall')
	{
		$overall = number_format($stats_row['statB'],0);
	}
}

//output this data
echo '<tr class="row4">
	<td>' . $rank . '</td>
	<td>' . $rsn . '</td>
	<td>' . $cmb . '</td>
	<td>' . $hp . '</td>
	<td>' . $overall . '</td>
	<td>' . $highest . '</td>
      </tr>';
//use foreach count to look like clan rank
$rank++;
}

echo '</table>';
?>

 

 

[boo_lolly]

it's a little difficult to follow your code and figure out what you are doing. try using better variable names to not get confused. in your while loop you're pumping one value into a new nested array for each index in your existing array. whats the deal with that???

[B34ST]

ah, I am doing that because i have one table with a list of members and another table with all the stats so i have to get all the usernames first and then go and get the relevant stats for each of the members. there is probly an easier way of doing it but thats the only way i could think of doing it

[B34ST]

I have solved this problem now simply by getting the combat lvl out of the db first and then doing the rest so no need for the nested foreach loop. Thanks for the help in particular asking why I was using the nested loop.