MySQL (PDO) exec return nothing, no error, no row added

Question

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')

Your specific error is that `order` is a reserved word in mySQL — Pekka, Nov 21 '12 at 20:52
Oh man! I completely forgot about it! You're probably right, I'm trying this fix right now. — Jeremy Dicaire, Nov 21 '12 at 20:54
for a start `catch(Exception $e){`should come after `$res = $req->execute();` — david strachan, Nov 21 '12 at 20:58
@david strachan: code modified, no error ;) (Sorry I thing I removed it to make sure it wasn't the error. — Jeremy Dicaire, Nov 21 '12 at 21:04
`echo 'Erreur : '. $e->getMessage();` not `die('Erreur : '.$e->getMessage());` to see exceptions — david strachan, Nov 21 '12 at 21:10
Please learn about using parametrized queries for safety and readability. http://bobby-tables.com/php.html has examples. — Andy Lester, Nov 21 '12 at 21:19
Another typo `catch(Exception $e)` should be `catch (PDOException $e)` — david strachan, Nov 21 '12 at 22:10
@DavidStrachan the other thing that went bad was his first insert column of id. it is auto_increment and non nullable but you were inserting a null hard coded null. u should always never pass parameters for them — Drew, Nov 21 '12 at 22:20
@DrewPierce, you're right, its an old habbit, it was working fine with mysql 4 (without pdo)... If I remember correctly :) I'll put this on my NotToDo List — Jeremy Dicaire, Nov 21 '12 at 22:27
but mostly it was the wrapping with single quotes see my bold comments below — Drew, Nov 21 '12 at 22:28

score 3 · Answer 1 · edited May 23 '17 at 12:30

3

See this post on how to make PDO output error messages.

Your specific error is that order is a reserved word in mySQL.

edited May 23 '17 at 12:30

Community

1
1

answered Nov 21 '12 at 20:56

Pekka

442,112
142
972
1,088

1

just a note reserved words can be used (but it is definitely not recommended) but these should be quoted with backticks i.e. "select `order` from 'table'". It's good habit to always do that. – Marcin Orlowski Nov 21 '12 at 20:58
Unfortunatly it's not working. – Jeremy Dicaire Nov 21 '12 at 20:59
Thanks for this tip, but I'll take your first advise, not using reserved words :) – Jeremy Dicaire Nov 21 '12 at 21:00

Drew · Accepted Answer · 2012-11-21T22:08:11.930

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 | +----+-----------+-----------+---------+-----------------+-----------------+--------+--------+--------+--------+---------+---------+---------+-----------+--------------+---------------------+-------------+------------+---------------------+---------------------+-------------+

Many thanks for your help Drew you helped me a lot. (Do you have a PayPal account? I will pay you a coffee :) ) — Jeremy Dicaire, Nov 21 '12 at 22:34
Quotes around int values are allowed in mySQL. That can't have been his problem - I think it's more likely that you fixed a missing quote? — Pekka, Nov 21 '12 at 22:52
@Pekka well then there was the first parameter the auto incr not null that was getting a null hard coded over by Values (NULL, ... — Drew, Nov 21 '12 at 22:57

MySQL (PDO) exec return nothing, no error, no row added

2 Answers2