Request a Call back

Essential Steps for Creating MySQL Database using PHP

Niral Modi

October 7, 2013

PHP or Hypertext Preprocessor is a feature-rich web programming language used for developing attractive websites with dynamic content that can interact with the database. While PHP supports the front end user interface and functionality development for a website, database provides a platform for storing, retrieving and managing the data at the back-end for the use of website and its users.

MySQL is an open source database platform capable of handling lots of data and database connections. With the knowledge of PHP and MySQL, one can develop the most powerful, fast and scalable web applications which provide clean and simple usability to the users.

There are a few simple steps for creating MySQL database using PHP.

First of all, install the latest version of MySQL on your PC or laptop.

Secondly, the admin privilege would be required in order to create a new database.

You can create a database using mysql_query function.

Syntax of mysql_query function is

bool mysql_query( sql, connection );

This would return true on success and false on failure. Usually two parameters are passed within this boolean function. One is the query passed for creating the database and other is the connection resource of the database. Being an optional parameter, if connection is not specified here then the last opened connection by mysql_connect will be used.

Wondering what is mysql_connect? mysql_connect is a function through which a connection is established to the database. This function normally takes five parameters as input (all are optional) and returns a MySQL link identifier when connection is established successfully or the “false” on failure to establish the connection.

Here “server” represents the host name that runs the database. By default this value is “localhost:3306” while the “user” and “passwd” are username and password required for accessing the database. The new_link is a parameter specified in case if user wants make a second call to the database. In this case new connection is not established and the identifier of already created connection is returned. MYSQL_CLIENT_SSL, MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE and MYSQL_CLIENT_INTERACTIVE are the constants you can specify for client flags in the last parameter of mysql_connect.

Example:

<?php

$db_host = ‘localhost:3036’;

$db_user = ‘training’;

$db_passwd = ‘training123’;

$connection = mysql_connect($db_host, $db_user, $db_passwd);

if(! $conn)

{

echo “Not Connected”;

}

echo “Connected successfully”;

$sql_query = ‘CREATE Database sample_db;

$retval = mysql_query( $sql_query, $connection );

if(! $retval)

{

echo “Could not create database”;

}

echo “Database sample_db created successfully”;

mysql_close($conn);//this closes the database connection after the process

?>

You have to now populate your database with appropriate data and for this you have to create database tables as database stores everything in structured tabular format.

To create table, you will once again require the mysql_query function. This would be same as database creation but the only difference would be in the query that is passed in first parameter of mysql_query(). Here instead of database creation query ‘CREATE Database database_name’, here table creating query ‘CREATE TABLE table_name (column_name column_type)’ will be passed.

Example:

<?php

$db_host = ‘localhost:3036’;

$db_user = ‘training’;

$db_passwd = ‘training123’;

$connection = mysql_connect($db_host, $db_user, $db_passwd);

if(! $conn)

{

echo “Not Connected”;

}

echo “Connected successfully”;

$ sql_query = ‘CREATE TABLE trainee ( ‘.

‘trainee_id INT NOT NULL AUTO_INCREMENT, ‘.

‘trainee _name VARCHAR(20) NOT NULL, ‘.

‘trainee _address VARCHAR(20) NOT NULL, ‘.

‘joined_course VARCHAR(30) NOT NULL, ‘.

‘join_date timestamp(14) NOT NULL, ‘.

‘primary key (trainee_id ))’;

mysql_select_db(‘ sample_db’);

$retval = mysql_query( $sql_query, $connection );

if(! $retval)

{

echo “Could not create database”;

}

echo “Database sample_db created successfully”;

mysql_close($conn);

?>

Hence, you have now got a database by name “sample_db” having one row of data in it. Similarly you can add much more data into your database by adding more tables to it like the one shown above and hence perform data managing functions on MySQL database using PHP.

Those who want to learn the procedure for implementing PHP and MySQL concepts in creation of real websites or web applications should join a PHP training course backed by live projects. PHP live project training with MySQL will give actual PHP programming experience to the learner as well as is the best way to improve one’s job profile for better career opportunities. This kind of training will give the learner a better idea of how PHP and MySQL programming is done in the real time for creation of dynamic content found on the websites and therefore will improve his/her chances of employment. Hence, select a good PHP training institute and join an appropriate course to master the skills of PHP development.