Monthly Archives: November 2010

Creating Database Table In PHP

Leave a reply

In this article, we will learn to create a database table in a MySQL database through PHP script. Following are the steps that we will follow to create a database table:

1. Establishing connection with MySQL server
2. Creating a database (if it does not exists). If database already exists then skip this step
3. Selecting the database (in which table is to be created).
4. Writing SQL statement for creating a table
5. Executing the SQL statement
To understand above steps let us look at the following program that creates a table by name: products in shopping database. The products table that we are going to create will have five fields (columns): id, item_code, item_name, quantity and price. The fields: id, item_code and quantity are considered to be of int (integer) data type, item_name of character data type and price of float data type.
Coding for “Creating Database Table In PHP”
createtable_products.php
<?php
$user=”username”;
$password=”password”;
$connect = mysql_connect(“localhost”, $user, $password) or die (“Please check your server connection.”);
//create the database if it doesn’t already exist
$create = mysql_query(“CREATE DATABASE IF NOT EXISTS shopping”) or die(mysql_error());
mysql_select_db(“shopping”);
$sql = “CREATE TABLE products(
id int(6) NOT NULL auto_increment,
item_code int(4),
item_name varchar(50),
quantity int(4),
price float)”;
$res = mysql_query($sql) or die (mysql_error());
echo “products table successfully created!”;
?>
Above program has all the five steps of creating a table. In the first two lines, two variables $user and $password are initialized to the username and password of the MySQL server respectively. From the 3rd line onwards, we see that the five steps for creating a table are executed. Let us see look at each statement step by step:
1. Establishing connection with MySQL server
Before proceeding with creation of a database or a table, the first thing we need to connect with MySQL server. The connection is established with the help of a valid user name and password.
$connect = mysql_connect(“localhost”, $user, $password) or die (“Please check your server connection.”);
In above command localhost signifies that MySQL server is installed on the local machine. The string localhost will be replaced by the IP address of the server or server name (Example : sqlserver.com or 216.237.120.79 ) in case we try to connect with the remote server. The $user and $password variables contain the valid userid and password supplied by the administrator. The keyword die is for displaying error messages if any information supplied is wrong.
2. Creating a database
After we are connected to the MySQL server, we must then create the database (if it does not exists) followed by selecting it i.e. making it active in memory. The command for creating database is :
$create = mysql_query(“CREATE DATABASE IF NOT EXISTS shopping”) or die(mysql_error());
Above statement is creating a database by name “shopping” (in case it doesn’t already exists). If the database is already there, this command won’t do anything
3. Selecting the database
Selecting the database means loading the database in memory (i.e. making it active). The command for selecting a given database is :
mysql_select_db(“shopping”);
Above command is selecting the database: shopping. Now all SQL statements that are executed will be applied to the tables belonging to this (shopping) active database.
4. Writing SQL statement for creating the table
The below given SQL statement creates a table by name products that has five fields out of which id field is set to auto_increment i.e. its value  will automatically increase by 1 with every record inserted
$sql = “CREATE TABLE products(
id int(6) NOT NULL auto_increment,
item_code int(4),
item_name varchar(50),
quantity int(4),
price float)”;
The rest of the fields (columns) in the table are item_code that will store an integer values of almost 4 digits, item_name field will store the name of the item of up to 50 characters long and quantity field can store an integer value of up to 4 digits and price field can store a fractional value (value with decimal points)
5. Executing the SQL statement
Below given statement executes the above SQL statement of creating the products table
$res = mysql_query($sql) or die (mysql_error());
The $res variable is for storing the result of the execution of the SQL statement
For more information, refer my book: “Developing Web Applications in PHP and AJAX” available at: http://www.amazon.com/Developing-Web-Applications-PHP-AJAX/dp/0070707103/ref=ntt_at_ep_dpt_4

Different Methods of sending data from one PHP script to another

1 Reply

When we want some feed back from the user on some subject or want some information to be supplied by the user, we take the help of forms. A form consists of certain input elements to accept user data that is then passed to another PHP script for further processing on selecting Submit button. In this article, we will learn different HTTP Request methods like GET and POST that are used for sending data from one PHP script to another.

<form action=”display.php” method=”get”>

Name: <input type=”text” name=”name” />

Age: <input type=”text” name=”age” />

<input type=”submit” />

</form>

Above form has two input boxes named “name” and “age”. The form “action” points to a php file : “display.php” and the “method” is “get”. Meaning the contents of the form will be submitted to php script : “display.php” using the HTTP request-method GET for further processing.

The information is passed from one PHP script to another using any of the following two HTTP Request methods :

  • GET
  • POST

GET

In this method the information passed is less secure as it is displayed in the browser’s address bar and moreover, there is a limit on the amount of information passed (it can be of maximum 100 characters).

POST

In this method, the information passed is more secure as it is not displayed in the browser’s address bar.

$_GET array

The $_GET array is an array where data from the previous form sent using HTTP GET method are stored. The data from the previous form is sent in the form of pairs : variable name(s) and its value(s).

Recall that information sent from a form with the GET method is less secure as it is displayed in the browser’s address bar and also the size of the information passed is limited.

When the user clicks the “Submit” button, the URL sent will look something like this:

http://localhost/display.php?name=john&age=22

We can see that when we use the GET method, all the information passed is attached in the browser address bar after the ? mark.

The target file : “display.php” file can now extract the data from $_GET array using following statements :

Welcome <?php echo $_GET["name"]; ?>.<br>

Your age is <?php echo $_GET["age"]; ?>

The name and age sent from the source PHP script is accessed from $_GET array and displayed .

$_POST array

The $_POST array is an array which collects the values sent from a form using HTTP POST method.

Example :

<form action=”display.php” method=”post”>

Enter your name: <input type=”text” name=”name” />

Enter your age: <input type=”text” name=”age” />

<input type=”submit” />

</form>

When the user clicks the “Submit” button, the $_POST["name"] and $_POST["age"] variables will be automatically set by PHP. The $_POST array contains all POST data and the URL will not display any form data (so it is more secure).

The destination PHP script : “display.php” file can now use the $_POST array to retrieve the form data with the help of following code :

Welcome <?php echo $_POST["name"]; ?>.<br>

Your age is <?php echo $_POST["age"]; ?>

Note : Information sent from a form with the POST method is invisible to others and also there is no limit to the amount of information sent.

To conclude : HTTP POST method is a better way of transmitting data because :

  • Variables sent with HTTP POST are not shown in the URL
  • Variables have no length limit

$_REQUEST array

$_REQUEST array contains the contents of both $_GET and $_POST. That is, it is used to collect the information from a form which is sending data by either method : GET or POST method.

So, in case we are not known of HTTP method used by the source PHP script, it is a wise decision to access the information using $_REQUEST array. The target file : display.php made in the earlier examples can be rewritten as :

Example :

Welcome <?php echo $_REQUEST["name"]; ?>.<br />

Your age is <?php echo $_REQUEST["age"]; ?>

It will access the information sent from the source PHP file sent by any method : GET or POST

For more information, refer my book: “Developing Web Applications in PHP and AJAX” available at: http://www.amazon.com/Developing-Web-Applications-PHP-AJAX/dp/0070707103/ref=ntt_at_ep_dpt_4

Matching the Password and Confirm Password Fields in jQuery

Leave a reply

In this article, we are going to learn how to validate Password and Confirm Password fields of a form. That is, we will learn  to confirm that the passwords entered by the user in the two fields : Password and Confirm Password fields exactly match.

HTML file for “Matching the Password and Confirm Password Fields in jQuery”

Let us make an HTML file that displays three labels and three text fields for Userid, Password and Confirm Password as shown below:

<body>

<form>

<div><span>User Id </span><input  /></div>

<div><span>Password </span><input name=”password” /><span> Password cannot be blank</span></div>

<div><span>Confirm Password </span><input class=”confpass” /><span class=”error”> Password and Confirm Password don’t match</span></div>

<input value=”Submit”>

</form>

</body>

In the HTML file, the label messages User Id, Password and Confirm Password  are enclosed in span elements that are assigned class names: ‘label’. The input text fields are assigned the respective class names ‘userid’, ‘password’ and ‘‘confpass’  for retrieving the data entered in them via jQuery . Finally, all the input text fields are followed by the error message nested inside the span element of class ‘error’.

The reasons of assigning the classes to all the items items: label, error message and submit button is to automatically apply the properties defined in the class selectors ‘.label’,  ‘.error’ and ‘.submit’ (defined in the style sheet: style.css). Also, the combination of label, input text field and error are nested inside the div element so that we can apply style property to div element to creating spacing among each combination of label, input text field and error message.

The style sheet with the respective class selectors is as shown below:

style.css

.label {float: left; width: 120px; }

.error { color: red; padding-left: 10px; }

.submit { margin-left: 125px; margin-top: 10px;}

div{padding: 5px; }

In the style sheet file, the class selector: ‘.label’ contains the float property set to value: ‘left’ so as to make the label appears on the left side of the browser window (creating space for the input text field to appear on its right) and the width property is set to value 120px to define the width that the label can consume. The class selector ‘.error’ assigns the red color to the error messages  and for making the error message appear at the distance of 10px from the element on its left. The class selector ‘.submit’ contains the margin-left property set to value: 125px and margin-top property set to value:10px to make it appear at the distance of 125 px from the left border of the browser window (to appear below the input text fields) and at the gap of 10px from the element just above it. The type selector ‘div’ has the padding property set to value: 5px for creating some space among the div elements where each div element contains a combination of label, input text field and error message

The jQuery code to test that data entered in the Password and Confirm Password fields are exactly same is as shown below :

$(document).ready(function() {

$(‘.error’).hide();

$(‘.submit’).click(function(event){

data=$(‘.password’).val();

var len=data.length;

if(len<1)

{

$(‘.password’).next().show();

}

else

{

$(‘.password’).next().hide();

}

if($(‘.password’).val() !=$(‘.confpass’).val())

{

$(‘.confpass’).next().show();

}

else

{

$(‘.confpass’).next().hide();

}

event.preventDefault();

});

});

Before we understand the jQuery code, let us understand the usage of .next() method that is used in it:

.next()

This method retrieves the next following sibling in the specified element. Remember, this method returns the very next sibling for each element and not all next siblings like nextAll() method does

Syntax:

.next(selector)

where selector is an optional parameter used for specifying the selector expression for matching with the specified elements

Code for “Matching the Password and Confirm Password Fields in jQuery”

In the jQuery code, initially, we hide the error messages (elements nested inside the span element of class ‘error’). Thereafter, we attach a click event to the submit button. In the event handling function of the click event, we retrieve the data in the Password field (input text field of class ‘password’) and store it in variable ‘data’. If the length of the contents in variable ‘data’ is found less than 1  i.e. if user has not entered anything in the Password field, we make the element next to the password field (which is span element of class ‘error’) to appear on the screen i.e. we display the error message: ‘Password cannot be blank’  on the screen.

If the user has not left the password empty, we check whether the data entered in the password field and the Confirm password field (data in elements of class ‘.password’ and ‘.confpass’) are exactly the same. If the two don’t match, we display the error message (which is the element next to the Confirm Password field): ‘Password and Confirm Password’ don’t match’ on the screen. We also invoke the preventDefault() method of the event object to prevent the data entered by the user  from sending it to the server as we are mainly interested in just confirming that the data entered in Password and Confirm Password fields match

On execution of jQuery code, if we leave the Password field blank and select Submit button, we get the error message: ‘Password cannot be blank’  on the screen as shown in below given figure :

Error message appears if password is not entered

If the contents of Password and Confirm Password fields don’t match, we get the error message: ‘Password and Confirm Password don’t match’ as shown in below given figure

Error message appears if password and confirm password don’t match

This finishes my article on “Matching the Password and Confirm Password Fields in jQuery”

For more information, refer my book : “jQuery Recipes A Problem-Solution Approach” available at : http://www.amazon.com/jQuery-Recipes-Problem-Solution-Approach-Development/dp/1430227095/ref=sr_1_1?ie=UTF8&s=books&qid=1287320549&sr=8-1

More articles :

Forward Navigation Via Toolbar Buttons

Sending List box option to the server asynchronously

Leave a reply

In this article, we will learn how multiple items selected from the list box are sent to the server asynchronously.

For this article, we will make three files namely:

  • listajax.php – PHP script that will display a list box
  • listajax.js – JavaScript file that creates XMLHttpRequest object to send request to the server asynchronously. The options of the list box selected by the user are passed to this JavaScript file
  • showlistdata.php – Server side PHP script to display the response i.e. the options of the list box selected by the user

Coding for “Sending List box option to the server asynchronously”

The coding of listajax.php is as under :

listajax.php

<html>

<head>

<script type=”text/JavaScript” src=”listajax.js”></script>

</head>

<body>

Choose one or more category: <br>

<select multiple=”multiple” size=”4″ onblur=’itemdisplay()’ >

<option value=”Camera” selected>Camera</option>

<option value=”Mobile”>Mobile</option>

<option value=”Book”>Book</option>

<option value=”Computer”>Computer</option>

<option value=”Washing Machine”>Washing Machine</option>

<option value=”Refrigerator”>Refrigerator</option>

</select><br><br>

<div id=”info”></div></body>

</html>

Above program creates a list box with name “Category” with six items : Camera, Mobile and Book, Computer, Washing Machine and refrigerator displayed in it. The multiselect feature of the listbox is set with the help of “multiple” attribute. So, user can select more than one item from the list box (by pressing Ctrl key). After selecting the item(s), the moment user presses tab key or clicks anywhere else on the web page i.e. when the focus of the listbox is lost, a method : itemdisplay() is invoked. Beside this,  a <div> element is defined with id : “info”  for displaying the server response

The coding of the JavaScript file, listajax.js is as under :

listajax.js

1.       function makeRequestObject(){

2.       var xmlhttp=false;

3.       try {

4.       xmlhttp = new ActiveXObject(‘Msxml2.XMLHTTP’);

5.       } catch (e) {

6.       try {

7.       xmlhttp = new

8.       ActiveXObject(‘Microsoft.XMLHTTP’);

9.       } catch (E) {

10.    xmlhttp = false;

11.    }

12.    }

13.    if (!xmlhttp && typeof XMLHttpRequest!=’undefined’) {

14.    xmlhttp = new XMLHttpRequest();

15.    }

16.    return xmlhttp;

17.    }

18.    function itemdisplay()

19.    {

20.    var xmlhttp=makeRequestObject();

21.    optionsArray=document.getElementById(‘Category’).options;

22.    var selectedArray = new Array();

23.    var x=0;

24.    for(var i=0; i< optionsArray.length; i++)

25.    {

26.    if(optionsArray[i].selected)

27.    {

28.    selectedArray[x]=optionsArray[i].value;

29.    x++;

30.    }

31.    }

32.    var cat=”";

33.    for(var i=0;i<selectedArray.length;i++)

34.    {

35.    if( i!=selectedArray.length -1) {

36.    cat +=selectedArray[i]+”,”;

37.    }

38.    else

39.    {

40.    cat+=selectedArray[i];

41.    }

42.    }

43.    xmlhttp.open(‘GET’, ‘showlistdata.php?&Category=’+cat, true);

44.    xmlhttp.onreadystatechange=function() {

45.    if (xmlhttp.readyState==4 && xmlhttp.status == 200) {

46.    var content = xmlhttp.responseText;

47.    if( content ){

48.    document.getElementById(‘info’).innerHTML = content;

49.    }

50.    }

51.    }

52.    xmlhttp.send(null)

53.    }

Explanation of the above program

Statements from 1-17 are making an XMLHttpRequest object.

21.       optionsArray=document.getElementById(‘Category’).options;

The element in the web page with name : “Category” (our listbox) is searched and all the items available in it are assigned to an array : optionsArray.

22.       var selectedArray = new Array();

23.       var x=0;

24.       for(var i=0; i< optionsArray.length; i++)

25.       {

26.       if(optionsArray[i].selected)

27.       {

28.       selectedArray[x]=optionsArray[i].value;

29.       x++;

30.       }

31.       }

With the help of a loop, all the elements in the optionsArray are checked one by one and all the items selected by the user are stored in a separate array : selectedArray.

32.       var cat=”";

33.       for(var i=0;i<selectedArray.length;i++)

34.       {

35.       if( i!=selectedArray.length -1) {

36.       cat +=selectedArray[i]+”,”;

37.       }

38.       else

39.       {

40.       cat+=selectedArray[i];

41.       }

42.       }

All the items stored in array : selectedArray (containing only the list of items selected by the user ) are retrieved and stored in a variable cat (after separating them by commas except the last one)

43.       xmlhttp.open(‘GET’, ‘showlistdata.php?&Category=’+cat, true);

44.       xmlhttp.onreadystatechange=function() {

45.       if (xmlhttp.readyState==4 && xmlhttp.status == 200) {

46.       var content = xmlhttp.responseText;

47.       if( content ){

48.       document.getElementById(‘info’).innerHTML = content;

49.       }

50.       }

51.       }

XMLHttpRequest request is made to the  server (by GET method) for the file showlistdata.php, sending all the item(s) selected by the user (stored in variable cat) to it. The selected item list sent to the requested file will be retrieved in showlistdata.php file using $_GET array.

The state and the response to asynchronous request is checked. If the value of readyState property becomes 4 (meaning the request is complete) and the value of the status of the HTTP Request is 200 (which means there is no error), the response is then retrieved from the response stream and is assigned to variable: content which is then applied to the innerHTML property (property  used to display results) of the element with id : “info” to display all the items that are selected by the user in the list box

Again the response generated by the server is based on the execution of the file : showlistdata.php. Lets analyze what it returns

showlistdata.php

<?php

echo “The categories of items selected are : ” . $_GET['Category'] ;

?>

Above file returns the text : “The categories of items selected are “ and the category name(s) selected by the user. The categories selected by the user are passed to this file while making the XMLHttpRequest for the file by following command :

43.  xmlhttp.open(‘GET’, ‘showlistdata.php?&Category=’+cat, true);

Output of “Sending List box option to the server asynchronously”

On executing the file, listajax.php we get a list box with several options as shown in below given figure :

Selecting various items by pressing tab key.

We can select as many category of items as we want followed by pressing tab key. All the categories selected are displayed separated by commas as shown in below given figure.

Selected items are displayed

For more information, refer my book: “Developing Web Applications in PHP and AJAX” available at: http://www.amazon.com/Developing-Web-Applications-PHP-AJAX/dp/0070707103/ref=ntt_at_ep_dpt_4

Here are few  links of my published articles :

Iphone Application to Read And Set Slider’s Value

Validating Date Through Jquery

How to Validate an Email Address Through Jquery

Validating select element in jQuery

Selecting Multiple options in Select element using jQuery

Mobile Web Development using jQTouch:Session Management