Fetching Data from MySql Database

Hi Friends,
Hope u r doing well.
In this post u r going to learn "How to fetch data from Mysql Database".
If u r not aware of "How to store data in Mysql Database" u better go through My Previous Tutorial .

The Data fetching process can be divided in to few steps.
1. Connecting to Database.
2. Fetching the Data as resource.
3.  Extracting data from resource.


Connecting to Database:
 I explained this concept clearly in my previous post .

Fetching data from Database:

This can be done through mysql_query() function.

Syntax: mysql_query(select_query, connection_object);

example: $result=mysql_query("select * from user_table",$connection);

But the best practice is to prepare query first and then execute it.

for example:

<?php
            $sql_select="select * from user_table";
            $result= mysql_query($sql_select,$connection);
?>

Here, $result is a resource. A resource is a special variable that holds the reference of another external resource which contains actual fetched data.

Extracting Data from Resource:

The data from a resource can be fetched using a mysql_fetch_array() function 

syntax: mysql_fetch_array(result_resource, result_type);

in the above syntax the second parameter result_type is optional one.

example: mysql_fetch_array($result, MYSQL_BOTH)

Generally this function is used with in a while loop, which ends when all rows fetched from database are extracted. This function returns an array which contains one record filed's names and their corresponding values as key value pairs.

Let us assume we have two records in table 'user_table' with fields uname, age, profession.
like :
-------------------------------------------------------------
uname               age                  profession
Venkatesh        25                   Web Developer
Mahesh             26                   Marketing Executive 
 -------------------------------------------------------------
Those two rows are fetched from Database using mysql_query() function.

We can extract those two rows as arrays as follows:

 <?php

 while($row=mysql_fetch_array($result)) 
{
       echo $row['uname']."     ".$row['age']."       ".$row['profession'];    
       echo "<br>";  // moving cursor to next line using html break tag.
}?>
The output will be:

Venkatesh      25      Web Developer
Mahesh             26      Marketing Executive

The while loop will get rotated till all rows are extracted.


So, We can process the fetched data as per our requirement.
Here, in this example am going to print them in tabular format.

Program:
<?php

$con=mysql_connect('localhost','root','');
mysql_select_db('my_db',$con);
$sql_select="select * from user_table";
$result=mysql_query($sql_select,$con);

//from here am embedding html code in php.
?>
<table border="1">
<tr><th>Name</th><th>Age</th><th>Profession</th></tr>
<?php
while($row=mysql_fetch_array($result))
{   
//while loop starts here
?>
<tr><td><?=$row['uname'];?></td> 
         <td><?=$row['age'];?></td> 
         <td><?=$row['profession'];?></td>
</tr> 
<?php
//while loop ends here
}

?>
</table>


Output:

I hope you understood well. 
In my next post I will explain you how  to upload a file in PHP.

Up to then Take Care Bye..
Urs Venky ... :)

PHP with MySql - Storing values in MySql Database

Hi Friends,
In this post am going to explain "How to store form data in MySql DB using PHP".
If u r not aware of "getting and processing form data" u can go through my previous post.

Storing Data in MySql Data base is a sequence of five steps. they are:

1. Creating MySql Connection.
2. Creating a New Database in Mysql.
3. Selecting the Database.
4. Creating a table with required schema(structure).
5. Inserting values in to Database.

Creating MySql Connection:

In PHP creating a connection can be achieved through mysql_connect() function.
This Function takes three parameters
1. Host Name - generally, if we are executing in local system we use 'localhost'.
2. User Name - For Local system we use 'root'.
3. Password -   Generally local server doesn't need any password u can pass it as empty string.

 syntax:  mysql_connect("host_name","username","password")
 EX:
      <?php
          $con = mysql_connect("localhost","root","");
          if (!$con)
          {
            die('Could not connect: ' . mysql_error());
          }
      ?>

Creating New Database:
Open MySql Console and Type
    CREATE DATABASE database_name;      //and press enter

              or

you can do this with php mysql_query()function. This function takes two parameters 
1.  MySql Query - This can be any valid mysql query.
2.  Connection Object - This is the Connection object created with mysql_connect() function
<?php

  if(mysql_query("CREATE DATABASE  database_name",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

?>
here database_name is user defined(You can name it as your wish).
for more explanation click here.

Selecting the Database:
This can be achieved through mysql_select_db() function.
This function takes two parameters 
1. database name - This is existed database name.
2. connection object -  This is the Connection object created with mysql_connect() function

 syntax:          mysql_select_db("database_name", connection_object);
    Ex:              mysql_select_db("my_db", $con);


Creating a Table:
The CREATE TABLE statement is used to create a table in MySQL.
You can do this with mysql console:
Syntax:
                CREATE TABLE IF NOT EXISTS table_name
          (
           column_name1 data_type,
           column_name2 data_type,
           column_name3 data_type,
           ....
          )

Example: 

          CREATE TABLE IF NOT EXISTS student

          (

            roll_number varchar(20),

            name varchar(50),

            age  int,

            address text,

            total_marks int,

            average float

          )
click here for more data types in MySql

You can also do this with php also:
Syntax:
            mysql_query('create_query_string', connection_object);

Example:
       <?php
              $sql_create="create table if not exists student(roll_num varchar(10), name varchar(20), age int)";               
         mysql_query($sql_create,$con);
       ?>

Directly we can write query in mysql_query() function. But in special cases like when query string is too long, it is best practice to assign query string to  a variable and the use that variable in mysql_query() function.

Inserting Values in to Database:
The INSERT INTO statement is used to add new records to a database table.

Syntax:
            mysql_query('insert_query_string', connection_object);

Example:
       <?php
              $sql_insert="insert into student values('1','venkatesh',25)";               
         mysql_query($sql_insert,$con);
       ?>



Here is the code for posting values from HTML form and Storing them in MySql Database
Form.html:
<html>
<head>

<script language="javascript">

function validate(){

var email=document.getElementById('email').value;

var pass= document.getElementById('pwd').value;

var atpos=email.indexOf("@");            // getting the position of '@' symbol in email

var dotpos=email.lastIndexOf(".");     // getting the position of last '.' symbol in email 

if(email=="")                                           // if email value is empty

{

        alert("please enter email id");                   // show an alert message

 document.getElementById('email').focus();    // place the cursor in email field

return false;                                                   // stop the execution and return false.

}
//checking @ and . positions

else if (atpos<1 || dotpos<atpos+2 || dotpos+2>=email.length)   

{

alert("Not a valid e-mail address");

document.getElementById('email').focus();

                        return false;

}

else if(pass=="")                     //if password is empty

{

alert("please enter password");          

document.getElementById('pwd').focus();

return false;

}

  else if(pass.length<6)             //if password length is less than 6 chars

{

alert("please enter password");

document.getElementById('pwd').focus();

return false;

}

return true;                        // if all conditions are satisfied then return true.

}

</script>

</head>
<body>

<form action="targetpage.php" method="post" onsubmit="return validate()">

<table width="100%" align="center">

<tr><th>Email</th><td align="center"><input type="text" name="email" id="email" /></td></tr>

<tr><th>Password</th><td align="center"><input type="password" name="pwd" id="pwd" /></td></tr>

<tr><td colspan="2" align="center"><input type="submit" value="register" /></td></tr>

</table>

</form>

</body>
</html>
---------------------------------------------------------------------------------------
targetpage.php:
<?php
// the isset function will check whether the email value is empty or not 
if(isset($_REQUEST['email']))  
{
     extract($_REQUEST);
     $con = mysql_connect("localhost","root","");
   if (!$con)
   {
      die('Could not connect: ' . mysql_error());
   }

   if(mysql_query("CREATE DATABASE  database_name",$con))
   {
     echo "Database created";
   }
   else
   {
     echo "Error creating database: " . mysql_error();
   }

   mysql_select_db("my_db", $con); 

   $sql_create="create table if not exists login_details(email varchar(250), password varchar(250))";                  
   mysql_query($sql_create,$con);
   $sql_insert="insert into  login_details values('".$email."','".$pwd."')";              
   $result=mysql_query($sql_insert,$con);
   if($result)
       echo "values inserted successfuly";
   else
       echo "error occurred while storing values"; 
}
?>

----------------------------------------------------------------------------------------

     

Processing HTML data in PHP page

Hi friends,
In this post am going to explain how we process the data posted from HTML form in PHP page.
If you not aware of HTML forms u can go through my previous post 

The values posted from HTML form to php page can be retrieved in 3 ways:
1. Using $_GET  - This is used when the values are posted using method 'get' in HTML form tag.
   ex: if you have a HTML element with name='email'.

        <form method='get' action='targetpage.php'>
       <input type='text' name='email'>
        </form>
        
     In targetpage.php the value of 'email' field can be retrieved as $_GET['email'].

2. Using $_POST - This is used when the values are posted using method 'post' in HTML form tag.

   ex: if you have a HTML element with name='email'.

        <form method='post' action='targetpage.php'>
       <input type='text' name='email'>
        </form>


    In targetpage.php the value of 'email' field can be retrieved as $_POST['email'].

3. Using $_REQUEST - This is used when the values are posted using method 'post' or 'get' in HTML form tag. 

    In targetpage.php the value of 'email' field can be retrieved as $_REQUEST['email'].

Note: $_REQUEST variable can be used in both cases(for get and post also). So It is a better practice to use $_REQUEST for form data processing.

Up to know we learned how to get values from HTML form to php page. Now we'll move a little bit forward i.e using extract() function.

as per our knowledge we can get form values like $_GET['email'], $_POST['email'], and $_REQUEST['email'].

using extract() function makes it easier to use values as php variables.
you can refer description of extract() function  
for ex: 

<?php
 echo $_REQUEST['email'];    //prints the email value
?>

<?php
extract($_REQUEST);
echo $email;                     // prints the email value
?> 

the above two pieces of code does same thing i.e prints email value.
But using extract method makes coding easier,  when there is a need of using same variable in multiple places.


So, it is better practice to use extract() method to fetch values from $_REQUEST or $_POST or $_GET.

Here is the code for posting values from HTML form and processing them in php page
Form.html:
<html>
<head>

<script language="javascript">

function validate(){

var email=document.getElementById('email').value;

var pass= document.getElementById('pwd').value;

var atpos=email.indexOf("@");            // getting the position of '@' symbol in email

var dotpos=email.lastIndexOf(".");     // getting the position of last '.' symbol in email 

if(email=="")                                           // if email value is empty

{

        alert("please enter email id");                   // show an alert message

 document.getElementById('email').focus();    // place the cursor in email field

return false;                                                   // stop the execution and return false.

}
//checking @ and . positions

else if (atpos<1 || dotpos<atpos+2 || dotpos+2>=email.length)   

{

alert("Not a valid e-mail address");

document.getElementById('email').focus();

                        return false;

}

else if(pass=="")                     //if password is empty

{

alert("please enter password");          

document.getElementById('pwd').focus();

return false;

}

  else if(pass.length<6)             //if password length is less than 6 chars

{

alert("please enter password");

document.getElementById('pwd').focus();

return false;

}

return true;                        // if all conditions are satisfied then return true.

}

</script>

</head>
<body>

<form action="targetpage.php" method="post" onsubmit="return validate()">

<table width="100%" align="center">

<tr><th>Email</th><td align="center"><input type="text" name="email" id="email" /></td></tr>

<tr><th>Password</th><td align="center"><input type="password" name="pwd" id="pwd" /></td></tr>

<tr><td colspan="2" align="center"><input type="submit" value="login" /></td></tr>

</table>

</form>

</body>
</html>
---------------------------------------------------------------------------------------
targetpage.php:
<?php
extract($_REQUEST);
echo "Email is: ".$email;
echo "Password is: ".$pwd;
?>

----------------------------------------------------------------------------------------

In my next post I'll explain you how to store values in database and  how to retrieve values from database.

Up to then Take Care bye.... 
Urs Venky..... :)

A HTML Form Submission to php page with best practices

Hello friends,

In this post i am explaining a simple HTML form submission with standard real-time methods.

The best practice is to design a form using a table for good look and feel.

Here is an example of well aligned Login Form:

Fig: A Simple Login Form

Code:

<body>

 <form action="targetpage.php" method="post" onsubmit="return validate()">

<table width="100%" align="center">

<tr><th>Email</th><td align="center"><input type="text" name="email" id="email" /></td></tr>

<tr><th>Password</th><td align="center"><input type="password" name="pwd" id="pwd" /></td></tr>

<tr><td colspan="2" align="center"><input type="submit" value="login" /></td></tr>

</table>

</form>

</body>

Explanation:

• The 'action' property in 'form' tag specifies the target php page to where the form elements has to be posted. 
• Its best practice to use value 'post' instead of 'get' for property 'method' to hide the posted values to the target page. 

           for example:         

               If you use 'get', after form submission the URL in target page will look like                         

                        http://targetpage.php?name=myname&pwd=mypassword 

                            i.e values posted are visible.        

               If you use 'post' instead of 'get'  the URL looks like   

                         http://targetpage.php          i.e values are hidden.

• The other property I used with form tag is 'onsubmit' and I called a java script function on this event. The onsubmit event is fired before form submission i.e. on clicking the submit button. Generally this event is generated to validate the form. For this we need function definition. Here it is.

Javascript Code:

<script language="javascript">

function validate(){

var email=document.getElementById('email').value;

var pass= document.getElementById('pwd').value;

var atpos=email.indexOf("@");            // getting the position of '@' symbol in email

var dotpos=email.lastIndexOf(".");     // getting the position of last '.' symbol in email 

if(email=="")                                           // if email value is empty

{

        alert("please enter email id");                   // show an alert message

document.getElementById('email').focus();    // place the cursor in email field

return false;                                                   // stop the execution and return false.

}
//checking @ and . positions

else if (atpos<1 || dotpos<atpos+2 || dotpos+2>=email.length)   

{

alert("Not a valid e-mail address");

document.getElementById('email').focus();

                        return false;

}

else if(pass=="")                     //if password is empty

{

alert("please enter password");          

document.getElementById('pwd').focus();

return false;

}

  else if(pass.length<6)             //if password length is less than 6 chars

{

alert("please enter password");

document.getElementById('pwd').focus();

return false;

}

return true;                        // if all conditions are satisfied then return true.

}

</script>

 Explanation:

In validate function I wrote four conditions in if-else stack .

1. For checking whether the email value is empty or not.

2. For checking whether the email value is valid or not. 

3. For checking whether password is empty or not.

4. For checking whether the length password is grater than 6 or not. 

If all conditions returned false (i.e all fields are filled with valid data) finally 'true' will be returned. So that the form get submitted successfully to the intended php page.

The Data processing in php will be described  in my next post.

Hope This is use full.

Bye Take Care.   :) urs Venky.....