New question: problem with PDO and MYSQL INSERT

I try to be safer and start using PDO and prepared statements. This was recommended to me, and I read these two sites: http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/ and also http: //webdevrefinery.com/forums/topic/1272-your-mysql-code-sucks

I hit a brick wall and I do not understand why the following does not work. I am trying to insert a line (to register 404 error). I read about named and unnamed placeholders, and I think the named placeholder method will be easier to maintain. I also tried using "try" and "catch" for the first time. All this is perfect for me, so please be kind! I do not get any errors, but the code does not update the database - I return zero rows.

Here is the code:

$referer = $_SERVER['HTTP_REFERER'];
$domainName = "http://domain.com";
$_dtNow = date("d-m-Y H:i:s");
$_referer = $domainName.$_SERVER['REQUEST_URI'];
$_thisPage = $domainName.$url;
$_ip = $_SERVER['REMOTE_ADDR'];
$_host = $_SERVER['REMOTE_HOST'];
if(isset($_SERVER['HTTP_USER_AGENT'])) {$_ua = $_SERVER['HTTP_USER_AGENT'];} else {$_ua = "unset";}

$host =     'localhost';
$port =     3306; // This is the default port for MySQL
$database = 'databaseName';
$username = 'username';
$password = 'password';
  // Construct the DSN, or "Data Source Name".  Really, it just a fancy name
  // for a string that says what type of server we're connecting to, and how
  // to connect to it.  As long as the above is filled out, this line is all
  // you need :)
  $dsn = "mysql:host=$host;port=$port;dbname=$database";

try {
  // Connect!
  $db = new PDO($dsn, $username, $password);  
  $data = array( 
                'dateTime' =>   $_dtNow, 
                'referer' =>    $_referer, 
                'page' =>       $_thisPage,
                'ip' =>         $_ip,
                'host' =>       $_host,
                'ua' =>         $_ua
                );  
  $statement = $db->prepare("INSERT INTO 404s (dateTime, referer, page, ip, host, ua) value (:dateTime, :referer, :page, :ip, :host, :ua)");
  $statement->execute($data);
}
catch(PDOException $e) {  
    echo $e->getMessage();  
}  



?>
+3
source share
3 answers

Are you sure the table name 404slooks like an invalid identifier.

 INSERT INTO 404s (dateTime, referer, page, ip, host, ua) value (:dateTime, :referer, :page, :ip, :host, :ua)

Try:

 INSERT INTO `404s` (dateTime, referer, page, ip, host, ua) value (:dateTime, :referer, :page, :ip, :host, :ua)

Use backquote around `404s`

Note Keep in mind that such a design as:

create table `404` ( `33` integer);

Are valid.

` , - SQL, .

, .

+4

@Dave Kiss SQL 'value'. "".

INSERT INTO 404s (dateTime, referer, page, ip, host, ua) VALUES (:dateTime, :referer, :page, :ip, :host, :ua)

, .

BTW: (, 404) - . , backticks:

INSERT INTO `404s` (dateTime, referer, page, ip, host, ua) VALUES (:dateTime, :referer, :page, :ip, :host, :ua)

+1

Try adding a colon to your keys in the data array.

/* Execute a prepared statement by passing an array of insert values */

  $data = array( 
                ':dateTime' =>   $_dtNow, 
                ':referer' =>    $_referer, 
                ':page' =>       $_thisPage,
                ':ip' =>         $_ip,
                ':host' =>       $_host,
                ':ua' =>         $_ua
                );  
  $statement = $db->prepare("INSERT INTO 404s (dateTime, referer, page, ip, host, ua) value (:dateTime, :referer, :page, :ip, :host, :ua)");
  $statement->execute($data);
0
source

All Articles