...

View Full Version : mysql_fetch_array and a while loop...



LaMa
05-05-2009, 06:21 PM
Hi,

I'm new to this community and php in general.
Please bear with me as I try to explain my beginner's problem.

My goal is to run a database query to retrieve 10 rows based on latitude/longitude from a column named "WOONPLAATS"
in a table named "POSTCODES" and to store all results into a single variable to feed it into a second database query later on.

Here is the code I have tried:

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN 4.1548057940675 AND 4.5785282059325) AND (latitude BETWEEN 51.869565217391 AND 52.130434782609) LIMIT 10");
while ($arr = mysql_fetch_array($query)) {
$nearbylocations = $arr['WOONPLAATS'];
}

However this seems to store only 1 result (the "WOONPLAATS" value from the first row) into the $nearbylocations variable.

Can anyone explain how to store all 10 results into a single variable, formatted with spaces in between each result?
At the moment, I'm using 10 seperate queries/variables...not very efficient.

Here's the entire code to help illustrate what I'm trying to achieve:

if ($_GET['mode'] == 'search') {

if ($_POST != $nil) $_SESSION['searchwhatphrase'] = $_POST['SEARCHWHAT'];
$searchwhatstring= "$_SESSION[searchwhatphrase] ";
$searchwhatstring=str_replace(" ", '* ', $searchwhatstring);

if ($_POST != $nil) $_SESSION['searchwherephrase'] = $_POST['SEARCHWHERE'];
$searchwherestring= "$_SESSION[searchwherephrase] ";

// Start geolocating the where_search string...
$SQL = "SELECT * FROM POSTCODES WHERE ID > '1' AND MATCH (WOONPLAATS, POSTCODE) AGAINST ('$searchwherestring' IN BOOLEAN MODE) LIMIT 1";
$result = mysql_query( $SQL ); $row = mysql_fetch_array( $result );
$lat = $row[LATITUDE];
$long = $row[LONGITUDE];

$longitude = (float) $long;
$latitude = (float) $lat;
$radius = 9; // in miles

$lng_min = $longitude - $radius / abs(cos(deg2rad($latitude)) * 69);
$lng_max = $longitude + $radius / abs(cos(deg2rad($latitude)) * 69);
$lat_min = $latitude - ($radius / 69);
$lat_max = $latitude + ($radius / 69);

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 10");
while ($arr = mysql_fetch_array($query)) {
$temp1 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 9");
while ($arr = mysql_fetch_array($query)) {
$temp2 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 8");
while ($arr = mysql_fetch_array($query)) {
$temp3 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 7");
while ($arr = mysql_fetch_array($query)) {
$temp4 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 6");
while ($arr = mysql_fetch_array($query)) {
$temp5 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 5");
while ($arr = mysql_fetch_array($query)) {
$temp6 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 4");
while ($arr = mysql_fetch_array($query)) {
$temp7 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 3");
while ($arr = mysql_fetch_array($query)) {
$temp8 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 2");
while ($arr = mysql_fetch_array($query)) {
$temp9 = $arr['WOONPLAATS'];
}

$query = mysql_query("SELECT * FROM POSTCODES WHERE (longitude BETWEEN $lng_min AND $lng_max) AND (latitude BETWEEN $lat_min AND $lat_max) ORDER BY ID DESC LIMIT 1");
while ($arr = mysql_fetch_array($query)) {
$temp10 = $arr['WOONPLAATS'];
}

if ($searchwhatstring != $nil && $searchwhatstring != '*' && $searchwhatstring != '* ' && $searchwhatstring != ' *') {
$what_search = "WHERE STATUSS != '1' AND MATCH (TITLE, CAPTION, DESCRIPTION) AGAINST ('$searchwhatstring' IN BOOLEAN MODE)";
} else {
$what_search = "WHERE STATUSS != '1'";
}

if ($searchwherestring != $nil && $searchwherestring != '*' && $searchwherestring != '* ' && $searchwherestring != ' *')
$where_search = "AND MATCH (COUNTRY, CITY, STREET, ZIP) AGAINST ('$searchwherestring $temp1 $temp2 $temp3 $temp4 $temp5 $temp6 $temp7 $temp8 $temp9 $temp10' IN BOOLEAN MODE)";

$query1 = @mysql_query ("SELECT * FROM $table $what_search $where_search");
$numrows = @mysql_num_rows ($query1);
if (!isset ($_GET['show'])) {
$display = 1;
} else {
$display = $_GET['show'];
}

$start = (($display * $_SESSION['limit']) - $_SESSION['limit']);
$query2 = @mysql_query ("SELECT * FROM $table $what_search $where_search LIMIT $start,$_SESSION[limit]");
while ($row = @mysql_fetch_array ($query2)) {
include "browse_display.php";
}
}

Thanks for any help!

venegal
05-05-2009, 06:37 PM
With every
$nearbylocations = $arr['WOONPLAATS']; you are overwriting the variable $nearbylocations.

You could for example use an array, to store all of the values. Declare
$nearbylocations = array(); somewhere before the while loop and use
$nearbylocations []= $arr['WOONPLAATS']; to put it all in. In order to get the format you specified, you can then use
echo implode(" ", $nearbylocations);

Instead of an array you can also use a string and concat the new values in, like

$nearbylocations .= $arr['WOONPLAATS']." ";
This will immediately get you the format you specified, except for that one space you will have at the end, if you don't do anything against it.

LaMa
05-05-2009, 07:03 PM
Thank you very much for the help.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum