We have so far covered topics like PHP Variables, Arrays, Form Handling in PHP, HTML data Handling, Protecting from hackers, File input/output (I/O), A Web Counter Example.

Today, we’ll cover:

  • PHP and MySQL
  • Starter MySQL
  • Connecting a database with PHP
  • Magic quotes Problem
  • MySQL connection function
  • Analysing that mySQL data
  • Terminating Execution

PHP and MySQL Chemistry

Open source has brought a lot more than Linux to the computing world. It has also given us PHP and MySQL. PHP and MySQL are viewed by many as the world’s best combination for creating data-driven sites. MySQL databases are ideal for storing that data we have collected about a user or for holding user preferences between visits. It is free and it is easy.

MySQL central is http://www.mysql.com/

We’re not going to go through installation of mysql. WAMP has already installed MySQL for us. The diagram below shows the relationship between your user (e.g. IE or Firefox), the scripting language (PHP) and the database (MySQL).

Revisiting SQL (Structured Query Language)

The Query language is the basic method by which data is entered or extracted from a database. It is common to all database systems. It is simply a command to the MySQL database in order to tell it to do something. SQL is a reasonably powerful query language. However it is incredibly simple. You can learn it in a night.

The fundamental SQL commands are:

  • CREATE
  • SELECT
  • INSERT
  • DELETE
  • UPDATE

Creating a Table

Creating a table is very simple in SQL. You need to go to WAMP -> PHPMyAdmin if you want to mess with databases.

CREATE TABLE people (
 first varchar(30),
 last varchar(30),
 address varchar(255)
 );
INSERT INTO people VALUES (‘Barack', ‘Obama', ‘Washington');
 INSERT INTO people VALUES (‘Adolf', ‘Hitler’, ‘Unknown');
 INSERT INTO people VALUES (‘Pervez’, ‘Musharraf', ‘Currently Homeless');

First MySQL-PHP Program

Any how, we have our first PHP-MySQL program that shows all the basic components put into use. Code is given below:

<?php
$db = mysql_connect("localhost", "user", "password");
mysql_select_db("mydb",$db);
$result= mysql_query("SELECT * FROM people", $db);
for($i = 0; $i < mysql_num_rows($result); $i++)
{
  $first= mysql_result($result, $i, "first");
  $last = mysql_result($result, $i,  "last");
  $addr=mysql_result($result, $i, "address")."<br>";
}
?>

You can also specify fields indices (e.g. 0, 1 or 2) instead of field names (e.g. first, last and address) in line 7,8 and 9.

Alternate way

<?
$db = mysql_connect("localhost", "user", "password");
mysql_select_db("mydb",$db);
$result= mysql_query("SELECT * FROM people", $db);
while($row = mysql_fetch_array($result))
{
 echo $row["first"];             //echo $row[0];
 echo $row["last"];              //echo $row[1];
 echo $row["address"] . "<br>";  //echo $row[2];
}
?>

Explanation

Step 1. mysql_connect()

mysql_connect() establishes a connection to a MySQL server. It takes 3 parameters.

  • The address of the database server
  • Your Username for that database user
  • Your password for that database user

$conn = mysql_connect(“address”, “user”, “pass”);

Step 2. mysql_select_db()

In our code mysql_select_db() then tells PHP that any queries we make are against the mydb database.

mysql_select_db(“dbname”,$conn);

We could create multiple connections to databases on different servers. But for now, you’ll only need one database.

Step 3. mysql_query()

Next, mysql_query() does all the hard work.Using the database connection identifier, it sends a line of SQL to the MySQL server to be processed. This is the key command for interacting with the database. In our example the results that are returned are stored in the variable $result.

Step 4 & 5: Iterating through each row and displaying each field

Using mysql_num_rows($result), we iterate through each row, and return the value of the specified fields. Finally, mysql_result() is used to display the values of fields from our query:

mysql_result($result,$row,”first”);

So there we have it. We have successfully executed a simple PHP script to retrieve some information.

Magic Quotes Problem

For example say, we are putting a record into our database – the mysql query might be:

insert into people values (‘Cameron Diaz’); 

In you php this would be sent to the database using mysql_query as follows:

mysql_query( ‘ insert into people values (‘Cameron Diaz’) ‘);

This would cause a parse error. So make sure you use the correct quotes to avoid this.

MySQL Connection Function

Here is a function that automates connecting to a certain database. Save it in a separate file e.g. ConnectToDB.inc

function db_connect()
{
  $result = mysql_connect("localhost", "user", "pass");
  if (!$result) return false;
  if (!mysql_select_db("people")) return false;
  return $result;
}

It is a good practice to write these statements in a function and separate file. Include this file and call the function wherever you want to connect to database.

<?
   include_once("ConnectToDB.inc");
?>

Now we have connected by calling this function, we can access the database to make a query to it. Remember to send a mySQL instruction to the database we use mysql_query(). You can make absolutely any query that you would type into the database command line via PHP in this way.

Practical Example

For example, to create a table from our PHP code you might type:

mysql_query(“CREATE TABLE actors (name varchar(30), age integer)”);

Remember that this is something that you would only want to do once – once the table is created we don’t want to wipe it by accident.

Inserting data

Equally we can populate our tables with INSERT statements via mysql_query()

mysql_query(“INSERT INTO actors VALUES (‘Drew Barrymore’,34) “);

 mysql_query(“INSERT INTO actors VALUES (‘Cameron Diaz’,36) “);

 mysql_query(“INSERT INTO actors VALUES (‘Tom Cruise’,40) “);

These are hard coded examples – but we could be using variables in these statements.

MySQL Select Example

We use a SELECT statement to grab data from a certain table and then put the result into a variable ready to analyze.

$result = mysql_query(“SELECT * FROM actors”);

However now result has all the info we want inside it… you can use any of the following function to access the returned result. mysql_fetch_array() is an extended version of mysql_fetch_row(). In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys. Which you use is up to you. Both functions are pretty similar.

mysql_num_rows()

mysql_num_rows() returns the number of rows in a result set. This command is only valid for SELECT statements.

mysql_query(“SELECT * FROM actors WHERE age<35″);

print mysql_num_rows(). ” actors are younger than 35″;

It’s a great function for when you need to loop round all the results in your query, or just to know how many matches you got.

mysql_affected_rows()

mysql_affected_rows() returns the number of rows affected by the last INSERT, UPDATE or DELETE query associated with. For example:

mysql_query(“DELETE FROM mytable WHERE id < 10″);

print “Records deleted: “.mysql_affected_rows().”<BR>”;  

Note: this function does not work with SELECT statements – only on statements which modify records.

Terminating Execution

There are two ways to stop the execution of a script. The first is using the exit() statement which simply stops the script wihtout returning anything. More useful – especially for bug checking – is the die() command. This language construct can be used to output an error message or execute a function before terminating the script.

die(“Could not execute query”);

This would simply exit the script and send the message to the browser. However you can add a die statement to another using the or command. For example:

mysql_query($query) or die(“Could not execute query”);

Alternatively you can use die to fire off a function. May be you want to email notification to yourself when a major error has occurred or add errors to a log file?

Next >> Lecture 5. More on PHP-MySQL

Tagged with: PHP
 

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 

Looking for something?

Use the form below to search the site:


Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Related News Feeds

Set your Twitter account name in your settings to use the TwitterBar Section.