You are here:

Using MYSQL with PHP

To enable web designers to create database facilities that are compatible with our Linux servers and Apache Web Server, users can use the software MySQL and run php to enable web access for users. You will need to be familiar with Oracle, sql statements and have general programing knowledge then you will find creating/editing and manipulating tables within mysql very easy. If you wish to read about it first you can go to

PHP Basics
http://uk.php.net/
MySQL functions - table of contents

We offer PHP 3 to all users.

We also have PHP 4 but it has a number of bugs, however you may use it if you report back all errors and bugs. This is under your own responsibility so you cannot blame us if your project does not work! Please check which modules have been compiled in so that you don't start coding for a module that has not been added. Please go to PHP 4 information page . The examples given are using php3 so therefore please insert 4 for php-cgiwrap and for the file extension.
e.g. <a href="/php4-cgiwrap/spider/phpinfo.php4">
or for a refresh code the syntax for example would be
<META HTTP-EQUIV="Refresh" CONTENT="0; URL=/php-cgiwrap/showme/people.php/">

Each user need to be authorised to use mysql and you may apply at the link below. Your database name will be your username and you will get a new mysql password. You cannot change this password and if you loose or forget it you will need to email the The Webmaster for advice.

You will be granted the following rights :-

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Grant_priv
References_priv
Index_priv
Alter_priv

This is an automated application and will be processed overnight and an email sent to the mail address that you provide in the form. If you do not receive an email the next day then you probably gave an incorrect address, please re-apply.

Application for a mysql database

If you wish to convert an existing Access database into mysql please follow the instructions.

How To Convert An Access DataBase to Mysql.

Your database account will be created using your user name , you will of course be able to create as many tables with names of your choice as you wish.

Once you receive your confirmation email you should putty to your Linux account and login in the normal way.
From the server prompt enter the command as sent in the confirmation message; e.g. mysql -p yourusername and press ENTER and enter your mysql password when prompted. If you are successful you will get the mysql> prompt and you are now ready to create your tables and enter data.

For the purposes of this 'getting started' exercise you will:-

from within mysql create a table called book which will contain three fields
from within mysql enter some data into this table
exit from mysql and from the .public_html area create an html file, test it
move to the cgi-bin directory (you will need to create this if you haven't already done so with the command mkdir cgi-bin from your .public_html directory)
move your html file to this directory and rename it using the .php3 file extension
insert the php statement to enable it to use the php facilities.

Please login to your Linux account and proceed from the root directory % prompt:-
% mysql -p yourusername( substitute your lsbu user name and press ENTER and when prompted enter your mysql password)
msyql> create table book (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,title VARCHAR(20), publisher VARCHAR(20),price DECIMAL(6,2));
Query OK, 0 rows affected (0.08 sec)
mysql>

YOU CANNOT PROCEED IF YOU HAVEN'T
CREATED YOUR TABLE CORRECTLY

Now enter say 5 rows of data. You have two choice of entering data

1st method:-
mysql> insert into book (ID,TITLE,PUBLISHER,PRICE)
-> VALUES (NULL,'Long Nights','Waterloo', 3.50);
Query OK, 1row affected (0.07 sec)

repeat this for as many rows you wish to enter but you do not need to specify the id field which will increment automatically
insert into book (TITLE,PUBLISHER,PRICE)
-> VALUES ('Funny Nights','Waterloo ',7.50);
continue with the following rows of data
('Short Nights','Waterloo',2.50);
('Your Good Life','JS Fields',4.75);
('Our Quest','JS Fields',4.50);

if you wish to add multiple rows at one time you can do this separating each set of values by a comma and finishing with a semi colon , for example:-
mysql> insert into book (TITLE,PUBLISHER,PRICE)
->         VALUES ('Your Happy Day','JS Fields',5.50),
->        ('White Years','Waterloo',2.50),
->        ('Blue Days','JS Fields',6.00);
Query OK, 3 rows affected (0.02 sec)

2nd method:-
You can create a plain text which contains as many rows of data that you wish to enter and you can upload into the table directly . You can create this file in your Linux account, or if you create it locally on your hard disk you will need to sftp it into your account in the root directory.

Create a plain text file called for example book.sql which contains the following lines (note each line is as long to the semi colon)

insert into book (TITLE,PUBLISHER,PRICE) values ('Blue Skies','Waters',3.50);
insert into book (TITLE,PUBLISHER,PRICE) values ('Green Fields','Days',4.50);
insert into book (TITLE,PUBLISHER,PRICE) values ('White Lines','Waters',2.50);
insert into book (TITLE,PUBLISHER,PRICE) values ('Brown Trees','Fields',4.75);
insert into book (TITLE,PUBLISHER,PRICE) values ('Red Boxes','Fields',4.50);

If you created this file using note pad for example please sftp it to your account, or else go back to your Linux prompt (or login again if you disconnected from session) and type
mysql -p yourusername <book.sql (substituting your own username and file name if different)

4. Move to your .public_html directory (or web directory for corporate web pages) and now design your html page and test it to make sure it does what you want it do . This following example is called book.html and you can use this to get started if you wish:

(you can see that we need separate table definition for the static and the php part of the html which in this case is for the column headers and the book details which will fetch from the table. )

Test this page (don't' forget you may need to run the command wwwset (personal pages) or webset (for corporate/work sites ) to set all file privileges and if you are happy with it move it into your cgi-bin directory (or lower level if you wish to create a separate directory area for php files) .

You must now rename your file to book.php3 and you need to edit your html file to include the php statements to enable the retrieval of data from the table. Run the command wwwset (personal web pages for staff or students) or webset (corporate pages) again because you have now changed this file from being a plain html file to being an executable script.

The example that follows is compatible for the above html file :
(please insert your own user name and password for identification)

<html>
<body>
<CENTER>
<h2>A DATABASE OF PUBLICATIONS </H2>
</CENTER>
<p>
<table border="1" width="100%">
<tr>
<td width="10%" align="center"><big><strong>item id </strong></big></td>
<td width="30%" align="center"><big><strong>title</strong></big></td>
<td width="30%" align="center"><big><strong>publisher</strong></big></td>
<td width="30%" align="center"><big><strong>price </strong></big></td> </tr>
</table>
<?php
    $mysql_link = mysql_connect ("mysql.lsbu.ac.uk", "myusername", "mypassword");
    $testresult = mysql_select_db ( "myusername" ,$mysql_link);
    $query="select * from book";
    $ mysql_result = mysql_query ($query,$mysql_link);
        if ( mysql_num_rows ($mysql_result)>0){
            print "<table border=\"1\" width=\"100%\">";
            while ($row= mysql_fetch_row ($mysql_result)){
            print "<td width=\"10%\">$row[0]</td>";
            print "<td width=\"30%\">$row[1]</td>";
            print "<td width=\"30%\">$row[2]</td>";
            print "<td width=\"30%\">$row[3]</td>";
                 print "</tr>";
            }
            print "</table>";
        }
?>
</body>
</html>

Please remember to substitute your own user name and mysql password above and NOT TO USE the example given (myusername - mypassword) - ALSO please do not leave any spaces between the quotes and text then Uploaded your script (text and not binary)

CUT AND PASTE - READY TO EDIT
If you would like to create and upload data at the same time, you can create a script file outside of mysql and then simply run it. Download the example file called script.sql for the above table and edit it to suit your definitions and sftp it back to the Linux. From the Linux prompt type mysql yourusername <script.sql and this will create and add the data all at the same time.

if you are writing your own php upload file scripts please note that you must create your own tmp directory in root area of your account otherwise the files will not upload. You also need to install a copy of php.ini in your cgi-bin directory. To do this right click this link and select save as and then ftp the file to the server to your cgi-bin.

SCRIPTS READY TO CUT AND PASTE

php scripts

If you would like other tutorials you are welcome to use Fintan Culwin's Course Materials which are : -

Useful tips and other help sites

Referencing images outside of the cgi-bin directory

Using images with PHP

Remote connection to mysql
Users who connect to mysql remotely ie myodbc should refer to mysql.lsbu.ac.uk

Finally please note that ICT backs up all mysql databases every night however if you wish to do this yourself you can do this by setting up a cron job in your account inserting the appropriate details in the following statement mysqldump -u username -ppassword databasename > filename.dmp (no space between -p and your password)

To insert this data into a new or existing database, putty and logon to your account and from the Linux prompt use the command mysql -u username -p database.name <filename.dmp