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 06:38 PM. Reason: spelling

  • #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.

  • Users who have thanked venegal for this post:

    LaMa (05-05-2009)

  • #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
    •