Page 1 of 1

Ordering table outside MySQL

Posted: Sun Apr 05, 2009 8:26 am
by Killkip
Hi,

So I'm trying to make this database of star systems. I'm having trouble sorting the results of my radius search function. You type in a star system and enter an x amount of ly. The code will calculate based off the systems coordinates which are in range and which are not. So the trouble is I want them to be able to select that the results are ordered by distance. Unfortunately like I said this distance is calculated and is not in the database, meaning a simple ORDER BY does not work.

This is my code:

Code: Select all

<html>
<head>
<title>SQO Stellar Search Database</title>
</head>
<body>
<?php
$sn1=$_POST["sysname1"];
include("mysqlconnect.php");
$gb=$_POST['gb'];
$var=$_POST['var'];
$orderby=$_POST['orderby'];
$login = mysql_query("SELECT * FROM gebruikers WHERE gebruikersnaam='".$gb."'");
$results = mysql_num_rows($login); 
$gebr = mysql_fetch_array($login);
if ($results==1){
include("menu.php");
$max=50;}
else
{include("menu2.php");
$max=30;}
$sys1 = mysql_query("SELECT * FROM systems WHERE sysname='".$sn1."'");
if ($orderby==name){
$sys2 = mysql_query("SELECT * FROM systems ORDER BY sysname");}
else if ($orderby==starclass){
$sys2 = mysql_query("SELECT * FROM systems ORDER BY starclass, sysname");}
else {
$sys2 = mysql_query("SELECT * FROM systems");}
 
$tellen=mysql_num_rows($sys1);
$con1 = mysql_fetch_array($sys1);
 
   if ($tellen==1 && $var<=$max)
   {
   echo "<div id='content'>";
   echo "<h3><b>Systems in range</b></H3>";
echo '<TABLE BORDER="1">';
 echo '<TR>';
 echo '<TD WIDTH="200" bgcolor="#99CCFF">';
 echo '<B>System name:</B>';
    
 echo '</TD>'; 
   echo '<TD WIDTH="130" bgcolor="#99CCFF">'; echo '<B>Star class:</B>';
 echo '</TD>'; 
   echo '<TD WIDTH="100" bgcolor="#99CCFF">'; echo '<B>Distance:</B> (ly)';
 echo '</TD>'; 
 echo '</TR>';
 
    while ($row = mysql_fetch_array($sys2))
    {
    $Xverschil=$con1[Xcoord]-$row[Xcoord];
    $Yverschil=$con1[Ycoord]-$row[Ycoord];
    $Zverschil=$con1[Zcoord]-$row[Zcoord];
    $distance=round(sqrt(pow($Xverschil,2)+pow($Yverschil,2)+pow($Zverschil,2)),2);
        if ($distance<$var and $distance<>0) 
        
{     
        
        echo '<TR>';
        echo '<TD WIDTH="200" bgcolor="white">';
        echo "$row[sysname] <FORM METHOD='post' ACTION='search2.php' style='display:inline; margin-top:0px; margin-bottom:0px;' ><INPUT name='sysname' type='hidden' VALUE='$row[sysname]'><INPUT name='gb' type='hidden' VALUE='$gebr[gebruikersnaam]'><input type=image name='formEditProfileSubmit' src='dubbelarrow.png'  value='Bewerk' ></form> ";
    
        echo '</TD>'; 
        echo '<TD WIDTH="130" bgcolor="white">'; 
        echo $row[starclass];
        echo '</TD>';   
        echo '<TD WIDTH="100" bgcolor="white">'; 
        echo $distance;
        echo '</TD>';   
        echo '</TR>';
        
        
        }else{}
 
        
        
    } 
echo '</TABLE>';
echo '</div>'; 
}
 
else if ($var>$max && $results==0) {?><div id='content'>Maximum distance for guests is <?php echo "$max";?> ly</div> <?php
}
else if ($var>$max && $results==1) {?><div id='content'>Maximum distance is <?php echo "$max";?> ly</div> <?php
}
else 
{
?><div id='content'> System doesn't  excist in database.<BR><BR> Do you want to <A HREF='add.php'>add</A> the system to the SQO Stellar Search Database? </div> <?php
}
mysql_close();
?>
</body>
</html>
It may be a bit messy, but I have just 2 weeks of PHP experience. So is there anyone who can help me to order the table by distance?

Re: Ordering table outside MySQL

Posted: Sun Apr 05, 2009 8:34 am
by requinix
Two options:
1) Include the distance formula in the query so you can sort by it
2) Calculate the distance for each star and sort the results. Which is basically what MySQL would do but a bit less efficiently.

I like #1.

Re: Ordering table outside MySQL

Posted: Sun Apr 05, 2009 8:36 am
by Killkip
#1 sounds great, any hints on how I would do that?

Re: Ordering table outside MySQL

Posted: Sun Apr 05, 2009 10:35 am
by requinix
You can use an expression in a query just like a field.

Code: Select all

SELECT ..., expression AS something... ORDER BY something

Re: Ordering table outside MySQL

Posted: Sun Apr 05, 2009 11:22 am
by Killkip

Code: Select all

$sys2 = mysql_query("SELECT * , expression AS xxx FROM systems ORDER BY xxx");
So I have to insert the formula where you typed "something" and where I used xxx ? I'm really clueless here on how to use this "expression AS". I started by scaling down the formula to one line but then another problem pops up.

Code: Select all

round(sqrt(pow($con1[Xcoord]-$row[Xcoord],2)+pow($con1[Ycoord]-$row[Ycoord],2)+pow($con1[Zcoord]-$row[Zcoord],2)),2)
The formula was in a while loop using $row to select the correct system, but how would I do that in the query?

Thanks for your help so far! :)