fetch all rows satisfying condition and then do some action for each row

Question

I have a php webpage which finds all the users in 1km radius of latitude and longitude provided as input. The lat and long are given as input to database and it generates a list of 10 users as output. I want to send a message to each user of this generated list after retrieving their device ids. But i am not able to do so. Please help

$sql = "SELECT number, userID, 
( 6371 * acos( cos( radians(19.143) ) 
               * cos( radians( latitude ) ) 
               * cos( radians( longitude ) 
                   - radians(72.831) ) 
               + sin( radians(19.143) ) 
               * sin( radians( latitude ) ) 
             )
) AS distance 
FROM users
WHERE device = 'android' 
HAVING distance < 2 
ORDER BY distance;";

$result = mysql_query($sql,$link);

if (mysql_num_rows($result) != 0) 
{
    while($row = mysql_fetch_assoc($result))
    {
                //send notification code
    }
}

When i execute this code, i get an error

mysql_fetch_assoc() expects parameter 1 to be resource, string given in..

Please help. Thanks in advance.

What's the output of echo $result;? It should be a resource, not a string. — Wojciech Zylinski, Jan 02 '13 at 14:14
you are supposed to open a database at some point, and to pass it to mysql_query — njzk2, Jan 02 '13 at 14:14
[**Please, don't use `mysql_*` functions in new code**](http://stackoverflow.com/a/14110189/1723893). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — NullPoiиteя, Jan 02 '13 at 14:14
are `latitude` and `longitude` variables, or is that a column name? — NappingRabbit, Jan 02 '13 at 14:14
`code`$link = mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); — Rishi Jasapara, Jan 02 '13 at 14:15
Do as said above, but use or die(mysql_error()); that way it might show relevant info instead of the error you tell it to — Mattt, Jan 02 '13 at 14:21
Try `$result = mysql_query($sql,$link) or die(mysql_error())` — , Jan 02 '13 at 14:21
The warning is still shown, but the push notifications are sent.The code is working now.. i think every suggestion here helped. Thanks alot guys :) — Rishi Jasapara, Jan 02 '13 at 14:22
Why shouldn't pring the sql variable and try running the same on query browser — satdev86, Jan 02 '13 at 14:52

score 0 · Answer 1 · answered Jan 02 '13 at 14:30

0

Please try this

mysql_fetch_array($result,**MYSQL_ASSOC**)

insead of:

mysql_fetch_assoc()

it may solve your problem

answered Jan 02 '13 at 14:30

sandip

3,279
5
31
54

score 0 · Answer 2 · answered Jan 02 '13 at 14:50

0

First check the existence or result

if (!$result) {
    echo mysql_error();
    exit;
}

if it exits then debug your query.

answered Jan 02 '13 at 14:50

satdev86

800
7
14

Pifon · Answer 3 · 2013-01-02T14:35:01.987

-1

first thought: ORDER BY distance;"; will give error, try: ORDER BY distance ASC"; So I think you have string - error message in your result - you may try checking mysql_error() if empty before fetching results.

Above assuming - connection works and $link set

SECOND thought: as per :http://php.net/manual/en/function.mysql-query.php

**The query string should not end with a semicolon.

Data inside the query should be properly escaped.**

edited Jan 02 '13 at 14:35

answered Jan 02 '13 at 14:17

Pifon

346
3
16

there is an extra semicolon, but the `ASC` is default and can be omitted. – NappingRabbit Jan 02 '13 at 14:18
1

The qualifier ASC or DESC is not required. – Mattt Jan 02 '13 at 14:19

fetch all rows satisfying condition and then do some action for each row

3 Answers3