How to PHP Scripting- Connect to MySQL Server

The tutorial takes you through establishing a MySQL Server connection using PHP on a web page. To perform basic queries from within MySQL is very easy. This article will show you how to get up and running.

 

Open a connection to MySQL

The first job is to actually connect to MySQL. As its name suggests, mysql_connect( ) does exactly that.

Open connection to MySQL
	<?php
	  $servername = "localhost";
	  $username = "username"; //created in last tutorial
	  $password = "password"; //created in last tutorial

	  // Create connection
	  $conn = mysqli_connect($servername, $username, $password);
	  // Check connection
	  if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	  }
	  echo "Connected successfully";
	?>

 

Close the Connection

Closing a connection to a database is quite easy. If you’ve used a file handle, as above, you just do this:

Close Connection
      mysql_close( $conn );

 

Create a MySQL Database

The following examples create a database named ‘MyDatabase’.

Create MySQL Database
	<?php
	  $servername = "localhost";
	  $username = "username";
	  $password = "password";

	  $conn = new mysqli($servername, $username, $password);
	  if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	  } 

	  // Create database named 'MyDatabase'
	  if ($conn->query("CREATE DATABASE MyDatabase") === TRUE) {
		echo "Database created successfully";
	  } else {
		echo "Error creating database: " . $conn->error;
	  }

	  $conn->close();
	?>

 

Create a MySQL Table

Since our database is created now it’s time to add some tables to it. The CREATE TABLE statement is used to create a table in MySQL database.

Create MySQL Table
	<?php
	  $servername = "localhost";
	  $username = "username";
	  $password = "password";
	  $dbname = "MyDatabase";

	  // Create connection
	  $conn = new mysqli($servername, $username, $password, $dbname);
	  // Check connection
	  if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	  } 

	  // sql to create table
	  $create = "CREATE TABLE Employee(
		id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
		first_name CHAR(30) NOT NULL, 
		last_name CHAR(30) NOT NULL, 
		email_address VARCHAR(50))";

	  if ($conn->query($create) === TRUE) {
		echo "Table tbltable created successfully";
	  } else {
		echo "Error creating table: " . $conn->error;
	  }

	  $conn->close();
	?>

Notice that each field name is followed by a data type declaration; this declaration identifies what type of data the field can hold, whether string, numeric, temporal or Boolean. MySQL supports a number of different data types.

 

Field TypeDescription
INTA numeric type that can accept values in the range of -2147483648 to 2147483647
DECIMALA numeric type with support for floating-point or decimal numbers
CHARA string type with a maximum size of 255 characters and a fixed length
VARCHARA string type with a maximum size of 255 characters and a variable length
TEXTA string type with a maximum size of 65535 characters
DATEA date field in the YYYY-MM-DD format
TIMEA time field in the HH:MM:SS format
DATETIMEA combined date/time type in the YYYY-MM-DD HH:MM:SS format

Muhsin Yakkiparamban

Muhsin Yakkiparamban is the co-founder of Lauyou Learning. Muhsin holds Bachelor Degree in Civil Engineering from KMCT Calicut.

You may also like...