Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    May 2009
    Location
    The Netherlands
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    mysql_fetch_array and a while loop...

    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:
    Code:
      $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:
    Code:
    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!
    Last edited by LaMa; 05-05-2009 at 07:38 PM. Reason: spelling

  2. #2
    GŁtkodierer
    Join Date
    Apr 2009
    Posts
    2,127
    Thanks
    1
    Thanked 426 Times in 424 Posts
    With every
    PHP Code:
    $nearbylocations $arr['WOONPLAATS']; 
    you are overwriting the variable $nearbylocations.

    You could for example use an array, to store all of the values. Declare
    PHP Code:
    $nearbylocations = array(); 
    somewhere before the while loop and use
    PHP Code:
    $nearbylocations []= $arr['WOONPLAATS']; 
    to put it all in. In order to get the format you specified, you can then use
    PHP Code:
    echo implode(" "$nearbylocations); 
    Instead of an array you can also use a string and concat the new values in, like
    PHP Code:
    $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.

  3. Users who have thanked venegal for this post:

    LaMa (05-05-2009)

  4. #3
    New to the CF scene
    Join Date
    May 2009
    Location
    The Netherlands
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much for the help.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •