PHP - Working with Forms, Working with Database

Working with Forms
Working with Database

Working with Forms

Creating Simple HTML form

HTML Forms

What makes the Web so interesting and useful is its ability to disseminate information as well as collect it, primarily through an HTML-based form. These forms are used to encourage site feedback, facilitate forum conversations, collect mailing addresses for online orders, and much more. But coding the HTML form is only part of what's required to effectively accept user input; a server-side component must be ready to process the input.

Note:

This is an simplified example to educate you how to use PHP to process HTML form information. This chapter does not describe forms in details or in simple words this isn't a HTML form tutorial. In this chapter we will just tell you that how to obtain and process the information that your site user has entered in the form. For HTML tutorial, please visit out XHTML/HTML tutorial at http://education.mkdtutorials.com.

Creating a Simple HTML Form

In this example, we will create a simple form, which will accept user input and when user click on submit button sends the information to the server side PHP script to process the information.

This example is divided in two parts:

1. Client side HTML form [simpleform.php]
2. Server side form processing script [simpleform.php]

Source code simpleform.php

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<title>PHP Tutorial: Working with Forms</title>
</head>

<body><form  name="simple" action="simpleform.php" method="get">
<table width="200" border="1" cellpadding="1">

  <tr>
    <th scope="row">Name</th>
    <td><input name="name" type="text" id="name" accesskey="n" /></td>
  </tr>
  <tr>
    <th valign="top" scope="row">Address</th>
    <td><textarea name="address" cols="40" 
rows="5" id="address" style="background-color:#34FFDD"></textarea></td>
  </tr>
  <tr>
    <th scope="row">
      <input type="reset" name="Reset" value="Reset" 
style="background-color:#0066CC; border: 
thin dotted solid #003333; cursor:wait; size:16; color:#FFFFFF" /></th>

    <td><input name="Submit" type="submit" id="Submit" 
style="background-color:#0066CC; border: thin dotted solid #003333;  
cursor:hand; width:150px; size:16; color:#FFFFFF" value="Submit" /></td>
  </tr>

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

The example HTML page above contains two input fields and a submit button. When the user fills in this form and click on the submit button, the form data is sent to the "simpleform.php" file. The above page should look like the figure given below:

The server side script:

source of simpleform.php

<html >
<head>
<title>Hello <?php echo $_GET["name"];?> </title>
</head>

<body>
<hr />
<center><h3>Hello  User </h3></center>
You have entered the following information:
<p>Your Name <b> <?php echo $_GET["name"];?></b>
<br />
Your Address <address><?php echo $_GET["address"];?></address>
</p>
</body>
</html>

Now open the simpleform.php in browser, fill the form and press submit the form to view the output. Your output should be like one given below:

As you can see, we have used the following syntax to retrieve the value that we have entered in the from.

There are two common methods for passing data from one script to another:

1. GET and 2. POST.

Syntax to access value of a form field:

if the method is GET:

$_GET["form_field_name"];
if the method is POST $_POST
["form_field_name"];

In the above example GET method is used to make the example verbose. You can use any method as per your choice and requirement. But, GET method is not recommended for that contains sensitive information such as Login form, form that accepts credit card information, etc and form that accepts lengthy data.

Although GET is the default, you'll typically want to use POST because it's capable of handling considerably more data, an important behavior when you're using forms to insert and modify large blocks of text. If you use POST, any posted data sent to a PHP script must be referenced using the $_POST syntax.

For example, suppose the form contains a text-field value named email that looks like this:

Once this form is submitted, you can reference that text-field value like so:

$_POST['email']

Of course, for sake of convenience, you can first assign this value to another variable, like so:

$email = $_POST['email'];

But following the best practice of never presuming user input will be safe, you should filter it through one of the several functions capable of sanitizing data, such as htmlentities(), like so:

$email = htmlentities($_POST['email']);

The htmlentities() function converts strings consisting of characters capable of maliciously modifying an HTML page should the user-submitted data be later published to a Web site, such as a Web forum.

More Complex Form

In this example we will create a self submitting form, which accept user input process the information and display the output, and all in one page.

Step-by-Step description:

<body>
<?php
$name=$_POST["name"];
$email=$_POST["email"];
$mobile=$_POST["mobile"];
$addr=$_POST["address"];
$own=$_POST["own"];
$state=$_POST["state"];
$sex=$_POST["sex"];
$feedback=$_POST["feedback"];
$owns=array();

if (!isset($_POST['submit'])) { //if the form is not submitted we will display the form itself. Otherwise display the contents of the form. ?>

In the section we have declared variables to store the values of form fields. In the last line of the above declaration we have used isset() function to check if the form has been submitted. If user has submitted the form, then it will jump directly to else part.

The else part

<?php
}
else
{
?>
<h3> Dear <?php echo $name;?>, thank 
you very much for your valuable Feedback</h3>
<hr />
You have  entered following information :<br />

<?php
 echo "<pre>";
 echo  "Name <b>".$name."</b><br />";
 echo  "Contact No".$mobile."<br />";?>
"EMail Address " <a href="mailto:
<?php echo $email;?>"> <?php echo $email;?></a>
Your Have <b><?php  foreach ($own as $x=>$val){
#if (!$x=="none")
  echo "<br>".$val;

 }


?>
</b>
<?php  echo  "Your address is  ".$address ."     ".$state;
 echo "<br />  And You are a  $sex ";
 ?>

<b>MKDTutorials Education thanks you for 
your valuable suggestion and feedback. </b>
<?php
}
?>

In the else part we are just printing the value entered by user. As you can see above, this part will only execute if the has submitted the form.

The complete Code...

Click here to view the code [Link the feedback.php.txt]

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type"
 content="text/html; charset=windows-1252" />
<title>MKDTutorials EDUCATION FEEDBACK</title>
<style type="text/css">
<!--
body {
 background-color: #0099CC;
 margin-left: 0px;
 margin-top: 0px;
}
-->
.red{
color:#FF0000;
font-style:italic;
}
.fields
{
background-color:#34FFDD;
color:#333366
}
.feedback{
background-color:#006699;
color:#FFFFFF;
font-weight:bold;
}
</style>


</head>

<body>
<?php
$name=$_POST["name"];
$email=$_POST["email"];
$mobile=$_POST["mobile"];
$addr=$_POST["address"];
$own=$_POST["own"];
$state=$_POST["state"];
$sex=$_POST["sex"];
$feedback=$_POST["feedback"];
$owns=array();

if (!isset($_POST['submit'])) {
?>

<hr />
<h3> Welcome to MKDTutorials Education Feedback Page </h3>
<hr />

<span class="red"><b>* </b> fields are mandatory </span>
<form action="<?php  echo $PHP_SELF;?>" method="POST"
 enctype="application/x-www-form-urlencoded"
 name="feedback" target="_self" dir="ltr" lang="en">
<table width="70%" border="0" cellspacing="0" cellpadding="1">
  <tr>
    <td  width="30%">Name</td>
    <td width="70%" colspan="2">
<input name="name" type="text" size="30" />
 <span class="red"><b>* </b> </span> </td>
    </tr>
  <tr>
    <td valign="top">email ID </td>
    <td colspan="2"><input name="email"
 type="text" size="35" maxlength="100" /> 
 <span class="red"><b>* </b> </span></td>
  </tr>
  <tr>
    <td valign="top">Mobile no </td>
    <td colspan="2"><input name="mobile" type="text"
 class="fields" size="25" maxlength="13" /></td>
  </tr>
  <tr>
    <td valign="top">Address </td>
    <td colspan="2"><textarea name="address" 
cols="40" rows="5" class="fields"></textarea></td>
    </tr>
 <tr>
    <td valign="top">State</td>
    <td colspan="2"><select name="state"  >
  <option>Please Select  State</option>
  <option value="A.P.">A.P.</option>
  <option value="A.S.">A.S.</option>
  <option value="B.R.">B.R.</option>
  <option value="C.G.">C.G.</option>
      <option value="U.P.">U.P.</option>
      <option value="D.L.">Delhi</option>
      <option value="Other">Other</option>
    </select></td>
    </tr>
  <tr>
    <td>Gender </td>
    <td><label>
      <input name="sex" type="radio" value="Male"  accesskey="M" />
      Male</label></td>
    <td><label>
      <input name="sex" type="radio" value="Female"  accesskey="F" />
      Female</label></td>
  </tr>
  <tr>
    <td>Which of Following do you own ?</td>
    <td colspan="2"><table width="100%" border="0"
 cellspacing="0" cellpadding="1">
      <tr>
        <td><input type="checkbox" name="own[]"  value="car"/>Car</td>
        <td> <input type="checkbox" name="own[]"  
value="computer"/>Computer </td>
      </tr>
      <tr>
        <td><input type="checkbox" name="own[]"  value="mobile"/>Mobile</td>
        <td><input type="checkbox" name="own[]"  value="none"/>None</td>
      </tr>
    </table></td>
    </tr>
  <tr>
    <td> </td>
    <td colspan="2"> </td>
  </tr>
  <tr>
    <td valign="top">Feedback 
 <span class="red"><b>* </b> </span></td>
    <td colspan="2" valign="top">
<textarea name="feedback" cols="45" rows="10" class="feedback">
</textarea> </td>
  </tr>
  <tr>
    <td><input name="reset" type="reset" 
value="Clear Form" /></td>
    <td colspan="2"><input name="submit"
 type="submit" value="Submit Form" /></td>
    </tr>
</table>

</form>

<?php
}
else
{
?>
<h3> Dear <?php echo $name;?>, thank you 
very much for your valuable Feedback</h3>
<hr />
You have  entered following information :<br />

<?php
 echo "<pre>";
 echo  "Name <b>".$name."</b><br />";
 echo  "Contact No".$mobile."<br />";?>
"EMail Address " <a href="mailto:<?php echo $email;?>">
 <?php echo $email;?></a>
Your Have <b><?php  foreach ($own as $x=>$val){
#if (!$x=="none")
  echo "<br>".$val;

 }


?>
</b>
<?php  echo  "Your address is  ".$addr ."     ".$state;
 echo "<br />  And You are a  $sex ";
 ?>

<b>MKDTutorials Education thanks you for 
your valuable suggestion and feedback. </b>
<?php
}
?>
<br />
<hr />

</body>
</html>

save the file as feedback.php and open the script to view the output. The page should look like the one given below:

When User Fills the form and submit it, user will get the following screen.

Using validation with Forms

In the example, we created a form that accepts user input, but nothing stops the user to enter incorrect value in the form fields. User may enter invalid email id, e.g. email that does not contains @/. etc. In such a scenario the information coming to server is incorrect and incomplete.

To keep the information correct, validations are used. Validation assures that the we have already filtered the unwanted values, and we can now assume that, the information available on the server side is accurate. Validation is necessary to insure the quality, accuracy and desirability of data. With validation we can force the user to enter valid value in the respective form fields so that user does not attempt to submit incomplete form.

There are two types of validation:

1. Server side validation this type of validation uses server-side technologies such as ASP, JSP or PHP and validation is performed on the server.

2. Client side this type of validation runs on client browser and user client side technologies such as Javascript. This type of validation entirely runs on client browser hence reduces the processing load on the server and increases server response time.

Note: This chapter assumes that you've a sound understanding of Javascript. If you to learn Javascript, please refer to our Javascript tutorial at http://education.mkdtutorials.com.

Improved Feedback form with Client side validation

Unlike the previous example which was divided in 2 parts, this example is divided in three parts:

1. HTML Form
2. Client Side JavaScript
3. PHP script to process the information

Part 3, the PHP script is same as the previous example. Part 1 the HTML is same with minor changes in the form declaration. Part 2, the Client side JavaScript force the user to enter data in all mandatory form fields.

The JavaScript

<?php
if (!isset($_POST['submit'])) {
?>
<script language = "Javascript">


function emailcheck(email) {

  var at="@"
  var dot="."
  var lat=email.indexOf(at)
  var lstr=email.length
  var ldot=email.indexOf(dot)
  if (email.indexOf(at)==-1){
     alert("Invalid E-mail ID")
     return false
  }

  if (email.indexOf(at)==-1 |
| email.indexOf(at)==0 || email.indexOf(at)==lstr){
     alert("Invalid E-mail ID")
     return false
  }

  if (email.indexOf(dot)==-1 || 
email.indexOf(dot)==0 || email.indexOf(dot)==lstr){
      alert("Invalid E-mail ID")
      return false
  }

   if (email.indexOf(at,(lat+1))!=-1){
      alert("Invalid E-mail ID")
      return false
   }

   if (email.substring(lat-1,lat)==dot || email.substring(lat+1,lat+2)==dot){
      alert("Invalid E-mail ID")
      return false
   }

   if (email.indexOf(dot,(lat+2))==-1){
      alert("Invalid E-mail ID")
      return false
   }

   if (email.indexOf(" ")!=-1){
      alert("Invalid E-mail ID")
      return false
   }

    return true
 }
function sexcheck()
{
var flag=0

var value=document.feedback.sex
//alert(value.length)
 for(i=0;i<value.length;i++)
 {
  if(value[i].checked ==true)
   flag=1;
 }
 if(flag==1)
  return true
 else
  return false
}
function validateFeedbackForm(){
 var name=document.feedback.name
 var sex=document.feedback.sex
 var emailID=document.feedback.email
 var feedback=document.feedback.feedbk

 if ((name.value==null)||(name.value==""))
 {
  alert("Please Enter Your Name")
  name.focus()
  return false
 }

 if ((emailID.value==null)||(emailID.value=="")){
  alert("Please Enter your Email ID")
  emailID.focus()
  return false
 }
 if (emailcheck(emailID.value)==false){
  emailID.value=""
  emailID.focus()
  return false
 }

 if(sexcheck()==false)
 {
  alert("Please Select your Gender")
  return false
 }
 if((feedback.value.length<10)|| (feedback.value==""))
 {
  alert("Your Feedback should contain atleast 10 character")
  return false
 }
 return true
 }
</script>
<?php
}
?>

The HTML:

Only the line below has been modified to use the JavaScript:

<form action="<?php  echo $PHP_SELF;?>" method="POST" 
onSubmit="return validateFeedbackForm();"   name="feedback" target="_self">

Rest, everything is same.

Assignment

1. Create an HTML form form.php, which contains 4 fields:
Name (text box),
Mobile Number(text box)
Gender (radio button)
Address (text Area)

On submitting the form, data goes to form.php which process the data and display it in HTML table, like one given below:

NAME	Mobile Number	Gender	Address
[form data]	[form data]	[form data]	[form data]

2. in the above HTML form (form.php) use JavaScript validation to check no form field is empty and all fields contains valid values

Working with Database

Introduction

What is Database?

A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model. The model in most common use today is the relational model. Other models such as the hierarchical

A database is an organized collection of data that is useful to us. The data inside in a database can be modified, deleted or new data can be added.

They are stored in the form of records as shown in the table below:

The above table shows a single record. The columns-Associate_TID, Associate_name, Age ,Commission are the fields or attributes, while the row containing the corresponding the values-900678432, Smith, 28, 25000 is a single record. There can be a number of records in a table and number of tables in a database.

The tables in a database are related to each other through one/more attributes/fields. A Database is diagrammatically shown below. It consists of two tables-Associate and Asso_INFO. While Associate stores information regarding an Associate TID, dept and salary, the table Associate_INFO stores TID, name and date of join of the Associate. The common link between the tables is established by the field Asociate-TID.

Why use MySQL?

If you're looking for a free or inexpensive database management system, several are available from which to choose: MySQL, PostgreSQL, one of the free-but-unsupported engines from commercial vendors, and so forth.

When you compare MySQL with other database systems, think about what's most important to you: Performance, support, features (SQL conformance, extensions, and so forth), licensing conditions and restrictions, and price all are factors to take into account. Given these considerations, MySQL has many attractive features to offer:

. Speed:

MySQL is fast. The developers contend that MySQL is about the fastest database you can get.

. Ease of use:

MySQL is a high-performance but relatively simple database system and is much less complex to set up and administer than larger systems.

. Query language support.

MySQL understands SQL, the language of choice for all modern database systems.

. Capability

Many clients can connect to the server at the same time. Clients can use multiple databases simultaneously. You can access MySQL interactively using several interfaces that let you enter queries and view the results:

command-line clients, Web browsers, or X Window System clients. In addition, a variety of programming interfaces are available for languages such as C, Perl, Java, PHP, and Python.

. Connectivity and security:

MySQL is fully networked, and databases can be accessed from anywhere on the Internet, so you can share your data with anyone, anywhere.

. Portability:

MySQL runs on many varieties of UNIX, as well as on other non-UNIX systems, such as Windows and OS/2. MySQL runs on hardware from home PCs to high-end servers.

. Small size:

MySQL has a modest distribution size, especially compared to the huge disk space footprint of certain commercial database systems.

. Availability and cost MySQL is an Open Source project, freely available under the terms of the GNU General Public License (GPL). This means that MySQL is free for most in-house uses. (If you want to sell MySQL or services that require it, that is a different situation and you should contact MySQL AB.)

. Open distribution:

MySQL is easy to obtain; just use your Web browser. If you don't understand how something works or are curious about an algorithm, you can get the source code and poke around in it. If you don't like how something works, you can change it. If you think you've found a bug, report it; the developers listen.

. MySQL is a relational database management system:

A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility.

The SQL part of "MySQL" stands for "Structured Query Language. SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist.

Creating a Connection

Before you can access and work with data in a database, you must create a connection to the database.

In PHP, mysql_connect() function is used to connect to database . Syntax of mysql_connect:

$con=mysql_connect("localhost/ip", "user_name", "password");

MySQL localhost

If you've been around the internet a while, you'll know that IP addresses are used as identifiers for computers and web servers. In this example of a connection script, we assume that the MySQL service is running on the same machine as the script.

When the PHP script and MySQL are on the same machine, you can use localhost as the address you wish to connect to. localhost is a shortcut to just have the machine connect to itself. If your MySQL service is running at a separate location you will need to insert the IP address or URL in place of localhost. Please contact your web host for more details if localhost does not work.

Example:

Source code of mysql_connect.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>PHP Tutorial: Working with MySQL</TITLE>
</HEAD>

<BODY>
<hr>
<?php
$con=mysql_connect("localhost","root","") ;
if(!$con)
  die ("<b>Unable to Connect to MySQL ".mysql_error()."</b>");
else
 echo "<b>Connection Created Successfully.</b>";

mysql_close($con)or die(mysql_error());
?>
<hr>
</BODY>
</HTML>

The above example attempts to connect to MySQL database. If connection is established then print the success message like the one below

Otherwise displays the mysql error.

Retrieving Database and Table list

Now that you've successfully used PHP to make a connection to MySQL, it's time to familiarize yourself with some of the built-in MySQL-related functions. In this section, you use the following functions:

. mysql_list_dbs()- Used to list the databases on a MySQL server.

Syntax: resource mysql_list_dbs ( [resource link_identifier ] )

mysql_list_dbs() will return a result pointer containing the databases available from the current mysql daemon.

. mysql_num_rows()- Returns the number of rows in a result set.

Syntax:
int mysql_num_rows ( resource result )
mysql_num_rows() returns the number of rows in a result set.

. mysql_tablename()- Despite its name, can extract the name of a table or a database from a result.

Syntax:
string mysql_tablename ( resource result, int i )

Getting database list

<?php
//PHP Code to retrieve the list of databases in MySQL.

class dbUtils{
 private $list_db;
 private $total_db;
 private $list_of_dbs;

 public function  __construct(){

 }
 public function connect(){
  $con=mysql_connect("localhost","root","");
  if(!$con)
   return NULL;
  else
   return $con;

 }
 public function listDB()
 {
  $con=$this->connect();

  if(!$con)
    $this->list_db="<b>Unable t
o Connect to MySQL ".mysql_error()."</b>";
  else
  {
   #echo "<b>Connection Created Successfully.</b>";

   $total_db=mysql_list_dbs($con)or die(mysql_error());
   $i=0;
   $list_of_dbs="<ul type=\"disc\">";
   if($total_db)
   {
    for($i;$i<mysql_num_rows($total_db);$i++)
    {
     $db_list[$i]=mysql_tablename($total_db,$i);
     $list_of_dbs.="<li>$db_list[$i]</li>";
    }

   }
   $list_of_dbs.="</ul>";
   mysql_close($con)or die(mysql_error());
   return $list_of_dbs;
  }
 }
}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php
$dbutil=new dbUtils();
?>
<HTML>
<HEAD>
<TITLE>PHP Tutorial: Working with MySQL</TITLE>
</HEAD>

<BODY>
<hr>
<h3>List  of Databases on localhost</h3>
<hr>
<FIELDSET>
 <LEGEND>Databases</LEGEND>
 <?php
 echo $dbutil->listDB();
 ?>

</FIELDSET>
</BODY>
</HTML>

Description:
After connecting to database we have created a variable to hold the result of the mysql_list_dbs() function.

$total_db=mysql_list_dbs($con)or die(mysql_error());

After declaring all necessary variables begin a for loop. This loop will continue for as long as the value of $i is less than the number of rows in the $total_db result value:

for($i;$i<mysql_num_rows($total_db);$i++) {

Once you're within the for loop, get the name of the database reflected in the current row of the result:

$db_list[$i]=mysql_tablename($total_db,$i);
$list_of_dbs.="<li>$db_list[$i]</li>";

Close the for loop, the bulleted list, and your PHP block:

}
$db_list .= "</ul>";
?>

Save the above script and open the page in browser to view the output.

Output:

Output may vary from system to system depending upon how much you have played with your MySQL server, but it should look like the one below:

Getting Table list

To retrieve the list of tables we have just added 1 more for loop inside the main for loop. This for loop retrieve list of tables of each database and add it to the list.

<?php
//PHP Code to retrieve the list of databases in MySQL.


$con=mysql_connect("localhost","root","") ;
$list_db;
if(!$con)
  die ("<b>Unable to Connect to MySQL ".mysql_error()."</b>");
else
{
 #echo "<b>Connection Created Successfully.</b>";

 $total_db=mysql_list_dbs($con)or die(mysql_error());
 $i=0;
 $list_of_dbs="<ul type=\"disc\">";
 if($total_db)
 {
  //Main loop to retireve the l
ist of databases on  the MySQL server
  for($i;$i<mysql_num_rows($total_db);$i++)
  {
   $db_list[$i]=mysql_tablename($total_db,$i);
   $list_of_dbs.="<li>$db_list[$i]";
   $tables=mysql_list_tables($db_list[$i]);
   $list_of_tables="<ul>";
   $i1=0;
   if($tables)
   {
 //For Loop to retireve list of tables in current database
 for ($i1;$i1<mysql_num_rows($tables);$i1++)
    {
   $table_list[$i1]=mysql_tablename($tables,$i1);
   $list_of_tables.="<li>$table_list[$i1]</li>";
    }
    $list_of_tables.="</ul>";
    $list_of_dbs.="$list_of_tables </li>";
   }

  }

 }
 $list_of_dbs.="</ul>";
 mysql_close($con)or die(mysql_error());
}

?>

<!DOCTYPE HTML PUBLIC 
"-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>PHP Tutorial: Working with MySQL</TITLE>
</HEAD>

<BODY>
<hr>
<h3>List  of Databases on localhost</h3>
<hr>
<FIELDSET>
 <LEGEND>Databases</LEGEND>
 <?php
 echo $list_of_dbs;
 ?>

</FIELDSET>
</BODY>
</HTML>

Creating Databases and Tables

Creating Databases

PHP Script to create database

<?php
class dbUtils{
 private $flag;
 private $sql;//="create database ";
 private $msg;//="";*/
 #function createDB
 public function __construct(){
  $this->flag=0;
  $this->sql="";
  $this->msg="";
 }
 public function connect(){
  $con=@mysql_connect("localhost","root","");
  if(!$con)
   return NULL;
  else
   return $con;
 }
 function createDB($db_to_create)
 {
  $msg="";
  $flag=$this->flag;
  if((!$db_to_create)|| ($db_to_create==""))
   $flag=-1;
  if(isset($_POST["submit"])&& ($flag >=0)){
   $sql="create database ".$db_to_create;
   $con=$this->connect();
   if($con){
    $result=mysql_query($sql,$con);
    if($result)
 {
 $msg.="Database <b>$db_to_create</b> create successfully.";
     $flag=0;
     //return $msg;
    }
    elseif(!$result)
    {
$msg.="Unable to create database $db_to_create ";
     $msg.=mysql_error();
     $flag=-1;
     //return $msg;
    }
    if($flag<0)
 $msg.="\n<br>Unable to create database <b>
  $db_to_create</b>\n<br /> Please Enter a 
valid database name to create";
    mysql_close($con);
    //echo $msg;
    return $msg;

   } else{
   $msg="Unable to connect to database";
   return $msg;
   }
  }
 }
}
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script>
function validateForm()
{
 var db=document.form1.db_name
 if((db.value=="")||(db.value.length==0))
 {
 alert("Please enter Name of the database to create")
 db.focus()
 return false
 }

return true
}
</script>
<meta http-equiv="Content-Type" 
content="text/html; charset=iso-8859-1" />
<title>PHP Tutorial: Working with MySQL</title>
</head>

<body>

<form action="<?php echo $PHP_SELF;?>
" method="post" name="form1" onSubmit="return validateForm();">
  Enter database name to create 
<input type="text" name="db_name" maxlength="10" size="20">

  <br>
  <input name="submit"
 type="submit" value="Create Database">
</form>
<hr>
<?php
if(isset($_POST["submit"]))
{
?>
<div style="border:thin #FF0000 
solid; width:80%; height:auto">
<?php
$db=$_POST["db_name"];
$dbutil=new dbUtils();
echo $dbutil->createDB($db);
echo "</div>";
}
?>
</body>
</html>

How the above example works?

1. Create a variable and store the database name user has entered in the form.

$db_to_create=$_POST["db_name"];

2. Create an object of dbUtils class

$dbutil=new dbUtils();

3. Call the createDB function of dbUtils class and pass

$db_to_create $dbutil->createDB($db_to_create);

4. in the createDB function of dbUtils class, check if the database name is blank.

5. Create a variable to hold the query to issue, which will create the new database:

$sql = "CREATE database ".$db_to_create;

6. Add the connection information just as you have been doing:

$con = $this->connect();

7. Check connection is established with database or not

if($con){

7. Issue the query, using the mysql_query() function.

$result = mysqlquery($sql, $con);

8. Check if result is initialized and return the result.

if($result){

Note:

The concept of $PHP_SELF variable and isset() function
has been described in the previous chapter.

Output:

Creating Tables

In this example we are going to use a web based interface to create tables. The interface gives you a list of available databases, a Text field to enter name of table and a Text area to enter table structure in SQL format.

Our interface will look like the one below:

This example is a multi file PHP script and it is divided in to parts:

PHP script to retrieve list of database and print it in desired format

<?php
$list_of_dbs="";
function  getDBList($element_type, $param1)
{
$con=mysql_connect("localhost","root","") or die("Unable to Connect");
$total_db=mysql_list_dbs($con)or die(mysql_error());
 $i=0;

 if($total_db)
 {
  //Main loop to retireve the list of databases on  the MySQL server
  for($i;$i<mysql_num_rows($total_db);$i++)
  {
   $db_list[$i]= mysql_tablename($total_db,$i);
   $list_of_dbs.="<$element_type $param1=\"$db_list[$i]\"
>$db_list[$i]</$element>";
  }
 }
mysql_close($con) or die(mysql_error());
return $list_of_dbs;
}

?>

Main PHP script, this one is similar to previous example, except that we are issuing a Create Table command, instead of Create Database command.

<?php
require("listdb.php");
$db=$_POST["db_name"];
$command=$_POST["table_command"];
$table_name=$_POST["tbl_name"];
$con=mysql_connect("localhost","root","") or die();

$flag=0;
$sql="create table ";
$msg="";
if((!$db)|| ($db=="")||(strlen($command)
<=5)||($command=="")||(strlen
($table_name)<=0)||($table_name==""))
 $flag=-1;


if(isset($_POST["submit"])&& ($flag >=0)){

 $sql.=$table_name;
 $sql.=$command;

 if($con)
 {
  mysql_select_db($db, $con);
  #echo $sql;
  $result=mysql_query($sql,$con);
  if($result)
  {
   $msg.="Table <b>$table_name</b
> create successfully.";
   $flag=0;
  }
  elseif(!$result)
  {
   $msg.="Unable to create table $table_name ";
   $msg.=mysql_error();
   $flag=-1;
  }
  if($flag<0)
  $msg.="\n<br>Unable to create table <b>  
$table_name</b>\n<br /> 
Please Enter a valid database name to create";
  mysql_close($con);
 }


}
?>

In the Table name field enter test_tbl field and enter the following SQL command in the Command Text area

(
Id int not null auto_increment primary key,
name varchar(50),
address varchar(50),
email varchar(100)
)

Output:

Try it yourself.

Note: in the coming subsections of this chapter this table (test_tbl) will be used for reference, so make sure you have created the above table.

Using MySQL DML command

When data is put into a MySQL table it is referred to as inserting data. When inserting data it is important to remember the exact names and types of the table's columns. If you try to place a 500 word essay into a column that only accepts integers of size three, you will end up with a nasty error!

Note: As stated in the previous sub-section, we are going to use the table created in the previous sub-section for examples coming in this chapter.

Inserting records

Now that you have created your table, let's put some data into that table! Here is the PHP/MySQL code for inserting data into the table we created in the previous lesson [since we have the interface ready we can create the table anytime and I assume that you've already created one].

<?php
class dbUtils{
 private $flag;
 private $sql;//="create database ";
 private $msg;//="";*/
 #function createDB
 public function __construct(){
  $this->flag=0;
  $this->sql="";
  $this->msg="";
 }
 public function connect(){
  $con=@mysql_connect("localhost","root","");
  if(!$con)
   return NULL;
  else
   return $con;
 }
 public function createDB($db_to_create){
 }
 public function createTable($db_to_use,$tbl_name,$command){
 }
 public function insert_record($db_to_use, $command){
  $con;
  $msg1="Result=";
  #echo $db_to_use.$command;
  if(!$db_to_use||!$command) {
  return "Either Database name of Command is empty";
  }
  else{
  $con=$this->connect();

   if($con){

    mysql_select_db($db_to_use);
    $result=mysql_query($command,$con) or die(mysql_error());
     if(!$result)
      return "<b> Unable to insert records i
n the specified table".mysql_error()."</b>";
     else
      return "<b>Record(s) updated successfully</b>";

   }
   else
   {
    return  " Unable to connect to DB";
   }
  }
 }
 public function update_record($db_to_use, $commnad){
 }
 public function delete_record($db_to_use, $commnad){
 }
}
?>
<HTML>
<HEAD>
<TITLE> PHP Tutorial : Workign with MySQL[Insert Record] </TITLE>
</HEAD>
<BODY>
<TABLE border="1"  style="border: 1px solid blue;">
<form name="insert" action="<?php $PHP_SELF;?>" method="post">
<TR>
 <TD>Enter Database to Use</td>
 <TD><INPUT TYPE="text" NAME="db_name" size="20" maxlength="20"></td>
</tR>
<TR>
 <TD>Enter Table Name</td>
 <TD><INPUT TYPE="" NAME="tbl" size="30" maxlength="30"></td>
</tR>
<TR>
 <TD>Enter Name</td>
 <TD><INPUT TYPE="text" NAME="name" size="30" maxlength="30"></td>
</tR>
<TR>
 <TD>Enter Address</td>
 <TD><textarea cols="20" rows="4" name="address"></textarea></td>
</tR>
<TR>
 <TD>Enter Email Address</td>
 <TD><INPUT TYPE="text" NAME="email" size="50" maxlength="90"></td>
</tR>
<TR>
 <TD><INPUT TYPE="Submit" NAME="submit" value="Insert Record"></td>
 <TD><INPUT TYPE="Reset" NAME="Reset" value="Reset"></td>
</tR>
</form>
</TABLE>
<?php
 if(isset($_POST["submit"])){
?>
<div style="border:thin #FF0000 solid; width:80%; height:auto">

<?php
 $db=$_POST["db_name"];
 $tbl=trim($_POST["tbl"]);
 $name=$_POST["name"];
 $addr=$_POST["address"];
 $email=$_POST["email"];
 if($db&&$tbl) {
   $sql="insert into ".$tbl." values 
('null','".$name."','".$addr."','".$email."');";
   $dbutil=new dbUtils();
   $msg2=$dbutil->insert_record($db,$sql);
   if($msg2)
    echo $msg2;
   else
    echo "Unable to insert record i
nto $tbl an error has occured ".$msg2;
  }
 else{
  echo "Please enter Valid Database and/or table name to process";
 }
?>
 </div>
<?php
 }
?>
</BODY>
</HTML>

About Script should produce the following HTML form

After you fill data in the form and click on Insert record button it should display the following output

Explanation of the above script:

1. In the above script, at first we check if the form has been submitted

<?php
 if(isset($_POST["submit"])){
?>

2. If the form has been submitted then we retrieve the form field values and construct the query

$db=$_POST["db_name"]; //Retrieve the database name
$tbl=trim($_POST["tbl"]);// retrieve table name from form
$name=$_POST["name"];//Retrieve the Name from form
$addr=$_POST["address"];//Retrieve the Address from form
$email=$_POST["email"];//Retrieve the email id from form
if($db&&$tbl){
 $sql="insert into ".$tbl." values ('null','".$name."','".$addr."','".$email."');";

3. Now, we create an object of dbUtils class

$dbutil=new dbUtils();

4. Call the insert_record function of dbUtils class

$msg2=$dbutil->insert_record($db,$sql);

5. In the insert_record function change the database to use user specified database and execute the query to insert the record into the database table, besides other required rituals

mysql_select_db($db_to_use);    
$result=mysql_query($command,$con) or die(mysql_error()); 
  if(!$result)

 return "<b> Unable to insert records i
n the specified table".mysql_error()."</b>";
        else
 return "<b>Record(s) updated successfully</b>";

Updating Records

Following PHP script update the name of the user whose id is 2:

<html>
<head>
<title>PHP Tutorial :Working with MySQL</title>

</head><body>
<?php
$command="update test_tbl set name='Neeraj Bhardwaj' where id=2;";
$con=mysql_connect("localhost","root","") or die();
mysql_select_db("test", $con) or die(mysql_error());

  #echo $sql;
  $result=mysql_query($command,$con)or die (mysql_error());
  if($result)
  {
   echo "Record Updated  Successfully <br>.";
  }

?>
</body>
</html>

This example is like the previous example, except that we are updating the record(s), and we've used update command instead of insert command.

Deleting Records

<html>
<head>
<title>PHP Tutorial :Working with MySQL</title>

</head><body>
<?php
$command="delete from test_tbl where id=2;";
$con=mysql_connect("localhost","root","") or die();
mysql_select_db("test", $con) or die(mysql_error());

  #echo $sql;
  $result=mysql_query($command,$con)or die (mysql_error());
  if($result)
  {
   echo "Record Deleted Successfully <br>.";
  }

?>
</body>
</html>

Above PHP script delete a record from table test_tbl whose id is 2.

All three examples above are same except the SQL command used. As you can see it is quite easy to play with MySQL using PHP. Of course, you can use HTML forms to perform these operations. By doing so you can perform SQL operations dynamically.

Retrieving record from database

I assume that you have read tutorial of adding data into tables. I will try my best to keep it as easy as I can.

Functions we will use:

mysql_connect();
mysql_select_db();
mysql_query() ;
mysql_fetch_assoc();
mysql_num_rows();

Ok - now I assume that you have read the above tutorial I mentioned in which we inserted data into table name as test_tbl. Now suppose we have a table named as "test_tbl" and it has the following fields.

id, name and address, email.

and we have N members registered (means we have N rows, where we N is the number of rows/records in the table ) here they are (the data in test_tbl table) so these are the total values inserted in table "test_tbl". Now I will make a PHP file mysql_select.php which will retrieve data from test_tbl table.

<HTML>

<HEAD>
<TITLE>PHP Tutorial : Working with MySQL</TITLE>
</HEAD>

<BODY>
<?php
// Connects to your Database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
$data = mysql_query("SELECT * FROM test_tbl")
or die(mysql_error());
print "<table border cellpadding=3>";

while($info = mysql_fetch_array( $data ))
{
 print "<tr>";
 print "<th>Name:</th> <td>".$info['name'] . "</td> ";
 print "<th>Address:</th> <td>".$info['address'] . " </td></tr>";
 print "<th>" Email ID </th><td>
<a href="mailto:".$info['email'].">".$info['email']."</a>";
}
print "</table>";
?>

</BODY></HTML>

What is mysql_num_rows?

This function returns the number of rows effected by SELECT statement. You can use different clauses in SELECT statement, for exmaple you retrieve the name of member whose ID is 2. which is Aman Kumar, so you will use query like this. If there is not data in table it will diplay the error "There is no data in the table"..

$res=mysql_query("SELECT name FROM test_tbl WHERE id=2");

now we have used WHERE clause in the above statement.

What is mysql_fetch_assoc() ?

This function fetches a a result row as associatve array, and returns an associatve array. In other meaning it fetches the result from the row. In the for() loop I have displayed the fetched result. So the output of this file will be

Retrieving data with mysql_fetch_array

Syntax:

array mysql_fetch_array ( resource result [, int result_type ] ) Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.

mysql_fetch_array() is an extended version of mysql_fetch_row(). In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.

<?php
class dbUtils{

 public function __construct(){

 }

 public function connect(){
  $con=@mysql_connect("localhost","root","");
  if(!$con)
   return NULL;
  else
   return $con;
 }
 public function createDB($db_to_create){
 }
 public function createTable($db_to_use,$tbl_name,$command){
 }
 public function insert_record($db_to_use, $command){
 }
 public function update_record($db_to_use, $commnad){
 }
 public function delete_record($db_to_use, $commnad){
 }

 public function show_record_array($db_to_use,$table){
  $con=$this->connect();
  $sql="SELECT * FROM ".$table;#"
  echo $sql;
     mysql_select_db($db_to_use);
     $result = mysql_query($sql) or die(mysql_error());

     while($row = mysql_fetch_array($result)) {

         foreach($row as $key=>$value) {
             echo " $key= $value     \n";
         }
   echo "<br />";
     }
     mysql_free_result($result);
     mysql_close();
 }
}
?>
<html>
<head>
<title>PHP Tutorial: Working with MySQL[MySQL Select]</title>
</head>

<body>

<form action="<?php echo $PHP_SELF;?>"
 method="post" name="form1">
  Enter database Name<input type="text" 
name="db_name" maxlength="10" size="20">

  <br>
  Enter Table Name <input type="text" 
name="table" maxlength="10" size="20">

  <br>
  <input name="submit" 
type="submit" value="Display Records">
</form>
<hr>
<?php
 if(isset($_POST["submit"]))
 {
?>
<div style="border:thin #FF0000 
solid; width:90%; height:auto">

<?php
$db=$_POST["db_name"];
$table=$_POST["table"];
$dbutil=new dbUtils();
echo $dbutil->show_record_array($db,$table);
echo "</div>";
}
?>


</body>
</html>

On Executing above script produces the following output:

Error Handling

Error handling in PHP is a fairly straightforward process. PHP will simply send to the browser (or standard output if executing a script from the command line).

* an error message containing relative information to the error such as the filename of the script, the specific line in the file that caused the error,
* a (sometimes) helpful message describing the nature of the error

The default error handling in PHP is very simple. An error message with filename, line number and a message describing the error is sent to the browser

Database functions can fail. There are several possible classes of failure, ranging from critical-the DBMS is inaccessible or a fixed parameter is incorrect to recoverable, such as a password being entered incorrectly by the user.

The PHP interface functions to MySQL support two error-handling functions for detecting and reporting errors:

int mysql_errno(resource connection)

Returns the error number of the last error on the connection resource string

mysql_error(resource connection)

Returns a descriptive string of the last error on the connection resource

Example below shows a script illustrated with additional error handling. We have deliberately included an error where the name of the database test is misspelled as "test_tbl".

The error handler is a function, showError( ), that-with the database name error-prints a phrase in the format:

Error 1049 : Unknown database 'test_tbl'

The error message shows both the numeric output of mysql_errorno( ) and the string output of mysql_error( ). The die( ) function outputs the message and then gracefully ends the script.

Warning:

The functions mysql_query( ) and mysql_unbuffered_query( ) return false only on failure; that is, when a query is incorrectly formed and can't be executed.

A query that executes but returns no results still returns a result resource handle. However, a successive call to mysql_num_rows( ) reports no rows in the result set.

The mysql_connect( ) and mysql_pconnect( ) functions don't set either the error number or error string on failure and so must be handled manually. This routine handling can be implemented with a die( ) function call and an suitable text message, as in Example below.

Example Querying a database with error handling

<html>
<head>
  <title>PHP Tutorial: Working With MySQL</title>
</head>
<body><pre>
<?php

   function showError(  )
   {
      die("Error " . mysql_errno(  ) . " : " . mysql_error(  ));
   }

   // (1) Open a  connection to the database
   if (!($connection = @ mysql_connect("localhost",
                                       "roor","")))
      die("Could not connect");

   // NOTE : 'test' is deliberately misspelt to
   // cause an error
   if (!(mysql_select_db("test", $connection)))
      showError(  );

   // (2) Execute a  query on the test through the  connection

   if (!($result = @ mysql_query ("SELECT * FROM test_tbl",
                                   $connection)))
      showError(  );

   // (3) While there are still rows in the result set,
   // fetch the current row into the array $row
   while ($row = mysql_fetch_row($result))
   {
      // (4) Print out each element in $row, that is,
     // print the values of the attributes
      for ($i=0; $i<mysql_num_fields($result); $i++)
         echo $row[$i] . " ";

      // Print a carriage return to neaten the output
      echo "\n";
   }
   // (5) Close the database connection
   if (!mysql_close($connection))
      showError(  );
?>
</pre>
</body>
</html>

The MySQL error-handling functions should be used with the @ operator that suppresses default output of error messages by the PHP script engine. Omitting the @ operator produces messages that contain both the custom error message and the default error message produced by PHP. Consider an example where the string localhost is misspelled, and the @ operator is omitted:

if (!($connection = mysql_connect("localhos", "root",:"") )) die("Could not connect");

Warning: MySQL Connection Failed:

Unknown MySQL Server Host 'localhos' (0) in error-handling.php on line 42 Could not connect

TIPS: Don't forget to add an @ operator as the prefix to any function call that is handled manually with a custom error handler. The @ operator prevents PHP from issuing its own internal error message.

Assignment:

1. In the sub-section MySQL DML command of this chapter, there are two dummy functions [update_record, delete_record] to implement update and delete operations respectively. Implement SQL Update and delete commands using the functions.

2. Write a PHP function to return list of databases as a drop-down list, i.e.

3. Write a PHP script to generate the following output:

in the above script, there is a drop down list that contain name of all databases in MySQL.

This Example work like this, when user select database name from drop-down list, enter desired table name in the text box and press Show Records button it should display the record in show format.

4. Implement simple login using PHP-MySQL, the login page should look like, the one given below:

Create All necessary tables in MySQL and use JavaScript to ensure that form is not submitted when any of both fields are empty. Display appropriate Login Success/Login Failed message.

« Previous Next Chapter »

SHARE THIS PAGE

PHP - Working with Forms, Working with Database

0 Comments:

Post a Comment