New Users: Quick MySQL

Quick MySQL 3.5-Apollo

So you have played with PHP may even have read the Quick PHP guide and now want to talk to MySQL using PHP. I have written this page purely for orientation with reference to Uniform Server, it is not intended in any way to teach you MySQL you can find better material on the Internet. Its sole purpose is to get you up and running with MySQL on Uniform Server hence it is a very basic introduction.

Where is the on-off switch for MySQL? When you start Uniform Server 3.5-Apollo it automatically starts both the MySQL and Apache servers.

Introduction

MySQL is an open-source DBMS it integrates with PHP they are the worlds most popular duo and effectively drive the Internet. MySQL is a collection of tables with various selectable handlers the default being MyISAM this provides for fast reading with slightly slower writing.

You can administer the MySQL server using a command line interface however Uniform Server includes phpMyAdmin greatly easing this task. This section looks at accessing the server and database using PHP.

Note: Windows 7 users

• For $dbhost use 127.0.0.1 and not localhost

If you wish to use localhost check out MySQL host name 127.0.0.1 or localhost for a solution

Top

Security

The MySQL server is a secure production server however it should be noted that the default installation is insecure this is because the user root name and password are universally know. (Well if it wasn't you would not be able to access your server, now would be a good time to change the MySQL root password ). Note for the examples on this page I have set my root password to fred123 when you see this substitute your own password accordingly.

How to use PHP to connect to MySQL server

Every one has their own way of doing things I will explain my preferred method of connecting to the MySQL server use whatever method you are happy with. The following examples are intended only to confirm operation of the MySQL server.

I prefer to do things in little snippets, when it comes to MySQL I think connect, open, do something and then close. The follow explains why In quick PHP I mentioned include external files in a nut shell I use them to implement my little snippets. Its a personal preference you are free to use whatever you like.

Top

PHP Connect

MySQL is a server in its own right hence it has a host name associated with it. In addition it is an authenticated server meaning in order to allow access to a database it requires a user name and password. PHP uses one function to make a connection:

• mysql_connect($dbhost, $dbuser, $dbpass)

• $dbhost Host name: The server is running locally on Uniserver hence can be either localhost or 127.0.0.1
• $dbuser User name: Uniserver default is root
• $dbpass User password: Uniserver default is root — I will be using fred123

With a connection established it is equally easy to connect to a database with the following PHP function:

• mysql_select_db($dbname)

• $dbname Database name

Once you have finished with the database close it using the following PHP function:

• mysql_close($conn)

• $conn This is an handle (reference) return by the connect function.

Note: When your script ends PHP will automatically close all database connections so strictly speaking you do not have to specifically close the connection.

You can embed the above in your code however when you need to change the connection or database it can become a pain hence I pop these functions into external files (scripts) as follows:

Scripts — Note these are not the final scripts

These are not the final scripts I am slowly building these up over the next few sections.

Create a new text page and name it config_db.php copy the following code into it:

<?php // Database configuration file config_db.php $dbhost = 'localhost'; //server name localhost or 127.0.0.1 $dbuser = 'root'; //User name default root $dbpass = 'fred123s'; //Password default root -- I will be using ''fred123'' ?>

Create a new text page and name it open_db.php copy the following code into it:

<?php // Open database open_db.php $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql'); ?>

Create a new text page and name it close_db.php copy the following code into it:

<?php // Close database close_db.php mysql_close($conn); ?>

Save the above scripts in folder www (path *\Uniform Server\udrive\www)

Using the scripts

Using the scripts is straightforward for example:

Create a new text page and name it test_db.php copy the following code into it:

<html> <title>Test db</title> <body> <p>Quick db test</p> <?php include 'config_db.php'; include 'open_db.php'; //do something to database, etc include 'close_db.php'; ?> </body> </html>

Save the script in folder www Display the page by typing http://localhost/test_db.php into your browser address bar.

Results

With luck you will receive an error messages I trust not because of typos the above was a copy and past exercise.

The warning is due to an error I introduced in config_db.php take a look at line $dbpass = 'fred123s'; it should read $dbpass = 'fred123';

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost' (using password: YES) in W:\www\open_db.php on line 2 Error connecting to mysql

I introduced the error to show you the warning message note the line number and file name they provide a clue where to look for the bug in your code.

Correct the bug (remove the offending s from the password in config_db.php ) and re-run the script, this time there will be no errors. You will see the message "Quick db test".

You can create a database using phpMyAdmin but we do not want to take this easy option lets do it the PHP way.

Top

Creating a Database

In the previous section I implied it was difficult to create a database using PHP. Well I lied it takes only one line of code.

Creating a database

There are several ways to do this I prefer to run a query on the database using the SQL command CREATE DATABASE followed by the database name to be created. Sure you can use one line for this but I like to construct a query string first and then pass it to the PHP function or is that a method who cares its only terminology. The two lines look like this:

• $query = "CREATE DATABASE mpg_test";
• $result = mysql_query($query);

The first line creates a variable $query this is assigned (made equal) to the string in quotes, the SQL command is CREATE DATABASE followed by the name of the database to be created mpg_test.

The second line is interesting, you pop the string $query into the php function mysql_query() and it goes off and creates your database.

Well never trust anyone or functions, always test to see if something happened. If the database was created then you can connect to it. Take this new code snippet, the last line of code checks for a connection it kills the program and displays Cannot select database if it fails to connect.

New code

Change your old test_db.php to this and save it to a new file test2_db.php:

<html> <title>Test db 2</title> <body> <p>Quick db test</p> <?php include 'config_db.php'; include 'open_db.php'; $query = "CREATE DATABASE mpg_test"; $result = mysql_query($query); mysql_select_db('mpg_test') or die('Cannot select database'); include 'close_db.php'; ?> </body> </html>

View the page in your browser you can confirm the database was created by using apanel and running phpMyAdmin (if this is already running refresh your browser to see the new changes). To the left of phpMyAdmin you will see a drop down menu click on the down arrow you will find mpg_test(0) listed note (0) indicates no tables in the database.

Delete a database

Deleting a database is similar to the above use the DROP DATABASE SQL command.

$query = 'DROP DATABASE mpg_test'; $result = mysql_query($query);

In general you do not spend time creating or deleting databases invariably you manipulate a database in the next section I show the final scripts that allow access to a named database

Top

Finished Scripts.

Once you have created a database most of your time will be spent processing its data. I have modified the scripts to reflect this, they now target a specific database.

Scripts

config2_db.php Configuration file now includes the database name.

<?php // Database configuration file config2_db.php $dbhost = 'localhost'; //server name localhost or 127.0.0.1 $dbuser = 'root'; //User name default root $dbpass = 'fred123'; //Replace with your root password' $dbname = 'mpg_test' //Replace with your database name ?>

open2_db.php Connects to server and selects a database

<?php // Open database open2_db.php $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql'); mysql_select_db($dbname) or die('Cannot select database'); ?>

close2_db.php No change:

<?php // Close database close2_db.php mysql_close($conn); ?>

Save the scripts in folder www

Top

Using the scripts

Using the scripts is straightforward the next example shows how to create a contact list table:

Creating a Table

The following shows how to use the scripts to connect to the MySQL server and create a table in the database mpg_test

Save the following as test3_db.php

<html> <title>Test db 3</title> <body> <p>Quick db contacts</p> <?php include 'config2_db.php'; include 'open2_db.php'; $query = 'CREATE TABLE contactlist( '. 'clid INT NOT NULL AUTO_INCREMENT, '. 'clname VARCHAR(50) NOT NULL, '. 'clemail VARCHAR(50) NOT NULL, '. 'clsubject VARCHAR(50) NOT NULL, '. 'clmessage TEXT NOT NULL, '. 'PRIMARY KEY(clid))'; $result = mysql_query($query); include 'close2_db.php'; ?> </body> </html>

The includes (include config_db.php and include open_db.php) open the database.
A query string CREATE TABLE is used to create a table containing a contact list.
Finally the database is closed using include close_db.php

Display the page by typing http://localhost/test3_db.php into your browser address bar. This creates the table contactlist check using phpMyAdmin.

To the left of phpMyAdmin you will see a drop down menu click on the down arrow you will find mpg_test(1) listed note (1) indicates there is one table in the database. From the drop down menu click on mpg_test(1) and the table will display on the right. The table has no data hence records are set to zero.

With the table created adding data into it is again straightforward.

Top

Adding data to table

Adding data to the table created in the example above is accomplished using the INSERT INTO command. This crude example shows two entries:

Save the following as test4_db.php

<html> <title>Test db 4</title> <body> <p>Quick db contacts</p> <?php include 'config2_db.php'; include 'open2_db.php'; $query = "INSERT INTO contactlist (clname,clemail)VALUES ('fred1','fred1@int.com')"; mysql_query($query) or die('Error, query failed'); $query = "INSERT INTO contactlist (clname,clemail)VALUES ('fred2','fred2@int.com')"; mysql_query($query) or die('Error, query failed'); include 'close2_db.php'; ?> </body> </html>

Display the page by typing http://localhost/test4_db.php into your browser address bar. This populates the table contactlist with two records fred1 and fred2 check using phpMyAdmin.

To the left of phpMyAdmin you will see a drop down menu click on the down arrow you will find mpg_test(1) listed note (1) indicates there is one table in the database. From the drop down menu click on mpg_test(1) and the table will display on the right.

Back to the left menu you will see contactlist displayed click on this and and then on browse in the right window top left, this will display the two records and the data entered.

With the data inserted there are several ways to retrieve it.

Top

Reading Data Example 1

Reading data from the database

Save the following as test5_db.php

<html> <title>Test db 5</title> <body> <p>Quick db contacts</p> <?php include 'config2_db.php'; include 'open2_db.php'; $query = "SELECT clname, clemail FROM contactlist"; $result = mysql_query($query) or die('Error, query failed'); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "Name = {$row['clname']} " . "Email = {$row['clemail']} <br>"; } include 'close2_db.php'; ?> </body> </html>

The while() loop will keep fetching new rows until mysql_fetch_array() returns FALSE, no more rows to read. The content of the rows are assigned to the variable $row and the values in row are then printed.

MYSQL_ASSOC the second argument to mysql_fetch_array(), returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.

Display the page by typing http://localhost/test5_db.php into your browser address bar.

When I am faced with all them curly braces it drives me crazy I prefer the next method

Top

Reading Data Example 2

This is my preferred way to read data from a database: It uses the list() function to assign values to a list of variables, this is accomplished in one operation.

Save the following as test6_db.php

<html> <title>Test db 6</title> <body> <p>Quick db contacts</p> <?php include 'config2_db.php'; include 'open2_db.php'; $query = "SELECT clname, clemail FROM contactlist"; $result = mysql_query($query) or die('Error, query failed'); while(list($name,$email)= mysql_fetch_row($result)) { echo "Name :$name " . "Email : $email <br>"; } include 'close2_db.php'; ?> </body> </html>

The list() function assigns the named variables $name and $email in the order returned by mysql_fetch_row(). Now you can use the variables where you like.

Display the page by typing http://localhost/test6_db.php into your browser address bar.

Top

Conclusion

One final example to show how easy it is to mix PHP and HTML save the following as test7_db.php

<html> <title>Test db 7</title> <body> <h2 align="center">Quick db contacts</h2> <?php include 'config2_db.php'; include 'open2_db.php'; $query = "SELECT clname, clemail FROM contactlist"; $result = mysql_query($query) or die('Error, query failed'); ?> <table align="center" cellpadding="6" border="1"> <tr><td><b>Name</b></td><td><b>Address</b></td></tr> <?php while(list($name,$email)= mysql_fetch_row($result)){ echo "<tr><td>$name</td><td>$email</td></tr>"; } include 'close2_db.php'; ?> </table> </body> </html>

The while loop now outputs table rows with each row containing $name and email $email.

The above examples barley scratch the surface they serve as a starting point, I trust they have given you a feel for the power of both MySQL and PHP.

Top

---

Ric