PHP functions within mysql_queries

Is it possible to use the data from the current record in a data base within a function that is nested within the query that is searching the table?

In other words, inside of table_a there is a field named postarea, would this then be possible:

SELECT * FROM table_a WHERE .........  && some_function(postarea)>32

Code (markup):

 

Nope, that's not possible. But there are a lot of MySQL functions that may help.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

 

No, the function makes a query to another table and does some complex math that cant' be done in a mysql query.....

How about this.....Is there a such a thing as a query that is too long? The way that I worked the above problem out is by creating a query from the function. So it will produce a query that looks like:

SELECT * FROM here WHERE 
(((blue=color) || (yellow=color)) && 
((this=that) || (that=this) || or (i=eye)))

Code (markup):

Only the parts in the (), like (blue=color), there will be over 3000 of these in some cases, and the query could be searching over 100,000 records.

Is there a limit to how many arguments you can pass, and if not, how much processor power is a single query going to consume if it has over 3000 arguments? Is it something I should worry about?

 

You can use the IN() function to make the query shorter.

So instead of:

[...] WHERE this = 'that' OR this = 'something' OR this = 'something-else' OR [...]

Code (sql):

You can do:

[...] WHERE this IN('that', 'something', 'something-else')

Code (sql):

 

If you can show us some code (PHP and MySQL) maybe we can help you better - a query with 3000 comparisons doesn't seem a Good Idea to me, but maybe I'm wrong...

 

Okay, so I am making an advanced search form for a classifieds site.

One search option is to limit the results to within a geographical area.

I have a table named post_grid that stores UK postcodes with the coordinates of the postcode.

I made a function called distance_between(postcode_1, postcode_2) that queries the table post_grid and returns a number that represents how many miles there are between the 2 postcodes.

The person's postcode that is searching the data base is called $userpost.

There is another table that is used to store ads. In the ads table is a field called postcode.

There are ~2900 possible postcodes in the post_grid table.

Okay, so for some code:

$distancequery='';
if(isset($distance))
  {
  $query="SELECT * FROM post_grid";
  $result=mysql_query($query);
  $numcodes=0;

  while($row=mysql_fetch_array($result))
    {
    if(distance_between($userpost, $row['postcode'])<$distance)
      {
      $numcodes++;
      $area[$numcodes]=$row['postcode'];
      }
    }

if(!empty($category) || !empty($search) || isset($price))
  {
  $distancequery.=' && ';
  }
    $distancequery.='(';

    for($a=1; $a<=$numcodes; $a++)
      {
      $distancequery.=sprintf("(postcode='%s')",$area[$a]);
      if($a!=$numcodes)
        {
        $distancequery.=' || ';
        }
      }
    $distancequery.=')';
  }

Code (markup):

This is the code that builds a part of the query. This is the code that can create as many as ~2900 arguments. The problem I'm having is getting rid of this code to clean up the query a bit. What would be ideal is if I could change the above query section into:

(distance_between($userpost, postcode)<$distance)

Code (markup):

distance_between is a function
$userpost is the users post code
postcode is the postcode of the current record in the ads table
$distance is the number the user passed to the script

So I've been frying my brain all night trying to figure out a better way to do this, but for the life of me I can't come up with a better solution that works, and I can't find any materials on the internet that would help me to understand it better. I've searched "query within a query php mysql", "sub queries mysql", and a host of other similar terms.

 

How do you calculate 'distance_between()'? I'm sure we can do that in a subquery...
You can find more info on MySQL subqueries here:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

 

The table - post_grid - is made up of 3 fields. postcode, x, and y. x and y are coordinates and are the number of meters from a common point of origin. Because they use miles in the UK, I had to convert the meters into miles.

Here is the distance_between function:

function distance_between($base, $item)
  {
  $query="SELECT * FROM post_grid WHERE postcode='$base'";
  $result=mysql_query($query);
  $base_array=mysql_fetch_array($result);

  $query="SELECT * FROM post_grid WHERE postcode='$item'";
  $result=mysql_query($query);
  $item_array=mysql_fetch_array($result);

  $x=abs($item_array['x'] - $base_array['x']);
  $y=abs($item_array['y'] - $base_array['y']);
  $x*=$x;
  $y*=$y;
  $z=sqrt($y+$x)/1000;
  $z=round($z*.62, 1);
  return $z;
  }

Code (markup):

 

A self join should be enough:

SELECT SQRT(POW(ABS(a.x - b.x),2) + POW(ABS(a.y - b.y),2)) / 1000 * 0.62 AS distance
FROM post_grid AS a, post_grid AS b
WHERE a.postcode = '$item'
ORDER BY distance

Code (markup):

Beware, this is TOTALLY UNTESTED and just and idea...

 

That's great, I didn't know that math functions could exist in a query....That's a great start in the right direction, now I just need to figure out where to learn the math functions at, and then join that up with my other query, and I think that'll do the trick.

 

You can start here:
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

 

I just realized, by following the link above, that mysql is a language all in it's own. All this time I thought it was confined to simple SELECT's FROM's and WHERE's.