Profile Picture
IT Made Easy
The site provides some rough IT Notes that make your life easier. The site currently has notes on HTML, CSS, Regex, OOPS, JS, JQuery, PHP, Java, ASP.NET, C#, Database, Linux, URL Rewrite, SEO, Project Management and Hosting. Please do share this site if you like.

Database

Next
Next

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
  • $output = mysql_real_escape_string("unescaped_string", [$link])

                       //This provides a security feature which prevents                           //security vulnerabilities by escaping characters like                        //quotes


Create New Links WIth MySQL

To open a new link we need to make newlink as true. New link are 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.

IT Notes

by VinodSebastian



IT Downloads


Catalogue