title: php returns null while mysql returns value for a user variable
body: <p>The following query returns '5' when run using MySql Workbench but returns null using PHP, against the same local apache server:</p>
  
  <pre><code>SELECT @distance_in_km from branch where (@distance_in_km := 5) limit 1
  </code></pre>
  
  <p>On the other hand, when the query is</p>
  
  <pre><code>SELECT 5 as distance_in_km from branch limit 1
  </code></pre>
  
  <p>both workbench and PHP return 5.</p>
  
  <p>I know I can solve it (get the field distance_in_km to have the value 5 as a result of the query) by modifying the first query as follows:</p>
  
  <pre><code>SELECT 5 as distance_in_km, @distance_in_km from branch where (@distance_in_km := 5) limit 1
  </code></pre>
  
  <p>In this case workbench returns '5,5' and PHP returns '5, null' which is fine for me because I get the value using json so I can look for the field 'distance_in_km' instead of '@distance_in_km'.</p>
  
  <p>However, my real query is much more complicated and I want to avoid calculating the field 'distance_in_km' twice.</p>
  
  <p><strong>EDIT 1</strong></p>
  
  <p>It looks as if the problem lies in the way PHP evaluates the user variable 'distance_in_km'. On further testing I found out that although PHP returns null values it does know it's true value, because when adding a WHERE test, PHP evaluates correctly:</p>
  
  <pre><code>SELECT @distance_in_km from branch where (@distance_in_km := 5) &lt;= 4 limit 1
  </code></pre>
  
  <p>PHP returns 0 records, whereas changing the 4 to 5 in the above test, PHP returns 1 record.</p>
  
  <p>On the other hand, PHP does not sort properly when adding the clause 'ORDER BY @distance_in_km' (it returns the records sorted by the Primary key, which shows PHP is using the null value of '@distance_in_km' instead of it's real value.</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>PHP version 5.5.12,  Apache version 2.4.9, MYSQL version 5.6.17 (all taken from the wampserver 2.5 menu).</p>
  
  <p>From the same menu I opened MySQL console, and running the queries above and got the result as from PHP. Here is the session </p>
  
  <p><strong>using the console:</strong> </p>
  
  <pre><code>Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 476
  Server version: 5.6.17 MySQL Community Server (GPL)
  
  Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
  
  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql&gt; use q;
  Database changed
  mysql&gt; SELECT @distance_in_km from branch where (@distance_in_km := 5) limit 1;
  +-----------------+
  | @distance_in_km |
  +-----------------+
  | NULL            |
  +-----------------+
  1 row in set (0.00 sec)
  
  mysql&gt; SELECT @distance_in_km from branch where (@distance_in_km := 5) &lt;= 4 limit 1;
  Empty set (0.00 sec)
  
  mysql&gt; SELECT @distance_in_km from branch where (@distance_in_km := 5) &lt;= 5 limit 1;
  +-----------------+
  | @distance_in_km |
  +-----------------+
  |               5 |
  +-----------------+
  1 row in set (0.00 sec)
  
  mysql&gt;
  </code></pre>
  
  <p><strong>PHP Code</strong></p>
  
  <pre><code>$query = "SELECT @distance_in_km from branch where (@distance_in_km := 5) &lt;= 5 limit 1";
  $result = $db-&gt;query($query) or die('Err: Table not found: ' . mysql_error());
  echo json_encode($result-&gt;fetch_row());
  </code></pre>
  
  <p><strong>EDIT 3</strong></p>
  
  <p>According to the modified SELECT statement suggested by @Stan I tried changing my code but was not successful. One can see that distance_in_km does not get the proper values from branch.</p>
  
  <pre><code>mysql&gt; SELECT @distance_in_km, latitude from branch, (SELECT @distance_in_km := latitude from branch) as uservar limit 3;
  +-----------------+-----------+
  | @distance_in_km | latitude  |
  +-----------------+-----------+
  | 32.0616233      | 32.180644 |
  | 32.0616233      | 32.195598 |
  | 32.0616233      | 32.197176 |
  +-----------------+-----------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>EDIT 4</strong></p>
  
  <pre><code>SELECT @distance_in_km, co.*, c.*
  FROM `branch` c
  LEFT JOIN open_hours co ON c.open = co.id,
  (SELECT @distance_in_km := 111.1111 * DEGREES(ACOS(COS(RADIANS(latitude)) * 
      COS(RADIANS('32.177242')) * COS(RADIANS(longitude) - RADIANS('34.863834')) + SIN(RADIANS(latitude)) * 
      SIN(RADIANS('32.177242')))) from branch) as uservar 
  WHERE c.company='1' and @distance_in_km &lt;= 20
  ORDER BY @distance_in_km LIMIT 5 OFFSET 0;
  </code></pre>
  
  <p><strong>EDIT 5</strong></p>
  
  <p>Until a better solution comes up I ended defining the calculation as a UDF (User defined function) and I call it 3 times in the same SELECT!!!. Very inefficient but I see no other way. Thus the SELECT looks now:</p>
  
  <pre><code>SELECT distance_in_km(latitude, 32.177242, longitude, 34.863834) as km, c.latitude, c.longitude, c.id FROM `branch` c
              WHERE c.company='1' and distance_in_km(latitude, 32.177242, longitude, 34.863834) &lt;= 20
              ORDER BY distance_in_km(latitude, 32.177242, longitude, 34.863834) LIMIT 5 OFFSET 0
  </code></pre>
  
  <p>On further testing it looks like it works also if I use the variable km in the ORDER BY so that I have the function twice in the SELECT</p>
  
  <pre><code>SELECT distance_in_km(latitude, 32.177242, longitude, 34.863834) as km, c.latitude, c.longitude, c.id FROM `branch` c
              WHERE c.company='1' and distance_in_km(latitude, 32.177242, longitude, 34.863834) &lt;= 20
              ORDER BY km LIMIT 5 OFFSET 0 
  </code></pre>