I get the error PHP Fatal error: Call to a member function execute() on a non-object refering to the ....->execute() line whenever I call something like
$select_str = 'select id, stamp, lat, lng, spd from gps';
$select = $db->prepare($select_str);
$select->execute();
or
$insert = $db->prepare('insert into gps (id, lat, lng, spd) values (?, ?, ?, ?)');
$insert->execute(array($id, $lat, $lng, $spd));
From searching on the net I suspect that $select (or $insert) is somehow becoming a "dead object" which holds the data, but on which methods can't be called.
But I don't know how to prevent it and my PHP experience is very short (I'm coming from Perl land). Please help me to fix this, it is probably something minor.
And below is my complete script, I think (I hope) it is quite readable - it lets you create a table, insert a record, delete records, view them or drop the table again - all depending on the $_REQUEST['mode'] parameter:
<?php
@define('DBHOST', 'localhost');
@define('DBNAME', 'snake');
@define('DBUSER', 'snake');
@define('DBPASS', 'snake');
# lowercase mode and id parameters; replace commas by dots in lat, lng, spd
$mode = isset($_REQUEST['mode']) ? strtolower(trim($_REQUEST['mode'])) : '';
$id = isset($_REQUEST['id']) ? strtolower(trim($_REQUEST['id'])) : '';
$lat = isset($_REQUEST['lat']) ? strtr(trim($_REQUEST['lat']), ',', '.') : '';
$lng = isset($_REQUEST['lng']) ? strtr(trim($_REQUEST['lng']), ',', '.') : '';
$spd = isset($_REQUEST['spd']) ? strtr(trim($_REQUEST['spd']), ',', '.') : '';
# id must be 32 chars long hex number; lat, lng, spd must be decimal numbers
$id_ok = preg_match('/^[a-f0-9]{32}$/', $id);
$lat_ok = preg_match('/^[+-]?[0-9.]+$/', $lat);
$lng_ok = preg_match('/^[+-]?[0-9.]+$/', $lng);
$spd_ok = preg_match('/^\+?[0-9.]+$/', $spd);
# has the user selected a mode and provided valid input?
$create_ok = ($mode == 'create');
$insert_ok = ($mode == 'insert' && $id_ok && $lat_ok && $lng_ok && $spd_ok);
$delete_ok = ($mode == 'delete' && $id_ok);
$select_ok = ($mode == 'select');
$drop_ok = ($mode == 'drop');
# first call or invalid input: display web form and exit
if (!($create_ok || $insert_ok || $delete_ok || $select_ok || $drop_ok)) {
header('Content-Type: text/html; charset=utf-8');
print '<html>
<body>
<form method="post">
<p>Mode:<br />
<input type="radio" name="mode" value="create"><i>create table</i><br />
<input type="radio" name="mode" value="select" checked>select records (can specify id)<br />
<input type="radio" name="mode" value="insert">insert 1 record (must specify all)<br />
<input type="radio" name="mode" value="delete">delete records (must specify id)<br />
<input type="radio" name="mode" value="drop"><i>drop table</i><br />
</p>
<p>Id: <input type="text" name="id" size=32 maxlength=32 /> (32 hex chars)</p>
<p>Latitude: <input type="text" name="lat" size=10 /> (between -90 and 90)</p>
<p>Longitude: <input type="text" name="lng" size=10 /> (between -90 and 90)</p>
<p>Speed: <input type="text" name="spd" size=10 /> (not negative)</p>
<p><input type="submit" value="OK" /></p>
</form>
</body>
</html>
';
exit();
}
try {
# enable persistent connections and throw exception on any errors
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => true);
$db = new PDO('mysql:host=' . DBHOST . '; dbname=' . DBNAME, DBUSER, DBPASS, $options);
if ($create_ok) {
$db->exec('create table gps (
id char(32) not null check length(id)=32,
lat decimal(5,3) not null,
lgt decimal(5,3) not null,
spd decimal(5,3) unsigned not null,
stamp timestamp default now(),
index(id) )');
} else if ($insert_ok) {
$insert = $db->prepare('insert into gps (id, lat, lng, spd) values (?, ?, ?, ?)');
$insert->execute(array($id, $lat, $lng, $spd));
} else if ($delete_ok) {
} else if ($drop_ok) {
$db->exec('drop table gps');
header('Content-Type: text/plain');
print('Database dropped');
exit();
}
# display current table content in XML format
$select_str = 'select id, stamp, lat, lng, spd from gps';
# but filter by id if requested by user
if ($select_ok && $id_ok) {
$select = $db->prepare($select_str . ' where id = ?');
$select->execute(array($id));
} else {
$select = $db->prepare($select_str);
$select->execute();
}
header('Content-Type: text/xml; charset=utf-8');
print('<?xml version="1.0"?><gps>');
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
printf('<pos id="%s" stamp="%u" lat="%f" lng="%f" spd="%f" />',
$row['id'], $row['stamp'], $row['lat'], $row['lng'], $row['spd']);
}
print('</gps>');
} catch (Exception $e) {
header('Content-Type: text/plain');
print('Database problem: ' . $e->getMessage());
}
?>
And the MySQL user 'snake' has these permissions:
select * from mysql.user where User='snake';
+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | snake | 684bce5059b3e0a8 | Y | Y | N | Y | Y | Y | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 |
+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
Thank you! Alex
