Database

MySQL commands:

$link = mysql_connect("host",:"username", "password"); //$link= FALSE on error, Link Id otherwise.
$db = mysql_db_select("database", [$link]);            //$db = FALSE on error.
$rs = mysql_query("query", [$link]);                   //Is used for querying like select.
$rs = mysql_exec("query", [$link]);                    //Is used for executing commands like inserting, updating, deleting.
$row = mysql_fetch_array($rs);                         //Resource Id. Fetch as an index array.
$row = mysql_fetch_assoc($rs);                         //Resource Id. Fetch as an associative array.
mysql_close($link);                                    //Close link.
mysql_insert_id($rs);                                  //Last insertion Id.
mysql_error([$link]);                                  //Last error.
//This provides a security feature which prevents security vulnerabilities by escaping characters like quotes.
$output = mysql_real_escape_string("unescaped_string", [$link]);

Create new links with MySQL:

To open a new link, we need to make newlink as true. New link is created even if identical calls are made In SQL safe mode this parameter is ignored.

$link = mysql_connect("host",:"username", "password", newlink);

mysqli = MySQL Improved

PHP data objects are:

• used to connect with a large variety of RDBMS.
• secure. This is implemented by Prepare command.
• atomic. It uses beginTransaction, Commit and rollBack for those RDBMS that support transactions.

try
{
$dsn = ’mysql:host=localhost;dbname=library’;              //Dsn Data source name formatted according to the driver used.
$dbh = new PDO($dsn, ’dbuser’, ’dbpass’);              //Dbh Database handler.
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //By default PDO::ERRMODE_SILENT. So, no exception raised.

$dbh->beginTransaction();               //Begins transaction.
$affected = $dbh->exec("sql")           //For INSERT, UPDATE, or DELETE and return affected rows.
$dbh->exec("sql")
$dbh->commit();                         //Commits transaction.
$dbh->rollBack();                       //RollBack transaction.

$results = $dbh->query($sql);           //Returns a PDOStatement object.
$results->setFetchMode(PDO::FETCH_OBJ); //PDO::FETCH_BOTH, PDO::FETCH_ARRAY.
foreach ($results as $row)
{
}

$sql= "SELECT * FROM table WHERE id = :param1";      //Also ? instead of : for bound parameters.
$stmt = $dbh->prepare($sql);                         //No need of quoting here using PDO:quote().
$stmt->bindParam(’:param1’, $param1);
$stmt->setFetchMode(PDO::FETCH_OBJ);
$stmt->execute();
$results = $stmt->fetchAll();
foreach ($results as $row)
{
}
}
catch (PDOException $e)
{
echo "Failed: ". $e->getMessage();
}

Persistent connections:

Persistent connections do not close on the execution of script. Also, when a new connection is requested, it looks for an open identical connection (Same username, password and host) and if so, uses it.

• mysql_pconnect();
• mysqli_connect() with p: prefix on host.
• While creating PDO object use PDO::ATTR_PERSISTENT as a driver option Driver option is the fourth and final parameter and is an array of options.