I'm running macOS Sierra (10.12.6) and php7.1 and for way to long I have been trying to figure out how to read a php file that contains the PDO::FETCH_ASSOC. I'm getting an error which I (think) is related to not having php configured correctly with the extension pdo_pgsql. The php file looks like this:
<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$db = new PDO('pgsql:host=localhost;port=5432;dbname=some_db;', 'username', 'password');
$sql = $db->query("SELECT id, name, image, web, category, ST_AsGeoJSON(geom, 5) as geom FROM some_file ORDER BY name");
$features=[];
while ($row=$sql->fetch(PDO::FETCH_ASSOC)){
$feature=['type'=>'Feature'];
$feature['geometry']=json_decode($row['geom']);
unset($row['geom']);
$feature['properties']=$row;
array_push($features, $feature);
}
$featureCollection=['type'=>'FeatureCollection', 'features'=>$features];
echo json_encode($featureCollection);
?>
and the part of the html file that is referring to it is:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>WebMap 101</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.0.1/dist/leaflet.css">
<script src="https://unpkg.com/leaflet@1.0.1/dist/leaflet.js"></script>
<script src="https://code.jquery.com/jquery-2.2.4.min.js"></script>
<script src="resources/leaflet.ajax.js"></script>
<script src="resources/turf.min.js"></script>
<style>
#header {
height: 75px;
background-color: darkgoldenrod;
}
#mapdiv {
height: 650px;
background-color: salmon;
}
#side_panel {
height: 650px;
background-color: beige;
}
#footer {
height: 75px;
background-color: darkgray
}
.attraction {
margin-bottom: 5px;
}
</style>
</head>
<body>
<div id="header" class="col-md-12">
<h1 class="text-center">Mexico City</h1>
</div>
<div id="side_panel" class="col-md-3">
<h1 class="text-center">Attractions</h1>
<button id="zoomToZocalo" class="form-control btn-primary">Zocalo</button>
</div>
<div id="mapdiv" class="col-md-9"></div>
<div id="footer" class="col-md-12">
<h4 id="map_coords" class="text-center">Latitude: 19.4 Longitude: -99.1 Zoom Level: 11</h4>
<h4 class="text-center">©2016 <a href="http:page_name.com">Page name</a></h4>
</div>
<script>
var mymap = L.map('mapdiv')
mymap.setView([19.4, -99.1], 11);
var backgroundLayer = L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png');
mymap.addLayer(backgroundLayer);
var zocaloMarker = L.marker([19.43278, -99.13333]).addTo(mymap).bindPopup("<h3 class='text-center'>Zocalo</h3><a href='https://en.wikipedia.org/wiki/Z%C3%B3calo' target='blank'><img src='img/zocalo.jpg' width='200px'></a>");
$("#zoomToZocalo").click(function() {
mymap.setView([19.43278, -99.13333], 17);
});
var lyrAttractions;
$.ajax({
url: 'load_attractions.php',
success: function(response) {
if (lyrAttractions) {
mymap.removeLayer(lyrAttractions)
};
lyrAttractions = L.geoJSON(JSON.parse(response), {
pointToLayer: function(feature, latlng) {
var str = "<button id='zoomTo" + feature.properties.name.replace(/ /g, '');
str += "' class='form-control btn btn-primary attraction'>";
str += feature.properties.name + "</button>";
$("#side_panel").append(str);
$("#zoomTo" + feature.properties.name.replace(/ /g, '')).click(function() {
mymap.setView([latlng.lat, latlng.lng], 17);
});
var str = "<h4 class='text-center'>" + feature.properties.name + "</h4><hr>";
str += "<a href='" + feature.properties.web + "' target='blank'>";
str += "<img src='img/" + feature.properties.image + "' width='200px'>";
str += "</a>";
return L.marker(latlng).bindPopup(str);
}});
lyrAttractions.addTo(mymap);
mymap.fitBounds(lyrAttractions.getBounds());
}});
mymap.on('mousemove', function(e) {
var str = "Latitude: " + e.latlng.lat.toFixed(5) + " Longitude: " + e.latlng.lng.toFixed(5) + " Zoom Level: " + mymap.getZoom();
$("#map_coords").html(str);
});
</script>
</body>
</html>
In Google developer tool I get this error:
VM102:1 Uncaught SyntaxError: Unexpected token < in JSON at position 0
at JSON.parse (<anonymous>)
at Object.success ((index):80)
at i (jquery-2.2.4.min.js:2)
at Object.fireWith [as resolveWith] (jquery-2.2.4.min.js:2)
at z (jquery-2.2.4.min.js:4)
at XMLHttpRequest.<anonymous> (jquery-2.2.4.min.js:4)
Line 80 is corresponding to the above "lyrAttractions = L.geoJSON(JSON.parse(response), {"
The network tab shows:
Image: Network tab in Google Developer
And the Network -> Respons-tab for load_attraction.php show:
<br />
<font size='1'><table class='xdebug-error xe-uncaught-exception' dir='ltr' border='1' cellspacing='0' cellpadding='1'>
<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> Fatal error: Uncaught Error: Call to a member function fetch() on boolean in /Users/agh/Sites/webmaps101/load_attractions.php on line <i>7</i></th></tr>
<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> Error: Call to a member function fetch() on boolean in /Users/agh/Sites/webmaps101/load_attractions.php on line <i>7</i></th></tr>
<tr><th align='left' bgcolor='#e9b96e' colspan='5'>Call Stack</th></tr>
<tr><th align='center' bgcolor='#eeeeec'>#</th><th align='left' bgcolor='#eeeeec'>Time</th><th align='left' bgcolor='#eeeeec'>Memory</th><th align='left' bgcolor='#eeeeec'>Function</th><th align='left' bgcolor='#eeeeec'>Location</th></tr>
<tr><td bgcolor='#eeeeec' align='center'>1</td><td bgcolor='#eeeeec' align='center'>0.0245</td><td bgcolor='#eeeeec' align='right'>362600</td><td bgcolor='#eeeeec'>{main}( )</td><td title='/Users/agh/Sites/webmaps101/load_attractions.php' bgcolor='#eeeeec'>.../load_attractions.php<b>:</b>0</td></tr>
</table></font>
Now over 60 hours later I still haven't found a solution, and starting to get depressed deluxe. My assumption has been that the php extension pdo_pgsql is not working properly. I installed php@7.1 with homebrew using brew install php@7.1,
Here are some stuff that might be helpful:
$ php -v returns:
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies
with Xdebug v2.7.2, Copyright (c) 2002-2019, by Derick Rethans
with Zend OPcache v7.1.30, Copyright (c) 1999-2018, by Zend Technologies
$ which php returns:
/usr/local/opt/php@7.1/bin/php
and
$ php -m | grep pdo_pgsql returns:
pdo_pgsql
and
$ php --ini returns:
Configuration File (php.ini) Path: /usr/local/etc/php/7.1
Loaded Configuration File: /usr/local/etc/php/7.1/php.ini
Scan for additional .ini files in: /usr/local/etc/php/7.1/conf.d
Additional .ini files parsed: /usr/local/etc/php/7.1/conf.d/ext-opcache.ini
$ brew doctor returns:
Warning: "config" scripts exist outside your system or Homebrew directories.
`./configure` scripts often look for *-config scripts to determine if
software packages are installed, and which additional flags to use when
compiling and linking.
Having additional scripts in your path can confuse software installed via
Homebrew if the config script overrides a system or Homebrew-provided
script of the same name. We found the following "config" scripts:
/usr/local/php5/bin/curl-config
/usr/local/php5/bin/freetype-config
/usr/local/php5/bin/icu-config
/usr/local/php5/bin/iodbc-config
/usr/local/php5/bin/libmcrypt-config
/usr/local/php5/bin/libpng-config
/usr/local/php5/bin/libpng12-config
/usr/local/php5/bin/php-config
/usr/local/php5/bin/xslt-config
/Library/Frameworks/Python.framework/Versions/3.6/bin/python3-config
/Library/Frameworks/Python.framework/Versions/3.6/bin/python3.6-config
/Library/Frameworks/Python.framework/Versions/3.6/bin/python3.6m-config
/Library/Frameworks/Python.framework/Versions/3.7/bin/python3-config
/Library/Frameworks/Python.framework/Versions/3.7/bin/python3.7-config
/Library/Frameworks/Python.framework/Versions/3.7/bin/python3.7m-config
/Applications/Postgres.app/Contents/Versions/latest/bin/gdal-config
Warning: You have unlinked kegs in your Cellar.
Leaving kegs unlinked can lead to build-trouble and cause brews that depend on
those kegs to fail to run properly once built. Run `brew link` on these:
git
python
Running the phpinfo-file gives me this:
Server API Apache 2.0 Handler
Virtual Directory Support disabled
Configuration File (php.ini) Path /usr/local/etc/php/7.1
Loaded Configuration File /usr/local/etc/php/7.1/php.ini
Scan this dir for additional .ini files /usr/local/etc/php/7.1/conf.d
Additional .ini files parsed /usr/local/etc/php/7.1/conf.d/ext-opcache.ini
PHP API 20160303
PHP Extension 20160303
Zend Extension 320160303
Zend Extension Build API320160303,NTS
PHP Extension Build API20160303,NTS
and further down in the same file:
pdo_pgsql
PDO Driver for PostgreSQL enabled
PostgreSQL(libpq) Version 11.3
Module version 7.1.30
Revision $Id: 9c5f356c77143981d2e905e276e439501fe0f419 $
Running pear config-show returns:
Auto-discover new Channels auto_discover <not set>
Default Channel default_channel pear.php.net
HTTP Proxy Server Address http_proxy <not set>
PEAR server [DEPRECATED] master_server pear.php.net
Default Channel Mirror preferred_mirror pear.php.net
Remote Configuration File remote_config <not set>
PEAR executables directory bin_dir /usr/local/opt/php@7.1/bin
PEAR documentation directory doc_dir /usr/local/share/pear@7.1/doc
PHP extension directory ext_dir /usr/local/lib/php/pecl/20160303/
PEAR directory php_dir /usr/local/share/pear@7.1
PEAR Installer cache directory cache_dir /private/tmp/pear/cache
PEAR configuration file cfg_dir /usr/local/share/pear@7.1/cfg
directory
PEAR data directory data_dir /usr/local/share/pear@7.1/data
PEAR Installer download download_dir /private/tmp/pear/download
directory
Systems manpage files man_dir /usr/local/share/man
directory
PEAR metadata directory metadata_dir <not set>
PHP CLI/CGI binary php_bin /usr/local/opt/php@7.1/bin/php
php.ini location php_ini /usr/local/etc/php/7.1/php.ini
--program-prefix passed to php_prefix <not set>
PHP's ./configure
--program-suffix passed to php_suffix <not set>
PHP's ./configure
PEAR Installer temp directory temp_dir /private/tmp/pear/temp
PEAR test directory test_dir /usr/local/share/pear@7.1/test
PEAR www files directory www_dir /usr/local/share/pear@7.1/htdocs
Cache TimeToLive cache_ttl 3600
Preferred Package State preferred_state stable
Unix file mask umask 22
Debug Log Level verbose 1
PEAR password (for password <not set>
maintainers)
Signature Handling Program sig_bin /usr/local/bin/gpg
Signature Key Directory sig_keydir /usr/local/etc/php/7.1/pearkeys
Signature Key Id sig_keyid <not set>
Package Signature Type sig_type gpg
PEAR username (for username <not set>
maintainers)
User Configuration File Filename /Users/agh/.pearrc
System Configuration File Filename /usr/local/etc/php/7.1/pear.conf
I've tried php7.3 and php7.2 as well, but encounter the problem that Module API(=20160303)and PHP API(=20170718) don't match and therefore the extension pso_pgsql can't be loaded. When I installed php7.1 (which is using the same API as the module), at least that problem was gone.
However, when I add extension=pdo_pgsql.so in the php.ini file and run php --iniI get an error saying PHP Warning: Module 'pdo_pgsql' already loaded in Unknown on line 0
I've read that there might be another file where pdo_pgsql.so is loaded from and some suggest looking in conf.d folder. I used to have several files in conf.d (think it was during previous php-installations (7.2 and 7.3), e.g "ext-pdo_pgsql.ini". Now I only have "exe-opcache.ini" which only contains information of where the opcache.so files is located. But is there any other place where pdo_pgsql.so can be loaded from?
I've noticed that the icon of the file pdo_pgsql.so differs from the icons connected to extensions that I have installed just recently with pecl. pdo_pgsql has a black terminal with text "exec" and the other extensions just white papers. I can't remember when I got the pdo_pgsql.so file in the first place (since I've tried so many things), because when trying to pecl install pdo_pgsqlI end up with an error (finally) saying:
Installing shared extensions: /private/tmp/pear/temp/pear-build-aghlTd4GE/install-PDO-1.0.3/usr/local/Cellar/php@7.1/7.1.30_1/pecl/20160303/
cp: modules/*: No such file or directory
make: *** [install-modules] Error 1
ERROR: `make INSTALL_ROOT="/private/tmp/pear/temp/pear-build-aghlTd4GE/install-PDO-1.0.3" install' failed
I think I might have got pdo_pgsql.so from running the script found here: https://gist.github.com/doole/8651341 and it seems to work with a few modifications. It finally ends up with returning:
extension=/usr/local/lib/php/extensions/pdo_pgsql.so
I manually move the file in the same ext_dir folder given by pear config-show (but have also tried to change the ext_dir to /usr/local/lib/php/extensions/ without luck.)
But, since pdo_pgsql is presented when running $ php -m I assume it is already(?) included in the php package? But how on earth can I enable it to load in the php.ini file, without the error that it has already being loaded? On the other hand, it says in the phpinfo-file that PDO driver for PostgreSQL is enabled. Does it load by default, without telling it to do so in php.ini? Is that why it is "already loaded"?
Soo... am I looking in the wrong direction? Is my original error PDO::FETCH_ASSOC caused by something else? If so: what on earth can it be?
I would be very happy if someone could provide some advice. I'm out of ideas.
