What to expect ?
Using PHP script insert user entered data/information into database and extract/fetch data stored in database and display it on the browser.
This video tutorial also teaches the MySQL queries.
In this video tutorial we have used a readymade form which uses POST method to pass the user entered information. To watch the coding/designing of this form go to POST Method In Action: Simple Form
Video Tutorial: Insert And Extract/Fetch Data From Database: PHP Script
[youtube https://www.youtube.com/watch?v=4oViKzEDiMo]
MySQL Commands and Uses
mysql > drop database abc;
This command will delete the database by name abc, if its already present.
mysql > create database abc;
This MySQL command creates a new database by name abc.
mysql > use abc;
After creating the database abc, we must tell the console that we want to operate on the newly created database. i.e., use abc;
mysql > create table bbc(name varchar(15), company varchar(20));
This MySQL command is used to create a table by name bbc with two string type data fields name and company with character length of 15 and 20 respectively.
mysql > desc bbc;
This shows the description or the structure of the table.
mysql > select * from bbc;
This returns all the contents stored in the table bbc.
PHP Script
Connecting the PHP Script to database
<?php
$conn = mysql_connect("localhost","root","");
$db = mysql_select_db("abc",$conn);
?>
mysql_connect() is a standard function which takes 3 parameters. First parameter is host name, second parameter is the MySQL username, and the 3rd parameter is the MySQL password.
mysql_select_db() is also a php standard function. Firs parameter is the database name, with which you want the script to be connected to, and the second parameter is optional.
NOTE: We can put this database connection code inside a separate file and include it inside whichever script we want using include(); or include_once(); function. This way we can reduce the coding and this helps a lot while we are editing this information.
Compete Source Code
postform.php
<html> <head><title>POST Method in Action</title></head> <body> <form action="post.php" method="post"> Name <input type="text" name="user"><br /> Company<input type="text" name="comp"><br /> <input type="submit" value=" Submit Info"> </form> </body> </html>
This is a simple form which contains 2 input fields(user and comp) and a submit button. It uses POST method to pass the user submitted information/data.
post.php
<?php
$conn = mysql_connect("localhost","root","");
$db = mysql_select_db("abc",$conn);
?>
<?php
$name = $_POST['user'];
$company = $_POST['comp'];
# echo "My name is $name
And I'm the CEO of my company {$company}"
$sql = "INSERT into bbc values('$name','$company')";
$qury = mysql_query($sql);
if(!$qury)
echo mysql_error();
else
{
echo "Successfully Inserted";
echo "View Result";
}
?>
After storing the information passed by the user inside $name and $company variables, we use the MySQL query to store this data into the database.
$sql = "INSERT into bbc values('$name','$company')";
This MySQL query is stored into $sql variable and is passed to a standard PHP function for execution. i.e., mysql_query($sql);
Based on the result of the execution of the query, we show the proper messages using if statement.
mysql_error() is a standard PHP error function. This helps a lot in development stage, as it shows a descriptive error message. It is not advised to have this error function in the script at deployment stage.
show.php
If the query is executed successfully, a link to show.php file is shown and a “Successfully Inserted” message will be displayed on the browser.
<?php
$conn = mysql_connect("localhost","root","");
$db = mysql_select_db("abc",$conn);
?>
<?php
echo "<ul>";
$sql = "select * from bbc";
$qury = mysql_query($sql);
while($row = mysql_fetch_array($qury))
echo "<li>Name: $row[0]</li><li>Company: $row[1]</li>";
echo "</ul>";
?>
We have already explained the select query above.
$row = mysql_fetch_array($qury)
This PHP function gets all the result stored in $query and stores it inside $row variable in the form of an array.
If we put this above statement inside a While loop, the loop terminates once there are no more elements in the $query variable.
Now, echo/print the contents of the $row variable to get the contents being fetched by the database using SELECT query.
We can either use the indexing like: $row[0], $row[1] etc or we can directly mention the database field names to reduce confusion i.e., $row[name], $row[company] etc.