php pg_fetch_array only show first result

Question

i query to check if a point(input) is intersect with polygons in php:

$sql1="SELECT ST_intersects((ST_SetSRID( ST_Point($startlng, $startlat),4326))
                    , zona_bahaya.geom) as intersek
                    FROM zona_bahaya";
$query1 = pg_query($conn,$sql1);
$check_location = pg_fetch_array($query1);  
if (in_array('t',$check_location)) {
dosemthing1;} 
else {dosomething2;}

it's work peroperly before i update the data
after data updated, it's only show the first row when i check the pg_fetch_array result. here is the result {"0":"f","intersek":"f"} .
i try to check from pgadmin and it's can show 8 result (1 true(intersected) and 7 false(not intersect)) using updated data with this query:

SELECT ST_intersects((ST_SetSRID( ST_Point(110.18898065505843, -7.9634510320131175),4326))
                    , zona_bahaya.geom) as intersek
                    FROM zona_bahaya;

to solve it, i order the query result descended so the 'true' gonna be the first like this:

order by intersek desc

anybody can help me to findout way it just only show the first row???

here some geom from STAsText(zonabahaya.geom) not all of them : MULTIPOLYGON(((110.790892426072 -8.19307615541514,110.791999687385 -8.19318330973567,110.794393723931 -8.1927980624753,110.794586347561 -8.19205508561603,110.795329324421 -8.19120203811094,110.796540101525 -8.19023891996003,110.797503219676 -8.18933083713203,110.798576408472 -8.18919324882476,110.79929186767 -8.18957849608512,110.800337538805 -8.19059664955894,110.800585197758 -8.19150473238694,110.80022746816 -8.19238529755349,110.799787185576 -8.19290813312112,110.799589319279 -8.19300706626968,110.798788231202 -8.19299429992581,110.798537293576 -8.19311976873883,110.79850269889 -8.1933090511224,110.798620939451 -8.19433728092441)))

@JimJones Jones i update the question with some geom. is it what you want ? — astaga
– astaga, Commented Sep 3, 2021 at 4:45

Jim Jones · Accepted Answer · 2021-09-04 07:03:06Z

1

In order to filter only the records that intersect you have to use ST_Intersects in the WHERE clause:

SELECT *
FROM zona_bahaya
WHERE ST_Intersects(ST_SetSRID(ST_Point(110.18, -7.96),4326),zona_bahaya.geom);

Since you're dealing with points and polygons, perhaps you should take a look also at ST_Contains.

In case you want to fetch only the first row you must set a limit in your query - either using LIMIT 1 or FETCH FIRST ROW ONLY -, but it would only make sense combined with a ORDER BY, e.g.

SELECT *
FROM zona_bahaya
JOIN points ON ST_Intersects(points.geom,zona_bahaya.geom)
ORDER BY gid
FETCH FIRST ROW ONLY;

Demo: db<>fiddle

edited Sep 4, 2021 at 7:03

answered Sep 3, 2021 at 10:24

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

astaga Over a year ago

i am not sure this is the answer i looking for since the problem is come from php. but it's good option to show only the true.

Collectives™ on Stack Overflow

php pg_fetch_array only show first result

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related