Saturday, July 7, 2012

SHOW TABLES FROM DATABASE

SHOW TABLES FROM DATABASE
When you use command line show tables list all table in the database.By using php we can laist the tables in your database .we have created number of tables in my_database for previous tutorials.Now we can list them all in fallowing manner.

<?php
$dbname = 'my_database';

if (!mysql_connect('localhost', 'root', '')) {
    echo 'Could not connect to mysql';
    exit;
}

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    echo "Table: {$row[0]}\n"."</br>";
}

mysql_free_result($result);
?>
 Result
Table: answer
Table: biodata
Table: comments
Table: computer_sale
Table: computer_sale_january
Table: current_users
Table: customer
Table: dept_division
Table: employee
Table: gallery
Table: image_data
Table: item
Table: login
Table: login4
Table: namelist
Table: orders
Table: person
Table: product
Table: question
Table: search
Table: training
Table: user_online
Table: users
Table: vote

Friday, July 6, 2012

Simple Image Gallery php

Simple Image Gallery php

First You need make 2 folders as in this example called "img_gal" and "img_big".img_big is inside the img_gal.Then insert big images to img_big folder and then by using image processing software you can re-size the images to appear as thumbnails and store in img_gal folder.Please remember to keep same name in each images in 2 folders.


 Create following php file to display the above image gallery.

<?php

$images = "img_gal/";
$big    = "img_big/";
$cols   = 3;

if ($handle = opendir($images)) {
   while (false !== ($file = readdir($handle))) {
       if ($file != "." && $file != ".." && $file != rtrim($big,"/")) {
           $files[] = $file;
       }
   }
   closedir($handle);
}

$colCtr = 0;

echo '<table width="100%" cellspacing="3"><tr>';

foreach($files as $file)
{
  if($colCtr %$cols == 0)
    echo '</tr><tr><td colspan=3><hr style="color:#c00;background-color:#c00;height:10px;border:none;" /></td></tr><tr>';
  echo '<td align="center"><a href="' . $images . $big . $file . '"><img src="' . $images . $file . '" /></a></td>';
  $colCtr++;
}

echo '</table>' . "\r\n";

?>

Add and Delete Users php

Add and Delete Users php
 There is an administrator in Database management system with edit delete privileges  of data base content. and also could manage the database users by creating and deleting users.For this tutorial ,I have created login4 table in my database.
 --
-- Table structure for table `login4`
--

CREATE TABLE IF NOT EXISTS `login4` (
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `username` varchar(25) NOT NULL DEFAULT '',
  `password` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `login4`
--

INSERT INTO `login4` (`date`, `username`, `password`) VALUES
('2012-07-06 01:12:45', ' Glenn', 'glen123'),
('2012-07-06 01:25:21', 'Angelina ', 'ang123');

Then created admin.php as follows


Admin Area
Username:
Password:

Delete Users

Username:Delete
Glenn
Angelina

<?php

   $connection = mysql_connect("localhost","root","");
    if(!$connection){
    echo "Couldn't make a connection!!!";
    exit;
    }
    $db = mysql_select_db("my_database",$connection);
    if(!$db) {
    echo "The database disapeared!";
    mysql_close($connection);
    exit;
    }
?>
<html>
<head>
<title>Login Admin Area</title>
</head>
<body>
<p align="center"><big><big>Admin Area</big></big></p>
<table bgcolor=#142984 align="center">
<form method="POST" action="AdminAdd.php">
<input type="hidden" name="react" value="add_user">

<tr bgcolor=#dde2f7><td>Username: </td><td><input type="text" name="user" size="12"></td></tr>

<tr bgcolor=#dde2f7><td>Password: </td><td><input type="text" name="pass" size="12"></td></tr>
<tr bgcolor=#dde2f7><td colspan=2><input type="submit" value="Add User"></td></tr>

</form>
</table>
<h4><center>Delete Users</center></h4>
<form method="POST" action="AdminDel.php">

<input type="hidden" name="react" value="delete_user">
<center><table bgcolor=#142984><tr bgcolor=#dde2f7><td>Username:</td><td>Delete</td></tr>

<?php

 $query = "SELECT username FROM login4 " or die("Cannot Select");
   $result = mysql_query($query, $connection);

   $no= mysql_num_rows($result);
     if($no) {

      
       // we have at least one user, so show all users as options in select form
       while($row = mysql_fetch_array($result))
       {
          echo "<tr bgcolor=#dde2f7><td>".$row[0]."</td><td><a href='AdminDel.php?id=$row[0]'><img src='user.jpg'></a></td></tr>";
       }
     } else {
       echo "<tr bgcolor=#dde2f7><td colspan=2>No users created yet</td></tr></table></center>";
     }
?>

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

AdminAdd.php:-which carries data into database

<?php

  $username=$_POST['user'];

  $password=$_POST['pass'];

  $connection = mysql_connect("localhost","root","");
    if(!$connection){
    echo "Couldn't make a connection!!!";
    exit;
    }
    $db = mysql_select_db("my_database",$connection);
    if(!$db) {
    echo "The database disappeared!";
    mysql_close($connection);
    exit;
    }

  if($username==""||$password=="")

   {

      echo "ENTER A USERNAME and PASSWORD for that go to page Admin.php";

       exit;

     }

   else

    {

       $sql="INSERT INTO login4 VALUES(SYSDATE(),'$username','$password')";

       $result=mysql_query($sql) or die("Could not Execute");

       if($result) { echo ("New user"." ".$username." "."Created");

     }}

  ?>

AdminDel.php:-to delete records from database
<?php


 $id=$_GET['id'];

  if($id=="")

  {

    echo "Please go to the first page, AdminIntf.php for Fun";

    exit;

   }



   $connection = mysql_connect("localhost","root","");
    if(!$connection){
    echo "Couldn't make a connection!!!";
    exit;
    }
    $db = mysql_select_db("my_database",$connection);
    if(!$db) {
    echo "The database disappeared!";
    mysql_close($connection);
    exit;
    }

  $sql= "DELETE FROM login4 where username='$id'";

 $result=mysql_query($sql) or die("Could not Execute");

  if($result)

    {

      echo("The user"." ".$id." "."Successfully Deleted");

    }
?> 

Tuesday, July 3, 2012

Storing user information

Storing user information
Storing user information we can use following functions
  • Cookie
  • session
  • $_POST[](If browser does not support cookies.)
Cookie :-By using cookies we can pass user information from page to page.with browser page back it does not expires until expiration time is passed.
Ex:-First create html form to pass user information to php file

First Name:
Last Name:
<html>
<body>
<table>
<form action="c1.php" method="post">
<tr><td>First Name: </td> 
<td><input type="text" name="Fname" /></td></tr>
<tr><td>Last Name: </td> 
<td><input type="text" name="Lname" /></td></tr>
<tr><td colspan=2><input type="submit" /></td></tr>
</form>
</table>
</body>
</html>

Then we will be passing data into 2 php file called c1.php&c2.php

 c1.php
<?php
$Fname=$_POST['Fname'];
$Lname=$_POST['Lname'];
setcookie("user", "$Fname $Lname", time()+3600);
echo "welcome"." ".$_COOKIE["user"];
?>
</br><a href=" c2.php">NEXT</a>
Result(input First Name=Angelina,Last name=Jolie)
welcome Angelina Jolie
NEXT
 c2.php
<?php
echo "welcome Again"." ".$_COOKIE["user"];
?>
welcome Again Angelina Jolie

Session:-By using session also we can pass user information from page to page until it unset or destroy,but with browser back pages it expires.
Ex:-You can use above HTML file to input user information.


First Name:
Last Age:





<html>
<body>
<table>
<form action="s1.php" method="post">
<tr><td>First Name: </td> 
<td><input type="text" name="Fname" /></td></tr>
<tr><td>Last Age: </td> 
<td><input type="text" name="Lname" /></td></tr>
<tr><td colspan=2><tr>
</form>
</table>
</body>
</html>

s1.php
<?php
session_start();
$Fname=$_POST['Fname'];
$Lname=$_POST['Lname'];
$_SESSION['First_Name']=$Fname;
$_SESSION['Last_Name']=$Lname;
?>

<html>
<body>

<?php

echo "Welcome"." ". $_SESSION['First_Name']." "
.$_SESSION['Last_Name'];

?>
</br><a href=" s2.php">NEXT</a>
</body>
</html> 
Result
Welcome Angelina Jolie
NEXT
s2.php
<?php
session_start();
?>
<html>
<body>

<?php

echo "Welcome Again"." ". $_SESSION['First_Name']." "
.$_SESSION['Last_Name'];
session_destroy();
?>
</br><a href=" s3.php">NEXT</a>
</body>
</html> 
Welcome Again Angelina Jolie
NEXT

In s2.php session has been destroyed.So s3.php displays only Welcome Again.

<?php
session_start();
?>
<html>
<body>

<?php

echo "Welome Again"." ". $_SESSION['First_Name']." ".$_SESSION['Last_Name'];

?>

</body>
</html>
Welcome Again

$_POST[]:-You are now familiar with $_POST with previous post we used $_POST repeatedly.we can use $_POST when browser does not support cookies.But with $_POST we can pass user information only into one php file otherwise  we have to submit it repeatedly.
Ex:-


First Name:
Last Age:


<html>
<body>
<table>
<form action="p1.php" method="post">
<tr><td>First Name: </td> 
<td><input type="text" name="Fname" /></td></tr>
<tr><td>Last Name: </td> 
<td><input type="text" name="Lname" /></td></tr>
<tr><td colspan=2><td></tr>
</form>
</table>
</body>
</html>

p1.php can carry the information but p2.php no information without re submitting information.

<?php
$Fname=$_POST['Fname'];
$Lname=$_POST['Lname'];

echo "welcome"." ".$Fname." ".$Lname;
?>
</br><a href=" p2.php">NEXT</a>
welcome Angelina Jolie
NEXT
<?php
$Fname=$_POST['Fname'];
$Lname=$_POST['Lname'];
echo "welcome"." ".$Fname." ".$Lname;
?>
welcome

Monday, July 2, 2012

Mysql Limit

Mysql Limit

Previous post we were discussed pagination to limit the number of records display in mysql table.By adding limit to the query also we can limit the number of row display in a table.





First Name Last Name
Scarlett Johansson
Clooney Anderson
Kate Hudson
Scarlett Clooney
Kate Eva


This example too,I have taken same table called "namelist" in my_database

<?php
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'my_database';
$dbConn = mysql_connect ($dbHost,$dbUser, $dbPass) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($dbName) or die('Cannot select database. ' . mysql_error());
?>
<html>
<head>
<title>Display</title>
</head>
<center><form name="form" action="" method="post">
<select name="limit" onchange="this.form.submit()">
<option readonly="readonly">Number</option>
<option>5</option>
<option>10</option>
<option>15</option>
<option>20</option>
</select>
</form></center>
<table align="center" bgcolor=#ffffff>
<tr bgcolor=#f2e81d>
<th> First Name</th>
<th>Last Name </th>
</tr>
<?php
$limit=$_POST['limit'];
$query = "SELECT * FROM namelist LIMIT 0, $limit";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)){
?>
<tr bgcolor=#f0eda4><td><?php echo $row["FirstName"];?></td>
<td><?php echo $row["LastName"];?></td>
</tr>
<?php }?>
</table>
</body>
</html>

Pagination php

Pagination php
When mysql database is populated with large number of data you have to navigate from one page to other to view your data.For this tutorial, I have created table called "namelist" in my_database and populated 20 rows with data and broken down 5 rows into one page view.
bellow codes are to make table

--
-- Table structure for table `namelist`
--

CREATE TABLE IF NOT EXISTS `namelist` (
  `id` int(45) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(15) DEFAULT NULL,
  `LastName` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

--
-- Dumping data for table `namelist`
--

INSERT INTO `namelist` (`id`, `FirstName`, `LastName`) VALUES
(1, 'Scarlett', 'Johansson'),
(2, 'Clooney', 'Anderson'),
(3, 'Kate ', 'Hudson'),
(4, 'Scarlett', 'Clooney'),
(5, 'Kate', 'Eva'),
(6, 'Angelina ', 'Jolie'),
(7, 'Jennifer ', 'Aniston'),
(8, 'Reese', 'Witherspoon'),
(9, 'Megan ', 'Fox'),
(10, 'Nicole ', 'Kidman'),
(11, 'Penelope ', 'Cruz'),
(12, 'Alyssa ', 'Milano'),
(13, 'Keira ', 'Knightley'),
(14, 'Cameron ', 'Diaz'),
(15, 'Jean ', 'Harlow'),
(16, 'Claudette ', 'Colbert'),
(17, 'Barbara ', 'Stanwyck'),
(18, ' Lucille ', 'Ball'),
(19, 'Vivien ', 'Leigh'),
(20, 'Jane ', 'Wyman');

ScarlettJohansson
ClooneyAnderson
Kate Hudson
ScarlettClooney
KateEva


bellow codes display the above page

<?php
    mysql_connect("localhost", "root", "") or die(mysql_error());

 mysql_select_db("my_database") or die(mysql_error());
  
    $tbl_name="namelist";      
    $adjacents = 3;
  
  
    $query = "SELECT COUNT(*) as num FROM $tbl_name";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];
  
  
    $targetpage = "pagi2.php";   
    $limit = 5;                               
    $page = $_GET['page'];
    if($page)
        $start = ($page - 1) * $limit;           
    else
        $start = 0;                              
  
  
    $sql = "SELECT column_name FROM $tbl_name LIMIT $start, $limit";
    $result = mysql_query($sql);
  
  
    if ($page == 0) $page = 1;                  
    $prev = $page - 1;                          
    $next = $page + 1;                          
    $lastpage = ceil($total_pages/$limit);      
    $lpm1 = $lastpage - 1;                      
  
  
    $pagination = "";
    if($lastpage > 1)
    {  
        $pagination .= "<div class=\"pagination\">";
        //previous button
        if ($page > 1)
            $pagination.= "<a href=\"$targetpage?page=$prev\"><img src='images/barrow.jpg'> previous</a>";
        else
            $pagination.= "<span class=\"disabled\"><img src='images/barrow.jpg'> previous</span>";  
      
        //pages  
        if ($lastpage < 7 + ($adjacents * 2))    //not enough pages to bother breaking it up
        {  
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                  
            }
        }
        elseif($lastpage > 5 + ($adjacents * 2))    //enough pages to hide some
        {
            //close to beginning; only hide later pages
            if($page < 1 + ($adjacents * 2))      
            {
                for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                  
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";      
            }
            //in middle; hide some front and some back
            elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
            {
                $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                  
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";      
            }
            //close to end; only hide early pages
            else
            {
                $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                  
                }
            }
        }
      
  
        if ($page < $counter - 1)
            $pagination.= "<a href=\"$targetpage?page=$next\">next <img src='images/parrow.jpg'> </a>";
        else
            $pagination.= "<span class=\"disabled\">next <img src='images/parrow.jpg'></span>";
        $pagination.= "</div>\n";      
    }
?>

    <?php
    $sql = "SELECT *FROM $tbl_name LIMIT $start, $limit";
    $result = mysql_query($sql);
        while($row = mysql_fetch_array($result))
        {
  
        echo $row['FirstName']."".$row['LastName']."</br>";
  
        }
    ?>

<?php echo $pagination ?>

different colors in alternative rows mysql

Different colors in alternative rows mysql


You may have seen different colors in mysql alternative rows in table. This tutorial I will display you the alternative colors in mysql rows and mouse over color change in table rows.Alternative color you can get using php but for mouse over effect you need to use java scripts.Create following examples and see what is happening with those codes.

First Name Last Name Age
Scarlett Johansson 25
Clooney Anderson 25
Kate Hudson 35
Scarlett Clooney 25
Kate Eva 34

For this tutorial I have used table "biodata"
<html>
<head>
<title>table row colors</title>
<script type="text/javascript">

function hiLiteRows(){
var x = document.getElementsByTagName('tr');
for (var i=0;i<x.length;i++)
{
x[i].onmouseover = function () {this.origColor=this.style.backgroundColor;
this.style.backgroundColor='#FFFFFF';
}
x[i].onmouseout = function () {this.style.backgroundColor=this.origColor;}
}
}

window.onload=hiLiteRows;

</script>

</head>
<body>
<?php
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'my_database';

$dbConn = mysql_connect ($dbHost,$dbUser, $dbPass) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($dbName) or die('Cannot select database. ' . mysql_error());

?>

<html>
<head>
<title>Display</title>
</head>
<table align="center" bgcolor="#ffffff">
<tr bgcolor="#bfbd93">
<th> First Name</th>
<th>Last Name </th>
<th> Age </th>
</tr>

<?php
$color1 = "#f6f0e0";
$color2 = "#bfbd93";

$query = "SELECT * FROM biodata";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)){
$row_color = ($row_count % 2)? $color1 : $color2;
$row_count++;

?>
<tr bgcolor="<?php echo $row_color;?>">
<td><?php echo $row["FirstName"];?></td>
<td><?php echo $row["LastName"];?></td>
<td><?php echo $row["Age"];?></td>
</tr>
<?php }?>
</table>
</body>
</html>