Store values of SQL query in variables

Question

I have a SELECT SQL statement that returns a Latitude, Longitude and Distance as result (using the Haversine formula). Now I need to store these values as variables within the PHP script as I need to do further processing on the results. Can anyone tell me how I can do this?

Here is my SQL Statement

UPDATED

$query = $db->prepare("SELECT `Latitude`, `Longitude`, (6378160 * acos( cos( radians(:latitude) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( Latitude ) ) ) ) AS distance FROM `mytable` HAVING distance < :radius ORDER BY distance;");
// Assign parameters
$query->bindParam(':latitude',$newLatitude);
$query->bindParam(':longitude',$newLongitude);
$query->bindParam(':radius',$radius);

//Execute query
$query->execute();
$q   = $db->query($query);
$q->setFetchMode(PDO::FETCH_ASSOC);

// fetch
while($r = $q->fetch()){
  $eLatitude = $r->Latitude;
  $eLongitude = $r>Longitude;
  $distance = $r->Distance;
}

I updated my php code above. Is this code's syntax correct? Or the way I'm doing it is all wrong?

execute query and in while loop fetch results and assign them to variables, what's the problem — v0d1ch
– v0d1ch, Commented Jan 3, 2013 at 14:44
Also, there are much more efficient ways to do this if you search here for mysql geolocation. — Eric Petroelje
– Eric Petroelje, Commented Jan 3, 2013 at 14:46
@vodich I am new to PDO/PHP altogether so I am not pretty sure how this can be done — user1809790
– user1809790, Commented Jan 3, 2013 at 14:46

david strachan · Accepted Answer · 2013-01-04 18:01:27Z

1

PDO::FETCH_ASSOC: returns an array indexed by column name Manual

while($r = $q->fetch()){
  echo $r['Latitude']. "\n"; //Or do what ever instead of echo
  echo $r['Longitude']. "\n";
  echo $r['Distance']. "\n";
}

ie XML
while($r = $q->fetch()){
  $node = $dom->createElement("marker");
  $newnode->setAttribute("lat",  $r['Latitude']);
  $newnode->setAttribute("lng",  $r['Longitude']);
  $newnode->setAttribute("distance",  $r['Distance']);
}

ie JSON
while($r = $q->fetch()){
  data[] = $r;
}

answered Jan 4, 2013 at 18:01

david strachan

7,2282 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fathah Rehman P · Accepted Answer · 2013-01-03 14:51:34Z

0

you can use

$queryresult = mysql_query("SELECT `Latitude`, `Longitude`, (6378160 * acos( cos( radians(:latitude) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( Latitude ) ) ) ) AS distance FROM `mytable` HAVING distance < :radius ORDER BY distance;");
if (!$queryresult) {
    echo 'Error ' . mysql_error();
    exit;
}
$resultrow = mysql_fetch_row($queryresult);

echo $resultrow[0];  // this will be latitude
echo $resultrow[1];  // this will be longitude

answered Jan 3, 2013 at 14:51

Fathah Rehman P

8,8014 gold badges43 silver badges42 bronze badges

1 Comment

EM-Creations Over a year ago

Since the original mysql functions have now been deprecated and will be removed in the future, please don't provide examples using the mysql functions, especially when the person asking the question is already using MySQLi.

Collectives™ on Stack Overflow

Store values of SQL query in variables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related