Accessing Mysql with PHP

MySql is a simple query language for accessing databases. The easiest way to use it, or to learn it, is from a command prompt. Three excellent tutorials for working with command prompts are available:

Developer Connection.
SQL Syntax Paul Dubois
The Analysis and Solutions Company or San Diego State University.

Here we discuss accessing a MySql database using PHP. This can be done providing PHP has been compiled with MySql.

Many PHP functions communicate with MySql, but we will use mysql_query() the most. It sends a MySql query and returns the results in the form of a variable. If we were using MySql with the command prompt, the variable would be displayed on screen. Since we are using PHP, much of our effort will be devoted to turning the results variable into usable code.

PHP code is highlighted in green, MySql code in pink. MySql reserved words are CAPITALIZED -- for clarity, not by necessity.

Getting Started

MySql resides on a server -- the host. Four pieces of information are needed from your server before you begin.

The host name
Your user name
Your password
The database name

The first three allow you to log in.

PHP MYSQL -- Logging In and Out

You must always connect to the host before using MySql. Best practice recommends closing the connection when done.

<?php
mysql_connect('mysql53.secureserver.net', 'user17', 'bojangles');

// queries go here

mysql_close();
?>

The code can be written equivalently as:

<?php
$host='mysql53.secureserver.net';
$userName='user17';
$password='bojangles';
mysql_connect($host, $userName, $password);

// queries go here

mysql_close();
?>

Even if you do not explicitly close the connection with mysql_close(), it will be closed at the ?> which signals the ending of the PHP code. No MySql variables or connections will be carried over to the next segment of PHP code.

Trapping Errors

PHP is parsed for errors before it is run. Thus syntax errors nearly always result in the entire block of code doing nothing. The wise programmer asks PHP for visible output.

echo 'Wow --This script ran.';

Since isolating errors in comment blocks is a tedious process, it is well to practice a bit of PHP before beginning. Always use semicolons; Match up your quotes; And be sure you understand "echo" statements.

Though PHP balks on syntax errors, it lets you make logical errors of all sorts, and it even permits you to misspell function names. At least these errors produce some output for you to figure out.

MySql, is a horse of a different color. It continues right on, when you make an error. It will even tell you what the error was:

mysql_query($query); 
echo(mysql_error()); echo"<br>";

or echo (mysql_error()) doesnt work.

or exit does. BRAAC!!!!

Some folks like to tell MySql to stop when it makes an error:

mysql_query($query) or exit(mysql_error());

When I'm really gritting my teeth, I like to be told whether each query succeeds or fails. Setting the function result to a variable, lets you test for execution of queries.

$result17 = mysql_query($query);
if (!$result17) echo(mysql_error())
 else echo"Successful query";
echo"<br>";

Though error trapping is not included in the example code following, it is always apropos.

How to List Databases

Here we use MySql code for the first time. The MySql code is placed in a string and set equal to the PHP variable $query.

<?php
mysql_connect('myhost', 'myuser', 'password');

$query = "SHOW DATABASES";
$result = mysql_query($query);

while ($row = mysql_fetch_row($result)) {
   echo "$row[0] <br>";}

mysql_free_result($result);
mysql_close();
?>

The output is explained below under "How to get Data from Tables"

How to Select Databases for Use

Listing databases is one of the very few things that can be done in MySql without selecting a database for use. Before any database can be accessed, it must be selected with the statement USE databaseName. First one connects to the hosting server with username and password.

<?php
mysql_connect('myhost', 'myuser', 'password');
$query = "USE mydb";
mysql_query($query);
mysql_close();
?>

Note that the host name, user name, and password are in single quotes, but the database name is not. We will notice shortly that neither are table names, nor fieldnames.

How to List Tables in a Database

<?php
mysql_connect('myhost', 'myuser', 'password');
mysql_select_db('mydb');

$query = "SHOW TABLES FROM mydb  ";
$result = mysql_query($query);
echo(mysql_error());

while ($row = mysql_fetch_row($result)) {
   echo "Table: {$row[0]} <br>";
}
mysql_free_result($result);
mysql_close();
?>

PHP MYSQL -- How to Create a Table

$query="CREATE TABLE names(
  id INT NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY(id),
  firstname VARCHAR(31), 
  lastname  VARCHAR(31),
  city      VARCHAR(31),
  state     VARCHAR(15))";
mysql_query($query);

The table created will be named "names" and it will have five columns or fields.
The names of the fields will be "id", "firstname", "lastname", "city" and "state".
"Id" will be of type integer, while the other fields will be character strings of 16 or 32 characters.
???????????? Was this correct?
We specify that "id" can never be null-- it will have some value, and that it will auto increment.
Then we make it the primary key, which requires a column in which every value is unique.

Information about data types from Developer Zone

MySql does not support a boolean data type. One alternative is to use TINYINT(1) which will allow values from 0 to 9. Another alternative suggested by Corey Jackson is the use of
ENUM('0','1') NOT NULL as a substitute for boolean. Though this permits only values of '0' and '1', they are characters, not numbers.

PHP MYSQL -- How to Copy an Existing Table

$query="

CREATE TABLE rodents LIKE rabbits;
INSERT rodents SELECT * FROM rabbits;
";
mysql_query($query);

The first line creates the table, and the second copies the data.

How to Delete a Table

$query="DROP TABLE users";
mysql_query($query);

How to Show Information about Table Structure

Table fields can be listed with any of the following queries.

SHOW COLUMNS FROM tablename

DESCRIBE tablename

SHOW CREATE TABLE tablename

The first two queries produce the same result. The result variable contains as many rows as columns. Each row is an array describing one field of the table as follows:

0 -- fieldname
1 -- data type
2 -- whether values can be NULL
3 -- whether the column is a key
4 -- default value
5 -- extra information, e.g. autoincrement

Reference

The third method "SHOW CREATE TABLE tablename" gives a more compact form of the same information. It reproduces the command which would be needed to recreate the table.

$query="SHOW CREATE TABLE names";
$result = mysql_query($query);
echo(mysql_error());echo"
";

while ($row = mysql_fetch_row($result)) {
   print_r($row);
   echo"
";
}

More Info on table columns: Developer's Connection

Corrupt MyISam Tables

Reference

How to Change Table Structure

alter table [table name] drop column [column name]
alter table [table name] add column [new column name] varchar (20)

alter table [table name] change [old column name] [new column name] varchar (50)

alter table [table name] add unique ([column name])
alter table [table name] drop index [colmn name] //deletes unique from table

Pantz

The code for changing a column name or column type (or both) is identical. Here the type of column 'time' is changed.

$query="ALTER TABLE musicFiles CHANGE time time VARCHAR(15)";
mysql_query($query) or exit(mysql_error());
echo "Check to verify that the column name (or data type) has changed";

PHP MYSQL -- How to Put Data in Tables

We'll discuss several methods of inserting data into tables, ranging from the simplest to the more complex.

Data As Strings

The This code inserts a row of data into the table "names." Nothing was inserted into the "id" column, because it was set to autoincrement.

$query= "INSERT INTO names (firstname, lastname, city, state) 
     VALUES('Nancy', 'Knox','Madison','Alabama')";
mysql_query($query);

Here again, we see that the table name and field names are not quoted, but that the VALUES must be placed within single quotes.

Data As Variables

$firstname="Nancy";
$lastname="KNOX";
$city="Madison";
$state="AL";
$query= "INSERT INTO names (firstname, lastname, city, state) 
     VALUES('$firstname', '$lastname','$city','$state')";
mysql_query($query);

Not only is MySql code embedded in PHP code, but PHP variables are embedded in the MySql code. The embedded PHP variables are enclosed in single quotes. The single quotes are not PHP code, but MySql code.

Data from Forms

Data entered into forms can be stored in a table.

This form is produced by the following html code which calls the action file "connect5b.php".

<form action="connect5b.php" method="post">
<table>
<tr><td>First Name: <td> <input type="text" name="firstname">
<tr><td>Last Name: <td><input type="text" name="lastname">
<tr><td>City: <td> <input type="text" name="city">
<tr><td>State:<td> <input type="text" name="state">
<tr><td>    <td><input type="Submit" value="Submit Data">
</table>
</form>

When the data is submitted, the action file "connect5b.php" is run, inserting the form data into the table "names".

$firstname="$_POST[firstname]";
$lastname="$_POST[lastname]";
$city="$_POST[city]";
$state="$_POST[state]";
$query= "INSERT INTO names (firstname, lastname, city, state) 
     VALUES('$firstname', '$lastname','$city','$state')";
mysql_query($query);

Data in a File

There are many advantages to loading data from a file directly into a MySql table. This can only be done if you have MySql file privileges. For security reasons, you are unlikely to have such privileges unless you have a dedicated server. Since my web server is shared (inexpensive), I cannot demonstrate the use of files. It can be done with code similar to:

 $query= "LOAD DATA  INFILE './cats.txt' INTO TABLE names";
 mysql_query($query);

Developer Connection provides more details as well as syntax information.

My Attempt to load from a file

Incidentally, you cannot move data from MySql tables into an outfile without file privileges either.

PHP MYSQL -- How to Get Data from Tables

When data is first extracted from MySql, it is in the form of a MySql data structure called a Resource. We place this data structure into a PHP variable which I have called $result. We cannot directly access this data, but several PHP functions have been created to do so. These include:

mysql_num_rows	Returns the number of rows in a result
mysql_num_fields	Returns the number of fields in a result
mysql_fetch_row	Fetches a result row as an enumerated array
mysql_fetch_assoc	Fetches a result row as an associative array

There are a number of "mysql_fetch_ " functions. They fetch one row at a time, each time moving a pointer in the $result variable to the next row.

Getting Data with an Enumerated Array

$query="SELECT * FROM names";
$result = mysql_query($query);

$num=mysql_num_rows($result);
$fld=mysql_num_fields($result);
echo"Number of rows is $num <br>";
echo"Number of fields is $fld <br>";

for ($i=0; $i<$num; $i++){
  $row = mysql_fetch_row($result);
  for ($j=0; $j<$fld; $j++){
      $example[$i][$j]=$row[$j];
  };
};

for ($i=0; $i<$num; $i++){
  for($j=0; $j<$fld; $j++){
  echo ($example[$i][$j].'   ');
  };
  echo"<br>";
};

Getting Data with an Associative Array

$query="SELECT * FROM names";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result)){
   print_r($row);
   echo"<br>";
 }

Choosing Specific Data

Selecting Specific Columns

$query="SELECT name,age FROM example";
$result = mysql_query($query);

Use of DISTINCT

$query="SELECT DISTINCT composer FROM musicFiles";
$result = mysql_query($query);

Using WHERE to get Specific Rows

$query="SELECT * FROM example WHERE name='Nancy Knox'";
$result = mysql_query($query);

Using LIKE to get a Range of Results

Pattern matching is done with LIKE or NOT LIKE. MySql uses two wildcard characters: the underscore _ and the percent sign %. The underscore refers to one character, while the percent refers to any number of characters. Pattern matching is not case sensitive.

$query="SELECT * FROM example WHERE age like '6_' ";
//selects ages from 60 to 69
$result = mysql_query($query);

$query="SELECT * FROM musicFiles WHERE composer LIKE '%$_POST[composer]%' ";
$result = mysql_query($query);

Developer Zone

Using ORDER BY to Order Results

$query="SELECT * FROM example ORDER BY age";
$result = mysql_query($query);

Or for descending order:

$query="SELECT * FROM example ORDER BY age DESC";
$result = mysql_query($query);

Developer Zone

How to Change Data in a Table

Data in a table can be changed with the UPDATE command. The trick is to designate which row you want changed.

UPDATE designates the table, and supervises the process.
SET makes the actual change(s).
WHERE designates the row(s) to be changed.

The first example changes 'column2' to "rabbit" where 'column1' is "Flopsy".

$query="UPDATE table1 SET column2='rabbit' WHERE column1='Flopsy'";

The next example changes the field 'time' in the row where 'fileName' is equal to "mo-ps-9";

$query="UPDATE musicFiles SET time='12:15' WHERE fileName='mo-ps-9'";

How to Delete Rows from a Table

DELETE FROM is used to delete entire rows from a table. The following example deletes all rows where 'column2' equals "guinea pig".

$query="DELETE FROM table2 WHERE column2='guinea pig'";

Maximum, Minimum, Sum and Mean

MySql has four functions that operate on columns:
MAX( )
MIN( )
SUM( )
AVG( )

Thus to find the maximum value of column id:


SELECT MAX(id) FROM $tableName

Counting Rows

The COUNT(*) function can be used to find out how many incidences of a datum occur in a column, e.g. how many pieces are composed by each composer:

$query="SELECT composer, COUNT(*) FROM musicFiles GROUP BY composer";
$result = mysql_query($query);

If the GROUP BY clause is not included, it won't work.

Working with More than One Table

Join
Left Join

More to Do

use [db name];

MySQL - Backup

MySQL - Group By

MySql Tutorial

Privileges

Privileges Table 1-1: Privileges available for use within GRANT and REVOKE commands ALL PRIVILEGES FILE RELOAD ALTER INDEX SELECT CREATE INSERT SHUTDOWN DELETE PROCESS UPDATE DROP REFERENCES USAGE

First Name:
Last Name:
City:
State: