当前位置： 文江博客话题详情

INSERT 语句在 PHP 脚本中默默失败，但在提示符下有效

发布于 2024-09-28 11:55:04 字数 5436 浏览 3 评论 0原文

我在 PostgreSQL 8.4.5 中有下表：

snake=> create table gps (
id bytea check(length(id) = 16),
stamp timestamp DEFAULT current_timestamp,
pos point not null);

并且我可以从 psql 提示符将记录插入其中：

snake=> insert into gps (id, pos) values (decode(md5('x'), 'hex'), point(0, 0));
INSERT 0 1
snake=> insert into gps (id, pos) values (decode(md5('x'), 'hex'), point(0, 0));
INSERT 0 1

但由于某种原因，下面列出的 PHP 脚本中 INSERT 失败，其结果返回为 0。请问有人有吗？知道哪里出了问题或者如何获取更多信息？我很惊讶没有抛出异常。

<?php

$id  = trim($_REQUEST['id']);
$lat = strtr(trim($_REQUEST['lat']), ',', '.');
$lon = strtr(trim($_REQUEST['lon']), ',', '.');

if (preg_match('/^[a-fA-F0-9]{32}$/', $id) &&
    preg_match('/^[+-]?[0-9.]+$/', $lat) &&
    preg_match('/^[+-]?[0-9.]+$/', $lon)) {

        try {
                $db = new PDO('pgsql:host=/tmp', 'snake', 'snake');
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $insert = $db->prepare("insert into gps (id, pos) values (decode(?, 'hex'), point(?, ?))");
                $res = $insert->execute($id, $lat, $lon);

                $select = $db->prepare("select encode(id, 'hex') as id, extract('epoch' from stamp) as stamp, pos[0] as lat, pos[1] as lon from gps");
                $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" lon="%f" />',
                            $row['id'], $row['stamp'], $row['lat'], $row['lon']);
                }
                printf('<res val="%d" />', $res);

                print '</gps>';
        } catch (Exception $e) {
                print 'Database problem: ' . $e->getMessage();
        }

} else {
        header('Content-Type: text/html; charset=utf-8');
        print '<html>
<body>
<form method="post">
<p>Id: <input type="text" name="id" size=32 maxlength=32 /></p>
<p>Latitude: <input type="text" name="lat" /></p>
<p>Longitude: <input type="text" name="lon" /></p>
<p><input type="submit" value="Save" /></p>
</form>
</body>
</html>
';

}

?>

我得到的输出表明结果为 0：

<gps>
<pos id="0cc175b9c0f1b6a831c399e269772661" stamp="1287306960" lat="51.000000" lon="7.000000"/>
<pos id="0cc175b9c0f1b6a831c399e269772661" stamp="1287323377" lat="51.000000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287323381" lat="51.000000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287323442" lat="51.300000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287325610" lat="51.300000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287325612" lat="51.300000" lon="7.000000"/>
<pos id="9dd4e461268c8034f5c8564e155c67a6" stamp="1287325692" lat="0.000000" lon="0.000000"/>
<res val="0"/>
</gps>

问候， 亚历克斯

PS：这是我当前的脚本，似乎工作正常 -

<?php

$id  = trim($_REQUEST['id']);
$lat = strtr(trim($_REQUEST['lat']), ',', '.');
$lng = strtr(trim($_REQUEST['lng']), ',', '.');

if (preg_match('/^[a-fA-F0-9]{32}$/', $id) &&
    preg_match('/^[+-]?[0-9.]+$/', $lat) &&
    preg_match('/^[+-]?[0-9.]+$/', $lng)) {

        try {
                # enable persistent connections and throw exception on errors
                $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                                 PDO::ATTR_PERSISTENT => true);

                $db = new PDO('pgsql:host=/tmp dbname=snake', 'snake', 'snake', $options);

                #$db->exec('create table gps (id bytea check(length(id) = 16), stamp timestamp DEFAULT current_timestamp, pos point not null)');

                $insert = $db->prepare("insert into gps (id, pos) values (decode(?, 'hex'), point(?, ?))");
                $insert->execute(array($id, $lat, $lng));

                $select = $db->prepare("select encode(id, 'hex') as id, extract('epoch' from stamp) as stamp, pos[0] as lat, pos[1] as lng from gps");
                $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" />',
                            $row['id'], $row['stamp'], $row['lat'], $row['lng']);
                }
                print '</gps>';
        } catch (Exception $e) {
                print 'Database problem: ' . $e->getMessage();
        }

} else {
        header('Content-Type: text/html; charset=utf-8');
        print '<html>
<body>
<form method="post">
<p>Id: <input type="text" name="id" size=32 maxlength=32 /></p>
<p>Latitude: <input type="text" name="lat" /></p>
<p>Longitude: <input type="text" name="lng" /></p>
<p><input type="submit" value="Save" /></p>
</form>
</body>
</html>
';
}

?>
原文

I have the following table in PostgreSQL 8.4.5:

snake=> create table gps (
id bytea check(length(id) = 16),
stamp timestamp DEFAULT current_timestamp,
pos point not null);

and I'm able to INSERT record into it from psql prompt:

snake=> insert into gps (id, pos) values (decode(md5('x'), 'hex'), point(0, 0));
INSERT 0 1
snake=> insert into gps (id, pos) values (decode(md5('x'), 'hex'), point(0, 0));
INSERT 0 1

But for some reason INSERT fails in my PHP script listed below and its result is returned as 0. Does anybody please have an idea what is wrong there or how to get more info? I'm surprised that an exception isn't thrown.

<?php

$id  = trim($_REQUEST['id']);
$lat = strtr(trim($_REQUEST['lat']), ',', '.');
$lon = strtr(trim($_REQUEST['lon']), ',', '.');

if (preg_match('/^[a-fA-F0-9]{32}$/', $id) &&
    preg_match('/^[+-]?[0-9.]+$/', $lat) &&
    preg_match('/^[+-]?[0-9.]+$/', $lon)) {

        try {
                $db = new PDO('pgsql:host=/tmp', 'snake', 'snake');
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $insert = $db->prepare("insert into gps (id, pos) values (decode(?, 'hex'), point(?, ?))");
                $res = $insert->execute($id, $lat, $lon);

                $select = $db->prepare("select encode(id, 'hex') as id, extract('epoch' from stamp) as stamp, pos[0] as lat, pos[1] as lon from gps");
                $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" lon="%f" />',
                            $row['id'], $row['stamp'], $row['lat'], $row['lon']);
                }
                printf('<res val="%d" />', $res);

                print '</gps>';
        } catch (Exception $e) {
                print 'Database problem: ' . $e->getMessage();
        }

} else {
        header('Content-Type: text/html; charset=utf-8');
        print '<html>
<body>
<form method="post">
<p>Id: <input type="text" name="id" size=32 maxlength=32 /></p>
<p>Latitude: <input type="text" name="lat" /></p>
<p>Longitude: <input type="text" name="lon" /></p>
<p><input type="submit" value="Save" /></p>
</form>
</body>
</html>
';

}

?>

I get the output which indicates that result is 0:

<gps>
<pos id="0cc175b9c0f1b6a831c399e269772661" stamp="1287306960" lat="51.000000" lon="7.000000"/>
<pos id="0cc175b9c0f1b6a831c399e269772661" stamp="1287323377" lat="51.000000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287323381" lat="51.000000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287323442" lat="51.300000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287325610" lat="51.300000" lon="7.000000"/>
<pos id="92eb5ffee6ae2fec3ad71c777531578f" stamp="1287325612" lat="51.300000" lon="7.000000"/>
<pos id="9dd4e461268c8034f5c8564e155c67a6" stamp="1287325692" lat="0.000000" lon="0.000000"/>
<res val="0"/>
</gps>

Regards,
Alex

PS: Here is my current script, seems to work ok -

<?php

$id  = trim($_REQUEST['id']);
$lat = strtr(trim($_REQUEST['lat']), ',', '.');
$lng = strtr(trim($_REQUEST['lng']), ',', '.');

if (preg_match('/^[a-fA-F0-9]{32}$/', $id) &&
    preg_match('/^[+-]?[0-9.]+$/', $lat) &&
    preg_match('/^[+-]?[0-9.]+$/', $lng)) {

        try {
                # enable persistent connections and throw exception on errors
                $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                                 PDO::ATTR_PERSISTENT => true);

                $db = new PDO('pgsql:host=/tmp dbname=snake', 'snake', 'snake', $options);

                #$db->exec('create table gps (id bytea check(length(id) = 16), stamp timestamp DEFAULT current_timestamp, pos point not null)');

                $insert = $db->prepare("insert into gps (id, pos) values (decode(?, 'hex'), point(?, ?))");
                $insert->execute(array($id, $lat, $lng));

                $select = $db->prepare("select encode(id, 'hex') as id, extract('epoch' from stamp) as stamp, pos[0] as lat, pos[1] as lng from gps");
                $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" />',
                            $row['id'], $row['stamp'], $row['lat'], $row['lng']);
                }
                print '</gps>';
        } catch (Exception $e) {
                print 'Database problem: ' . $e->getMessage();
        }

} else {
        header('Content-Type: text/html; charset=utf-8');
        print '<html>
<body>
<form method="post">
<p>Id: <input type="text" name="id" size=32 maxlength=32 /></p>
<p>Latitude: <input type="text" name="lat" /></p>
<p>Longitude: <input type="text" name="lng" /></p>
<p><input type="submit" value="Save" /></p>
</form>
</body>
</html>
';
}

?>

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

许你一世情深 2024-10-05 11:55:04

我相信您需要使用参数数组而不是多个参数来执行 PDOStatement->insert() ：

$res = $insert->execute($id, array($lat, $lon));

请参阅 手册。

I believe you need to do PDOStatement->insert() using an array of parameters, not multiple arguments:

$res = $insert->execute($id, array($lat, $lon));

See the manual.

回复 原文
~没有更多了~

关于作者

国产ˉ祖宗

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

ni139999

文章 0 评论 0

Smile

文章 0 评论 0

木子李

文章 0 评论 0

仅此而已

文章 0 评论 0

qq_2gSKZM

文章 0 评论 0

内心激荡

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文