Insert And Extract/Fetch Data From Database: PHP Script

Advertisement:

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



YouTube Link: http://www.youtube.com/watch?v=4oViKzEDiMo [Watch the Video In Full Screen.]


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

1
2
3
4
5
6
7
8
9
10
<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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
	$conn = mysql_connect("localhost","root","");
	$db      = mysql_select_db("abc",$conn);
?>
 
<?php
 
	$name       =	  $_POST['user'];
	$company = 	  $_POST['comp'];
 
	# echo "My name is $name <br /> 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<br />";
		echo "<a href='show.php'>View Result</a>";
	}
?>

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?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><br />";
 
	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.

Advertisement:

Enter your email address:

RSS Feed




48 thoughts on “Insert And Extract/Fetch Data From Database: PHP Script

  1. jordan

    thanks so much for everything it helped a lot ….i was just wondering if u can tell us about inserting and displaying images into and from the mysql database

  2. Satish Post author

    @jordan, Thanks. Will let you know about working with images in a video tutorial.

    @praveen, Thanks for your feedback. You can help us by letting your other friends know about Technotip.com

  3. Azhar Alam

    thanx a lot bro..
    fter watching this i will keep on following u. :P
    dear,
    in show.php when u run in apache after u login u were enablle to see all every one’s company name how to make it personal i mean like
    only limited access to his information
    like profile.php

  4. Satish Post author

    @Azhar Alam, In show.php there is a sql statement: $sql = “select * from bbc”;

    Make the following changes $sql = “SELECT * from bbc WHERE name=’$name'”;

    where $name should be taken dynamically using sessions or $_GET[] etc methods. This way we can customize the display to everyone.

  5. Asraful Haque

    This code is very helpful .It also explains how to fetch data with the help of $row = mysql_fetch_array($qury) with coding.
    Thank you so much!

  6. sudipta saha

    Thankss a lot…….plz sent me a coading page that how i fetch data from two field and how i display it on screen………plzzzzzzzzzzzzzzzzz help me……………My email id is- sudiptasaha61@yahoo.com………….plzzzzzzzzzzzzzzzzzzzz nobody can not help me about this…………….sudipta

  7. Satish Post author

    @sudipta, Hey, you can get onto our forum and post your exact question in the relevant section. I’m sure people will be glad to help you out.

    In above video tutorial, I’ve explained about fetching data from two fields. Analyze the coding and you’ll get it. I know its difficult to understand from someone else’s code, but you’ll if you put some effort.
    Let me know in the forum, if you need anything more.

    @Asraful, @nidhi, @Dawson, @sudipta,
    Thanks for your feedback.. it means a lot to me

  8. micky

    Hello sir, can you help me to to figure out my details.php form. i want people to see what i submitted to a website ……..like numbers by using the number to check the info. like a courier tracking number where you will see your shipping details

  9. Satish Post author

    Hey @micky, since this is a question about a custom application development, I can’t help you in this comment section; as it may turn out to be irrelevant to other users.

    You could get our professional service or post about this on our official forum, so that we can help you in your effort.

    Thanks a lot for understanding ..

  10. manu

    hi satish,
    while updating data i got error like this, for this where the mistake has been done?

    ” You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE img_id=’1” at line 1 ”

    thanks in advance

  11. manu

    Hi satish thanks for ur reply…this is my update query..

    “UPDATE project_images SET img1='”.$img1.”‘,img2='”.$img2.”‘,img3='”.$img3.”‘,img4='”.$img4.”‘,project_type='”.$project_type.”‘,
    website_type='”.$website_type.”‘,page_name='”.$page_name.”‘,keyword='”.$keyword.”‘,title='”.$title.”‘,
    description='”.$description.”‘,header='”.$header.”‘,page_header='”.$page_header.”‘ WHERE img_id='”.$id_gallery.”‘”;

  12. manu

    hi satish, can you send the code for this..

    After registering the user,admin only can create username and password and send to user mail id?

    can you send the code for this pls??

    1. Satish Post author

      @manorama, Since this is a custom application request, we could do this with a minimum charge, please use the contact form to let us know your exact requirements.

      We can help you for free, if you can post your code on our form and ask your doubts. We can go through the code and try our best to help you out,

    1. Satish Post author

      @anu, Shortly we’re going to start CodeIgniter Framework Video tutorial, so stay subscribed.
      As a preparation/support, we’re currently publishing Object Oriented Programming using PHP tutorials. CodeIgniter is by far ‘The Best’ PHP Framework I’ve found so far, I’m sure you’ll fall in love with it.

  13. MANORAMA

    hi satish, thanks a lot for helping…

    how to send matching profiles to their email id like naukari updates using php code???

    and that updates for weekly once or 15 days once??

    please help me in this.

    thanks in advance

  14. MANORAMA

    hi satish,

    how to send matching profiles to their email id like naukari updates using php code and that updates for weekly once or 15 days once??

    please help me in this.

    thanks in advance

  15. MANORAMA

    hi satish, thanks in advance

    how to send matching profiles to their email id like naukari updates using php code and that updates for weekly once or 15 days once??

    please help me in this.

  16. Durga Prasanna Acharya

    okk ,, Satish …your code is Awesome ….really nice…

    i want to say about one problem ,,that is if i want to see all data in same page ..where i able to insert ..i want to say inserting and display would be same page ..how it possible..help please….

    Thanks in Advance…

  17. ganesh

    Thank you Sathish. That was an excellent peace of information. I am trying to develop a portal on with wordpress where i need to create a form. This information will relay help me

Leave a Reply