Showing posts with label Databases. Show all posts
Showing posts with label Databases. Show all posts

Sunday, April 7, 2013

Databases -> Prepared Statements

Prepared Statements consist of two stages

  1. Prepared Statement
  2. Execute Statement  
Prepared Statements : The statement template is created by the application and to database server. The Database server parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.

Execute Statements : In this statement only execute. By doing this it save lot of time.

See following examples

//server name
$hostname='localhost';

//databse name
$dbname='abc';

//username of database
$username='root';

//password of database
$password='';
try {
    $db = new PDO("mysql:host=$hostname;dbname=$dbname",$username,$password);
    $db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8");  
    
} catch (PDOException $e) {
    echo $e->getMessage();
}

//search text
$search='watch';

$q = $db->prepare('SELECT * FROM products WHERE name LIKE ?');
$q->execute(array('%'.$search.'%'));
$num = $q->rowCount();
if ($num > 0) {
  while ($row = $q->fetch()) {
    print_r($row);
  }
} else {
  echo "No Record";
}

Databases - Structured Query Language -SQL

Full form of SQL is Structured Query Language.
It is language that is created to manage the data. Data may be person name, person address, person full detail or it may be about any products. Data is a row material can be anything whatever you can think.

When data is represent in form of useful data known as Information.

Data need to get, add, update and delete means manage the data. SQL is a Language that use to manage the data.

There is a proper way to add, updated, delete and get the data & we have to followed all the stuff.

All data placed in one place so that we can mange known as database.

Suppose we are ask to create a databases to manage the employees in company "abccompany".

  • Create a database name eg abccompany
  • Create different tables like employees, emp_accounts, emp_leaves, emp_holidays etc.
  • Place data according to table.

Following are the operators which is used in SQL

Operator Description
= Equal
<> or != Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column

Saturday, April 6, 2013

Databases -Joins

Joins are used to connect the two or more table on the basis of some key in table.

Following are the main type of table joins
Self Join: A joins between two or more table, in which they join to same table (means only one table involved in actual).

Left join: A join between two or more table, in which in output left's table all reocord & right table only those which is matched.


Right join: A join between two or more table, in which in output right's table all reocord & left table only those which is matched.


Inner join: A join between two or more table, in which in output only those record which is matched.


Outer join: A join between two or more table, in which in output they have all the record.