extends abstract class Phalcon\Db\Adapter\Pdo
implements Phalcon\Events\EventsAwareInterface, Phalcon\Db\AdapterInterface
Specific functions for the Mysql database system
use Phalcon\Db\Adapter\Pdo\Mysql; $config = [ 'host' => 'localhost', 'dbname' => 'blog', 'port' => 3306, 'username' => 'sigma', 'password' => 'secret' ]; $connection = new Mysql($config);
Escapes a column/table/schema name
echo $connection->escapeIdentifier('my_table'); // `my_table`
echo $connection->escapeIdentifier(['companies', 'name']); // `companies`.`name`
@param string|array identifier
Returns an array of Phalcon\Db\Column objects describing a table
print_r($connection->describeColumns("posts"));
Lists table indexes
print_r($connection->describeIndexes('robots_parts'));
Lists table references
print_r($connection->describeReferences('robots_parts'));
Constructor for Phalcon\Db\Adapter\Pdo
This method is automatically called in \Phalcon\Db\Adapter\Pdo constructor. Call it when you need to restore a database connection.
use Phalcon\Db\Adapter\Pdo\Mysql; // Make a connection $connection = new Mysql([ 'host' => 'localhost', 'username' => 'sigma', 'password' => 'secret', 'dbname' => 'blog', 'port' => 3306, ]); // Reconnect $connection->connect();
Returns a PDO prepared statement to be executed with ‘executePrepared’
use Phalcon\Db\Column;
$statement = $db->prepare('SELECT * FROM robots WHERE name = :name');
$result = $connection->executePrepared($statement, ['name' => 'Voltron'], ['name' => Column::BIND_PARAM_INT]);
Executes a prepared statement binding. This function uses integer indexes starting from zero
use Phalcon\Db\Column;
$statement = $db->prepare('SELECT * FROM robots WHERE name = :name');
$result = $connection->executePrepared($statement, ['name' => 'Voltron'], ['name' => Column::BIND_PARAM_INT]);
Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server is returning rows
//Querying data
$resultset = $connection->query("SELECT * FROM robots WHERE type='mechanical'");
$resultset = $connection->query("SELECT * FROM robots WHERE type=?", array("mechanical"));
Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server doesn’t return any rows
//Inserting data
$success = $connection->execute("INSERT INTO robots VALUES (1, 'Astro Boy')");
$success = $connection->execute("INSERT INTO robots VALUES (?, ?)", array(1, 'Astro Boy'));
Returns the number of affected rows by the lastest INSERT/UPDATE/DELETE executed in the database system
$connection->execute("DELETE FROM robots");
echo $connection->affectedRows(), ' were deleted';
Closes the active connection returning success. Phalcon automatically closes and destroys active connections when the request ends
Escapes a value to avoid SQL injections according to the active charset in the connection
$escapedStr = $connection->escapeString('some dangerous value');
Converts bound parameters such as :name: or ?1 into PDO bind params ?
print_r($connection->convertBoundParams('SELECT * FROM robots WHERE name = :name:', array('Bender')));
Returns the insert id for the auto_increment/serial column inserted in the lastest executed SQL statement
//Inserting a new robot
$success = $connection->insert(
"robots",
array("Astro Boy", 1952),
array("name", "year")
);
//Getting the generated id
$id = $connection->lastInsertId();
Starts a transaction in the connection
Rollbacks the active transaction in the connection
Commits the active transaction in the connection
Returns the current transaction nesting level
Checks whether the connection is under a transaction
$connection->begin(); var_dump($connection->isUnderTransaction()); //true
Return internal PDO handler
Return the error info, if any
Name of the dialect used
Type of database system the adapter is used for
Active SQL bound parameter variables
Sets the event manager
Returns the internal event manager
Sets the dialect used to produce the SQL
Returns internal dialect instance
Returns the first row in a SQL query result
//Getting first robot
$robot = $connection->fetchOne("SELECT * FROM robots");
print_r($robot);
//Getting first robot with associative indexes only
$robot = $connection->fetchOne("SELECT * FROM robots", Phalcon\Db::FETCH_ASSOC);
print_r($robot);
Dumps the complete result of a query into an array
//Getting all robots with associative indexes only
$robots = $connection->fetchAll("SELECT * FROM robots", Phalcon\Db::FETCH_ASSOC);
foreach ($robots as $robot) {
print_r($robot);
}
//Getting all robots that contains word "robot" withing the name
$robots = $connection->fetchAll("SELECT * FROM robots WHERE name LIKE :name",
Phalcon\Db::FETCH_ASSOC,
array('name' => '%robot%')
);
foreach($robots as $robot){
print_r($robot);
}
Returns the n’th field of first row in a SQL query result
//Getting count of robots
$robotsCount = $connection->fetchColumn("SELECT count(*) FROM robots");
print_r($robotsCount);
//Getting name of last edited robot
$robot = $connection->fetchColumn("SELECT id, name FROM robots order by modified desc", 1);
print_r($robot);
Inserts data into a table using custom RDBMS SQL syntax
// Inserting a new robot
$success = $connection->insert(
"robots",
array("Astro Boy", 1952),
array("name", "year")
);
// Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);
Inserts data into a table using custom RBDM SQL syntax
//Inserting a new robot
$success = $connection->insertAsDict(
"robots",
array(
"name" => "Astro Boy",
"year" => 1952
)
);
//Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);
Updates data on a table using custom RBDM SQL syntax
//Updating existing robot
$success = $connection->update(
"robots",
array("name"),
array("New Astro Boy"),
"id = 101"
);
//Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101
//Updating existing robot with array condition and $dataTypes
$success = $connection->update(
"robots",
array("name"),
array("New Astro Boy"),
array(
'conditions' => "id = ?",
'bind' => array($some_unsafe_id),
'bindTypes' => array(PDO::PARAM_INT) //use only if you use $dataTypes param
),
array(PDO::PARAM_STR)
);
Warning! If $whereCondition is string it not escaped.
Updates data on a table using custom RBDM SQL syntax Another, more convenient syntax
//Updating existing robot
$success = $connection->updateAsDict(
"robots",
array(
"name" => "New Astro Boy"
),
"id = 101"
);
//Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101
Deletes data from a table using custom RBDM SQL syntax
//Deleting existing robot $success = $connection->delete( "robots", "id = 101" ); //Next SQL sentence is generated DELETE FROM `robots` WHERE `id` = 101
Gets a list of columns
Appends a LIMIT clause to $sqlQuery argument
echo $connection->limit("SELECT * FROM robots", 5);
Generates SQL checking for the existence of a schema.table
var_dump($connection->tableExists("blog", "posts"));
Generates SQL checking for the existence of a schema.view
var_dump($connection->viewExists("active_users", "posts"));
Returns a SQL modified with a FOR UPDATE clause
Returns a SQL modified with a LOCK IN SHARE MODE clause
Creates a table
Drops a table from a schema/database
Creates a view
Drops a view
Adds a column to a table
Modifies a table column based on a definition
Drops a column from a table
Adds an index to a table
Drop an index from a table
Adds a primary key to a table
Drops a table’s primary key
Adds a foreign key to a table
Drops a foreign key from a table
Returns the SQL column definition from a column
List all tables on a database
print_r($connection->listTables("blog"));
List all views on a database
print_r($connection->listViews("blog"));
Gets creation options from a table
print_r($connection->tableOptions('robots'));
Creates a new savepoint
Releases given savepoint
Rollbacks given savepoint
Set if nested transactions should use savepoints
Returns if nested transactions should use savepoints
Returns the savepoint name to use for nested transactions
Returns the default identity value to be inserted in an identity column
//Inserting a new robot with a valid default value for the column 'id'
$success = $connection->insert(
"robots",
array($connection->getDefaultIdValue(), "Astro Boy", 1952),
array("id", "name", "year")
);
Returns the default value to make the RBDM use the default value declared in the table definition
//Inserting a new robot with a valid default value for the column 'year'
$success = $connection->insert(
"robots",
array("Astro Boy", $connection->getDefaultValue()),
array("name", "year")
);
Check whether the database system requires a sequence to produce auto-numeric values
Check whether the database system requires an explicit value for identity columns
Return descriptor used to connect to the active database
Gets the active connection unique identifier
Active SQL statement in the object
Active SQL statement in the object without replace bound paramters
Active SQL statement in the object
© 2011–2016 Phalcon Framework Team
Licensed under the Creative Commons Attribution License 3.0.
https://docs.phalconphp.com/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html