MYSQL Syntax confusion

Question

I have a database with companies and their zip code addresses. I created a function that would give me names of companies that is within a certain radius. However, I am very bad at MYSQL and the query part is not working. I researched the function from this site and is suppose to work if carried out correctly.I keep getting an error saying that results2 is false. Can someone help me figure out what is wrong about my syntax following $sql =?

  function zipcodeRadius($lat, $lon, $radius)
  {
    $radius = $radius ? $radius : 20;

    $lat = intval($lat);
    $lon = intval($lon);
    $radius = intval($radius);

    $sql = "SELECT company,( 3959 * ACOS(COS(RADIANS($lat) ) * COS(RADIANS( lat ) ) * COS( RADIANS( longitude ) - RADIANS($lon) ) + SIN(RADIANS($lat) ) * SIN(RADIANS( lat ) ) ) ) AS distance FROM COMPANY WHERE distance < 50 ORDER BY distance";
    $result2 = mysql_query($sql);

    while($row2= mysql_fetch_array($result2))
    {
        echo $row2[company]. " - ". $row2[zip];
        echo "<br/>";
    }

    }

yes, the error code is: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/content/72/10165472/html/wp-content/themes/responsive/find.php on line 45 — user1888582, Dec 09 '12 at 11:42
@user1888582 try your query in `phpmyadmin`. And see what is missing. — Yogesh Suthar, Dec 09 '12 at 11:43
I tried just the "SELECT..." query part in phpmyadmin and got this result: SQL query: "SELECT company,( 3959 * ACOS(COS(RADIANS(43) ) * COS(RADIANS( lat ) ) * COS( RADIANS( longitude ) - RADIANS(-71) ) + SIN(RADIANS(43) ) * SIN(RADIANS( lat ) ) ) ) AS distance FROM COMPANY WHERE distance < 50 ORDER BY distance" MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT company,( 3959 * ACOS(COS(RADIANS(43) ) * COS(RADIANS( lat ) ) * COS( RA' at line 1 — user1888582, Dec 09 '12 at 11:44
You can't reference in `WHERE` an alias defined in the `SELECT` list. See this similar question: **[Using 'case expression column' in where clause](http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685)** — ypercubeᵀᴹ, Dec 09 '12 at 11:45
Solution is either to **enclose the query in another one**, moving the `WHERE` part in the external query or **duplicate the calculation in the `WHERE` condition**. — ypercubeᵀᴹ, Dec 09 '12 at 11:47
@ypercube: Your solution worked. I changed WHERE to HAVE and its working fine now. Thank you so much. — user1888582, Dec 09 '12 at 11:56
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained and the [deprecation process](http://j.mp/Rj2iVR) has begun on it. 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). — tereško, Dec 09 '12 at 15:40

score 1 · Answer 1 · answered Dec 09 '12 at 11:48

1

Add

if (!$result) { print mysql_error();}

after mysql_query

answered Dec 09 '12 at 11:48

yunzen

32,854
11
73
106

score 1 · Answer 2 · answered Dec 09 '12 at 11:54

Your query:

SELECT company,
       (complex calculations) AS distance 
FROM COMPANY 
WHERE distance < 50 
ORDER BY distance ;

The problem is that distance is defined (as an alias) in the SELECT list and these aliases cannot be referenced in the WHERE clause. That is how SQL is processed. You have two solutions.

Either encapsulate the query in an external one and move the WHERE there:

SELECT company,
       distance
FROM
  ( SELECT company,
           (complex calculations) AS distance 
    FROM COMPANY 
  ) AS tmp
WHERE distance < 50 
ORDER BY distance ;

or duplicate the code of the calculations:

SELECT company,
       (complex calculations) AS distance 
FROM COMPANY 
WHERE (complex calculations) < 50 
ORDER BY distance ;

score 0 · Answer 3 · answered Dec 09 '12 at 11:45

0

$sql = "SELECT company,( 3959 * ACOS(COS(RADIANS($lat) ) * COS(RADIANS( $lat ) ) * COS( RADIANS( longitude ) - RADIANS($lon) ) + SIN(RADIANS($lat) ) * SIN(RADIANS( $lat ) ) ) ) AS distance FROM COMPANY WHERE distance < 50 ORDER BY distance";

check the variable name $lat used in query - i have corrected that part (shown in Bold-Italic font ) . try it if that works for you

answered Dec 09 '12 at 11:45

behinddwalls

644
14
39

Hi, thanks for your suggestion. Could you explain what was wrong with what I had before? – user1888582 Dec 09 '12 at 11:50
@user1888582 you got your answer? The difference between your query and this query is only the variable `$lat`. Other users was thinking that `lat` is a column name. – Yogesh Suthar Dec 09 '12 at 11:55

MYSQL Syntax confusion

3 Answers3