PDO语句没有执行？

Question

I'm trying to use PDO (php data object) to execute queries in a .php file like so:

global $db, $table;
$sth = $db->prepare('INSERT INTO $table(user, timerun, magexp, crimsons, blues, golds, greens) VALUES (:user,:timerun,:magexp,:crimsons,:blues,:golds,:greens) ON DUPLICATE KEY UPDATE timerun=timerun+:timerun, magexp=magexp+:magexp, crimsons=crimsons+:crimsons, blues=blues+:blues, golds=golds+:golds, greens=greens+green');
$sth->execute(array(':user' => $user, ':timerun' => $timerun, ':magexp' => $magexp, ':crimsons' => $cr, ':blues' => $bl, ':golds' => $go, ':greens' => $gr));
echo "success";

However, it doesn't actually update my table. I don't get an error or anything.

Am I doing something wrong or is PDO not supported? The PDO docs said "Beware: Some MySQL table types (storage engines) do not support transactions. When writing transactional database code using a table type that does not support transactions, MySQL will pretend that a transaction was initiated successfully. In addition, any DDL queries issued will implicitly commit any pending transactions."

I'm fairly certain my MySQL tables do support transactions, because the regular 'mysql_query' does work.

Thanks.

Answer 1

I'm not sure about Your code, You have variable inside single quoted string it will not work, You should use double quotation like this:

global $db, $table; 

$sth = $db->prepare("INSERT INTO $table(user, timerun, magexp, crimsons, blues, golds,  greens) VALUES (:user,:timerun,:magexp,:crimsons,:blues,:golds,:greens) ON DUPLICATE KEY   UPDATE timerun=timerun+:timerun, magexp=magexp+:magexp, crimsons=crimsons+:crimsons, blues=blues+:blues, golds=golds+:golds, greens=greens+green:"); 

$sth->execute(array(':user' => $user, ':timerun' => $timerun, ':magexp' => $magexp, ':crimsons' => $cr, ':blues' => $bl, ':golds' => $go, ':greens' => $gr)); echo "success";

For security:

First of all i would create some associative array with all possible tables from project as keys and then check if table from variable exists as array index using if(isset($validTables[$table])) and then continue the query. For example

<?php
  $validTables = array('foo' => true, 'bar' => true, 'other' => true);
  if(isset($validTables[$table])) 
  {
    // query logic here
  }
  else throw new Exception(sprintf('Security error %s table not exists', $table));

Check this code because i wrote it without parsing with php