PHP-MySQL tutorial -basics

This article or section is a stub. It does not yet contain enough information to be considered a real article. In other words, it is a short or insufficient piece of information and requires additions.

Draft

## Contents

* 1 Introduction
* 1.1 PHP and MYSQL
* 2 The MySQL extension
* 2.1 Connecting to a database
* 2.2 Sending a SQL statement
* 2.3 Processing of results and HTML output
* 2.4 Dealing with errors
* 3 Discussion of two small complete code examples for the MySQL Interface
* 3.1 A simple survey application
* 3.2 Generating a HTML table
* 3.3 Processing a result using field names with mysql_num_rows()
* 3.4 Workthough example: Guestbook

## Introduction[edit | edit source]

This is a beginners tutorial for PHP and MySQL, old style programming.  PHP examples will certainly not work with PHP7 . This page was made in 2010 from (10 year old) slides and would need some extra work and that may never happen, sorry. - Daniel K. Schneider

Objectives

* Understand the "traditional" low-level PHP-MySQL interface
* Learn how to connect to a MySQL database and send SQL commands
* Understand how to extract information from the results

Prerequisites

* Some PHP
* SQL and MySQL tutorial
* Some HTML, e.g. read the HTML and XHTML elements and attributes tutorial

Next steps

* PHP - MySQL - XML tutorial - basics (create XML as result of db queries and render it with XSLT)
* XQuery tutorial - basics (if you have interest in XML databases)

### PHP and MYSQL[edit | edit source]

PHP can interact with most RDBMS.

To interact with MySQL a PHP 5.2+ developer has three options. Let's shortly have a look them.

(1) PHP's MySQL Extension is the "traditional API" and works with all MySQL version in use (as of Jan 2010). "This is the original extension designed to allow you to develop PHP applications that interact with a MySQL database. The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available."(Overview, PHP Manual, retrieved 18:58, 6 February 2010 (UTC)) However, as of early 2010, it seems to us, that most CMS still use this older interface, probably in order to support older PHP/MySQL installations...

(2) PHP's mysqli Extension adds extra functionalities. "The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later. The mysqli extension has a number of benefits, the key enhancements over the mysql extension being: Object-oriented interface [(but also a procedural one)], Support for Prepared Statements, Support for Multiple Statements, Support for Transactions, Enhanced debugging capabilities, Embedded server support."(Overview, PHP Manual, retrieved 18:58, 6 February 2010 (UTC))

(3) The PHP Data Objects (PDO) is an abstraction layer. "PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code. [...] While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn't allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL's support for Multiple Statements." (Overview, PHP Manual, retrieved 18:58, 6 February 2010 (UTC)) Alternatively, many 2rd party abstraction layers exist.

The principle shortly :

* In PHP, SQL queries are built (ususally as a result of user request)
* These queries are sent by PHP to a MySQL server
* MySQL returns a result datastructure
* Thes results from MySQL are then arranged an inserted into an HTML page.

## The MySQL extension[edit | edit source]

A complete documented example:

* application

(Sorry, the application itself is currently locked since we can't cope with spamming ...)

To show all records: dump_results.phps

To add records we need two scripts:

`new-entry.php` produces the HTML form
`insert-entry.php` inserts the values from the forms an gives some feedback.
Add a record (new-entry.phps and insert-entry.phps)

Same principle for editing:

`edit-entry.php` produces the form
`replace-entry.php` replaces old values by new ones.
Edit a record (edit-entry.phps and replace-entry.phps)
Kill a record (delete-entry.phps)

### Connecting to a database[edit | edit source]

When interfacing a PHP script to a database, you first have to connect to a server and then select the database. Connecting to a database is fairly easy.

Information you need:

1. the host name of the database (usually "localhost", meaning that the MySQL server sits on the same machine as your web server)
2. the database user name and password

Syntax for a persistent connection:

mysql_pconnect(host, username, password);

Example:

$link = mysql_pconnect("localhost","clavel","secret");

$link is a so-called link identifier and will contain the link to the open connection (or "FALSE" if something went wrong).

Then you need some code for selecting a specific database. Since a MySQL server hosts several databases, you need to tell which one you will use.

Syntax:

mysql_select_db(dbname, [linkID]);

Example:

mysql_select_db("demo");

### Sending a SQL statement[edit | edit source]

Once you are connected to a database through a MySQL server, you can send SQL statements using the mysql_query function. This call will return a resource identifier that points to a result. The PHP manual defines a resource as "a special variable, holding a reference to an external resource. Resources are created and used by special functions." In the case of MysQL, the resource is some kind of table that includes columns and rows. As we shall see below, you then have the choice of a series of special functions to retrieve data from it.

Example:

$result = mysql_query("SELECT * FROM demo1");

`$result` is a so-called resource, i.e. a special variable, holding a reference to an external resource. It can be used like a "boolean" to test if a result was found.

A resource (TRUE) if ok
0 (FALSE) if there was a problem.

### Processing of results and HTML output[edit | edit source]

There exist several ways of dealing with the result. We first will show how to deal with the results "row by row" using mysql_fetch_row(), and mysql_num_fields().

Let's firs have a look at the definition of these functions:

(1) Mysql_fetch_row returns a line of the result as an array (a list of values). If you call it again, it will return the next line

Mysql_fetch_row syntax:

mysql_fetch_row(''resource'')

Example:

$result = mysql_query("SELECT * FROM demo1")
$row = mysql_fetch_row ($result);

(2) mysql_num_fields() returns the number of fields (columns) found in the result.

Syntax:

mysql_num_fields(resource)

Example use of mysql_num_fields:

$nb_fields = mysql_num_fields($result);

Below is a complete example that uses these two functions to retrieve results line by line (row by row) and cell by cell. This code produces an HTML table.

<?php
$link = mysql_connect( "localhost", "nobody", "")  or  die ("Unable  to  connect  to  SQL  server");
mysql_select_db("demo", $link)  or  die ( "Unable  to  select  database");
$result  =  mysql_query( "select  *  from  demo1  limit  100");

<table  border="1">
<?php
while  ($row  =  mysql_fetch_row($result))  {
echo  "<tr>";
for  ($i=0;  $i<mysql_num_fields($result);  $i++)  {
echo  "<td>";
echo  "$row[$i]";
echo  "</td>";

echo  "</tr>";

</table>

### Dealing with errors[edit | edit source]

The minimal precautions you should take is make sure that data base connection and the SQL query went ok, else you should abort execution of the script. The PHP die() function will exactly do this.

$DB_link = mysql_connect($host, $user, $pass) or die ("Could not connect to host.");
mysql_select_db($database, $DB_link) or die ("Could not find or access the database.");
$result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");

For most mysql operations, there can be errors and PHP allows to display error messages generated by the MySQL server. You can use the following PHP functions to display more information.

mysql_errno(resource)

returns a number code for the last mySQL operation or zero (0) if not error was found.

mysql_error(resource)

returns an string with some extra information.

Here is simple error handling example

<?php
// next line is correct
$link = mysql_connect("localhost", "mysql_user", "good_password");

// next line includes a bad database name
if (!mysql_select_db("nonexistentdb", $link)) {
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

// next line is correct
mysql_select_db("good_db", $link);

// next line includes a bad table name
if (!mysql_query("SELECT * FROM nonexistenttable", $link)) {
echo mysql_errno($link) . ": " . mysql_error($link) . "\n";

For the two errors you would see something like this:

1049: Unknown database 'nonexistentdb'
1146: Table 'good_db.nonexistenttable' doesn't exist

Remark: You also can test SQL expressions with a database administration tool.

## Discussion of two small complete code examples for the MySQL Interface[edit | edit source]

Below, we will discuss some more features and functions of the PHP/MYSQL interface. You also may have a look at the older source code (made over 10 years ago ....)

### A simple survey application[edit | edit source]

The life example itself is locked because it could be used for spamming: dump_results_demo.php

The source code however is available:

http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/

### Generating a HTML table[edit | edit source]

Source: [1]

The ''mysql_fetch_field()'' function allows to retrieve a field (column) name. We use this to fill in the HTML table headings.

$link = mysql_connect( "localhost", "nobody", "")  or  die( "Unable  to  connect  to  SQL  server");
mysql_select_db("demo", $link)  or  die ( "Unable  to  select  database");
$result  =  mysql_query( "select  *  from  demo1  limit  100");

<table  border="1">
<tr>
<?php
// 
- print the header line, extract the name from each field
while  ($field=mysql_fetch_field($result))  {
echo  "<th>";
echo  "$field->name";
echo  "</th>";

echo "<th>Edit</td>\n";
echo "<th>Delete</td>\n";
echo  "</tr>";

// 
-- print the table fields

while  ($row  =  mysql_fetch_row($result))  {
echo  "<tr>";
for  ($i=0;  $i<mysql_num_fields($result);  $i++)  {
echo  "<td>";
// test if this is the URL
if ($i == 4) {
// echo  "<a href=\"$row[$i]\">$row[$i]</a>";
echo  "CENSORED because of spam";

else {
echo  "$row[$i]";

echo  "</td>";

// 
- add edit/delete buttons
echo "<td><a href=\"edit-entry.php?rowid=".$row[0]."\">Edit</a></td>\n";
echo "<td><a href=\"delete-entry.php?rowid=".$row[0]."\">Delete</a></td>\n";
echo  "</tr>\n";

echo  "</table>";

### Processing a result using field names with mysql_num_rows()[edit | edit source]

(1) The mysql_result() function allows to access any cell in the result set. It may be somewhat easier to use than mysql_fetch_row() but it is not as fast.

Typical syntax:

mysql_result (resource, row, field)

`row` holds the record number. Indexation starts at 0 ! (zero).
`field` is the name of the field (column).

Example:

$name = mysql_result($result,0,’fullname’);

(2) The mysql_num_rows() function gets the number of rows in a result.

Syntax:

mysql_num_rows(resource);

Example:

$nb_records = mysql_num_rows($result);

Returns number of returned records in $result

HTML generation using field names example

http://tecfa.unige.ch/guides/php/examples/mysql-demo-source/dump_results_demo2.php
dump_results_demo2.phps
dump_results_demo2.source

<?php
mysql_pconnect( "localhost", "nobody", "")  or  die( "Unable  to  connect  to  SQL  server");
mysql_select_db("demo")  or  die ( "Unable  to  select  database");
$result  =  mysql_query( "select * from demo1");

<table  border="1">
<?php
$i = 0;
while ($i < mysql_num_rows($result)) {
echo  "<tr>";
echo  "<td>";
echo mysql_result($result,$i,’id’);
echo  "</td>";
echo  "<td>";
echo mysql_result($result,$i,’fullname’);
echo  "</td>";
echo  "<td>";
echo mysql_result($result,$i,’love’);
echo  "</td>";
echo  "<td>";
echo mysql_result($result,$i,’sports’);
echo  "</td>";
echo  "</tr>";
$i++;

echo  "</table>";

### Workthough example: Guestbook[edit | edit source]

(unfinished, e.g. translation is needed)

Example files:

Create a `comments` directory in your web server.
Copy the forllowing files: `comments-list.source, comments-insert.source, comments.html` from:
http://tecfa.unige.ch/guides/tie/code/act-php-mysql-FC/

Rename `.source` to `.php !!!`

Structure of the comments table:

http://tecfa.unige.ch/guides/tie/code/act-php-mysql-FC/solution/comments-table.txt

create table comments (
id int(10) default ’0’ not null auto_increment,
nom char(20) default ’’ not null,
prenom char(20) default ’’,
email char(50) default ’’ ,
computer char(10),
browser char(10),
version char(10),
comments char(200),
primary key (id),
key nom (nom)

Task

comments.html displays the form and sends values to comments-insert.php.
comments-insert.php writes data to mySQL. You will have to replace the following values and add some extra code:

$serveur="localhost";
$login="your_login";
$password="your_password";
$database="your_database";
$query_string = " ";
....

comments-list.php will display all comments in a table.
the HTML is funtional, but you will have to fix the 2 php files (comments are in french sorry)

Start with `comments-insert.php` then file `comments-list.php`

Solution:

http://tecfa.unige.ch/guides/tie/code/act-php-mysql-FC/solution/comments.html