SQL UNION Operator

SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

FULL OUTER JOIN

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL RIGHT JOIN

SQL RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side whenthere is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

SQL LEFT JOIN

SQL LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side whenthere is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2

ON table1.column_name=table2.column_name;

PHP TYPE HINTING

PHP TYPEHINTING

Functions are now able to force parameters to be objects.
<?php
// An example class
class MyClass {
    public $var = 'Hello World';
}

/**
 * A test function
 *
 * First parameter must be an object of type MyClass
 */
function myFunction(MyClass $foo) {
    echo $foo->var;
}

// Works
$myclass = new MyClass;
myFunction($myclass);
?>

Change variable type

PHP TYPE CASTING

Change variable type.
<?php
echo (float) ( (0.1+0.7)); // echoes 0!
echo (int) ( (0.1+0.7)); // echoes 0.8!
echo (double) ( (0.1+0.7)); // echoes 0.8!
?>

PHP TYPECASTING

TYPECASTING

Change variable type.
<?php
    echo (float) ( (0.1+0.7)); // echoes 0!
    echo (int) ( (0.1+0.7)); // echoes 0.8!
    echo (double) ( (0.1+0.7)); // echoes 0.8!
?>

xml XPath

XML XPath


XPath is a syntax for defining parts of an XML document.

Expression Description
------------------------------------

nodename Selects all nodes with the name "nodename"
/ Selects from the root node
// Selects nodes in the document from the current node that match the selection no matter where they are
. Selects the current node
.. Selects the parent of the current node
@ Selects attributes

Example:

Path Expression Result
------------------------------------

bookstore Selects all nodes with the name "bookstore"
/bookstore Selects the root element bookstore
Note: If the path starts with a slash ( / ) it always represents an absolute path to an element!

bookstore/book Selects all book elements that are children of bookstore
//book Selects all book elements no matter where they are in the document
bookstore//book Selects all book elements that are descendant of the bookstore element, no matter where they are under the bookstore element
//@lang Selects all attributes that are named lang

REQUIRE_ONCE vs INCLUDE_ONCE

REQUIRE_ONCE vs INCLUDE_ONCE

The require_once() statement is identical to require() except PHP will check if the file has already been included, and if so, not include (require) it again

REQUIRE vs INCLUDE

PHP REQUIRE vs INCLUDE

The require() function is identical to include(), except that it handles errors differently. If an error occurs, the include() function generates a warning, but the script will continue execution. The require() generates a fatal error, and the script will stop.

REQUIRE_ONCE vs INCLUDE_ONCE

The require_once() statement is identical to require() except PHP will check if the file has already been included, and if so, not include (require) it again.

Difference between php require and inlcude functions. 

Mysql Transactions

Mysql Transactions

Avoid inconsistency in the cases where the script terminates unexpectedly.

try {
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();

  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate)
      values (23, 50000, NOW())");

  $dbh->commit();
 
} catch (Exception $e) {

  $dbh->rollBack(); // roll back if error occured

  echo "Failed: " . $e->getMessage();
}

Mysql FULTEXT Search

FULTEXT Search

LIKE search

SELECT * FROM articles WHERE body LIKE '%$keyword%';

This was slow and inefficient.Every time someone searched for an article, they got
far too many results.

Fultext search

SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP')

MYISAM vs InnoDB

MYISAM vs InnoDB

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB

PDO vs MYSQLI

PDO MySQLi

Database support 12 different drivers MySQL only
API OOP OOP + procedural
Connection Easy Easy
Named parameters Yes No
Object mapping Yes Yes
Prepared statements
(client side) Yes No
Performance Fast Fast
Stored procedures Yes Yes

Database Support

PDO:

CUBIRD, mysql server, firebird/interbase, ibm, informix, mysql, oracle, odbc, db2, postgresql, sqlite, 4d

mysqli:

mysql