By A Web Design

PHP / MySQL - at the Command Prompt

Before you attempt to execute any PHP program, ensure that the path php.exe (i.e. the PHP runtime) installed on your computer has been registered in the system settings, PATH variable of your computer. Here is how you can do this.

Setting the path to the PHP runtime in Windows XP

Right click My Computer on the desktop and go to Properties as shown in Diagram 1. The System Properties window opens as shown in Diagram 2.

Diagram 1

Then traverse - My Computer > Properties > Advanced > Environment Variable > System Variable – Path.
Click on Edit as shown in Diagram 2 and Diagram 3 and Add C:\php; as shown in Diagram 4.

Diagram 2

Diagram 3

Diagram 4

The System settings in Windows XP are complete.

In a previous tutorial, we have learnt how to execute a PHP program using a Browser i.e.interaction between PHP and MySQL. Here we will learn how to execute the same PHP files in a command window at the command prompt.

Command line PHP script execution provides a quick way of testing PHP codespec without setting up a virtual domain and using a Web server to execute the PHP codespec. This helps you perform "build" and “test” tasks quickly and easily.

Creating PHP files:

Creating PHP file For MySQL Connection:

Go to any convenient logical drive on your hard disk and create a folder named studentdetail. In our example we’ve used G:\and within the drive created a folder named studentdetail as shown in Diagram 5.

Diagram 5

In the studentdetail folder, create a PHP file named config.php using any ASCII editor such as Notepad, Dreamweaver, Editplus or your favorite ASCII editor, as shown in Diagram 6. config.php will contain the codespec as shown in Example 1 below.

Diagram 6

NOTE: It is a good practice to keep your MySQL database connection details in a single file for ease of maintenance.

It is necessary to connect to MySQL database engine running in memory. To be able to do this from within the PHP code spec you must make available a User name, Password and exactly where the MySQL db engine is running. In this case ‘localhost’ indicates that the MySQL db engine is running on the same computer in which the PHP code snippet is being executed.

Example 1:

 
<?php
$user = "root";
$pwd = "passwd";
$host = "localhost";
$con = @mysql_connect($host, $user, $pwd) or die("Connection Failed!");
if($con)
{
  echo "Connection Established!";
} 
?>

Copy paste the above codespec into the file named config.php and save this file in:
G:\studentdetail

There is a minimum of error handling added to the PHP code within config.php. If the connection to MySQL succeeds, a message “Connection Established!” will be displayed on the Command Window.

Creating PHP File for MySQL Database:

In the studentdetail folder, create a php file named createdb.php using any ASCII editor such as Notepad, Dreamweaver, Editplus or your favorite ASCII editor, as shown in Diagram 7. createdb.php will contain the codespec as shown in Example 2 below.

Diagram 7

To create a MySQL database (i.e. studentdb,), it is necessary to connect to MySQL database engine running in memory. To be able to do this within the PHP code spec include the config.php file once. The codespec to include the config.php file in createdb.php is as given in Example 2.

The include_once() statement, as given in Example 2, includes and evaluates the specified file during the execution of the script. This is a behavior similar to the include() statement, with the only difference being that if the code from a file has already been included, it will not be included again. As the name suggests, it will be included just once.

Example 2:

 
<?php
include_once('config.php');
$qry="create database studentdb";
$result=mysql_query($qry);
if($result)
{
  echo("The MySQL Database Successfully Created!");
}
else
{
  echo("The MySQL Database Creation Failed!");
}
?>

Copy paste the above codespec into the file named createdb.php and save this file in:
G:\studentdetail

There is a minimum of error handling added to the PHP code within created.php. If the connection to MySQL and the database creation succeeds, a message “The MySQL Database Successfully Created” will be displayed on the Command Window.

If the connection to MySQL fails, a message “Connection Failed!” will be displayed on the Command Window.
If the connection to MySQL database creation fails, a message “The MySQL Database Creation Failed” will be displayed on the Command Window.

After creating the MySQL database - studentdb, which is an empty container, it is necessary to populate this empty container with table structure(s) to hold user data which can in turn be manipulated using PHP code.

The first table we are going to create using PHP code within the MySQL database – studentdb is the table StudentMaster. This MySQL table will be populated with student data to be manipulated later on demand using PHP codespec. Let’s take a look at the structure of the MySQL table StudentMaster:

The Structure Of The MySQL StudentMaster Table:

Table Definition:

Table Name:

StudentMaster

Column Definition:

Column Name	Data Type	Width
StudentID	INTEGER	3
StudentName	VARCHAR	25
StudentDegree	VARCHAR	25
Speciality	VARCHAR	35
DOB	VARCHAR	15

Table Description:

Column Name	Description of column contents
StudentID	Holds an identity number of the student
StudentName	Holds the name of the student
StudentDegree	Holds the degree acquired by the student
Speciality	Holds the specialty of the student
DOB	Holds the Date of birth of the student

Creating PHP File for MySQL Database Table:

In the studentdetail folder, create a php file named studentmaster.php using any ASCII editor such as Notepad, Dreamweaver, Editplus or your favorite ASCII editor, as shown in Diagram 8.

Diagram 8

Example 3:

 
<?php
include_once('config.php');
$db = "studentdb";
mysql_select_db($db) or die("Could not select database!");
echo "The MySQL Database Connection Established!";
$query = "CREATE TABLE StudentMaster(
StudentID INTEGER(3),
StudentName VARCHAR(25),
StudentDegree VARCHAR(25),
Speciality VARCHAR(35),
DOB VARCHAR(15))";
if (@mysql_query($query))
{
  echo("Table created successfully.");
}
else
{
  echo("Error creating table");
}
?>

Copy paste the above codespec into the file named studentmaster.php and save this file in:
G:\studentdetail

If the MySQL connection gets established, a message “Connection Established!” will be displayed on the Command Window.

There is a minimum of error handling added to the PHP code within studentmaster.php. If the database connection is established, a message “The MySQL Database Connection Established!” will be displayed on the Command Window. It will then create a table in a MySQL Db engine running in memory.

If the query is executed successfully, it will display “Table created successfully.” on the Command Window.

Executing PHP file at Command Prompt:

To run this file on the command prompt, go to Start -> Run. A window pops up.

Next, in ‘Open:’ , type in cmd as shown in Diagram 9.

Click on OK as shown in Diagram 9.

Diagram 9

Next, type in the path of the file or the drive in which your file is saved and press Enter as shown in Diagram 10.

For example:
<system prompt> G: [Enter]

You will notice that the prompt has now changed. This indicates that you are now in G:\ drive.

Diagram 10

To take a look at the files in this directory at the < System Prompt > enter dir and press Enter.

<system prompt> dir [Enter]

A list of files is displayed as shown in Diagram 11.

NOTE: The dir command displays the files in the current directory.

Diagram 11

Navigate your prompt where your files are located as shown in Diagram 12.

Diagram 12

In order to create database, you need to run createdb.php at the command prompt.
Enter php followed by the <filename.ext> at the command prompt as shown in Diagram 13.

<system prompt> php createdb.php [Enter]

As soon as you click on [Enter], the output is displayed “Connection Established! The MySQL Database Successfully Created! ”. This means that the MySQL database is created successfully.

Diagram 13

In order to create table(s) inside the database, you need to run studentmaster.php at the command prompt.
Enter php followed by the <filename.ext> at the command prompt.

<system prompt> php studentmaster.php [Enter]

As soon as you click on [Enter], the output is displayed “Connection Established! The MySQL Database Connection Established! Table created successfully. ”. This means that inside the MySQL database (i.e. studentdb), the MySQL table (i.e. studentmaster) has been created successfully as shown in Diagram 14.

Diagram 14

Verifying the Database and Table Is Created:

Log into MySQL as root with appropriate password as shown in Diagram 15. As soon as the system prompt changes to mysql> you are ready to verify the database created.

Diagram 15

Use the SHOW statement to find out what database(s) currently exist on the server as shown in Diagram 16.

mysql> Show databases;

Diagram 16

MySQL responds and displays a list of all the databases, which it controls as shown in Diagram 17.

The studentdb database is successfully created.

Diagram 17

Before working with data held within a table it is necessary to inform the MySQL db engine which database holds the tables. The USE command specifies which database the MySQL db engine should set as active as shown in Diagram 18.

mysql> Use studentdb;

Diagram 18