Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to make some operations with MySQL and PHP

This article will discuss about connecting php with mysql database and performing several operations on database using php, just like create and connect a database, create tables and insert datas on tables.

Today, we are going to learn about connecting our php application with mysql. We will also discuss on performing various operation in php using mysql.

Introduction

Storing information is a necessary part of any web application. Database like Mysql provides convenient way of storing large information in a manageable fashion. Php has methods by which you can perform database operations easily. To test these examples on your local computer you need Wamp server, Netbeans(Optional)

In this article we will discuss the following :

  1. Connecting with mysql
  2. Creating database using mysql in php
  3. Creating table using mysql in php
  4. Inserting data using mysql in php

Connecting with Mysql

Connecting your php site with Mysql database is quite simple. You simply need to know the host ,username and password.

Syntax : mysql_connect( servername, username, password);

Let’s discuss a simple example

Listing 1: PHP Connecting with mysql

<html>
<head>
	<title>MrBool Tutorial</title>
</head>
<body>
<?php
//making a connection
$con = mysql_connect("localhost","user","password");
if (!$con) {
	//executed in case we are not able to connect
    die('Could not connect: ' . mysql_error());
}
	// write some code
?> 

</body>
</html>
  1. We used function mysql_connect for connecting our php application to mysql.
  2. The first argument holds the name of host. We use localhost here since i am giving example for local computer. But when you try on your webserver then you need to specify the host name
  3. The second argument hold the user name. It must be name of mysql database user
  4. The third argument stores the password for the mysql database
  5. We store this in a connection variable.
  6. After this we check if connection was properly set or not. If not then we show the corresponding error
  7. One important thing here is that connection will itself close when script completes

Closing mysql connection

Listing 2: PHP closing connection

<html>
<head>
	<title>MrBool Tutorial</title>
</head>
<body>
<?php
$con = mysql_connect("localhost","user","password");
if (!$con) {
    die('Could not connect: ' . mysql_error());
}
// closes the connection
mysql_close($con);
?>
</body>
</html> 

We used mysql_close(connection name).This will close the connection .Its best practice to close the connection yourself and not rely completely on script to close connection itself

Creating database with mysql using php

Listing 3: PHP creating database

<html>
<head>
	<title>MrBool Tutorial</title>
</head>
<body>
<?php
$con = mysql_connect("localhost","user","password");
	if (!$con) {
   		die('Could not connect: ' . mysql_error());
	}
	//Creating a database using the mysql_query function
	if (mysql_query("CREATE DATABASE my_database",$con)) {
    	echo "Database successfully created";
	}else {
    	echo "There was an error creating database: " . mysql_error();
	}
	mysql_close($con);
?> 
</body>
</html>
  1. We made a connection as explained earlier
  2. If connection is successful then we run a mysql_query function which executes a sql query.
  3. Here we executed the query “create database my_database” which will create database named my_database..
  4. If the creation gets successful then we display the success message
  5. If the database creation is not successful then we display a error message. We show the detail using mysql_error method
  6. At last we close this connection

Creating tables

Listing 4: PHP creating table

<html>
<head>
	<title>MrBool Tutorial</title>
</head>
<body>
<?php
$con = mysql_connect("localhost","user","password");
	if (!$con) {
	    die('Could not connect: ' . mysql_error());
	}
		if (mysql_query("CREATE DATABASE my_database",$con)) {
	    	echo "Database was successfully created";
		}else {
	   		echo "There was an error creating database: " . mysql_error();
		}
		//Selecting the database
		mysql_select_db("my_database", $con);
		//Making appropriate sql query
		$sql = "CREATE TABLE myself(
		FirstName varchar(15),
		LastName varchar(15) )";
		//Running this query
		mysql_query($sql,$con);
		//Closing the connection
		mysql_close($con);
?>


</body>
</html> 
  1. As we learned here also we make a connection and create a database named my_database
  2. Now since we need to perform operation on this database so we will select this database.
  3. We select the database making use of mysql_select_db function.For first argument we give the name of database name. The second argument contain the name of connection
  4. After this we make a variable and store the query to create a table.The table has fields like firstname, lastname.
  5. Now we run the query using mysql_query function and pass this query and connection.
  6. After that we close the connection

Adding records to table

Listing 5: PHP inserting values in table

<html>
<head>
	<title>MrBool Tutorial</title>
</head>
<body>
<?php
$con = mysql_connect("localhost","user","password");
	if (!$con) {
   		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db("my_database", $con);
	//We insert into database executing the appropriate query
	mysql_query("INSERT INTO myself (FirstName, LastName)
	VALUES ('Mr', 'Bool')");
	mysql_close($con);
?>
</body>
</html>
  1. As explained we have made a connection and connected to database. After that we selected the database.
  2. Now we insert into this table using a sql query. For inserting this we use the function mysql_query.
  3. After adding the record we will close the connection

Points to Remember:

  1. We have not suppressed the error and warning message here but remember to do so in your real implementation. I will let you know how to do that also in next coming articles

  2. Always close the connection manually
  3. Prevent your queries from attacks like Sql Injection. Here we have not explained that, but when you make your site then please make sure that you take care of that every time.
  4. Username and Password for database must be kept in secure place so that user cant access that info easily.

This is all for today’s article. Will come up with some more exciting article next time.



My main area of specialization is Java and J2EE. I have worked on many international projects like Recorders,Websites,Crawlers etc.Also i am an Oracle Certified java professional as well as DB2 certified

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login