Selecting from a cursor

[aleniko]

Hi all;

I'm a Foxpro programmer and I'm trying to do some work with php. Here is my problem:

I need to create a few cursors and then select from the cursors.

In Foxpro its pretty simple -

select something from somewhere into cursor cur1

and then,

select * from cur1 where something = somethingelse

 

I'm having problems doing this with php. I tried declaring the cursor but I'm getting an error. Here is my entire declare code - all the math is for distance lookup.:

 

declare curdist1 CURSOR

FOR

  select acct,name,address2,krccust.city,krccust.state,krccust.zip,krccust.tel,zips.lat,zips.lon,

(

        3959 *

        acos(

                cos(radians($olat)) *

                cos(radians(zips.lat)) *

                cos(radians(zips.lon) - radians($olon)) +

                sin(radians($olat)) *

                sin(radians(zips.lat))

        )

) AS distance

                from krccust left join zips on krccust.zip=zips.zip  ;^M

 

$res = mysql_query("select * from curdist1 where distance > 10");

 

 

[MMDE]

I tried declaring the cursor but I'm getting an error.

Please post the error.

Please use code tags when posting code.

Please explain a bit more what exactly you are trying to do, as I certainly didn't understand (though someone else might understand it). From what I was able to gather, you may want to do something that is run on the client side, but if that is the case, you should know that PHP is serverside scripting.

[aleniko]

Error is:

Parse error: parse error, unexpected T_STRING, expecting '(' in /home/smartsco/public_html/krcweb/jj.php on line 34

Line 34 is

declare curdist1 CURSOR

 

I'm not sure what are code tags...

 

No, this is supposed to work server side only. I need to find records according to geographic proximity, but its a complicated criteria. So I want to create an initial cursor and do my queries on that.

 

Thanks and let me know if you need any additional info.

 

[Jessica]

Wait you just have that SQL randomly placed inside your PHP file?

[Barand]

Mysql does not have the cursor concept you describe but there are a couple of alternatives.

 

1)

    CREATE TEMPORARY TABLE curs1

    SELECT ... (your query) ...

 

    Then run your second query against this temp table.

 

2) Make your main query a table subquery

 

    select * from

      (

        SELECT .... (your main query) ...

      ) as curs1

    where distance > 10

   

 

[aleniko]

Thanks Barand;

So if I needed to run several queries on my initial query, what is the best way of doing so? Using option 2, I will need to run the initial query again and again for every secondary query I make, right?

 

Also, using option 1 would mean I need to create the temp table and populate it manually - not using a select command?

 

Thanks.

[Barand]

The example I gave was a single query

 

Example

 

$sql = "CREATE TEMPORARY TABLE curs1 SELECT x, y, z FROM mytable";
mysql_query ($sql);

mysql_query("SELECT x,y FROM curs1 ORDER BY x");

 

The temp table exists until the mysql connection is closed.

[aleniko]

Wonderful. You've solved my problem. Thanks once more!