MySQL（PDO）exec什么都不返回，没有错误，没有添加行[重复]

Question

This question already has an answer here:

• My PDO Statement doesn't work 1 answer

Well, I found similar questions on SO, but it still not working.

I run the following code, then... nothing! No error, nothing inserted.

Here's the php code:

    try
    {
        $obj_sqlsvr = new PDO('mysql:host=localhost;dbname=svmsys', 'root', '');
    }
    catch(Exception $e)
    {
        echo 'Erreur : '.$e->getMessage();
    }



    $servcatid = $_POST["servcatid"];
$sysname = $_POST["sysname"];
$sortorder = 1;
$friendlyname_fr = $_POST["friendlyname_fr"];
$friendlyname_en = $_POST["friendlyname_en"];


$fee_billtime = $_POST["fee_billtime"];
$fee_ot = isset($_POST["fee_ot"]) ? $_POST["fee_ot"] : 0;
$fee_1m = isset($_POST["fee_1m"]) ? $_POST["fee_1m"] : 0;
$fee_3m = isset($_POST["fee_3m"]) ? $_POST["fee_3m"] : 0;
$fee_6m = isset($_POST["fee_6m"]) ? $_POST["fee_6m"] : 0;
$fee_12m = isset($_POST["fee_12m"]) ? $_POST["fee_12m"] : 0;
$fee_24m = isset($_POST["fee_24m"]) ? $_POST["fee_24m"] : 0;
$fee_36m = isset($_POST["fee_36m"]) ? $_POST["fee_36m"] : 0;
$fee_other = isset($_POST["fee_other"]) ? $_POST["fee_other"] : 0;

$adm_timeadded = time();
$adm_addedby = 1;
$adm_active = $_POST["adm_active"];
$adm_start = $_POST["adm_start"];
$adm_expires = $_POST["adm_expires"];
$adm_soldlmt = $_POST["adm_soldlmt"];



    try
    {
        $req = $obj_sqlsvr->prepare("INSERT INTO pricelist(id, servcatid, sortorder, sysname, friendlyname_fr, friendlyname_en, fee_ot, fee_1m, fee_3m, fee_6m, fee_12m, fee_24m, fee_36m, fee_other, fee_billtime, adm_timeadded, adm_addedby, adm_active, adm_start, adm_expires, adm_soldlmt)
                VALUES(NULL, :servcatid,:sortorder,:sysname,:friendlyname_fr,:friendlyname_en,:fee_ot,:fee_1m,:fee_3m,:fee_6m,:fee_12m,
:fee_24m,:fee_36m,:fee_other,:fee_billtime,:adm_timeadded,:adm_addedby,:adm_active,:adm_start,:adm_expires,:adm_soldlmt)");
        $req->bindParam(':servcatid', $servcatid);
        $req->bindParam(':sortorder', $sortorder);
        $req->bindParam(':sysname', $sysname);
        $req->bindParam(':friendlyname_fr', $friendlyname_fr);
        $req->bindParam(':friendlyname_en', $friendlyname_en);
        $req->bindParam(':fee_ot', $fee_ot);
        $req->bindParam(':fee_1m', $fee_1m);
        $req->bindParam(':fee_3m', $fee_3m);
        $req->bindParam(':fee_6m', $fee_6m);
        $req->bindParam(':fee_12m', $fee_12m);
        $req->bindParam(':fee_24m', $fee_24m);
        $req->bindParam(':fee_36m', $fee_36m);
        $req->bindParam(':fee_other', $fee_other);
        $req->bindParam(':fee_billtime', $fee_billtime);
        $req->bindParam(':adm_timeadded', $adm_timeadded);
        $req->bindParam(':adm_addedby', $adm_addedby);
        $req->bindParam(':adm_active', $adm_active);
        $req->bindParam(':adm_start', $adm_start);
        $req->bindParam(':adm_expires', $adm_expires);
        $req->bindParam(':adm_soldlmt', $adm_soldlmt);
        $res = $req->execute();
    }
    catch(Exception $e)
    {
        echo 'Erreur : '.$e->getMessage();
    }

Table Structure:

Table Structure (again) in SQL format:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE IF NOT EXISTS `pricelist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `servcatid` int(11) NOT NULL,
  `order` int(11) DEFAULT NULL,
  `sysname` varchar(100) NOT NULL,
  `friendlyname_fr` varchar(100) NOT NULL,
  `friendlyname_en` varchar(100) NOT NULL,
  `fee_ot` decimal(10,2) DEFAULT NULL,
  `fee_1m` decimal(10,2) DEFAULT NULL,
  `fee_3m` decimal(10,2) DEFAULT NULL,
  `fee_6m` decimal(10,2) DEFAULT NULL,
  `fee_12m` decimal(10,2) DEFAULT NULL,
  `fee_24m` decimal(10,2) DEFAULT NULL,
  `fee_36m` decimal(10,2) DEFAULT NULL,
  `fee_other` decimal(10,4) DEFAULT NULL,
  `fee_billtime` int(2) NOT NULL DEFAULT '0',
  `adm_timeadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `adm_addedby` int(11) NOT NULL,
  `adm_active` tinyint(1) NOT NULL DEFAULT '1',
  `adm_start` datetime DEFAULT NULL,
  `adm_expires` datetime DEFAULT NULL,
  `adm_soldlmt` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Do you see any error ? And if so, can you please tell me where it is?

Thank you very much, Jeremy

EDIT I've renamed the field 'order' to 'sortorder' in both my code and in my table structure (Thanks to Pekka) since it's a reserved word. Still not working.

EDIT Adding Try/Catch arround the prepare and execute command. / Using echo command insteat of die command to diplay errors. (Thanks to david strachan)

EDIT Missing ' in query (Thanks to Drew Pierce)

EDIT PDO query is now parametrized (Thansk to Andy Lester), still no error message and no row in the table.

SAMPLE QUERY (Working on phpMyAdmin)

INSERT INTO pricelist(id, servcatid, sysname, sortorder, friendlyname_fr, friendlyname_en, fee_ot, fee_1m, fee_3m, fee_6m, fee_12m, fee_24m, fee_36m, fee_other, fee_billtime, adm_timeadded, adm_addedby, adm_active, adm_start, adm_expires, adm_soldlmt)
VALUES(NULL, '2','demo',NULL,'DEMO','DEMO', '12','','','','','','','','1', '1353533266','1','1','','','1')

</div>

Answer 1

The problem was the single quotes were around basically all of your values variables on the right side of your insert stmt and they only belong there for strings and timestamps and datetime but NOT for integer, decimal, tinyints.

<?php

    $host="localhost"; // Host name 
    $username="jonah"; // Mysql username 
    $passw="password123"; // Mysql password 
    $db_name="main"; // Database name 

try
{
    $obj_sqlsvr = new PDO('mysql:host=localhost;dbname=main;charset=UTF-8', $username, $passw);

}
catch(Exception $e)
{
    echo 'Erreur : '.$e->getMessage();
}



$servcatid = 11;//$_POST["servcatid"];
$sysname = 'ssss';//$_POST["sysname"];
$friendlyname_fr = 'ff';//$_POST["friendlyname_fr"];
$friendlyname_en = 'ffe';//$_POST["friendlyname_en"];


$fee_billtime = 11;//$_POST["fee_billtime"];
$fee_ot = isset($_POST["fee_ot"]) ? $_POST["fee_ot"] : 0;
$fee_1m = isset($_POST["fee_1m"]) ? $_POST["fee_1m"] : 0;
$fee_3m = isset($_POST["fee_3m"]) ? $_POST["fee_3m"] : 0;
$fee_6m = isset($_POST["fee_6m"]) ? $_POST["fee_6m"] : 0;
$fee_12m = isset($_POST["fee_12m"]) ? $_POST["fee_12m"] : 0;
$fee_24m = isset($_POST["fee_24m"]) ? $_POST["fee_24m"] : 0;
$fee_36m = isset($_POST["fee_36m"]) ? $_POST["fee_36m"] : 0;
$fee_other = isset($_POST["fee_other"]) ? $_POST["fee_other"] : 0;

$adm_timeadded = '2012-12-01';
$adm_addedby = 1;
$adm_active = 2;//$_POST["adm_active"];
$adm_start = '2012-12-01';//time();//$_POST["adm_start"];
$adm_expires = '2012-12-01';//time();//$_POST["adm_expires"];
$adm_soldlmt = 12;//$_POST["adm_soldlmt"];




try
{
    $req = $obj_sqlsvr->prepare("INSERT INTO pricelist(servcatid, sysname, sortorder, friendlyname_fr, friendlyname_en, fee_ot, fee_1m, fee_3m, fee_6m, fee_12m, fee_24m, fee_36m, fee_other, fee_billtime, adm_timeadded, adm_addedby, adm_active, adm_start, adm_expires, adm_soldlmt) VALUES($servcatid,'$sysname',NULL,'$friendlyname_fr','$friendlyname_en', $fee_ot,$fee_1m,$fee_3m,$fee_6m,$fee_12m,$fee_24m,$fee_36m,$fee_other,$fee_billtime, '$adm_timeadded',$adm_addedby,$adm_active,'$adm_start','$adm_expires',$adm_soldlmt)");
    $res = $req->execute();
}
catch(Exception $e)
{
    echo 'Erreur : '.$e->getMessage();
}
?>

mysql> select * from pricelist; +----+-----------+-----------+---------+-----------------+-----------------+--------+--------+--------+--------+---------+---------+---------+-----------+--------------+---------------------+-------------+------------+---------------------+---------------------+-------------+ | id | servcatid | sortorder | sysname | friendlyname_fr | friendlyname_en | fee_ot | fee_1m | fee_3m | fee_6m | fee_12m | fee_24m | fee_36m | fee_other | fee_billtime | adm_timeadded | adm_addedby | adm_active | adm_start | adm_expires | adm_soldlmt | +----+-----------+-----------+---------+-----------------+-----------------+--------+--------+--------+--------+---------+---------+---------+-----------+--------------+---------------------+-------------+------------+---------------------+---------------------+-------------+ | 1 | 11 | NULL | ssss | ff | ffe | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.0000 | 11 | 2012-12-01 00:00:00 | 1 | 2 | 2012-12-01 00:00:00 | 2012-12-01 00:00:00 | 12 | +----+-----------+-----------+---------+-----------------+-----------------+--------+--------+--------+--------+---------+---------+---------+-----------+--------------+---------------------+-------------+------------+---------------------+---------------------+-------------+