Oxxus » Tutorials » PHP turorial » Connecting MySQL and PHP

Connecting MySQL and PHP

PHP provides functions for connecting to most of databases systems like MySQL, Oracle, MSSQL, Interbase, dBase etc.

Although there are database systems that are commercial the most often used is MySql one which is an non commercial database system and there fore highly used and implemented.

Manipulating of any database is done by using the SQL, which stands for Structured Query Language thus providing standard syntaxes.

PHP talks very simply to the database and several mandatory requirements has to be met in order to access or use data from the database as explained in details below.

Connection to the database system

Before the connection is started the mysql access should be granted from the database administrator. The "mysql_connect()" function with the server's IP provided, user name and the password are required to connect to the database server.

After the successful connection is made SQL statements can be executed.

Do not forget to close the connection using "mysql_close()" after the required action is done.

This should be done every time you want to manipulate the database, or persistent connection could be used as well.

if(!mysql_connect("localhost", "test",  "test")) //tries to connect to the database server
die("Unable to  connect to the database server!"); //terminates the script, and outputs the error

With the example above the user test with password test will attempt to access the database server which is locate localy.

The localhost however can be the remote ip or domain name of the host where the database exists but the remote access service should be enabled at the server where database exists by the database or system administrator.

For example if the domain name is test.com the above example would be like one below:

if(!mysql_connect("test.com", "test",  "test")) //tries to connect to the database server
die("Unable to connect to the database  server!"); //terminates the script, and outputs the error

In both examples, if the connection is not available or permitted, the message
Unable to connect to the database server! will be displayed within the web page.

Creating database using the PHP

<?php
	$dbhost = 'test.com';
	$dbuser = 'test;
	$dbpass = 'test';
	$conn = mysql_connect($dbhost, $dbuser,  $dbpass) or die ('Error connecting to mysql');
	mysql_create_db('test_db');
	mysql_close($conn);
?>

Accessing created database

<?php
	$dbhost = 'test.com';
	$dbuser = 'test;

	$dbpass = 'test';
	$conn = mysql_connect($dbhost, $dbuser,  $dbpass) or die ('Error connecting to mysql');
	$dbname = 'test_db';
	mysql_select_db($dbname);
	mysql_close($conn);
?>

Creating tables

<?php
	$dbhost = 'test.com';
	$dbuser = 'test;
	$dbpass = 'test';

	$conn = mysql_connect($dbhost, $dbuser,  $dbpass) or die ('Error connecting to mysql');
	$dbname = 'test_db';
	mysql_select_db($dbname);
	$query="CREATE  TABLE user  (username varchar(20), email
	varchar(20))";

	mysql_query($query);
	mysql_close($conn);
?>

Inserting values into table

<?php
	$dbhost = 'test.com';
	$dbuser = 'test;
	$dbpass = 'test';
	$conn = mysql_connect($dbhost, $dbuser,  $dbpass) or die ('Error connecting to mysql');
	$dbname = 'test_db';

	mysql_select_db($dbname);
	$query = "INSERT INTO user (username,  email) VALUES ('test', '[email protected]')";
	mysql_query($query) or die('Error, insert query failed');
	mysql_close($conn);
?>

Accessing data from the tables

<?php
	$dbhost = 'test.com';
	$dbuser = 'test;
	$dbpass = 'test';
	$conn = mysql_connect($dbhost, $dbuser,  $dbpass) or die ('Error connecting to mysql');
	$dbname = 'test_db';

	mysql_select_db($dbname);
	$query = "SELECT username, email FROM user";
	$result = mysql_query($query);
	while($row = mysql_fetch_array($result,  MYSQL_ASSOC))
	{
		echo "Name :{$row['name']}  <br>" .
		"Subject :  {$row['subject']} <br>" .
		"Message :  {$row['message']} <br><br>";
	}

	mysql_close($conn);
?>

Updating data within tables

<?php
	$dbhost = 'test.com';
	$dbuser = 'test;
	$dbpass = 'test';
	$conn = mysql_connect($dbhost, $dbuser,  $dbpass) or die ('Error connecting to mysql');

	$dbname = 'test_db';
	mysql_select_db($dbname);
	$query = "UPDATE user SET email = '[email protected]'". "WHERE user = 'test'";
	mysql_query($query) or die('Error, query failed');
	mysql_close($conn);
?>

Backing up the table from database with it's contents

<?php
	$dbhost = 'test.com';

	$dbuser = 'test;
	$dbpass = 'test';
	$conn = mysql_connect($dbhost, $dbuser,  $dbpass) or die ('Error connecting to mysql');
	$dbname = 'test_db';
	mysql_select_db($dbname);
	$tableName = 'test';

	$backupFile = 'backup/test.sql';
	$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
	mysql_query($query) or die('Error, query failed');
	mysql_close($conn);
?>

Contents