Sorting via a variable?

[doomdude]

Is it possible to sort a table of mysql data, by using a variable?

 

I'm trying this:

 

<?php

$result = mysql_query("SELECT * FROM members ORDER BY '$totalpower'");
        echo "<table border='1' cellpadding='10'>";
               echo "<tr> 
			<th>Rank</th> 
			<th>Username</th> 
		</tr>";

while($row = mysql_fetch_array($result))
  {
  
                echo "<tr>";
                echo '<td> </td>';
                echo '<td>' . $row['Username'] . '</td>';
                echo "</tr>"; 
  }
        echo "</table>";
?>

 

It display's a list of username's but not in the correct power order.

 

$totalpower is defined:

 

<?php

//power calculations

        $result = mysql_query("SELECT * FROM members WHERE Username='$_SESSION[username]'")                 
        or die(mysql_error());  				    	
        while($row = mysql_fetch_array( $result )) {   						
            $power1 = $row['ounit1'] * 50;		
		$power2 = $row['ounit2'] * 70;	
		$power3 = $row['ounit3'] * 110;
		$power4 = $row['ounit4'] * 200;
		$power5 = $row['ounit5'] * 150;
		$power6 = $row['ounit6'] * 300;
		$power7 = $row['ounit7'] * 500;
		$power8 = $row['ounit8'] * 450;
		$power9 = $row['ounit9'] * 650;
		$power10 = $row['ounit10'] * 350;
		$power11 = $row['ounit11'] * 600;
		$power12 = $row['ounit12'] * 1000;	

		$dpower1 = $row['dunit1'] * 50;
		$dpower2 = $row['dunit2'] * 70;	
		$dpower3 = $row['dunit3'] * 110;
		$dpower4 = $row['dunit4'] * 200;
		$dpower5 = $row['dunit5'] * 150;
		$dpower6 = $row['dunit6'] * 300;
		$dpower7 = $row['dunit7'] * 500;
		$dpower8 = $row['dunit8'] * 450;
		$dpower9 = $row['dunit9'] * 650;
		$dpower10 = $row['dunit10'] * 350;
		$dpower11 = $row['dunit11'] * 600;
		$dpower12 = $row['dunit12'] * 1000;	

		$dbase = $row['baselevel'] * 3000;
        }

	$offencepower = $power1+$power2+$power3+$power4+$power5+$power6+$power7+$power8+$power9+$power10+$power11+$power12;
	$defencepower = $dpower1+$dpower2+$dpower3+$dpower4+$dpower5+$dpower6+$dpower7+$dpower8+$dpower9+$dpower10+$dpower11+$dpower12+$dbase;
	$totalpower = (($offencepower+$defencepower)/ 100 * 30) + $offencepower+$defencepower;

?> 

 

Also how do I add auto numbers in the left column e.g 1. 2. 3. 4.

 

Thanks in advance.

[Psycho]

Well, first off, are you POSITIVE that the variable $totalpower is defined as you expect it to? I would suggest NEVER creating your queries inside the mysql_query() function. Instead, create the queries as a variable - then if there are any problems you can echo the query to the page to validate it is built how you think it should.

 

However, even if $totalpower is defined correctly, your problem is that you enclosed in in single quotes. That tells MySQL that it is a string - not a field. You can enclose it in back-quotes or none at all. Again, this probably would have been more obvious had you echo'd the query tot he page

 

<?php

$query = "SELECT `Username` FROM members ORDER BY {$totalpower}";
$result = mysql_query($query);

$output = '';
$rank = 0;
while($row = mysql_fetch_assoc($result))
{
    $no++;
    $output .= "<tr>\n";
    $output .= "<td>{$rank }</td>";
    $output .= "<td>{$row['Username']}</td>\n";
    $output .= "</tr>\n"; 
}

?>
<table border='1' cellpadding='10'>
  <tr>
    <th>Rank</th> 
    <th>Username</th> 
  </tr>
  <?php echo $output; ?>
</table>";

 

A couple other points. 1) Only select the fields you need in our query - it is more efficient. Don't use '*' unless you need everything. Also, I would highly suggest you start learning to break the logic (i.e. PHP logic code) and the presentation (i.e. HTML code) in your scripts. I modified the code so that the logic to get the records is separated from the display of the records. It is easier to maintain code in this fashion.

[doomdude]

Hi thanks for the help, using:

 

<?php

$query = "SELECT `Username` FROM members ORDER BY {$totalpower}";
$result = mysql_query($query);

$output = '';
$rank = 0;
while($row = mysql_fetch_assoc($result))
{
    $no++;
    $output .= "<tr>\n";
    $output .= "<td>{$rank }</td>";
    $output .= "<td>{$row['Username']}</td>\n";
    $output .= "</tr>\n"; 
}

?>
<table border='1' cellpadding='10'>
  <tr>
    <th>Rank</th> 
    <th>Username</th> 
  </tr>
  <?php echo $output; ?>
</table>";

 

I'm given the error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/u835743061/public_html/top.php on line 45

Rank Username

";

 

Will this be because the $totalpower is called from the session? As the other place I use $totalpower is to just display the power of the user in the side bar? Will I need to call it some other way?

[Psycho]

I have no idea. Your query is failing and you have no error handling. I'm guessing that the value of $totalpower is not what you expect or is not what you need. You should always have code in your scripts to elegantly handle errors.

 

For a development environment, you can just use something like

$result = mysql_query($query) or die("Query: {$query}<br>Error: " . mysql_error());

 

However, for a production environment you would never want to display the raw error to the user.

 

 

In this particular situation, is $totalpower the VALUE for the user's total power? If so, you don't need a variable, just put in the field name that you want to sort by. The only reason to use a variable is if you wanted to sort by different fields.

[doomdude]

The variable $totalpower, is worked out from the field's ounit1 ounit2 .... ounit12 * the power of each, + the dunit1 dunit2... dunit12 + baselevel * 3000

 

Here:

 

<?php

//power calculations

        $result = mysql_query("SELECT * FROM members WHERE Username='$_SESSION[username]'")                 
        or die(mysql_error());  				    	
        while($row = mysql_fetch_array( $result )) {   						
            $power1 = $row['ounit1'] * 50;		
		$power2 = $row['ounit2'] * 70;	
		$power3 = $row['ounit3'] * 110;
		$power4 = $row['ounit4'] * 200;
		$power5 = $row['ounit5'] * 150;
		$power6 = $row['ounit6'] * 300;
		$power7 = $row['ounit7'] * 500;
		$power8 = $row['ounit8'] * 450;
		$power9 = $row['ounit9'] * 650;
		$power10 = $row['ounit10'] * 350;
		$power11 = $row['ounit11'] * 600;
		$power12 = $row['ounit12'] * 1000;	

		$dpower1 = $row['dunit1'] * 50;
		$dpower2 = $row['dunit2'] * 70;	
		$dpower3 = $row['dunit3'] * 110;
		$dpower4 = $row['dunit4'] * 200;
		$dpower5 = $row['dunit5'] * 150;
		$dpower6 = $row['dunit6'] * 300;
		$dpower7 = $row['dunit7'] * 500;
		$dpower8 = $row['dunit8'] * 450;
		$dpower9 = $row['dunit9'] * 650;
		$dpower10 = $row['dunit10'] * 350;
		$dpower11 = $row['dunit11'] * 600;
		$dpower12 = $row['dunit12'] * 1000;	

		$dbase = $row['baselevel'] * 3000;
        }

	$offencepower = $power1+$power2+$power3+$power4+$power5+$power6+$power7+$power8+$power9+$power10+$power11+$power12;
	$defencepower = $dpower1+$dpower2+$dpower3+$dpower4+$dpower5+$dpower6+$dpower7+$dpower8+$dpower9+$dpower10+$dpower11+$dpower12+$dbase;
	$totalpower = (($offencepower+$defencepower)/ 100 * 30) + $offencepower+$defencepower;

?> 

[Psycho]

OK, so Total Power is a calculated value. And, $totalpower is the calculated value for the current user. So, think about what you were doing. The query would have looked something like

SELECT `Username` FROM members ORDER BY 56000

 

That makes no sense, right? So, if this is a calculated value for each user, you then need to calculate the value for each user and sort by that. With all those fields it won't be a pretty query. In fact, I don't think it is a good idea to "hard-code" the multipliers in the code (i.e. * 70, * 110, * 200). I would put those in a separate table of the database.

 

After looking a your calculations, here is what I have come up with. I can't grantee it will work since I don't have your database, but I did test the logic on a database of mine. Note that you need to change "userid" to whatever the unique id field is called in your table (used in three places)

 

SELECT members.Username, ((calc_table.sum_total/100*30)+calc_table.sum_total) as total_power
FROM members
JOIN (SELECT userid, 
             (ounit1*50 + ounit2*70 + ounit3*110 + ounit4*200 + ounit5*150 + ounit6*300
              + ounit7*500 + ounit8*450 + ounit9*650 + ounit10*350 + ounit11*600 + ounit12*1000
              + dunit1*50 + dunit2*70 + dunit3*110 + dunit4*200 + dunit5*150 + dunit6*300
              + dunit7*500 + dunit8*450 + dunit9*650 + dunit10*350 + dunit11*600 + dunit12*1000
              + baselevel*3000) as sum_total
      FROM members
     ) as calc_table
  ON members.userid = calc_table.userid
ORDER BY total_power

 

Another option, would be to change your insert/update scripts to calculate the total power and store that value as part of the record.

 

 

[doomdude]

Thanks! I'm thinking i'll create a query that works out the users power then inserts it into there database.

 

e.g.

 

Have a "Update Power" link that works out the power then add's it into "totalpower" I'll then just be able to use the SORT query on the top scores page I believe.

[Psycho]

Thanks! I'm thinking i'll create a query that works out the users power then inserts it into there database.

 

e.g.

 

Have a "Update Power" link that works out the power then add's it into "totalpower" I'll then just be able to use the SORT query on the top scores page I believe.

 

I think it would be a poor idea to have to manually update that value. If someone makes changes to the data for a user, that value will be out of sync. Instead, create a function to calculate the power. Then, find any instances where you create/update those records. Use the function to calculate the value and use that value when you INSERT/UPDATE the records.