Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You an declare this MySQL function in your application, and it will remain in the database until the database server is restarted.</p> <pre><code>mysql_query("CREATE FUNCTION Distance(LAT_A INT, LON_A INT, LAT_B INT, LON_B INT, ) RETURNS INT READS SQL DATA DETERMINISTIC BEGIN DECLARE radius, deltaLat, deltaLon, result, distance BIGINT; SET radius=3956; SET deltaLat=LAT_B-LAT_A; SET deltaLon=LON_B-LON_A; SET result=POW(SIN(deltaLat/2), 2) + (COS(LAT_A) * COS(LAT_B) * POW(SIN(deltaLon/2.0), 2)); SET distance=radius * 2 * ATAN2(SQRT(result), SQRT(1 - result)); RETURN distance; END"); </code></pre> <p>This uses MySQL's <a href="http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html" rel="noreferrer">mathematical functions</a>. Offloading this processing to the database is fast and efficient (the data doesn't have to travel across the wire, and you're only returned the results you want).</p> <p>Once you've declared this, you can use it like so:</p> <pre><code>$query = "SELECT lat, lon FROM zipcodes WHERE Distance(lat, lon, 0, 0) &lt; 20"; mysql_query($query); </code></pre> <p>However if your database does restart, any functions or procedures declared previously are lost. It's possible to handle MySQL error 1305 (<code>Function functionName does not exist</code>) gracefully at the application level.</p> <p>In your database error handler:</p> <pre><code>switch (mysql_errno()): case 1305: if (false === $database-&gt;_declareStoredProcedureFlag) { if ($c = preg_match_all("/FUNCTION [a-zA-Z0-9]+\." . "([a-zA-Z0-9_]*) does not exist/is", mysql_error(), $matches) ) { $storedFunctionName = $matches[1][0]; $database-&gt;_declareStoredProcedureFlag = true; if (true === $database-&gt;declareStoredFunction($storedFunctionName)) { $result = mysql_query($query); } } } break; ... </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCreating a custom MySQL function?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAndy
    1. USAndy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreating a custom MySQL function?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs this the only option? I'd rather keep it confined to PHP at the moment. I'll wait another few minutes to mark an answer.
      singulars
      1. PO
      1. This table or related slice is empty.
    2. COyeh I guess it's not happening. I'm going to go with this, +1. Cheers mate.
      singulars
      1. PO
      1. This table or related slice is empty.
    3. COBecause you are using the values `lat` and `lon` in the `Distance()` function call in the query, and these come from the database, the only other option is to select the whole dataset (containing those values) from the table into PHP, run it through the PHP version of the function and essentially run number-crunching (which databases are designed to perform efficiently) in PHP (which is significantly slower). You then have the added overhead of transferring the data from the database to PHP. This is the optimum solution, but obviously adds complexity.
      singulars
      1. PO
      1. USAndy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload