CM_Db_Query_Insert PHP Code Examples

Example #1

0

Show file

File: SetupScript.php Project: cargomedia/cm

 public function load(CM_OutputStream_Interface $output)
 {
     $client = $this->getServiceManager()->getDatabases()->getMaster();
     $query = new CM_Db_Query_Insert($client, 'cm_requestClientCounter', ['counter' => 0]);
     $query->execute();
     $this->_setLoaded(true);
 }

Example #2

0

Show file

File: Options.php Project: cargomedia/cm

 /**
  * @param string $key
  * @param mixed  $value
  */
 public function set($key, $value)
 {
     $fields = array('key' => $key, 'value' => serialize($value));
     $query = new CM_Db_Query_Insert($this->_getDatabaseClient(), 'cm_option', $fields, null, null, 'REPLACE');
     $query->execute();
     $this->_clearCache();
 }

Example #3

0

Show file

File: TypeDumper.php Project: cargomedia/cm

 public function load(CM_OutputStream_Interface $output)
 {
     $dbClient = $this->getServiceManager()->getDatabases()->getMaster();
     $values = Functional\map($this->_getAllTypes(), function ($className, $type) {
         return [$type, $className];
     });
     $query = new CM_Db_Query_Truncate($dbClient, 'cm_tmp_classType');
     $query->execute();
     $query = new CM_Db_Query_Insert($dbClient, 'cm_tmp_classType', ['type', 'className'], $values, null, 'REPLACE');
     $query->execute();
 }

Example #4

0

Show file

File: InsertTest.php Project: NicolasSchmutz/cm

 public function testAll()
 {
     // Null value insertion
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('bar' => null));
     $this->assertSame('INSERT INTO `t``est` (`bar`) VALUES (NULL)', $query->getSqlTemplate());
     $this->assertEquals(array(), $query->getParameters());
     // Associative array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo1'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo1'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo2', 'bar2'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo2', 'bar2', 'fooX'), $query->getParameters());
     // Scalar and array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', 'foo3');
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo3'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo4', 'foo5'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'), array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo4', 'foo5', 'fooX'), $query->getParameters());
     // Array and array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo6'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo6'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo7', 'foo8'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo7', 'foo8'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo9', 'bar9'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'), array('foo' => 'fooX', 'bar' => 'barX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?,`bar` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo9', 'bar9', 'fooX', 'barX'), $query->getParameters());
     // Scalar and array of array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo10')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo10'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo11', 'foo12'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')), array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo11', 'foo12', 'fooX'), $query->getParameters());
     // Array and array of array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo13')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo13'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo14'), array('foo15')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo14', 'foo15'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo16', 'bar16')));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo16', 'bar16'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')), array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18', 'fooX'), $query->getParameters());
     // Statement
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, null, 'INSERT IGNORE');
     $this->assertSame('INSERT IGNORE INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
 }

Example #5

0

Show file

File: InsertTest.php Project: cargomedia/cm

 public function testAll()
 {
     // Null value insertion
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('bar' => null));
     $this->assertSame('INSERT INTO `t``est` (`bar`) VALUES (NULL)', $query->getSqlTemplate());
     $this->assertEquals(array(), $query->getParameters());
     // Associative array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo1'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo1'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo2', 'bar2'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo2', 'bar2', 'fooX'), $query->getParameters());
     // Scalar and array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', 'foo3');
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo3'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo4', 'foo5'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'), array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo4', 'foo5', 'fooX'), $query->getParameters());
     // Array and array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo6'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo6'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo7', 'foo8'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo7', 'foo8'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo9', 'bar9'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'), array('foo' => 'fooX', 'bar' => 'barX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?,`bar` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo9', 'bar9', 'fooX', 'barX'), $query->getParameters());
     // Scalar and array of array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo10')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo10'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo11', 'foo12'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')), array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo11', 'foo12', 'fooX'), $query->getParameters());
     // Array and array of array
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo13')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo13'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo14'), array('foo15')));
     $this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo14', 'foo15'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo16', 'bar16')));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo16', 'bar16'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?)', $query->getSqlTemplate());
     $this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18'), $query->getParameters());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')), array('foo' => 'fooX'));
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
     $this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18', 'fooX'), $query->getParameters());
     // Statement
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, null, 'INSERT IGNORE');
     $this->assertSame('INSERT IGNORE INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
     $query = new CM_Db_Query_Insert(self::$_client, 't`est', ['foo' => 'foo2', 'bar' => 'bar2'], null, ['id' => ['literal' => 'LAST_INSERT_ID(id)']]);
     $this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(id)', $query->getSqlTemplate());
     $exception = $this->catchException(function () use($query) {
         new CM_Db_Query_Insert(self::$_client, 't`est', ['foo' => 'foo2', 'bar' => 'bar2'], null, ['id' => ['literal' => 'COUNT(*)']]);
     });
     $this->assertInstanceOf('CM_Exception', $exception);
     $this->assertSame('Unescaped Value is not whitelisted', $exception->getMessage());
 }

Example #6

0

Show file

File: Db.php Project: NicolasSchmutz/cm

 /**
  * @param string            $table
  * @param string|array      $fields Column-name OR Column-names array OR associative field=>value pair
  * @param string|array|null $values Column-value OR Column-values array OR Multiple Column-values array(array)
  * @param array|null        $onDuplicateKeyValues
  * @param string|null       $statement
  * @return string|null
  */
 public static function insert($table, $fields, $values = null, array $onDuplicateKeyValues = null, $statement = null)
 {
     if (null === $statement) {
         $statement = 'INSERT';
     }
     $client = self::getClient();
     $query = new CM_Db_Query_Insert($client, $table, $fields, $values, $onDuplicateKeyValues, $statement);
     $query->execute();
     return $client->getLastInsertId();
 }

PHP CM_Db_Query_Insert Examples