get new num_rows count after filtering mysqli_fetch_array rows via php

[seany123]

Im trying to get the num_rows count of all the rows which have been selected but only after they have been filtered by php, i cant do the filtering through mysql as its requires the values to go through functions.

<?php
 $query = mysqli_query($db, "select * from users");
while ($result = mysqli_fetch_array($query)) {

if ($result[0] == custom_function($result[0])) {
//remove this row from the $result array?
}
}
?>

i thought about using a $i++ in the while loop to count the records until completion but that wont give me the final count until its run through the entire while loop and if i want to use this for pagination or something similar i wouldn't be able to?

 

sean

Edited April 24, 2015 by seany123

[ginerjm]

Why not save each $row into your own array if it is one you want. Then you can get the count of that array when you are done and use that array as your data source from that point on.

 

if ($result[0] == custom_function($result[0]))
{
$my_results[] = $result;
}

 

$my_results will hold all the 'valid' rows from your query.

[Barand]

What filtering are you doing in the PHP function?

[Muddy_Funster]

Im trying to get the num_rows count of all the rows which have been selected but only after they have been filtered by php, i cant do the filtering through mysql as its requires the values to go through functions.

...

 

If you have such complex filtering that MySQL really can't do it then your DB structure is seriously screwed.  Show us your filtering logic, I'm sure Barand would be able give you the MySQL equivalent without much sweat.

[Psycho]

 . . .  if i want to use this for pagination or something similar i wouldn't be able to?

 

If you need this for pagination, you definitely need to find a solution to filter via the DB - else you are going to need to create overly complicated logic to do pagination.

[seany123]

Why not save each $row into your own array if it is one you want. Then you can get the count of that array when you are done and use that array as your data source from that point on.

 

if ($result[0] == custom_function($result[0]))
{
$my_results[] = $result;
}

 

$my_results will hold all the 'valid' rows from your query.

 

yes that could work thankyou ill try.

 

What filtering are you doing in the PHP function?

 

If you have such complex filtering that MySQL really can't do it then your DB structure is seriously screwed.  Show us your filtering logic, I'm sure Barand would be able give you the MySQL equivalent without much sweat.

 

the script is a searching for users, i then take 2 fields from the users table $result['city'] and $result['postcode'] and pass them through this function which uses that info+ $users postcode and city and uses google api to return the distance between them in miles:

<?php

    //Function to get the distance between 2 points. can use postcode,cityname or geolocation.
    function getDistance($from, $to) {

    $from = urlencode($from);
    $to = urlencode($to);

    $data = file_get_contents("http://maps.googleapis.com/maps/api/distancematrix/json?origins=$from&destinations=$to&language=en-EN&sensor=false");
    $data = json_decode($data);

    $distance = 0;

    foreach($data->rows[0]->elements as $road) {
        $distance += $road->distance->value;
        $distance = ($distance * 0.000621371192);
    }
    return $distance;
    }

?>

then i filter out the results based on what $distance returns

 

thanks.

Edited April 24, 2015 by seany123

[Barand]

If you were to store the lat and long of each user, or had a lookup table of lat and long by postcode, you could do the distance calculations in the query. This would be far mode efficient than accessing the google api for every record.

[seany123]

If you were to store the lat and long of each user, or had a lookup table of lat and long by postcode, you could do the distance calculations in the query. This would be far mode efficient than accessing the google api for every record.

 

really? i think the first option would be the easiest to implement, i would just need convert the county+city+postcode/zip into the lat and long.. im sure there are already examples available of how its done.

 

but how would i then using mysql query filter out using lat+long for distance under $x miles apart?

[seany123]

upon doing a little research maybe its not so easy..

[Barand]

You haven't yet told us what you are trying to achieve, except you are calculating distances. From where to where?

[seany123]

im trying to create a search script which allows users to search for eachother based on different criteria, 1 of the criteria is they can find people within x miles of them.

 

so the user already has their postcode/city in the db, the search retrieves other users postcode/city etc.

 

so its calculating the distance from $user['city'].$user['postcode'] to $result['city'].$result['postcode']

Edited April 24, 2015 by seany123

[Barand]

Something like this, perhaps, assuming a postcode table ( postcode | latitude | longitude )

// Me and my latitude and longitude
$myId = 123;
$mylat = 54.123456;
$mylong = -2.345678;

// Find users within what distance?
$target = 25;

$sql = "SELECT name
    , latitude
    , longitude
    , ROUND(ATAN2(SQRT(POW(COS(RADIANS($mylat)) * SIN(RADIANS(longitude - $mylong)), 2) 
        + POW(COS(RADIANS(latitude)) * SIN(RADIANS($mylat)) 
        - SIN(RADIANS(latitude)) * COS(RADIANS($mylat)) 
        * COS(RADIANS(longitude - $mylong)), 2)), (SIN(RADIANS(latitude)) 
        * SIN(RADIANS($mylat)) + COS(RADIANS(latitude)) * COS(RADIANS($mylat)) 
        * COS(RADIANS(longitude - $mylong)))) * 6372.795,0) as distance
    FROM user
        INNER JOIN postcode USING (postcode)
    WHERE userid <> $myId    
    HAVING distance <= $target
    ORDER BY distance ";

Edited April 24, 2015 by Barand

[seany123]

Okay so im going to be getting the longitude and latitude of the city+postcode by using this code:

        $address = "Norwich, NR8 6QW"; //persons address as a string.
        $prepAddr = str_replace(' ','+',$address);
        $geocode=file_get_contents('http://maps.google.com/maps/api/geocode/json?address='.$prepAddr.'&sensor=false');
        $output= json_decode($geocode);
        $latitude = $output->results[0]->geometry->location->lat;
        $longitude = $output->results[0]->geometry->location->lng;
      

it seems to be working good, what datatype would be best to store these values in a mysql table?

 

 

Something like this, perhaps, assuming a postcode table ( postcode | latitude | longitude )

// Me and my latitude and longitude
$myId = 123;
$mylat = 54.123456;
$mylong = -2.345678;

// Find users within what distance?
$target = 25;

$sql = "SELECT name
    , latitude
    , longitude
    , ROUND(ATAN2(SQRT(POW(COS(RADIANS($mylat)) * SIN(RADIANS(longitude - $mylong)), 2) 
        + POW(COS(RADIANS(latitude)) * SIN(RADIANS($mylat)) 
        - SIN(RADIANS(latitude)) * COS(RADIANS($mylat)) 
        * COS(RADIANS(longitude - $mylong)), 2)), (SIN(RADIANS(latitude)) 
        * SIN(RADIANS($mylat)) + COS(RADIANS(latitude)) * COS(RADIANS($mylat)) 
        * COS(RADIANS(longitude - $mylong)))) * 6372.795,0) as distance
    FROM user
        INNER JOIN postcode USING (postcode)
    WHERE userid <> $myId    
    HAVING distance <= $target
    ORDER BY distance ";

 

once i have the longitude and latitude set up for each user i can then begin to test the code you have posted to see how it looks.

 

thanks

seany

[seany123]

 

Something like this, perhaps, assuming a postcode table ( postcode | latitude | longitude )

// Me and my latitude and longitude
$myId = 123;
$mylat = 54.123456;
$mylong = -2.345678;

// Find users within what distance?
$target = 25;

$sql = "SELECT name
    , latitude
    , longitude
    , ROUND(ATAN2(SQRT(POW(COS(RADIANS($mylat)) * SIN(RADIANS(longitude - $mylong)), 2) 
        + POW(COS(RADIANS(latitude)) * SIN(RADIANS($mylat)) 
        - SIN(RADIANS(latitude)) * COS(RADIANS($mylat)) 
        * COS(RADIANS(longitude - $mylong)), 2)), (SIN(RADIANS(latitude)) 
        * SIN(RADIANS($mylat)) + COS(RADIANS(latitude)) * COS(RADIANS($mylat)) 
        * COS(RADIANS(longitude - $mylong)))) * 6372.795,0) as distance
    FROM user
        INNER JOIN postcode USING (postcode)
    WHERE userid <> $myId    
    HAVING distance <= $target
    ORDER BY distance ";

i added that code to my query and after adjusting tablenames etc and removing the INNER JOIN postcode USING (postcode) it is working quite well.

 

can i ask what the reason for joining postcode? when its not being used in the query?

[Barand]

Postcode is the common key between the two tables. It is the PRIMARY key in the postcode table and a FOREIGN key in each user record. That is how relational databases work.

 

I suggested a postcode table with lat and long as these can be downloaded from the internet. Lat and Long would be FLOAT type.

Edited April 25, 2015 by Barand