Tuesday, November 1, 2011

SQL Joins:

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

SQL INNER JOIN :

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: INNER JOIN is the same as JOIN.

SQL INNER JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons with any orders.
We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

The
INNER JOIN keyword return rows when there is at least one match in both
tables. If there are rows in "Persons" that do not have matches in
"Orders", those rows will NOT be listed.

SQL LEFT JOIN:

The
LEFT JOIN keyword returns all rows from the left table (table_name1),
even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola

Friday, February 4, 2011

create action.php

<?php
require('common/dbconnect.php');
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<script type="text/javascript">
function delete(){
    var xmlhttp;
    if(window.XMLHttpRequest){
    xmlhttp=new XMLHttpRequest();
    }
    else{
    xmlhttp=new ActiveXObject("Microsoft.XMLHttp");
    }
    xmlhttp.open("GET","delete.php",true);
    xmlhttp.send();
    xmlhttp.onreadystatechange=function()
    {
    if(xmlhttp.readyState==4 && xmlhttp.status==200){
    document.getElementById("custdiv").innerHTML=xmlhttp.responseText;
    }
    }
}

</script>
</head>

<body>
<form action="" method="post">

   <div id="custdiv"></div>
   <input type="button" value="add" name="add" onclick="javascript:location.href='add.php'" />
    <table border="1" bordercolor="#3399FF">
    <tr bgcolor="#CCFFCC">
    <th> Sno </th>
    <th> Cust Name </th>
    <th> Cust Father </th>
    <th> Address </th>
    <th> Actions </th>
    <th></th>
    </tr>
    <?php
    $i=1;
    //$res=mysql_query("select custid,custname,custfather,address from customer") or die(mysql_error());
    $res=mysql_query("select * from customer");
        while($row=mysql_fetch_array($res)){
    ?>
    <tr>
    <td> <input type="checkbox" name="sno" value="<?php echo $i?>" /><?php echo $i?></td>
    <td>
    <?php echo $row['custname']; ?>
      </td>
    <td>
    <?php echo $row['custfather']; ?>
      </td>
    <td>
    <?php echo $row['address']; ?>
      </td>
    <td> <a href="update.php?cid=<?php echo $row['custid']; ?>">Update</a></td>
    <td><a href="delete.php?cid=<?php echo $row['custid']; ?>" >Delete</a></td>
    </tr>
    <?php $i++; } ?>
    </table>
</form>
</body>
</html>

create add.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<script type="text/javascript">

function addcust(){
   //alert("hai");
   str1=document.getElementById("cname").value;
   str2=document.getElementById("cfname").value;
   str3=document.getElementById("address").value;
   if(str1==""||str2==""||str3==""){
    alert("please add the fields");
    return false;
   }

   var xmlhttp;
    if(window.XMLHttpRequest){
    xmlhttp=new XMLHttpRequest();
    }
    else{
    xmlhttp=new ActiveXObject("Microsoft.XMLHttp");
    }
    url="addcust.php?name="+str1+"&fname="+str2+"&addres="+str3;
    xmlhttp.open("GET",url,true);
    xmlhttp.send();
    xmlhttp.onreadystatechange=function()
    {
    if(xmlhttp.readyState==4 && xmlhttp.status==200){
    document.getElementById("custdiv").innerHTML=xmlhttp.responseText;
    }
    }
return false;
}
</script>
</head>

<body>
   <div id="custdiv">
<table border="1">
    <tr>
    <td> Customer Name </td>
     <td><input type="text" name="cname" id="cname" value="" /></td>
    </tr>
    <tr>
    <td> Customer Father Name </td>
    <td> <input type="text" name="cfname" id="cfname" value=""/></td>
    </tr>
    <tr>
    <td> Address </td>
    <td> <input type="text" name="address" id="address" value="" /></td>
    </tr>
    <tr>
    <td></td>
    <td><input type="submit" value="submit" onclick="addcust()" /></td>
    </tr>
    </table>
   </div>
</body>
</html>

addcust.php

<?php
$name=$_GET["name"];
$fname=$_GET["fname"];
$address=$_GET["addres"];
require("common/dbconnect.php");
mysql_query("insert into customer(custname,custfather,address) values('$name','$fname','$address')") or die(mysql_error());
        header("Location:actions.php");
?>

update.php

<?php
    require("common/dbconnect.php");
        $cid=$_GET["cid"];
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<script type="text/javascript">
function update(){
   var xmlhttp;
   cid=document.getElementById("cno").value;
   str1=document.getElementById("cname").value;
   str2=document.getElementById("cfname").value;
   str3=document.getElementById("address").value;
   //alert(str1);
   //alert(str2);
   //alert(str3);
   if(str1==""||str2==""||str3==""){
     alert("insert any value not empty");
    return false;
   }
   if(window.XMLHttpRequest){
     xmlhttp = new XMLHttpRequest();
   }
   else{
     xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
   }
   url = "edit.php?name="+str1+"&fname="+str2+"&addr="+str3+"&cno="+cid;
   xmlhttp.open("GET",url,true);
   xmlhttp.send(null);
   xmlhttp.onreadystatechange=function()
   {
     if(xmlhttp.readyState==4 && xmlhttp.status==200){
        document.getElementById("cdiv").innerHTML=xmlhttp.responseText;
        //document.getElementById("cdiv").style.display="none";
    }
   }
return false;
}
</script>
</head>

<body>

<?php
    $res=mysql_query("select * from customer where custid=$cid");
    $row=mysql_fetch_array($res);
?>
<div id="cdiv">

   <input type="hidden" id="cno" name="cno" value="<?php echo $cid?>" />
     <table border='1'>
    <tr>
    <td>Customer Name</td>
    <td><input type="text" name="cname" id="cname" value="<?php echo $row['custname']?>" /></td>
    </tr>
    <tr>
    <td>Customer Father Name</td>
    <td><input type="text" name="cfname" id="cfname" value="<?php echo $row['custfather']?>" /></td>
    </tr>
    <tr>
    <td>Address</td>
    <td><input type="text" name="address" id="address" value="<?php echo $row['address']?>"/></td>
    </tr>
    <tr>
    <td></td>
    <td><button type="submit" onclick="update()">update</button></td>
    </tr>
   </table>
</div>
</body>
</html>

edit.php

<?php
$cid=$_GET["cno"];
$name=$_GET["name"];
$fname=$_GET["fname"];
$addr=$_GET["addr"];
require('common/dbconnect.php');
mysql_query("update customer set custname='$name',custfather='$fname',address='$addr' where custid=$cid")or die("error".mysql_error());
header("Location:actions.php");
?>

delete.php

<?php
require("common/dbconnect.php");
$cno=$_GET["cid"];
mysql_query("delete from customer where custid=$cno");
header("Location:actions.php");
?>

dbconnect.php

<?php
$con=mysql_connect("localhost","root","") or die("fail to connect".mysql_error());
mysql_select_db("base",$con) or die(mysql_error());
?>

insert,delet,update

Regstration.php(Controller )

<?php
class Registration extends Controller
{

    function Registration()
    {
       parent::Controller();
       $this->load->database();
       $this->load->model('registration_model');
       $this->load->helper('url');
       }
    function index()
       {

        $data=array();
        $submit = $this->input->post('submit');
        if($submit == 'Insert')
           {

            $name=$this->input->post('name');
            $age =$this->input->post('age');
            $gender=$this->input->post('gender');
            $address=$this->input->post('address');
            $this->registration_model->insert_values($name,$age,$gender,$address);

            }

            $row=$this->registration_model->getusers();
            $data['query']=$row;
            $this->load->view('registration',$data);

        }
    function update($id=NULL)
      {
        $data=array();

          $submit=$this->input->post('submit');
           if($submit=='Update')
           {

             $name=$this->input->post('name');
            $age=$this->input->post('age');
            $gender=$this->input->post('gender');
            $address=$this->input->post('address');

            $this->registration_model->update_values($name,$age,$gender,$address,$id);
               redirect('registration');
            }

            $row=$this->registration_model->getusers_byid($id);
            $data['query']=$row;

            $this->load->view('update_reg',$data);

    }
           function delete($id= NULL)
           {
             $this->registration_model->delete_values($id);
             $row=$this->registration_model->getusers();
            $data['query']=$row;

            $this->load->view('registration',$data);
           }

}
?>

Registration.php(Model)

<?php
class Registration_model extends Model
{
   function Registration_model()
     {
       parent::Model();
    }

    function insert_values($name,$age,$gender,$address)
    {
    $data=array(
                  'name'=>$name,
                  'age'=>$age,
                'gender'=>$gender,
                'address'=>$address );
    $query=$this->db->insert('user',$data);

    }

    function getusers()
        {
       $query = $this->db->get('user');
       return $query->result();
       }
       function getusers_byid($id)
        {
        $this->db->where('id',$id);
          $query = $this->db->get('user');
          return $query->row();
       }

    function update_values($name,$age,$gender,$address,$id)
    {

        $data=array('name'=>$name,
                    'age'=>$age,
                   'gender'=>$gender,
                   'address'=>$address);
            $this->db->where('id',$id);
            $this->db->update('user',$data);



    }
    function delete_values($id)
    {
        $this->db->where('id', $id);
        $this->db->delete('user');

    }

}

?>

Registration.php(view)

<html>
<title>
Registration</title>
<body>
<form action="<?php echo base_url()?>registration" method="post">
<table border="1">
<tr><td>Username:</td><td><input type="text" name="name" /></td></tr>
<tr><td>Age:</td><td><input type="text" name="age" /></td></tr>
<tr><td>Gender:</td><td><input type="text" name="gender" /></td></tr>
<tr><td>Address:</td><td><input type="text" name="address" /></td></tr>
<tr><td align="center" colspan="2"><input type="submit" name="submit" value="Insert" /></td>
</tr>
</table>
</form>

<table border="1">
<tr><td>NAME</td><td>AGE</td><td>GENDER</td><td>ADDRESS</td></tr>
<?php
$i="";
foreach($query as $value){?>
<form action="<?php echo base_url()?>registration" method="post">

<tr>
<td><?php echo $value->name;?></td>
<td><?php echo $value->age;?></td>
<td><?php echo $value->gender;?></td>
<td><?php echo $value->address;?></td>
<td><a href="<?php echo base_url()?>registration/update/<?php echo $value->id;?>">Update</a></td>
<td><a href="<?php echo base_url()?>registration/delete/<?php echo $value->id;?>">Delete</a></td>
</tr>
</form>
<?php
}?>

</table>

</body>
</html>

update.php(view)

<html>
<head>
</head>
<title>Update</title>
<body>
<form action="<?php echo base_url() ?>registration/update/<?php echo $query->id; ?>" method="post">

<table border="2" align="center">

<tr>
<td>NAME</td><td><input type="text" name="name" value="<?php echo $query->name; ?>" /></td></tr>
<tr><td>AGE</td><td><input type="text" name="age" value="<?php echo $query->age; ?>" /></td></tr>
<tr><td>GENDER</td><td><input type="text" name="gender" value="<?php echo $query->gender; ?>"/></td></tr>
<tr><td>ADDRESS</td><td><input type="text" name="address" value="<?php echo $query->address; ?>"/></td></tr>
<td colspan="2" align="center"><input type="submit" name="submit" value="Update" /></td>

</table>
</form>
</body>
</html>

Thursday, February 3, 2011

upload file

upload file Controller

<?php
class Upload extends Controller {

    function Upload()
    {
        parent::Controller();
        $this->load->helper(array('form', 'url'));
    }

    function index()
    {
        $this->load->view('upload_form', array('error' => ' ' ));
    }

    function do_upload()
    {
        $config['upload_path'] = "c:\upload files";
        $config['allowed_types'] = 'gif|jpg|png|pdf';
        $config['max_size']    = '1000';
        $config['max_width'] = '1024';
        $config['max_height'] = '768';

        $this->load->library('upload', $config);

        if ( ! $this->upload->do_upload())
        {
            $error = array('error' => $this->upload->display_errors());

            $this->load->view('upload_form', $error);
        }
        else
        {
            $data = array('upload_data' => $this->upload->data());

            $this->load->view('upload_success', $data);
        }
    }

}
?>

upload file view( upload_form)

<html>
<head>
<title>Upload Form</title>
</head>
<body>

<?php echo $error;?>

<?php echo form_open_multipart('upload/do_upload');?>

<input type="file" name="userfile" size="20" />

<br /><br />

<input type="submit" value="upload" />

</form>

</body>
</html>

upload file view(upload_success)

<html>
<head>
<title>Upload Form</title>
</head>
<body>

<h3>Your file was successfully uploaded!</h3>

<?php redirect('upload');?>
</body>
</html>

Thursday, January 27, 2011

Pagination

Pagination Controller

<?php
class Pagination extends Controller {
function Pagination() {
    parent::Controller();
    $this->load->helper('url');
    $this->load->database();
}

function index() {
    // load pagination class
    $this->load->library('pagination');
    $config['base_url'] = base_url().'pagination/index/';
    $config['total_rows'] = $this->db->count_all('user');
    $config['per_page'] = '3';
    $config['full_tag_open'] = '<p>';
    $config['full_tag_close'] = '</p>';

    $this->pagination->initialize($config);

    //load the model and get results
    $this->load->model('pagination_model');
    $data['results'] = $this->pagination_model->get_users($config['per_page'],$this->uri->segment(3));

    // load the HTML Table Class
    $this->load->library('table');
    $this->table->set_heading('ID', 'Name', 'Age', 'Gender','Address');

    // load the view
    $this->load->view('pagination', $data);
}
}

Pagination Model

<?php
class Pagination_model extends Model {
function Pagination_model(){
    parent::Model();
}

function get_users($num, $offset)
/*$num specifies the number of results to retrieve from the database and
    $offset specifies where it should start retrieving results from.*/
    {
    $query = $this->db->get('user', $num, $offset);
    return $query;
}
}

Pagination View

<html>
<head>
<title>CodeIgniter Pagination </title>
</head>
<body>
<h1>Welcome To Codeigniter</h1>
<?php echo $this->table->generate($results); ?>
<?php echo $this->pagination->create_links(); ?>
</body>
</html>

inser values in database

inser values in database

Controller

<?php
class Form_controller extends Controller
{

function Form_controller()
   {
    parent::Controller();
    $this->load->model('form_model');
    $this->load->helper(array('form','url'));

   }
   function index()
   {
    $data=array();
// var_dump($_POST);

        if( $this->input->post("submit") == 'Submit')
        {

            $name = $this->input->post('name');
            $age = $this->input->post ('age');
            $gender = $this->input->post('gender');
            $address = $this->input->post('address');
            $this->form_model->insertit($name,$age,$gender,$address);

        }

   //$data['mywords'] = 'hello world';

   $this->load->view('form',$data);
   }

}

Model

<?php
class Form_model extends Model
{
function Form_model()
{
    parent::Model();

}

function insertit($name,$age,$address,$gender){

          $data = array(

                'name'             => $name,
                'age'          => $age,
                'gender'          => $gender,
                'address'       => $address);
          $this->db->insert('user', $data );

}



}
?>

View
<html>
<body>
<form action="<?php echo base_url() ?>form_controller" method="post">
<table align="center" border="1">
<tr><td>NAME:</td><td><input type="text" name="name" id="name" /></td></tr>
<tr><td>AGE:</td><td><input type="text" name="age" id="age" /></td></tr>
<tr><td>GENDER:</td><td><input type="text" name="gender" id="gender" /></td></tr>
<tr><td>ADDRESS:</td><td><input type="text" name="address" id="address" /></td></tr>
<tr><td colspan="2" align="center"><input type="submit" name="submit" value="Submit" /></td></tr>
</table>
</form>
</body>
</html>

Add a check box and selected checkbox deleted and search box

Add checkbox controller

<?php
class Add_users extends Controller
{
   function Add_users()
   {
     parent::Controller();
    $this->load->database();
    $this->load->model('add_users_model');
    $this->load->helper('url');
   }
    function index()
    {
    $data=array();
    $submit=$this->input->post('submit');
    if($submit=='Add')
    {
    $name=$this->input->post('name');
    $this->add_users_model->add_values($name);

    }
    $submit=$this->input->post('submit');

    if($submit=='Delete'){

    $name=$_POST;

    foreach($name as $value){

    $this->add_users_model->delete_values($value);

    }
    }
    $res=$this->add_users_model->getusers('user');
    $data['query']=$res;

    $submit=$this->input->post('submit');
       if($submit=='Submit')
    //if(isset($_POST['submit'])=='Submit')
    {
    $name=$this->input->post('search');

    $data['query'] = $this->add_users_model->search_values($name);
    }

    $this->load->view('add_users',$data);
    }



}

add_model.php

<?php
class Add_model extends Model
{
function Add_model()
   {
    parent::Model();
   }
   function insert_values($name)
    {
    $data = array('name'=>$name );

             $query=$this->db->insert('user', $data );
    }

    function getusers()
    {
    $query = $this->db->get('user');
    return $query->result();

    }
    function delete_values($name)
    {
    //$row=$this->db->delete('user', array('name' => $name));
    $this->db->where('name', $name);
      $this->db->delete('user');

    }
function search_values($name)
   {
     $this->db->select('*');
    $this->db->from('user');
    $this->db->like('name',$name);
    $query = $this->db->get();
    return $query->result();

   }

}
?>

add_view.php

<html>
<title>Add Users</title>
<body>
<form action="<?php echo base_url()?>add_users" method="post">
USERNAME:<input type="text" name="name" /><br>

<input type="submit" name="submit" value="Add" />
</form>
<form action="<?php echo base_url()?>add_users" method="post">
<h4>Search:<input type="text" name="search">
<input type="submit" name="submit" value="Submit"></h4>
</form>
<form action="<?php echo base_url()?>add_users" method="post">
<?php $i=1;
foreach($query as $value){?>
<input type="checkbox" name="check<?php echo $i; ?>" value="<?php echo $value->name; ?>"><?php echo $value->name; ?><br>
<?php $i++; } ?>

<input type="submit" name="submit" value="Delete" />
</form>
</body>
</html>

PHP