Skip to content

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:

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.

Web Service

 

Server:

//We have to first code the class MySoapServer with a function named method with parameter param and locate this code in server.php.
$options = array(uri' => http://example.org/soap/server/);
$server = new SoapServer(NULL, $options);
$server->setClass(MySoapServer);
$server->handle();

Client:

try
{
$options = array(
location => http://example.org/soap/server/server.php,uri => http://example.org/soap/server/,'trace'=1);
$client = new SoapClient(NULL, $options);               
//WSDL URI if working in WSDL mode with options as next parameter. Here not in WSDL mode so first parameter is NULL and options have to include location an URI. Last parameter with trace=1 will debug.
$results = $client->method(param);                 //Method from WSDL. Returns XML. You can loop through it.
echo $client->__getLastRequestHeaders();           //Request headers.
echo $client->__getLastRequest();                  //Request XML body.
}
catch (SoapFault $e)
{
echo $e->getMessage();
}

 

XML

XML:

 

SimpleXML:

SimpleXML is a PHP 5 library that takes care of working with XML. Some methods are available after 5.1.3.

$library = new SimpleXMLElement($xmlstr);                   //Load XML from string.
$library = new SimpleXMLElement(library.xml, NULL, true);   //Load XML from file. True tells first parameter is a path to the file.

foreach ($library->book as $book)                           //Looping through many books is library. Children.
{
echo $book[isbn] . "n";                                     //Accessing an attribute.
echo $book->title . "n";                                    //Accessing a child.
}

foreach ($library->children() as $child => $valuechild)     //Get children of this element.
{
echo $child->getName()."=".$child;                          //Example a = this is a link.
echo   $child."=". $valuechild;                             //Example a = this is a link.           
foreach ($child->attributes() as $attr => $valueattr)       //Get attributes of this element.
{
echo $attr->getName()."=".$attr;                            //Example target = _blank.
echo   $attr."=". $valueattr;                               //Example target = _blank.
}
}

 

XPATH:

XPath or XML Path Language is a way to access and search XML Documents.

$results = $library->xpath(/library/book/title);        //Returns the XML Object under the XPath.

$newbook = $book->addChild(title, "Enders Game");       ///Adding a child. Third optional parameter is namespace.
$book->addAttribute(isbn, 0812550706);                  //Adding an attribute. Third optional parameter is namespace.

header(Content-type: text/xml);                         //Header for XML.
echo $library->asXML();                                 //Print XML.

//Remove the children of book[0]. You cannot remove an attribute of book. You can only set them to empty. For that to happen we will have to export to DOM and use a powerful functionality there.
$library->book[0] = NULL;               

$namespaces = $library->getDocNamespaces();             //Gets namespaces declared in the root element referenced. By passing true all namespaces declared recursively in children will be returned.
foreach ($namespaces as $key => $value)
{
echo "{$key} => {$value}";
}

$namespaces = $library->getNamespaces(true);            //Gets namespaces used (not declared) in the root element referenced. Passing True will return all namespaces used recursively in children will be returned.

 

DOM:

DOM is Document Object Model.

$dom = new DomDocument();

$dom->load(library.xml);                   //Load from file.
$dom->loadXML($xml);                       //Load from XMl string.

$dom->loadHTMLFile("librabry.html");       //Load from HTML file.
$dom->loadHTML($html);                     //Load from HTML string.

$dom->saveXML();                           //Save as XMl.
$dom->saveHTML();                          //Save as HTML.

$element = $dom->createElement("a", 'This is a link!'); //Creates an element.
$element = $dom->getAttribute("target");                //Returns value of attribute.
$element = $dom->hasAttribute("target");                //Checks to see if attribute exists. Returns TRUE or FALSE.
$element = $dom->setAttribute("target","_blank");       //Adds new attribute. Returns sub-DOM object.
$element = $dom->:removeAttribute("target");            //Removes attribute. Returns TRUE or FALSE.

Design Pattern

OO PHP

Functions

Strings

Arrays