WebDevelopment: 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:

Creating MySql Connection.
Creating a New Database in Mysql.
Selecting the Database.
Creating a table with required schema(structure).
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>

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>

<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>

</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";
}
?>

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

WebDevelopment

Saturday, 18 August 2012

PHP with MySql - Storing values in MySql Database

2 comments: