jueves, 16 de marzo de 2017

Arquitectura de InnoDB

Buffer Pool

The buffer pool is an area in main memory where InnoDB caches table and index data as data is accessed. The buffer pool allows frequently used data to be processed directly from memory, which speeds up processing. On dedicated database servers, up to 80% of physical memory is often assigned to the InnoDB buffer pool.
For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm.

miércoles, 15 de marzo de 2017

Tutorial PHP - PDO : Insertar , Modificar, Eliminar y Listar registros

Tutorial PHP - PDO : Insertar , Modificar, Eliminar y Listar registros




Para los que recien inician con PHP y han oido algo de PDO, bueno PDO es una libreria de PHP que nos permite abstraer ciertos conceptos de manejo de base de datos, las siglas dePDO significa PHP Data Objects (Objetos de Datos PHP), en resumidas utilizando PDO, nos permite evitar utilizar funciones propias de cada driver de base de datos, por ejemplo si usasMySQL dependiendo de la libreria mysql o mysqli, para conectarte usaras mysql_connect("localhost", "usuario", "contraseña") o $mysqli = new mysqli("localhost", "usuario", "contraseña", "basedatos"), de esto nos evita PHP PDO.

PHP PDO - setAttribute, beginTransaction and commit

PHP PDO - setAttribute, beginTransaction and commit

source : http://coursesweb.net/php-mysql/pdo-setattribute-begintransaction-commit

About try ... catch()

When it is created a PDO object with a connection to a database, in case of an error will throw the PDOException. If the error is not catched with try ... catch() PHP will stop the execution of the script.
PDOException is an extension of the PHP Exception class, that can "catch" the errors.
With try ... catch(), besides the fact that the error is taken and the script can continue its execution, it can also personalize the error message which will be displayed.
Syntax:
try {
  // ... PHP instructions
}
catch(PDOException $e) {
  echo 'Custom Error Message';

  // Output the error code and the error message
  echo $e->getCode(). '-'. $e->getMessage();
}
$e - is the object that will store the error detected by PHP.
getCode() - returns the error code.
getMessage() returns the error message.
If these methods are not added, it can be displayed only a custom message.

PHP PDO - prepare and execute

PHP PDO - prepare and execute



source : http://coursesweb.net/php-mysql/pdo-prepare-execute



With the exec() and query() methods the SQL command is automatically executed.
There is another variant, too. With PDO it's posible to "prepare" a SQL command without send /execute it. This is accomplished with the prepare() method.
- The prepare() method takes as argument an SQL statement and returns a PDOStatement object. This object contains an execute() method that will execute the SQL statement when it is called.
- The execute() returns TRUE, or FALSE in case of error.

So, prepare() can prepare an SQL statement to be executed by the execute() method, using this syntax:
$sqlprep = $PDOconnection->prepare("SQL statement");
$sqlprep->execute(Array);
- The "SQL statement" - can be any valid SQL statement: INSERT, SELECT, UPDATE, DELETE.
- The Array argument from the execute() method - is optional. It's an array of values with as many elements as there are bound parameters in the SQL statement being executed, specified in the prepare().

The prepared SQL instruction can be used to be executed multiple times, with different values. You can understand from the examples below.

• The examples presented it this tutorial use the "sites" table, created in the previous lessons, containig these data:
| id | | name               | | category          | | link                         |
------------------------------------------------------------------------------------
| 1 | | Courses - Tutorials | | education         | | http://coursesweb.net           |
| 2 | | PHP-MySQL Course    | | programming       | | http://coursesweb.net/php-mysql |
| 3 | | English Courses     | | foreign languages | | www.marplo.net/engleza       |

PHP PDO - Select query, fetch


PHP PDO - Select query, fetch

soruce: http://coursesweb.net/php-mysql/pdo-select-query-fetch



To select data in a MySQL table, use the SELECT query, and the PDO query() method. Then, you can get rows data with the fetch() methods and their FETCH constants.

Select data in a MySQL table

Once you have created and added some data in a MYSQL table, you can use a SELECT query, with the PDO query() method to get those data.
The query() method returns a result set with data returned by MySQL, or FALSE in case of error.
In the examples in this lesson it is used the "sites" table, created in the previous lesson, having these 3 rows:
| id | | name               | | category          | | link                         |
------------------------------------------------------------------------------------
| 1 | | Courses - Tutorials | | education         | | http://coursesweb.net           |
| 2 | | PHP-MySQL Course    | | programming       | | http://coursesweb.net/php-mysql |
| 3 | | English Courses     | | foreign languages | | www.marplo.net/engleza       |

PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL

PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL

source : http://coursesweb.net/php-mysql/pdo-exec-insert-update-delete

After the connection to database is successfully created and the PDO object instance is set, the object can be used to perform SQL queries.
The SQL queries with PDO can be made in two ways:
        - directly using "exec()", and "query()" methods,
        - or with the prepare() ... execute() statement.
The first variant is more simple, in this lesson it's presented the exec method.

PHP PDO - Introduction and Connecting to Databases

dsd




PHP PDO - Introduction and Connecting to Databases



PDO (PHP Data Objects) is a PHP extension for accessing databases in PHP.
PDO uses OOP characteristics (Object Oriented Programming) available since PHP 5.1.
Because PDO is working with classes and objects, you must be familiar with the PHP object-oriented programming.
PDO can work with the following databases:
  • MySQL
  • PostgreSQL
  • SQLite 2 & 3
  • Firebird
  • Informix (IBM Informix Dynamic Server)
  • ODBC
  • Oracle
  • DBLM: FreeTDS / Sybase / MS-SQL
  • IBM (IBM DB2)
- One of the PDO advantages is that you can use similar functions for processing queries to databases, regardless of their type (of those mentioned above). 
The PHP scripts that use PDO to connect to a database perform in general the following operations:
  1. Connect to the database server by calling new PDO(), that creates an object for working with that database.
  2. Implementing PDO specific functions to perform queries to the database.
  3. Getting and processing the returned data.
  4. Disconnect from the database server.
- To see if the PDO is available in your PHP server, for your database, you can check with phpinfo(), where it's a section for PDO, or with this method: PDO::getAvailableDrivers()
foreach(PDO::getAvailableDrivers() as $driver) {
  echo $driver.'<br />';
}

Connecting to database

Any interaction with a database begins by connecting to it. Depending on the type of database, the connection is made by creating an instance object, with: new PDO(). After the connection is created, we apply PDO methods to get and process data. These methods are largely the same, regardless of the connected database. Here is how to connect to some of these database:

Connecting to MySQL

<?php
// Connection data (server_address, database, username, password)
$hostdb = 'localhost';
$namedb = 'dbname';
$userdb = 'username';
$passdb = 'password';

// Display message if successfully connect, otherwise retains and outputs the potential error
try {
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  echo 'Connected to database';
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>
- The $conn variable is the PDO object that will contain the data received from MySQL server. The PDO methods for processing data will be aplied to this object.
Notice this statement: try() ... catch() This is used to not expose important connection data that can be displayed in case of errors.
        - For example, using this technique if your database name is incorrect, returns something like this:
SQLSTATE[42000] [1049] Unknown database 'dbname'
        - If the name or password is wrong, show something similar to:
SQLSTATE[28000] [1045] Access denied for user 'username'@'localhost' (using password: YES)
But if you use directly:
<?php
// Connection data (server_address, database, username, password)
$hostdb = 'localhost';
$namedb = 'dbname';
$userdb = 'username';
$passdb = 'password';

$conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
?>
        - The error message would display more data, including password, as shown below:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user 'thename'@'localhost' (using password: YES)' in E:\server\www\zzz.php:14 Stack trace: #0 E:\server\www\zzz.php(14): PDO->__construct('mysql:host=loca...', 'thenae', 'the_password') #1 {main} thrown in E:\server\www\zzz.php on line 7
• So it is recommended to apply try() ... catch().

Connecting to PostgreSQL

<?php
try {
  $conn = new PDO("pgsql:host=localhost port=5432 dbname=pdo", "username", "password");
  echo "PDO connection object created";
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to SQLite

When PDO is used with SQLite, you specify only the path to your file with the database. If that file not exist, it will try to create it.
<?php
try {
  // connect to SQLite database
  $conn = new PDO("sqlite:/path/to/database.sdb");
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to Firebird

Firebird is a database used in Windows.
<?php
try {
  $conn = new PDO("firebird:dbname=localhost:C:\Programs\Firebird\DATABASE.FDB", "SYSDBA", "masterkey");
}   
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to Informix

<?php
try {
  $conn = new PDO("informix:DSN=InformixDB", "username", "password");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to Oracle

<?php
try {
  $conn = new PDO("OCI:dbname=accounts;charset=UTF-8", "username", "password")
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to ODBC

There are many ODBC connections that can be created. Here's how to connect to a MS Access database called "accounts".
<?php
try {
  $conn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=Admin");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to DBLIB

Another database for Windows.
<?php
$hostdb = "localhost";
$port   = 10060;
$namedb   = "my_database";
$userdb = "username";
$passdb = "password";

try {
  $conn = new PDO ("dblib:host=$hostdb:$port;dbname=$namedb","$userdb","$passdb");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to IBM

The following example shows how to connect to a IBM DB2 database, named "accounts".
<?php
try {
  $conn = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=accounts; HOSTNAME=1.2.3,4;PORT=56789;PROTOCOL=TCPIP;", "username", "password");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Close the connection to database

Normally, PHP closes the connection to the database after the script was executed. But the disconnection can be done intentionally, attributing the null value to the PDO object, as it's shown in the example below.
The instance of that object will be destroyed, so, this method should be added after all the instructions at the PDO object were written.
    -Disconnecting with this method is useful because it releases the memory used.
<?php
// Connection data (server_address, database, username, password)
$hostdb = 'localhost';
$namedb = 'dbname';
$userdb = 'username';
$passdb = 'password';

// Display message if successfully connect, otherwise retains and outputs the potential error
try {
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  echo 'Connected to database';
    /* Instructions for working with $conn object */

  $conn = null;         // Close the connection
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>