School Of Computing And Mathematical Sciences

Programming

Using LJMU software developement tools

 

MySQL

PHP

Using SQL syntax to create Tables

From the command section of the SQLyog you can manually setup tables and enter data using pure SQL syntax the following example is a snippet of SQL syntax used to create a table called president , with 7 fields of varying types :

Syntax : CREATE TABLE tbl_name ( column_specs );

Actual example : 

CREATE TABLE president (
	last_name	VARCHAR(15) NOT NULL,
	first_name	VARCHAR(15) NOT NULL,
	suffix		VARCHAR(5) NOT NULL,
	city		VARCHAR(20) NOT NULL,
	state		VARCHAR(2) NOT NULL,
	birth		DATE NOT NULL,
	death		DATE NOT NULL
	}


Insert Data into a table

From the command section of SQLyog you can enter the values for your tables using the following syntax

Syntax : INSERT INTO tbl_name () VALUES();

Actual example based on the above president table :

INSERT INTO president VALUES(Clinton,Bill,P,Washington,1960,2025);

This will insert data into the corresponding fields within the table, you can also put multiple entries into the table by adding a comma like this : values(),(),() for as many entries as you need.


Using SQL syntax to delete tables

To delete a table a mySQL table use the following syntax :

Syntax : DROP TABLE tbl_name [, tbl_name] ...

In this instance : DROP TABLE president , be aware that this operation is non reversable.

Creating your first SQL-Query

When you need to start doing things with your data like selcting rows or multiple rows for use as reports or such like you can use the SELECT statement :

Syntax : SELECT [ALL | DISTINCT | DISTINCTROW ] FROM [table(s)] WHERE [condition]

This is the SELECT statement at its most basic level , to do more advanced tasks please follow some of the links detailed below or contact a member of technical staff or your lecturer

Actual example based on the above president table :

SELECT * FROM president WHERE last_name = "clinton" ;

This will select all fields from table president with the last name of clinton, you can replace the star with a field list to select specific fields.

Getting started with PHP

A PHP file normally contains HTML tags, just like an HTML file, and some PHP scripting code.

Below, is an example of a simple PHP script which sends the text "Hello World" to the browser window:

<html>
<body>
<?php echo "Hello World"; ?>
</body>
</html>

A PHP script block always starts with <?php and ends with ?>. A PHP script block can be placed anywhere in a HTMLdocument.

Each line of code in PHP must end with a semicolon. The semicolon is a separator and is used to distinguish one set of instructions from another.

There are two basic statements to output text with PHP: echo and print. In the example above the echo statement was used to output the text "Hello World".

Variables in PHP


All variables in PHP start with a $ sign symbol. Variables may contain strings, numbers, or arrays.

Below, the PHP script assigns the string "Hello World" to a variable called $txt:

<html><body>
<?php
$txt="Hello World";
echo $txt;
?>
</body>
</html>

To concatenate two or more variables together, use the dot (.) operator:

<html>
<body>
<?php
$txt1="Hello World";
$txt2="1234";
echo $txt1 . " " . $txt2 ;
?>
</body>
</html>

The output of the script above will be: "Hello World 1234" , this covers the absolute basics of PHP should you need more help follow the links below or contact your lecturer.

Using PhP to connect to a database

To connect to your mySQL account using PHP or other similar scripting language use the following code snippets as your guide

Like this if you are using PEAR database connection. PEAR has many advantages over the standard mysql_* functions 

<?php
include_once('DB.php')
$dbtype = 'mysql'; // the type of database
$dbhost = 'mysql.cms.livjm.ac.uk'; // hostname
// You need to change the following to reflect your MySQL account details
$dbuser = 'cmsdwynn'; // the username
$dbpass = 'ithinknot'; // password
$dbname = 'cmsdwynn'; // database
//
$dsn = "$dbtype://$dbuser:$dbpass@$dbhost/$dbname";
// Connect to database or die with an appropriate error message
$db = DB::connect($dsn);
if(DB::isError($db)) {
	die($db->getMessage());
}
 
?>

PHP also supports Oracle

<?php
$username = "your-oracle-username";
$passwd = "your-oracle-password";
$db="(DESCRIPTION=
           (ADDRESS_LIST=
             (ADDRESS=(PROTOCOL=TCP)
               (HOST=java.cms.livjm.ac.uk)(PORT=1521)
             )
           )
             (CONNECT_DATA=(SERVICE_NAME=o11g))
      )";
$conn = OCILogon($username,$passwd,$db);
if (!$conn)
{
    echo "Connection failed";
    echo "Error Message: [" . OCIError($conn) . "]";
    exit;
}
else
{
    echo "Connected!";
}
 
?>

You can also use the following steps to connect to your database put this at the top of all pages that need database connectivity

<?php
$connection = mysql_connect ("mysql","cmpssmar","password")
 or die ("Sorry - unable to connect to MySQL");
mysql_select_db("cmpssmar")
 or die ("Sorry - unable to connect to database cmpssmar");
echo("Congratulations - you connected to MySQL");
?>

 

Rather than have your database connection code repeated in many files, you can separate out the connection code into another file called dbase.inc say, and in each file that requires a database connection:

<?php
include_once('dbase.inc');
// Rest of your script
 
 
?>

Last modified: 23-Sep-08
Contact Us