Ordering table outside MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Killkip
Forum Newbie
Posts: 3
Joined: Sun Apr 05, 2009 8:25 am

Ordering table outside MySQL

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Ordering table outside MySQL

Post 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.
Killkip
Forum Newbie
Posts: 3
Joined: Sun Apr 05, 2009 8:25 am

Re: Ordering table outside MySQL

Post by Killkip »

#1 sounds great, any hints on how I would do that?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Ordering table outside MySQL

Post by requinix »

You can use an expression in a query just like a field.

Code: Select all

SELECT ..., expression AS something... ORDER BY something
Killkip
Forum Newbie
Posts: 3
Joined: Sun Apr 05, 2009 8:25 am

Re: Ordering table outside MySQL

Post 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! :)
Post Reply