//, PHP/Using MySQL and PHP

Using MySQL and PHP

Creating dynamic pages from online databases

This section deals mainly with exploring the
possibilities of combining PHP and MySQL. To check if PHP is setup to
use MySQL on your server, look for a section on MySQL in the phpinfo
file described earlier. The technical details of setting up MySQL on
your system are
covered comprehensively in various text books. See the "Source Code
Books" link below for more information.

Once
configured, PHP uses mysql_connect() to establish a link to the MySQL
manager program on the server. mysql_select_db() then specifies which
database the PHP file can access. Each database can contain numerous
tables. Once connected, PHP can be used to create SQL queries – simple
text strings that describe the kind of operation you require MySQL to
perform on the database. You can create or drop (delete) tables, alter
table structure, insert, update and delete records within a table,
retrieve records, individual fields or selected fields from records
that match specific criteria. Queries can be sorted in alphabetical,
numerical or chronological order – depending on the table and you can
also limit the total number of records matched. Queries can look for
exact matches (e.g. numerical fields) or approximate matches (e.g. in a
text field made up of user comments).

Webhosts vary
in how to configure MySQL, most create the database for you but some
will also help you to create the tables too. To learn more about
creating tables using SQL (and therefore via PHP too), use the linkbar
below to go to the Books section: PHP. The two larger reference books
describe creating SQL tables in detail.

Connecting to the database

PHP needs to know your username and password to connect
to the MySQL database. Remember that the PHP code remains on the server
at all times and your username and password are not revealed unless you
deliberately output them in the resulting HTML. (Not wise!) The
mysql_connect function returns a handler to the connection which you
will need each time you want to query the database, so store the
handler in a suitable variable like $connection. Also, if the
mysql_connect function fails, PHP may give the user a cryptic error
message which will confuse users, so hide these default messages using
@ in front of the function call and using " or die" to output a
sensible error message:

$connection = @mysql_connect("localhost",
"username","password") or die ("Could not connect!");

The
$connection variable is now used to select the database to use. You
will usually only have access rights to one database on a live internet
site although you can configure as many databases as you need for use
on an intranet site if you are the system administrator. Each database
can contain multiple tables and all tables within the database are
accessible from the one database connection, so it makes sense to make
multiple tables within the one database instead of opening and closing
database connections to connect to tables in separate databases. Store
the returned handler for use in debugging your scripts and use @ and
"or die" to handle errors cleanly:

$db = @mysql_select_db($dbname, $connection) 
or die("Could not select a valid database.");

On
many webhosts, the name of the database ($dbname) will be the same as
your username – one user, one database. On intranets, the database name
is up to you. Note that you haven’t made a connection to a table yet.

The
simplest connection to a table is to list the entire contents of the
table. Create a variable $sql to hold the text string that will
describe the query to make:

$sql = "SELECT * FROM $tablename";

Capitals
are only for the purposes of demonstration although some programmers
retain capitals to discriminate between SQL commands and data. It makes
no difference to the results. The * means all columns, not all rows.
i.e. all fields will be returned. You can limit the fields returned by
stating the fields by name, separated by commas –

SELECT id,name,email FROM $tablename;

etc. You can limit the number of rows returned (the actual number of
data records) by using statements like WHERE to only match those
records you are interested in. e.g.

SELECT * FROM $tablename WHERE id = 5

will display ALL fields for only those records where the value of the
field named id is equal to exactly 5. Remember this difference between
fields and values – the SELECT statement must contain either a list of
fieldnames or * but will return results from all records in the table
by default.

Getting and displaying results

After connecting to MySQL, selecting the database and
constructing your first SQL query, you need to obtain, process and
display the results. Use mysql_query to send the contents of the $sql
statement over the existing $connection and store the results as an
array. (Each record returned is one row in the array and each field
returned is one column in the array.)

$result = @mysql_query($sql, $connection) 
or die ("Could not execute query");

In many cases, your query will result in more than one record being
returned and often more than one field. PHP deals with this using a
while loop and the mysql_fetch_array function:

while ($row = mysql_fetch_array( $result)) {
// process each row in turn here
}

The
$row variable contains a hash (a paired list of data names – called
keys – and data values) where each value is identified by the field
name. e.g. to set the value $id equal to the value of the id field in
the current record, insert

$id = $row['id'];

into
the {} brackets in the while loop above. To store the output before the
loop moves on to the next record, store the value of $id in a string
that can be output as HTML and use the .= operator (fullstop+equals) to
add to existing contents of the string instead of clearing the string:

$htmloutput .= "<p>Id is set to $id</p>\n"; 

You can now use echo to display the results of the loop. The completed loop looks like:

while ($row = mysql_fetch_array( $result) {
// process each row in turn here
$id = $row['id'];
$htmloutput .= "<p>Id is set to $id</p>\n"; }
echo "<html> <body>\n$htmloutput</body> </html>";

This should output HTML code to the browser along the lines of:

<html> <body> 
<p>Id is set to owner</p>
<p>Id is set to guest</p>
<p>Id is set to anonymous</p>
</body> </html>

To
extend the HTML and make a more usable page out of the output, create a
new variable (e.g. $htmlblock), to contain large chunks of HTML code
like meta tags, titles, navigation bar(s), images etc. You can either
create two blocks – one above $htmloutput and one below – or create one
block and refer to $htmloutput within the $htmlblock variable to push
the output into the final page where you want it to appear. (Just like
the echo statement above, you simply refer to $htmloutput within the
$htmlblock string.) Do make sure you set the value for $htmloutput
before you refer to it in $htmlblock.

Adding and updating records

The SELECT * statement retrieves data from the table
with each field listed in the same order as the table itself. When
adding new records, values to be entered must be in the same sequence.
You can enter a specific string or the value from a PHP variable. If
you need to omit a field (e.g. an auto-increment field used for the
index field) use NULL:

INSERT INTO $tablename VALUES ("NULL","field2value","$field3");

Remember to escape the " marks if you create an insert statement within a $sql="" statement in PHP:

$sql = "INSERT INTO $tablename 
VALUES (\"NULL\",\"field2value\",\"$field3\")";

The
update statement requires more caution: If used alone, it will modify
ALL records with one command. e.g. the following SQL statement, if put
into a query, would set all firstnames to Fred:

UPDATE $tablename SET firstname = "Fred"

To
set only Fred’s record to firstname=Fred, you must specify that it only
applies to his record. e.g. if the database contains a unique field
called ‘id’ and the value for id in Fred’s record is 84:

UPDATE $tablename SET firstname = "Fred" WHERE id=84

To
update more than one field in Fred’s record with one query, create a
list as in the INSERT command above. If you need to specify Fred’s
record using his surname or other text string, use LIKE to match the
surname.

UPDATE $tablename SET firstname = "Fred",status="sacked" 
WHERE surname LIKE "Flintstone"

Don’t
let LIKE fool you – used without modifiers it looks only for an exact
match. Fred Stone won’t be affected, neither would Fred Flintstoner. To
match these records, the statement could be changed to:

UPDATE $tablename SET firstname = "Fred" WHERE surname LIKE "%stone_"

%
matches any and all characters (like the * used in DOS/Windows),
including nothing, and _ matches only a single character. %stone_ would
therefore also match Betty Waterstoner and Billy Stoner. For this
reason, always try to design your tables to include a unique number
field like id – make it auto_increment and the ‘primary key’ (main
indexed field for the table) to guarantee it remains unique.

2010-05-25T22:52:32+00:00 November 10th, 2007|MySQL, PHP|0 Comments

About the Author:

Leave A Comment