PHP PDO Inserting Data

Question

I am new with PDO and I am trying to insert data into a table.

This is the table I have:

CREATE TABLE  `Message` (
`ID` INT( 8 ) NOT NULL  AUTO_INCREMENT ,
`DateTime` DATETIME NOT NULL ,
`SmsSid` VARCHAR( 34 ) NOT NULL ,
`AccountSid` VARCHAR( 34 ) NOT NULL ,
`From` VARCHAR( 12 ) NOT NULL ,
`To` VARCHAR( 12 ) NOT NULL ,
`Body` VARCHAR( 160 ) NOT NULL ,
`FromCity` VARCHAR( 50 ) NULL ,
`FromState` VARCHAR( 50 ) NULL ,
`FromZip` VARCHAR( 50 ) NULL ,
`FromCountry` VARCHAR( 50 ) NULL ,
`ToCity` VARCHAR( 50 ) NULL ,
`ToState` VARCHAR( 50 ) NULL ,
`ToZip` VARCHAR( 50 ) NULL ,
`ToCountry` VARCHAR( 50 ) NULL ,
`ConversationNumber` INT( 4 ) NOT NULL ,
PRIMARY KEY (  `ID` )
) ENGINE = MYISAM

This is the PHP Code where I'm trying to insert into the table. What is the proper way to do this insert statement? prepare() query() exec()?

I am able to insert into a less complex table by using this similar code but not this one above. I'm unsure what exactly is causing the problem, syntax, column types, auto increment, datetime? Is there something with this table that I'm not handling right in the insert query? Also am I handling exceptions/error handling correctly to see the proper error messages I'd need to help debug?

<?php
try
{
$connectionString = new PDO("mysql:host=xxxx;dbname=xxxx;","xxxx","xxxx");
}
catch(PDOException $e)
{
echo 'Connection failed'.$e->getMessage();
}

$DateTime = "NOW()";
$SmsSid = "abcdef";
$AccountSid = "abcdef";
$FromWho = "abcdef";
$To = "abcdef";
$Body = "abcdef";
$FromCity = "abcdef";
$FromState = "abcdef";
$FromZip = "abcdef";
$FromCountry = "abcdef";
$ToCity = "abcdef";
$ToState = "abcdef";
$ToZip = "abcdef";
$ToCountry = "abcdef";
$ConversationNumber = "abcdef";

try
{
$executeQuery = $connectionString->prepare("INSERT INTO Message (SmsSid,AccountSid,`From`,To,Body,FromCity,FromState,FromZip,FromCountry,ToCity,ToState,ToZip,ToCountry,ConversationNumber) VALUES (:SmsSid,:AccountSid,:FromWho,:To,:Body,:FromCity,:FromState,:FromZip,:FromCountry,:ToCity,:ToState,:ToZip,:ToCountry,:ConversationNumber)");

$executeQuery->execute(array(':SmsSid'=>$SmsSid,':AccountSid'=>$AccountSid,':FromWho'=>$FromWho,':To'=>$To,':Body'=>$Body,':FromCity'=>$FromCity,':FromState'=>$FromState,':FromZip'=>$FromZip,':FromCountry'=>$FromCountry,':ToCity'=>$ToCity,':ToState'=>$ToState,':ToZip'=>$ToZip,':ToCountry'=>$ToCountry,':ConversationNumber'=>$ConversationNumber));


}
catch(PDOException $e)
{
echo 'Query failed'.$e->getMessage();
}

$connectionString = null;
?>

Are you getting any errors? By the way, I assume that all those placeholders are variables, why haven't you appended a $ to them? Also, if you know the order of your placeholders (column fields), then you can use the question mark syntax instead, it's not as messy. — silkfire, Feb 24 '13 at 23:51
Please don't include "Help is much appreciated." in your question. It is useless noise. — tckmn, Feb 24 '13 at 23:52
@Doorknob in my experience of answering, help is rarely appreciated — Explosion Pills, Feb 24 '13 at 23:54
There are several errors the first i see: NOW() does not work like that. Just use time(). Second: DateTime is not used at all, but is declared as `NOT NULL` — Julian Hille, Feb 24 '13 at 23:54
@ExplosionPills The general consensus is that this should be removed. — tckmn, Feb 24 '13 at 23:55
You have to ask PDO for errors, it does not give them per default; `PDO::ERRMODE_WARNING` — mario, Feb 24 '13 at 23:55
This does not look like a dup of the keyword reference question, although I'm still not certain. I'd say leave open until we can show which keyword is the problem, until then it is not a dup. — Kelly S. French, May 05 '14 at 19:58

Explosion Pills · Accepted Answer · 2013-02-25T00:00:28.980

3

From is a reserved word in MySQL. When you use it as the name of a column, you have to surround it with backticks as in

 `From`

By default, PDO does not throw exceptions. To make it throw exceptions on error, call

$pdoObject->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

edited Feb 25 '13 at 00:00

answered Feb 24 '13 at 23:53

Explosion Pills

188,624
52
326
405

This is giving me "Warning: PDO::setAttribute() expects exactly 2 parameters, 1 given" – Jsn0605 Feb 24 '13 at 23:58
@Jsn0605 sorry, I have corrected the error – Explosion Pills Feb 25 '13 at 00:00
Thanks, that helps a bunch but I still can't find my syntax error. "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From,To,Body,FromCity,FromState,FromZip,FromCountry,ToCity,ToState,ToZip,ToCount' at line 1" – Jsn0605 Feb 25 '13 at 00:02
1

@Jsn0605, yes, now you are seeing the error caused by not wrapping `From` in backticks, which was the first part of my answer. – Explosion Pills Feb 25 '13 at 00:03
You need to change it like this: `"INSERT INTO Message (SmsSid,AccountSid,\`From\` ...`. Also, note that different databases use different characters for quoting column and table names, so you might want to create a `quoteIdentifiers()` function for portability. – Matt Browne Feb 25 '13 at 00:04
I have updated all of the `From` issues above and in my code and I'm still getting the same error? – Jsn0605 Feb 25 '13 at 00:07
1

@Jsn0605 please post your full code somewhere. `TO` is also reserved – Explosion Pills Feb 25 '13 at 00:08
The problem was with `From` and `To` being MySQL reserved words. This was corrected by adding backticks around them. It works now, thanks! – Jsn0605 Feb 25 '13 at 00:11

silkfire · Answer 2 · 2013-02-25T00:10:08.520

It's really important that you escape (with backticks: `) the table names and column names as well. For example, FROM is a reserved keyword in MySQL.

$SmsSid             = "abcdef";
$AccountSid         = "abcdef";
$From               = "abcdef";
$To                 = "abcdef";
$Body               = "abcdef";
$FromCity           = "abcdef";
$FromState          = "abcdef";
$FromZip            = "abcdef";
$FromCountry        = "abcdef";
$ToCity             = "abcdef";
$ToState            = "abcdef";
$ToZip              = "abcdef";
$ToCountry          = "abcdef";
$ConversationNumber = "abcdef";

try {
   $executeQuery = $connectionString->prepare('INSERT INTO `Message` (`SmsSid`, `AccountSid`, `From`, `To`, `Body`, `FromCity`, `FromState`, `FromZip`, `FromCountry`, `ToCity`, `ToState`, `ToZip`, `ToCountry`, `ConversationNumber`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

   $executeQuery->execute(array($SmsSid, $AccountSid, $From, $To, $Body, $FromCity, $FromState, $FromZip, $FromCountry, $ToCity, $ToState, $ToZip, $ToCountry, $ConversationNumber));
}
catch(PDOException $e) {
       echo 'Query failed' . $e->getMessage();
}

Your Common Sense · Answer 3 · 2013-02-25T09:24:47.317

Try to automate some actions by using abstraction libraries, such as safemysql
Look, your code can be dramatically shorter, without repeating each variable name several times and - most important - there is no need to to care whether field name being a reserved word or not - all formatting is done automatically:

<?php
$db = new safemysql('host'=>'xxxx','db'=>'xxxx','user'=>"xxxx",'pass'=>"xxxx");
$insert = array( 
    'SmsSid'             => "abcdef",
    'AccountSid'         => "abcdef",
    'From'               => "abcdef",
    'To'                 => "abcdef",
    'Body'               => "abcdef",
    'FromCity'           => "abcdef",
    'FromState'          => "abcdef",
    'FromZip'            => "abcdef",
    'FromCountry'        => "abcdef",
    'ToCity'             => "abcdef",
    'ToState'            => "abcdef",
    'ToZip'              => "abcdef",
    'ToCountry'          => "abcdef",
    'ConversationNumber' => "abcdef",
);
$db->query("INSERT INTO Message SET DateTime = NOW(), ?u", $insert);
?>

Please note that I added NOW() function call directly into query, as it's Mysql function and require no formatting.

score 0 · Answer 4 · edited May 17 '18 at 23:44

      try {
     $db = new PDO('mysql:host=localhost;dbname=test_vendor_management_system', 'root', '');
     $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
    }

try {

    $stmt = $db->prepare("INSERT INTO car_details(`car_name`,`model_no`,`maker_id`,`dealer_id`,`car_added`) VALUES(?,?,?,?,NOW())");
    $stmt->execute(array($carcar_name,$model_no,$maker_id,$dealer_id));              
    echo "2nd Insertion Done  -".$affected_rows = $stmt->rowCount();
}catch(PDOException $e) {

    echo 'ERROR: ' . $e->getMessage();
}
 try {

       $values = array($carcar_name, $model_no, $maker_id, $dealer_id);
    $stmt = $db->prepare("INSERT INTO car_details
                        (`car_name`,`model_no`,`maker_id`,`dealer_id`,`car_added`)
                         VALUES
                        (:car_name,:model_no,:maker_id,:dealer_id,NOW())
                        ");
    $stmt->bindParam(':car_name', $carcar_name, PDO::PARAM_STR);
    $stmt->bindParam(':model_no', $model_no, PDO::PARAM_STR);
    $stmt->bindParam(':maker_id', $maker_id, PDO::PARAM_INT);
    $stmt->bindParam(':dealer_id', $dealer_id, PDO::PARAM_INT);
        $stmt->execute();
        echo "3rd Insertion Done  -".$affected_rows = $stmt->rowCount();
        echo "3rd Insertion Id  -".$insertId = $db->lastInsertId();
        }catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
        }

    $carcar_name="ESCORT/SQUIRE";
    $model_no="234";
    $maker_id="21";
    $dealer_id="12";

    try {

        $stmt = $db->prepare("INSERT INTO car_details(`car_name`,`model_no`,`maker_id`,`dealer_id`,`car_added`) VALUES(?,?,?,?,NOW())");
        $stmt->execute(array($carcar_name,$model_no,$maker_id,$dealer_id));              
        echo "2nd Insertion Done  -".$affected_rows = $stmt->rowCount();
    }catch(PDOException $e) {

        echo 'ERROR: ' . $e->getMessage();
    }   try {
        $stmt = $db->prepare("INSERT INTO car_details
                        (car_name,model_no,maker_id,dealer_id,car_added)
                         VALUES
                        (:car_name,:model_no,:maker_id,:dealer_id,:car_added)");

    $stmt->execute(array(':car_name'=>$carcar_name,
                         ':model_no'=>$model_no,
                         ':maker_id'=>$maker_id,
                         ':dealer_id'=>$dealer_id,
                         ':car_added'=>$car_added));

    echo "1st Insertion Done -".$affected_rows = $stmt->rowCount();
    }catch(PDOException $e) {

        echo 'ERROR: ' . $e->getMessage();
    }

score 0 · Answer 5 · edited Feb 18 '14 at 11:59

0

$sql = "INSERT INTO books2 (title,author,cover) values(?,?,?)";

$q = $conn->prepare($sql);

$q->bindParam(1, $title);

$q->bindParam(2, $author);

$q->bindParam(3, $cover, PDO::PARAM_LOB);

$q->execute();

edited Feb 18 '14 at 11:59

martincarlin87

10,848
24
98
145

answered Nov 18 '13 at 05:54

Bijon Krishna Bairagi

35
2

PHP PDO Inserting Data

5 Answers5