Parameters in a Sub Select

pmabey

I am using a Sub Select in a where clause and have it working with static values.

    ->where( function ( $q ) {
           $q->where( 'segment_id', '(SELECT map_id FROM maps WHERE ST_DWithin(geog, ST_GeomFromText(\'POINT(-122.1640 37.4321)\',4326),1000))', 'IN', false );
    } )

This populates a table correctly using this postgis based query. All good at this point.
I then wanted to pass the latitude/longitude values in from the calling javascript file.

I defined the values as follows:

            ajax: {
                    url: 'php/table.location.php',
                    type: 'POST',
                    data: function ( d ) {
                            d.longitude = "-122.1640";
                    }
            },

And then modified the where clause to take the longitude from the above parameter.

            $q->where( 'segment_id', '(SELECT map_id FROM maps WHERE ST_DWithin(geog, ST_GeomFromText(\'POINT(:longitude 37.4321)\',4326),1000))', 'IN', false );
            $q->bind( ':longitude', $_POST['longitude'] );

But it gives me an error:
DataTables warning: table id=fibers - SQLSTATE[HY093]: Invalid parameter number: :longitude

I've done some basic testing passing the parameter to a much simpler Sub select and it works fine so I am pretty sure its something to do with the POINT part of the SQL query thats messing it up.

Any suggestions?

bindrid

it value is going to the server as a string. Is that appropriate for this query?

pmabey

I tried sending it as an integer as well as float with no luck.

allan

You would need to have the longitude parameter being send by the Ajax request that both DataTables and Editor make. Is the above only set for Editor?

Allan

pmabey

I had it only on DataTables but added it to Editor as well with no change. This is where my PHP isn't strong but I have a simple script working with this SQL query as follows:

$lat = $_POST['latitude'];
$lon = $_POST['longitude'];

$query = "SELECT * FROM maps WHERE ST_DWithin(geog, ST_GeomFromText('POINT({$lon} {$lat})',4326),1000)";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

I'm basically trying to do the exact same thing but have the results returned in a DataTables. I'm sure its probably a simple syntax issue here but its not jumping out at me. One idea was whether I need to construct the whole "POINT" part of the query separately and pass it as a variable to the where function.

allan

I must confess I don't immediately see anything wrong with that.

Could you show me both your full PHP and Javascript please? Or a link to the page so I can inspect what is happening on the client-side.

Thanks,
Allan

pmabey

Made a little progress with some trial and error. I changed the PHP to:

                $q->where( 'segment_id', '(SELECT map_id FROM maps WHERE ST_DWithin(geog, ST_GeographyFromText(:coordinates),1000))', 'IN', false );
                $q->bind( ':coordinates', $_POST['coordinates'] );

And in the JS I am passing the POINT statement:

                ajax: {
                        url: 'php/table.location.php',
                        type: 'POST',
                        data: function ( d ) {
                                d.coordinates = "POINT(-122.1640 37.4321)";
                        }
                },

This actually works now.

My theory is the single quotes around the POINT statement were causing a problem, even though I tried to escape them. I tried just sending (-122.1640 37.4321) but that didn't work.

Anyway, it should be easy to construct this data within the Javascript each time.

Thanks for the extra set of eyes on this...it was driving me crazy !!

allan

Hi,

Good to hear you've got it working! If this is on a public site, you will probably want to add some validation to the $_POST['coordinates'] variable to make sure it is what you expect. Otherwise there is potential for an SQL injection attack.

Allan

pmabey

Thanks for the tip....fortunately its a private site but its still worth doing what you suggest to harden things up so I'll take a look into that.