PHP/Mysql for RSS feed not working correctly.

Fetchitnow · October 8, 2010

Hello All

I have had assistance on here before and its helped me loads. However I have a problem at the moment that has me totally baffled.

I will try and explain the structure as best I can so as you can fully understand what I am doing.

I have a member tracking device that 'pings' their current Long/Latitude details into a dbtable.

I have another dbtable that has the static Long/latitude location based on their actual address.

Within the members only area they can see who (mobile members) is closest to the static members. This is basically so they can trade jobs with one another. Using a basic table I am able to show how far each mobile member is away from the static address using the ORDER BY distance. This bit works a treat.....

However I am trying to do the same but for the mobile member but as an RSS feed for their mobile phones but it will not correctly display the list of members in distance order (closest first).

The first lot of code below is for the table that works. The second one is for the RSS that doesn't.

<?php
require('.php');
dbOpen();
chkAuth($userType);
$memID = $_GET['id'];

$query1 = "SELECT * FROM user_data WHERE id = $memID";
$result1 = mysql_query($query1) or die(mysql_error());
$num = mysql_num_rows($result1);	

$i=0;

while ($i < $num) {

$latitude1 = mysql_result($result1,$i,"lat");
$longitude1 = mysql_result($result1,$i,"lng");


$i++;
}


$query = mysql_query("SELECT *, ( 3959 * acos( cos( radians($latitude1) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($longitude1) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM tracking_location HAVING distance != 100 ORDER BY distance LIMIT 2 , 30") or die(mysql_error());
//distance LIMIT 0 , 20

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') {
global $latitude1, $longitude1;
$theta = $longitude1 - $longitude2;
$distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) +
(cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) *
cos(deg2rad($theta)));
$distance = acos($distance);
$distance = rad2deg($distance);
$distance = $distance * 60 * 1.1515;
switch($unit) {
case 'Mi': break;
case 'Km' : $distance = $distance * 1.609344;
}return (round($distance,5)); 
}


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Quick Look up - Fetch It Now</title>
<link type="text/css" rel="stylesheet" href="css" />

</head>
<body>

<?php include(".php"); ?>
<div id="content">

<p>Find couriers to work with fast</p>



  <?
if(mysql_num_rows($query) > 0){	
?>

<table width="100%" border="0" align="center" cellpadding="6" cellspacing="1" >

  <tr>
    <td width="10%" bgcolor="#EBEBEB"><p><strong> id</strong></p></td>
    <td width="10%" bgcolor="#EBEBEB"><p><strong> Locate</strong></p></td>
    <td width="10%" bgcolor="#EBEBEB"><p><strong> Lat</strong></p></td>
    <td width="10%" bgcolor="#EBEBEB"><p><strong> Long</strong></p></td>
    
    <td width="10%" bgcolor="#EBEBEB"><p><strong> Distance</strong></p></td>
    <td width="10%" bgcolor="#EBEBEB"><p><strong> Tracking</strong></p></td>
  
  </tr>

<?
	while($row = mysql_fetch_array($query)){

?>
  <tr>

      
  
<td valign="top" bgcolor="#F5F5F5"><p style="color:#505050;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;"> <?=$row["id"];?></p></td>
<td valign="top" bgcolor="#F5F5F5"><p style="color:#505050;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;"> <?=$row["locate"];?></p></td>
<td valign="top" bgcolor="#F5F5F5"><p style="color:#505050;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;"> <?=$row["lat"];?></p></td>
<td valign="top" bgcolor="#F5F5F5"><p style="color:#505050;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;"> <?=$row["lng"];?></p></td>
<td valign="top" bgcolor="#F5F5F5"><p style="color:#505050;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;"> <? echo getDistanceBetweenPointsNew($latitude1, $longitude1, $row["lat"], $row["lng"], $unit = 'Mi'); ?>

<td valign="top" bgcolor="#F5F5F5"><p style="color:#505050;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;"> <a href="http://www.fetchitnow.co.uk/tracking-courier.php?id=<?=$row["id"];?>">track</a></p></td>

    

                                                   

  </tr>

<?
		}
?>
</table>
<?
} else{
dbClose();
?>
<?
	}

?>


</table>
</form>



</table>
</form>
</div>


<?php include("php"); ?>
</body>
</html>

This is the code for the RSS feed that just does not want to put it into order of closest first.

<?
header('Content-type: application/rss+xml; charset=utf-8');
?>
<? require('*.php');

dbOpen();

$query1 = ("SELECT * FROM tracking_location WHERE id = 209");
$result1 = mysql_query($query1) or die(mysql_error());
$num = mysql_num_rows($result1);	

$i=0;

while ($i < $num) {

$latitude1 = mysql_result($result1,$i,"lat");
$longitude1 = mysql_result($result1,$i,"lng");


$i++;
}

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') {
global $latitude1, $longitude1;
$theta = $longitude1 - $longitude2;
$distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) +
(cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) *
cos(deg2rad($theta)));
$distance = acos($distance);
$distance = rad2deg($distance);
$distance = $distance * 60 * 1.1515;
switch($unit) {
case 'Mi': break;
case 'Km' : $distance = $distance * 1.609344;
}return (round($distance,0)); 
}

$query = mysql_query("SELECT *, ( 3959 * acos( cos( radians($latitude1) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($longitude1) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM user_data HAVING distance !=10 ORDER BY distance LIMIT 1, 20") or die(mysql_error());
?>
<?php


echo "<?xml version=\"1.0\" ?>"; 

echo "<rss version=\"2.0\">  

<channel>  
<title>FIN - RSS </title>  
<description>Couriers on outbound destinations looking for work to return back to their home base.</description>
<link>http://www.website.co.uk</link>" ;

if(mysql_num_rows($query) > 0 ){	

while($r = mysql_fetch_assoc($query)){


//while($r = mysql_num_rows($query)> 0) // While there is more rows to get (max of 5) we get an associative array
//{

echo "<item>\n";  
echo "<title>".str_replace('&','&',$r['companyname'])." Job to clear".$r['telephone']."</title>\n";  
echo "<description>" ?><? echo getDistanceBetweenPointsNew($latitude1, $longitude1, $r['lat'], $r['lng'], $unit = 'Mi');?><? echo "</description>\n";  
echo "<pubDate></pubDate>\n";

echo "</item>\n"; 

}
echo "</channel>\n";
echo "</rss>\n";
} else{
}
?>

BlueSkyIS · October 8, 2010

i suggest that you figure out a way to use the same code in both scripts, via include or something. Anyway, why does the first query LIMIT start at 2 while the second query starts LIMIT at 1?

Fetchitnow · October 9, 2010

There is no real reason to the Limit being different. I was just moving the figures around but they have no real influence on the result.

I just find it very strange that what I am asking is identical yet I can't get it to fire out the result I want.

BlueSkyIS · October 9, 2010

they should have a real influence on the result. in the first instance, LIMIT 2, 30 means give me 30 records starting with the second record. in the second instance, LIMIT 1, 20 means give me 20 records starting with the first record. The fact that the first instance skips the first record while the second one doesn't is pretty significant, and they are certainly not "identical".

Fetchitnow · October 11, 2010

I understand the influence and criteria of the LIMIT function and yes it will have an influence on the output. However changing the parameters will not alter the actual order in which they appear.

Having spent the majority of the weekend toying around I am still at a loss with it as it seems that even though I am instructing the SELECT Query to output the order by the distance closest to the varilable. However it appears to be ignoring the request altogether. :shrug:

It will really hack me off if this is something very simple that I am missing.

BlueSkyIS · October 11, 2010

another issue is the hard-coded search value in "SELECT * FROM tracking_location WHERE id = 209"

Fetchitnow · October 11, 2010

Ah yes. Well since the post I have even ripped that out.

I have pinned it down to:

$query = mysql_query("SELECT *, ( 3959 * acos( cos( radians($latitude1) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($longitude1) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM user_data HAVING distance !=10 ORDER BY distance LIMIT 1, 20") or die(mysql_error());
?>

The $latitude1 and $longitude1 are both number that relate to my current geographical location. So hopefully to test I should get a list of other static locations that are within 10 miles of me. I do get the distances for each post but they are not in order from nearest first.

I am running out of options as to what it might be. I know my luck it will be staring me in the face.

Thanks for the help so far though.

fenway · October 17, 2010

Dump the resultset and see.

Sign In

PHP/Mysql for RSS feed not working correctly.

Recommended Posts

Fetchitnow

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Fetchitnow

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Fetchitnow

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Fetchitnow

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information