Freelance PHP MySQL Jobs

As far as i know Scriptlance is one of the best place to find freelance programming jobs. Each day you can find about 50 new job postings. Most of the jobs will require advance knowledge on PHP and MySQL like creating a dating website, car rental website, or a shopping cart but some are quite easy like creating a site counter and signup form.

When you are new to Scriptlance it's better if you stick to find these easy jobs first and try to get good reviews. As you go learn how to do the more difficult ones. Check out the job (project) descriptions just to see what kind of jobs in demand and keep it in a list. Because there may be similar jobs in the future you should create a draft on the design (algorithm, database, process, etc). That way when you get enough experience and you see a similar job posted you can complete it faster


If you want to do some freelance jobs here's a little checklist that might help :
Get a clear description of the project. Ask whenever you are unsure about something. Create a demo / mockup whenever possible so you can be sure that you and your client are talking about the same thing.

Can you do it? Seriously, can you complete each and every features requested? Your client won't be happy if you say you can complete the job but fail to do so. One quick way to be sure is to make a prototype before even bidding the project then propose it to your (future) client to see if it is what she expected.

Don't be too optimistic. If you think you can complete a job in one day make sure you can do it in one day. This include all the testing and bug fixing.

Can you accept the payment? Some will want to pay using PayPal if you can accept PayPal it's no problem but if you can't maybe you should consider finding another project.

Ask for the PHP and MySQL version used by your client and develop the project using the same version. This will reduce the risk of creating buggy scripts just because your version and your client's version is different

Test and retest.

Be ready to fix bugs. All software have bugs, but make sure you are ready to fix them when it's found. Your clients will certainly expect a prompt response so give it to them. Even if your script is buggy but if you are prompt in responding and fixing it you can get good reviews.

KYOB
That's short for Kick Your Own Butt ! Freelancing requires hard discipline and because there's no boss or supervisor breathing on your neck it can be hard to feel that you are working. Sure, you still have deadlines, but the client is probably on the other side of the planet and most likely you will never see her face to face. It just doesn't feel the same as an ordinary job. KYOB is probably the most important ability you must master if you really want to be a successful freelancer.

By the way, if you're interested i suggest you read about Site Build It.

From my personal experience you can really earn a substantial income by making a website about something you know and enjoy. Your website doesn't always need to be related to computers, programming, web development or internet. You can make a site related your hobby if you want to. SBI's step by step process really works.

I do feel you really should take a peek on SBI and when you still have doubts just go ask a real humang being about it .

Finding Web Hosting For PHP And MySQL

There are a lot of things to consider when choosing a web hosting company. But one thing for sure is that price is no longer important. Web hosting is a very competitive field so the price just keeps getting lower and lower. It is so easy to find cheap web hosting for PHP and MySQL

The important things to consider when choosing PHP and MySQL web hosting are :

PHP and MySQL versions
If a web hosting company say that they support PHP 4 make sure it's the latest version not the 4.0.1 version. Same thing for MySQL but with an extra precaution. Some webhosting companty only support MyISAM tables, so if you need InnoDB make sure you ask if it's available.

Specific setting / feature
For example, you want to create a PHP script which change a file's permission using chmod(). Guess what, if PHP is run as the server your code won't work and there is nothing you can do about it. This exact thing happen to me with this website. I didn't foresee that i would make such application. Anyway just try imagining what you want to do with your web site and if you are uncertain whether a web host will support a feature, just ask.

Connection speed
All web hosting company claim that they have fast connection speed, but you have to test it to believe it. Use NetMechanic's free service to test the web host company's homepage. If the result is good then the claim is most likely true.

Data transfer
When you just started a website it doesn't matter much. But as your website grows you have to make sure you have enough bandwidth. One or two gigabytes (Gb) per month is more than enough for most web sites

Access to raw log file and online statistics (log file analyzer)
If you are serious in building a website, for commercial purposes for example, this is very critical. You can discover a lot of information from log files like the keywords used to find your website, most visited pages, peak times etc.

Storage space
Measure your own website, start small but leave room for expansion. For a small website 15 megabytes is plenty.

Customer support
They have to be there when you need them, period. Try asking some questions before you decide to go with a hosting company. If it takes more than 24 hours for them to reply then find another host.


For those of you who just want to experiment you can use MySQL PHP free hosting. There are probably hundreds of them out there, you just need to pick one. They usually place banners or other kinds of advertising on you web page and most are slow. Anyway you can't expect much from free services.

Actually even if you just experimenting with PHP and MySQL it's better to have you own web site. Free hosting usually have lots of restriction so you really can't do much experiment like opening a socket connection and stuff like that.

There's this one website that rank ten PHP MySQL website hosting company based on price, quality, performance and features. If you need a second opinion you can go there.

A bit off topic here. If you intend to build an e-business (for yourself or for a client) instead of just a website you should consider about Site Build It. It's an all-in-one site-building, site-hosting, and site-marketing service. It even outperformed Microsoft's bCentral and Yahoo Web Hosting Pro. The only drawback is that the HTML templates provided look a bit lame. But since now you can upload your own template i guess it's not a big deal anymore.

PHP MySQL Image Gallery

On the previous tutorial you already know how to upload and download files to the server. Now we're gonna reuse the codes to build an image gallery. It's a simple image gallery where the admin ( that's you ) is the only one who can add/modify/delete the album and images. The "normal folks" can only browse around the image gallery checking out the images from one album to another

The admin section contain the following :
Add New Album
Album List
Edit & Delete Album
Add Image
Image List
Edit & Delete Image

And the visitor page contain these :
Display Album List
Display Image List
Display Image Detail

Now, before we go straight to the codes we need to talk about the database design, directory layout, and configurations.
Database Design

For a simple image gallery like this we only need two tables, tbl_album and tbl_image. Here is the SQL to create these tables

Source code : image-gallery.sql
CREATE TABLE tbl_album (
al_id INT NOT NULL AUTO_INCREMENT,
al_name VARCHAR(64) NOT NULL,
al_description TEXT NOT NULL,
al_image VARCHAR(64) NOT NULL,
al_date DATETIME NOT NULL,
PRIMARY KEY(al_id)
);


CREATE TABLE tbl_image (
im_id INT NOT NULL AUTO_INCREMENT,
im_album_id INT NOT NULL,
im_title VARCHAR(64) NOT NULL,
im_description TEXT NOT NULL,
im_type VARCHAR(30) NOT NULL,
im_image VARCHAR(60) NOT NULL,
im_thumbnail VARCHAR(60) NOT NULL,
im_date DATETIME NOT NULL,
PRIMARY KEY(im_id)
);
Directory Layout

The image below show the file organization for the image gallery



The images directory is where we kept all of the images. The image icons are stored in the thumbnail sub-directory uder the gallery. Please remember to set write access to the album, gallery, and thumbnail directories otherwise the gallery script will not be able to save the images.
Configurations

There are some constants in the config file that you should change :
ALBUM_IMG_DIR, GALLERY_IMG_DIR
These are the absolute path to the images directories

THUMBNAIL_WIDTH
The PHP script will create a thumbnail ( icons ) for each image that you upload. In addition when you add an album image that image will also resized automatically.

One more note. If you want to test this gallery on your own computer please make sure you already have GD library installed. To check if GD library is installed on your system save the following code and run it.
if (function_exists('imagecreate')) {
echo 'OK, you already have GD library installed';
} else {
echo 'Sorry, it seem that GD library is not installed/enabled';
}
?>

This is a very simple form where you can enter the album name, description and image. After you click the "Add Album" button the script will do the followings :
Save the album image, resize it if necessary
Save the album information to database

Below is the screenshot of the form:



And here is the code snippet :

Example : admin/add-album.php
Source code : admin/add-album.phps

require_once '../library/config.php';
require_once '../library/functions.php';

if(isset($_POST['txtName']))
{
$albumName = $_POST['txtName'];
$albumDesc = $_POST['mtxDesc'];

$imgName = $_FILES['fleImage']['name'];
$tmpName = $_FILES['fleImage']['tmp_name'];

// we need to rename the image name just to avoid
// duplicate file names
// first get the file extension
$ext = strrchr($imgName, ".");

// then create a new random name
$newName = md5(rand() * time()) . $ext;

// the album image will be saved here
$imgPath = ALBUM_IMG_DIR . $newName;

// resize all album image
$result = createThumbnail($tmpName, $imgPath, THUMBNAIL_WIDTH);

if (!$result) {
echo "Error uploading file";
exit;
}

if (!get_magic_quotes_gpc()) {
$albumName = addslashes($albumName);
$albumDesc = addslashes($albumDesc);
}

$query = "INSERT INTO tbl_album (al_name, al_description, al_image, al_date)
VALUES ('$albumName', '$albumDesc', '$newName', NOW())";

mysql_query($query)
or die('Error, add album failed : ' . mysql_error());

// the album is saved, go to the album list
echo "";
exit;
}



Since we save the images as files instead inserting them to the database we need to make sure there won't be any name duplication problem. To prevent this we just generate some random name for every images that we upload. Take a look at code below :
$ext = strrchr($imgName, ".");
$newName = md5(rand() * time()) . $ext;
The first line is to extract the file extension from the file name. As an example let say we upload an image named "hyperalbum.jpg". Then strrchr("hyperalbum.jpg", ".") will return ".jpg". On the second line we generate a random number using rand() multiply it with current time and generate the hash code using md5(). It is a very common practice to use the combination of md5(), rand() and time() functions to generate a random name. After we append the file extension to the new name we can then use it to save the uploaded image.

But before we save the image we need to resize the image if it's too large. As you can see in the album list we only need small images for the album icons. To make the thumbnail we use createThumbnail() function defined in functions.php . Once everything is saved we print a little javascript code to go to the album list page. Note that we cannot simply use header("Location: index.php?page=list-album") to redirect to the album list page since a call to header() will only have an effect when no other output in sent before the call.

Admin : Album List

When your first login to the admin area and after adding a new album you can see this page. There's nothing really interesting on this one. It just a plain list of albums where we can see the albums we have and how many images on each album. Here is the snapshot :



In the "Images" column you can see how many images contained in an album. If you click on the number you will go to the image list so can see all the images in a particular album. And i'm sure i don't need to explain what that button with "Add Album" written on it does.

If you re-read the sql containing the table definitions of this gallery you can see that tbl_album doesn't contain any column storing the number of images in it. That number is the result of the left join in the sql query. You can see the sql code below.
$sql = "SELECT al_id,
al_name,
al_image,
COUNT(im_album_id) AS al_numimage
FROM tbl_album al
LEFT JOIN tbl_image im ON al.al_id = im.im_album_id
GROUP by al_id
ORDER BY al_name ";

In this query we must use LEFT JOIN instead of INNER JOIN because an album can have zero image in it. If we use INNER JOIN then the empty albums will not be shown in the list.

Now, if you right click on an album icon and view it's properties you can see that the url fo the icon is pointing to a PHP script instead of an image. The url look like this : viewImage.php?type=album&name=3b6a267a967d7535ff3b1ebc3d9e3c1e.jpg

In this image gallery whenever we would want to display an album or image icon or the full-size image we always use the viewImage.php file instead of linking to the actual image. There are several reasons to do this. The first is so you could move the images directory outside of your web root to prevent leechers from taking all the images.

The image gallery in our example doesn't do this. You could go to the images directory and list all the images in the gallery. If you set the value of ALBUM_IMG_DIR and GALLERY_IMG_DIR to a directory outside your webroot then you can prevent this. For example if your web root is /home/myname/public_html you can set ALBUM_IMG_DIR to /home/myname/images/album and GALLERY_IMG_DIR to /home/myname/images/gallery/.

The second reason is that you may want to restrict the access your gallery. For example the visitors must login before they can see the images. In viewImage.php you could check for the session variable to determine that. So if the visitors hasn't login yet you just display some blank or warning images

You can checkout the code here. It's really a simple script which requires two inputs. The type of image you wish to display ( album icon, image icon or full size image ) and the image file name. Then we only need to set the appropriate headers, read the image file and send it to the browser.

Next we'll see how to modify & delete an album.

User Authentication

With this basic authentication method we store the user information ( user id and password ) directly in the script. This is only good if the application only have one user since adding more user means we must also add the new user id and password in the script.

Let's start by making the login form first. You can see the code below.

Example : basic/login.php
Source : basic/login.phps

// ... we will put some php code here

?>







if ($errorMessage != '') {
?>

}
?>

User Id
Password

Nothing sophisticated in that form. It's just a basic login form with two input for entering the user id and password. Make sure that the form method is set to post since we certainly don't want to show up the user id and password in the address bar.

Right before the login form we there's a code for printing an error message. We can ignore this for now since we'll be talking about it shortly.

Once we submit the form we can start the authentication process. We simply check if the user id and password exist in $_POST and check if these two match the hardcoded user id and password.

Example : basic/login.php
Source : basic/login.phps
// we must never forget to start the session
session_start();

$errorMessage = '';
if (isset($_POST['txtUserId']) && isset($_POST['txtPassword'])) {
// check if the user id and password combination is correct
if ($_POST['txtUserId'] === 'theadmin' && $_POST['txtPassword'] === 'chumbawamba') {
// the user id and password match,
// set the session
$_SESSION['basic_is_logged_in'] = true;

// after login we move to the main page
header('Location: main.php');
exit;
} else {
$errorMessage = 'Sorry, wrong user id / password';
}
}
?>

// ... here is the login form shown previously

But before we start matching the user id and password. We must start the session first. Never forget to start the session before doing anything to the session since it won't work.

You can see above that the hardcoded user id and password are "theadmin" and "chumbawamba". If the submitted user id and password match these two then we set the value of $_SESSION['basic_is_logged_in'] to true. After that we move the application's main page. In this case it's called main.php

If the user id and password don't match we set the error message. This message will be shown on top of the login form.

Note : When starting the session you may stumble upon this kind of error :

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at C:\Webroot\examples\user-authentication\basic\login.php:1) in C:\Webroot\examples\user-authentication\basic\login.php on line 3

PHP will spit this error message if the script that call session_start() already send something ( a blank space, newline, etc ). The error above happen when i add a single space on the first line right before the php opening tag (

Checking if the user is logged in or not

Since the application main page, main.php, can only be accessed by those who already authenticated themselves we must check that before displaying the page.

The checking process is fairly simple. We just see if $_SESSION['basic_is_logged_in'] is set or not. If it is set we check if the value is true. If either of this condition is not met then the one accessing this page haven't login yet. And so we redirect to the login page and quit the script.

If $_SESSION['basic_is_logged_in'] is set and it's value is true then we can continue showing the rest of the page.

Here is the code for main.php

Example : basic/main.php
Source : basic/main.phps

// like i said, we must never forget to start the session
session_start();

// is the one accessing this page logged in or not?
if (!isset($_SESSION['basic_is_logged_in'])
|| $_SESSION['basic_is_logged_in'] !== true) {

// not logged in, move to login page
header('Location: login.php');
exit;
}

?>

This is the main application page. You are free to play around here since you
are an autenthicated user :-)

 

Logout

A little note about naming a session variable. As you can see the session that we used to mark whether a user is logged in or not is named 'basic_is_logged_in'. When setting a name for a session variable it's a good thing to use the application name as the prefix. In this case the prefix is 'basic_' . This is especially important when you have multiple application on one site where each requires different login information.

For example, suppose we have a cms application and a link exchange application where each have their own user authentication system. In both application we use the session variable $_SESSION['is_logged_in']. In this case if we already logged in in the cms application we will no longer be required to login in the link exchange application since both are using the same session name. This is usually not an intended feature. To avoid that kind of thing we can instead use $_SESSION['cms_is_logged_in'] and $_SESSION['exchange_is_logged_in']


The Logout Script

No login script is complete without the logout script right? So let's start making the logout script now.

The process of logging out a user is actually depends on how we check if a user is logged in or not. In our case we check if $_SESSION['basic_is_logged_in'] is already set or not and check whether it's value is true. Using this information we can build the logout script to simply unset this session or set the session value to false.

The logout script below use the first method ( unset the session ). Here is the code :

Example : basic/logout.php
Source : basic/logout.phps

// i will keep yelling this
// DON'T FORGET TO START THE SESSION !!!
session_start();

// if the user is logged in, unset the session
if (isset($_SESSION['basic_is_logged_in'])) {
unset($_SESSION['basic_is_logged_in']);
}

// now that the user is logged out,
// go to login page
header('Location: login.php');
?>

Before we unset the session we first check if the session is actually exist or not. In case you access the logout script before using the login form then this session variable won't exist yet.

Unsetting a variable is done simply by using the unset() statement. After we unset the session the next thing we do is simply moving to the login page. Pretty simple huh ?

Another note : You may already notice this but in each script i keep repeating about not to forget to start the session. The reason is that it is a very very very common error to forget about it when handling session. Once i spent a lot of time debugging a script and it was all because i forgot to add that one line.


A more common method of authenticating a user is by checking the database to see if the submitted user id and password combination exist. To use this kind of authentication we must first build the database table. The sql code to build it is shown below. We also add two user accounts for testing the login script

Source : database/tbl_auth_user.sql
CREATE TABLE tbl_auth_user (
user_id VARCHAR(10) NOT NULL,
user_password CHAR(32) NOT NULL,

PRIMARY KEY (user_id)
);

INSERT INTO tbl_auth_user (user_id, user_password) VALUES ('theadmin', PASSWORD('chumbawamba'));
INSERT INTO tbl_auth_user (user_id, user_password) VALUES ('webmaster', PASSWORD('webmistress'));

We will use the same html code to create login form created in previous example. We will only need to modify the login process a bit. The login script's content is shown below :

Example : database/login.php
Source : database/login.phps
// we must never forget to start the session
session_start();

$errorMessage = '';
if (isset($_POST['txtUserId']) && isset($_POST['txtPassword'])) {
include 'library/config.php';
include 'library/opendb.php';

$userId = $_POST['txtUserId'];
$password = $_POST['txtPassword'];

// check if the user id and password combination exist in database
$sql = "SELECT user_id
FROM tbl_auth_user
WHERE user_id = '$userId'
AND user_password = PASSWORD('$password')";

$result = mysql_query($sql)
or die('Query failed. ' . mysql_error());

if (mysql_num_rows($result) == 1) {
// the user id and password match,
// set the session
$_SESSION['db_is_logged_in'] = true;

// after login we move to the main page
header('Location: main.php');
exit;
} else {
$errorMessage = 'Sorry, wrong user id / password';
}

include 'library/closedb.php';
}
?>



// ... same html login form as previous example

Instead of checking the user id and password against a hardcoded info we query the database if these two exist in the database using the SELECT query. If we found a match we set the session variable and move to the main page. Note that the session name is prefixed by 'db_' to make it different than the previous example.

For the next two scripts ( main.php and logout.php ) the code is similar to previous one. The only difference is the session name. Here is the code for these two

Example : database/main.php
Source : database/main.phps

session_start();

// is the one accessing this page logged in or not?
if (!isset($_SESSION['db_is_logged_in'])
|| $_SESSION['db_is_logged_in'] !== true) {

// not logged in, move to login page
header('Location: login.php');
exit;
}

?>

// ... some html code here

Example : database/logout.php
Source : dabase/logout.phps

session_start();

// if the user is logged in, unset the session
if (isset($_SESSION['db_is_logged_in'])) {
unset($_SESSION['db_is_logged_in']);
}

// now that the user is logged out,
// go to login page
header('Location: login.php');
?>

Content Management System (CMS) Using PHP And MySQL

A Content Management System ( CMS ) is used to add, edit, and delete content on a website. For a small website, such as this, adding and deleting a page manually is fairly simple. But for a large website with lots of pages like a news website adding a page manually without a content management system can be a headache.

A CMS is meant to ease the process of adding and modifying new content to a webpage. The pages content are stored in database, not in the file server.

This tutorial will present an example of a simple content management system. You will be able to add, edit and delete articles using HTML forms.

For the database table we'll call it the news table. It consist of three columns :
id : The article's id
title : The title of an article
content : The article itself



First we need to create a script to add an article. It is just a form where a user can enter the article's title and content.

Example : cms-add.php
Source code : cms-add.phps , cms.txt

Title
Content

Whe an article is added the script just insert the article into the database. An article id is automatically generated by MySQL because the id column was created with AUTO_INCREMENT parameter .

if(isset($_POST['save']))
{
$title = $_POST['title'];
$content = $_POST['content'];

if(!get_magic_quotes_gpc())
{
$title = addslashes($title);
$content = addslashes($content);
}
include 'library/config.php';
include 'library/opendb.php';

$query = " INSERT INTO news (title, content) ".
" VALUES ('$title', '$content')";
mysql_query($query) or die('Error ,query failed');

include 'library/closedb.php';

echo "Article '$title' added";
}
?>



Now that we have the script to add articles let's create another script to view those articles. The script is list the title of articles available in database as clickable links. The article link have the article id appended like this

http://www.php-mysql-tutorial.com/examples/cms/article1.php?id=3

One possible implementation of article1.php is presented below :

Example : article1.php
Source code : article1.phps

include 'library/config.php';
include 'library/opendb.php';

// if no id is specified, list the available articles
if(!isset($_GET['id']))
{
$self = $_SERVER['PHP_SELF'];

$query = "SELECT id, title FROM news ORDER BY id";
$result = mysql_query($query) or die('Error : ' . mysql_error());

// create the article list
$content = '

';
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
list($id, $title) = $row;
$content .= "
1. $title
\r\n";
}

$content .= '

';

$title = 'Available Articles';
} else {
// get the article info from database
$query = "SELECT title, content FROM news WHERE id=".$_GET['id'];
$result = mysql_query($query) or die('Error : ' . mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);

$title = $row['title'];
$content = $row['content'];
}

include 'library/closedb.php';
?>

// ... more code here

When article1.php is first called the $_GET['id'] variable is not set and so it will query the database for the article list and save the list in the$content variable as an ordered list. The variable $title and $content will be used later when we print the result page. Take a look at the code below :

Example : article1.php
Source code : article2.phps


// ... previous code

?>

echo $content; // when displaying an article show a link // to see the article list if(isset($_GET['id'])) { ?>   Article List } ?>

If you click on an article link the script will fetch the article's title and content from the database, save it to $title and $content variable and print the HTML file . At the bottom of the page we place a code to show the link to the article list which is the file itself without any query string ( $_SERVER['PHP_SELF'] )


With this implementation each article request involve one database query. For a heavy load website with lots of articles using the above implementation can cause a very high amount of database-request. So we need a better cms solution to reduce the load.

One feasible solution is to implement caching ( cache ) which load an article from the database only once when the article was first requested. The article is then saved to a cache directory as a regular HTML file. Subsequent request to the article will no longer involve any database request. The script just need to read the requested article from the cache directory.

Example : article2.php
Source code : article2.phps

include 'library/config.php';
include 'library/opendb.php';

$cacheDir = dirname(__FILE__) . '/cache/';

if (isset($_GET['id'])) {
$cacheFile = $cacheDir . '_' . $_GET['id'] . '.html';
} else {
$cacheFile = $cacheDir . 'index.html';
}

if (file_exists($cacheFile))
{
header("Content-Type: text/html");
readfile($cacheFile);
exit;
}

// ... more code coming

?>



First we need to specify the cache directory where all cache files are located. For this example the cache directory is located in the same place as the article2.php script. I mean if article2.php is stored in C:/webroot then the cache dir is in C:/webroot/cache/

The script thent check if the article was already in the cache. An article is saved into the cache directory using a filename generated from it's id. For example if you request the article using a link like this :

http://www.php-mysql-tutorial.com/examples/cms/article2.php?id=3

Then the cache file for the article is

_3.html

This filename is just an underscore ( _ ) followed by the article id. In case article2.php is called like this :

http://www.php-mysql-tutorial.com/examples/cms/article2.php

no id is defined so we make the cache file name as index.html

If the cache file is found , the content is read and printed using readfile() and the script terminate. When the article is not found in the cache then we need to look in the database and get the page content from there.

Example : article2.php
Source code : article2.phps


// ... previous code


if(!isset($_GET['id']))
{
$self = $_SERVER['PHP_SELF'];

$query = "SELECT id, title FROM news ORDER BY id";
$result = mysql_query($query) or die('Error : ' . mysql_error());

$content = '

';
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
list($id, $title) = $row;
$content .= "
1. $title
\r\n";
}

$content .= '

';

$title = 'Available Articles';
} else {
// get the article info from database
$query = "SELECT title, content FROM news WHERE id=".$_GET['id'];
$result = mysql_query($query) or die('Error : ' . mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);

$title = $row['title'];
$content = $row['content'];
}

include 'library/closedb.php';

// ... still more code coming

?>

As you can see above the process of fetching the article list and content is the same as article1.php. But before showing the page we have to start output buffering so we can save the content of the generated HTML file.

See the code below. Just before printing the html we callob_start() to activate output buffering. From this point no output is sent from the script to the browser. So in the code example below anything between and tag is not sent to the browser but stored in an internal buffer first.

After the closing html tag we useob_get_contents() to get the buffer content and store int in a temporary variable, $buffer. We then call ob_end_flush() which stop the output buffering ( so the page is now sent to the browser ).

Example : article2.php
Source code : article2.phps


// ... previous code

ob_start();
?>



// ... same html code as article1.php




// get the buffer
$buffer = ob_get_contents();

// end output buffering, the buffer content
// is sent to the client
ob_end_flush();

// now we create the cache file
$fp = fopen($cacheFile, "w");
fwrite($fp, $buffer);
fclose($fp);
?>

Downloading Files From MySQL Database

When we upload a file to database we also save the file type and length. These were not needed for uploading the files but is needed for downloading the files from the database.

The download page list the file names stored in database. The names are printed as a url. The url would look like download.php?id=3. To see a working example click here. I saved several images in my database, you can try downloading them.

Example : download.php
Source code : download.phps








include 'library/config.php';
include 'library/opendb.php';

$query = "SELECT id, name FROM upload";
$result = mysql_query($query) or die('Error, query failed');
if(mysql_num_rows($result) == 0)
{
echo "Database is empty
";
}
else
{
while(list($id, $name) = mysql_fetch_array($result))
{
?>


}
}
include 'library/closedb.php';
?>





When you click the download link, the $_GET['id'] will be set. We can use this id to identify which files to get from the database. Below is the code for downloading files from MySQL Database.

Example : download.php
Source code : download.phps
if(isset($_GET['id']))
{
// if id is set then get the file with the id from database

include 'library/config.php';
include 'library/opendb.php';

$id = $_GET['id'];
$query = "SELECT name, type, size, content " .
"FROM upload WHERE id = '$id'";

$result = mysql_query($query) or die('Error, query failed');
list($name, $type, $size, $content) = mysql_fetch_array($result);

header("Content-length: $size");
header("Content-type: $type");
header("Content-Disposition: attachment; filename=$name");
echo $content;

include 'library/closedb.php';
exit;
}

?>

Before sending the file content using echo first we need to set several headers. They are :
header("Content-length: $size")
This header tells the browser how large the file is. Some browser need it to be able to download the file properly. Anyway it's a good manner telling how big the file is. That way anyone who download the file can predict how long the download will take.
header("Content-type: $type")
This header tells the browser what kind of file it tries to download.
header("Content-Disposition: attachment; filename=$name");
Tells the browser to save this downloaded file under the specified name. If you don't send this header the browser will try to save the file using the script's name (download.php).

After sending the file the script stops executing by calling exit.

NOTE :
When sending headers the most common error message you will see is something like this :

Warning: Cannot modify header information - headers already sent by (output started at C:\Webroot\library\config.php:7) in C:\Webroot\download.php on line 13

This error happens because some data was already sent before we send the header. As for the error message above it happens because i "accidentally" add one space right after the PHP closing tag ( ?> ) in config.php file. So if you see this error message when you're sending a header just make sure you don't have any data sent before calling header(). Check the file mentioned in the error message and go to the line number specified

Uploading Files To MySQL Database

Using PHP to upload files into MySQL database sometimes needed by some web application. For instance for storing pdf documents or images to make som kind of online briefcase (like Yahoo briefcase).

For the first step, let's make the table for the upload files. The table will consist of.
id : Unique id for each file
name : File name
type : File content type
size : File size
content : The file itself



For column content we'll use BLOB data type. BLOB is a binary large object that can hold a variable amount of data. MySQL have four BLOB data types, they are :
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

Since BLOB is limited to store up to 64 kilobytes of data we will use MEDIUMBLOB so we can store larger files ( up to 16 megabytes ).

Example : upload.txt
CREATE TABLE upload (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
type VARCHAR(30) NOT NULL,
size INT NOT NULL,
content MEDIUMBLOB NOT NULL,
PRIMARY KEY(id)
);

Uploading a file to MySQL is a two step process. First you need to upload the file to the server then read the file and insert it to MySQL.

For uploading a file we need a form for the user to enter the file name or browse their computer and select a file. The input type="file" is used for that purpose.



Example : upload.php
Source code : upload.phps

An upload form must have encytype="multipart/form-data" otherwise it won't work at all. Of course the form method also need to be set to method="post". Also remember to put a hidden input MAX_FILE_SIZE before the file input. It's to restrict the size of files.

After the form is submitted the we need to read the autoglobal $_FILES. In the example above the input name for the file is userfile so the content of $_FILES are like this :

$_FILES['userfile']['name']
The original name of the file on the client machine.

$_FILES['userfile']['type']
The mime type of the file, if the browser provided this information. An example would be "image/gif".

$_FILES['userfile']['size']
The size, in bytes, of the uploaded file.

$_FILES['userfile']['tmp_name']
The temporary filename of the file in which the uploaded file was stored on the server.

$_FILES['userfile']['error']
The error code associated with this file upload. ['error'] was added in PHP 4.2.0


Example : upload.php
Source code : upload.phps

if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

if(!get_magic_quotes_gpc())
{
$fileName = addslashes($fileName);
}

include 'library/config.php';
include 'library/opendb.php';

$query = "INSERT INTO upload (name, size, type, content ) ".
"VALUES ('$fileName', '$fileSize', '$fileType', '$content')";

mysql_query($query) or die('Error, query failed');
include 'library/closedb.php';

echo "
File $fileName uploaded
";
}
?>
Before you do anything with the uploaded file. You should not assume that the file was uploaded successfully to the server. Always check to see if the file was successfully uploaded by looking at the file size. If it's larger than zero byte then we can assume that the file is uploaded successfully.

PHP saves the uploaded file with a temporary name and save the name in $_FILES['userfile']['tmp_name']. Our next job is to read the content of this file and insert the content to database. Always make sure that you use addslashes() to escape the content. Using addslashes() to the file name is also recommended because you never know what the file name would be.

That's it now you can upload your files to MySQL. Now it's time to write the script to download those files.

Creating A Guestbook Using PHP and MySQL

You've seen it at least once right? Guestbook is one of the most common thing to find in a website. In this tutorial we'll create a guestbook using PHP and MySQL.

I have split this tutorial into two section, each covering a specific feature of the guestbook.
Creating The Sign-Guestbook Form
This part will cover creating the database tables, the guestbook form and the process of saving the entry to database

Viewing The Entries
You want to see the guestbook entries of course. This section covers fetching the entries from database and put int into an HTML table. You will also learn to show the entries in multiple pages using MySQL paging.


I think you should take a quick look what the finished guestbook look like. Just click here to see it.


Creating The Sign-Guestbook Form

We start by creating the table to store the data, guestbook. There are six fields in the guestbook table:1. id : the unique identifier for an entry in the guestbook
2. name : the visitor's name
3. email : visitor's email address
4. url : visitor's website url, if she has one
5. message : the message
6. entry_date : when did this entry added




I have put the SQL query needed to create the table in guestbook.txt.

Below is the HTML form code. It's pretty simple, we have text box for name, email and url plus a textarea to hold the message. The submit button is attached with a javascript function because we want to check the input values before the page is submitted.

Example :guestbook.php
Source code : guestbook.phps, guestbook.txt

Name *
Email
Website URL
Message *

Below is the javascript code to check the input form. The checkForm() function is called when the "Sign Guestbook" button is clicked.

The mandatory fields are name and message so if either is empty we pop an alert box to tell the visitor to enter the name and message. Email is not a mandatory field so we only check if in an email address is entered but we won't complain if there's none .

function checkForm()
{
// the variables below are assigned to each
// form input
var gname, gemail, gurl, gmessage;

with(window.document.guestform)
{
gname = txtName;
gemail = txtEmail;
gurl = txtUrl;
gmessage = mtxMessage;
}

// if name is empty alert the visitor
if(trim(gname.value) == '')
{
alert('Please enter your name');
gname.focus();
return false;
}
// alert the visitor if email is empty or
// if the format is not correct
else if(trim(gemail.value) != '' && !isEmail(trim(gemail.value)))
{
alert('Please enter a valid email address or leave it blank');
gemail.focus();
return false;
}
// alert the visitor if message is empty
else if(trim(gmessage.value) == '')
{
alert('Please enter your message');
gmessage.focus();
return false;
}
else
{
// when all input are correct
// return true so the form will submit
return true;
}
}

/*
Strip whitespace from the beginning and end of a string
*/
function trim(str)
{
return str.replace(/^\s+|\s+$/g,'');
}

/*
Check if a string is in valid email format.
*/
function isEmail(str)
{
var regex = /^[-_.a-z0-9]+@(([-a-z0-9]+\.)+(ad|ae|aero|af|ag|
ai|al|am|an|ao|aq|ar|arpa|as|at|au|aw|az|ba|bb|bd|be|bf|bg|bh|
bi|biz|bj|bm|bn|bo|br|bs|bt|bv|bw|by|bz|ca|cc|cd|cf|cg|ch|ci|
ck|cl|cm|cn|co|com|coop|cr|cs|cu|cv|cx|cy|cz|de|dj|dk|dm|do|dz|
ec|edu|ee|eg|eh|er|es|et|eu|fi|fj|fk|fm|fo|fr|ga|gb|gd|ge|gf|gh|
gi|gl|gm|gn|gov|gp|gq|gr|gs|gt|gu|gw|gy|hk|hm|hn|hr|ht|hu|id|ie|
il|in|info|int|io|iq|ir|is|it|jm|jo|jp|ke|kg|kh|ki|km|kn|kp|kr|
kw|ky|kz|la|lb|lc|li|lk|lr|ls|lt|lu|lv|ly|ma|mc|md|mg|mh|mil|mk|
ml|mm|mn|mo|mp|mq|mr|ms|mt|mu|museum|mv|mw|mx|my|mz|na|name|nc|
ne|net|nf|ng|ni|nl|no|np|nr|nt|nu|nz|om|org|pa|pe|pf|pg|ph|pk|
pl|pm|pn|pr|pro|ps|pt|pw|py|qa|re|ro|ru|rw|sa|sb|sc|sd|se|sg|sh|
si|sj|sk|sl|sm|sn|so|sr|st|su|sv|sy|sz|tc|td|tf|tg|th|tj|tk|tm|
tn|to|tp|tr|tt|tv|tw|tz|ua|ug|uk|um|us|uy|uz|va|vc|ve|vg|vi|vn|
vu|wf|ws|ye|yt|yu|za|zm|zw)|(([0-9][0-9]?|[0-1][0-9][0-9]|[2]
[0-4][0-9]|[2][5][0-5])\.){3}([0-9][0-9]?|[0-1][0-9][0-9]|[2]
[0-4][0-9]|[2][5][0-5]))$/i;
return regex.test(str);
}



After the form is submitted our job turns to saving the input into the database.

In the code below I include config.php and opendb.php which contain the database configuration and the code needed to open a connection to MySQL. It's a good practice to put these actions in separate file. That way everytime you need to connect to MySQL you can include these files instead of rewriting the code. Also you can change the database information from just one file instead of changing it in every file that use MySQL. To see what the content of config.php, opendb.php and closedb.php go to : Connecting to MySQL database


include 'library/config.php';
include 'library/opendb.php';

if(isset($_POST['btnSign']))
{
include 'library/config.php';
include 'library/opendb.php';


$name = trim($_POST['txtName']);
$email = trim($_POST['txtEmail']);
$url = trim($_POST['txtUrl']);
$message = trim($_POST['mtxMessage']);

if(!get_magic_quotes_gpc())
{
$message = addslashes($message);
}


// if the visitor do not enter the url
// set $url to an empty string
if ($url == 'http://')
{
$url = '';
}

$query = "INSERT INTO guestbook (name,
email,
url,
message,
entry_date)
VALUES ('$name',
'$email',
'$url',
'$message',
current_date)";

mysql_query($query) or die('Error, query failed');

header('Location: ' . $_SERVER['REQUEST_URI']);
exit;
}
?>

The script check if the $_POST['btnSign'] variable is set. If it is then the "Sign Guestbook" button must have been clicked and now we can read name, email, url and message from the $_POST global variable. After that we create an INSERT query string and execute the query using mysql_query().

Sometimes a message can contain single quotes, we need to escape these single quotes ( replacing it with \' ) otherwise MySQL will think that it's the end of a string and the query will fail. We use the addslashes() function to escape the string.

Unfortunately some web hosts set the magic_quotes_gpc setting on. This will make values containing single-quotes in $_GET, $_POST and $_COOKIE will be automatically escaped. If we use addslashes() when the string is already escaped the result would be a mess.

To check if magic_quotes_gpc is On use get_magic_quotes_gpc(). If it returns true then we don't have to call addslashes().

Ok, now affter all input is ready we can build the query string to enter the name, email, url, message and entry date. Note that for the entry_date field we use current_date. This is not a PHP variable or function, it's a built in MySQL function that returns ( guess what? ) the current date.

You also see that I didn't explicitly insert the value of id field. This is because id is set as auto_increment so when we insert a new row into the table a new value for id is automatically generated ( incremented for each new row).

After inserting the new guestbook entry the next thing we do is redirect back to current page using header('Location: ' . $_SERVER['REQUEST_URI']);

Why?

The redirect is just to prevent double submission. Suppose we don't use the redirect and the visitor hit the refresh button after signing up the guestbook then the form will be submitted again.

Note : If you get this kind of error message

Warning: Cannot modify header information - headers already sent by (output started at C:\webroot\guestbook\library\config.php:7) in C:\webroot\guestbook\guestbook.php on line 43

this mean the redirect failed because you already sent something to the browser. I got the error message above because i "accidentally" have a space right after the closing tag ( ?> ) in config.php. By removing this space the error is fixed.

This kind of errror is actually very common to see when your code is sending headers and fixing it is easy like the example above. Just check the file pointed by the error message and see if you accidentally sent ( print ) anyhing to the browser.

Form Validation With PHP

Whenever you make a form you should not leave it alone without any form validation. Why? Because there is no guarantee that the input is correct and processing incorrect input values can make your application give unpredictable result.

You can validate the form input on two places, client side and server side.

Client side form validation usually done with javascript. Client side validation makes your web application respond 'faster' while server side form validation with PHP can act as a backup just in case the user switch off javascript support on her browser. And since different browsers can behave differently there is always a possibility that the browser didn't execute the javascript code as you intended.



Some things you need to check :
empty values
numbers only
input length
email address
strip html tags

To show form validation with php in action I'll use the contact form in this website. Click here to see the contact form and then take a look at the source code.

This contact form requires four input :
sender name
sender email
message subject
message body

First let's focus on the client side validation. On the "Send Message" button I put this javascript code : onClick="return checkForm();", which is triggered when you click on it. Clicking the button will run the function checkForm().Every input is checked to see whether they are valid input. When an invalid input is found the function returns false so the form is not submitted. When you insert valid input the function will return true and the form is submitted.

Go ahead and play around with the form. Try entering only spaces for the input value or enter glibberish string as email address.

The code snippet below shows the client part of contact form.

Example : contact.php
Source code : contact.phps

Your Name
Your Email
Subject
Message

Now we'll take a better look at checkForm() function :

function checkForm()
{
var cname, cemail, csubject, cmessage;
with(window.document.msgform)
{
cname = sname;
cemail = email;
csubject = subject;
cmessage = message;
}

// ... the rest of the code

}

In the beginning of the function I use the keyword var to declare four variables to reference the form input . They are cname, cemail, csubject and cmessage. These variables will reference the form input sname, email, subject and message respectively.


Javascript treats a document and it's element as object. The message form is named msgform so to access is we use window.document.msgform and to access the sname input text we can use window.document.msgform.sname.

To avoid the hassle of writing the window.document.msgform part whenever we want to access a form object I use the with() keyword. Without it the checkForm() function would look like :

function checkForm()
{
var cname, cemail, csubject, cmessage;

cname = window.document.msgform.sname;
cemail = window.document.msgform.email;
csubject = window.document.msgform.subject;
cmessage = window.document.msgform.message;


// ... the rest of the code

}

Next we'll validate each form input.

function checkForm()
{
// variable declarations goes here ...

if(trim(cname.value) == '')
{
alert('Please enter your name');
cname.focus();
return false;
}
else if(trim(cemail.value) == '')
{
alert('Please enter your email');
cemail.focus();
return false;
}
else if(!isEmail(trim(cemail.value)))
{
alert('Email address is not valid');
cemail.focus();
return false;
}
// The rest of validation code goes here ...
}

To access the value of the name input box we use cname.value. The name values is trimmed to remove extra spaces from the beginning and end of the name. If you do not enter your name or only entering spaces then an alert box will pop up. Using cname.focus() the cursor will be placed to the name input box and then checkForm() return false which cancel the form submit.


The code above uses trim() function. This is not a built in javascript function. I can't understand why there is no trim() function in javascript, even VBScript has it. Anyway it's not a big deal because we can just make our own trim() function. The solution here uses regular expression to replace any spaces in the beginning and end of a string with blank string.

function trim(str)
{
return str.replace(/^\s+|\s+$/g,'');
}

The forward slash (/) is used to create a regular expression. Note that it is not a string, you don't have to use quotes and it won't work if you use quotes. Let's chop the regular expression notation so we can understand it better :
^ : the beginning of a string
$ : end of string.
\s : single whitespace character (tab also count as whitespace)
+ : one or more
| : conditional (OR)
g : global, mainly used for search and replace operation

So in english the search replace function above can be read as :

"Replace one or more whitespace character from the beginning or ending of a string with blank character"

As for the email input, we need to double check it. First, check if the email is entered and second check if the input is in a valid email format. For the second check well use isEmail() function. This function also uses regular expression.

A valid email format can be described as :

[ a string consisting of alphanumeric characters, underscores, dots or dash ] @ ( [ a valid domain name ] DOT [ a valid TLD ]) OR [a valid IP adress ]

In case you're wondering TLD means Top Level Domain such as com, net, org, biz, etc.

When you see the source code you will see that the regular expression in isEmail() function is actually written in one line. I have to break them into multiple lines just to fit the space. The PHP Manual explains the regular expression syntax for PHP in depth, but if you want to learn regular expression for javascript you can go to : http://www.regular-expressions.info

Finally, if all input are considered valid checkForm() returns true and the form will be submitted. This will set the $_POST['send'] variable and now we start validating the input on the server side using PHP.


$errmsg = ''; // error message
$sname = ''; // sender's name
$email = ''; // sender's email addres
$subject = ''; // message subject
$message = ''; // the message itself

if(isset($_POST['send']))
{
$sname = $_POST['sname'];
$email = $_POST['email'];
$subject = $_POST['subject'];
$message = $_POST['message'];

if(trim($sname) == '')
{
$errmsg = 'Please enter your name';
}
else if(trim($email) == '')
{
$errmsg = 'Please enter your email address';
}
else if(!isEmail($email))
{
$errmsg = 'Your email address is not valid';
}
else if(trim($subject) == '')
{
$errmsg = 'Please enter message subject';
}
else if(trim($message) == '')
{
$errmsg = 'Please enter your message';
}

// ... more code here
?>

The PHP validation is doing the same thing as the javascript validation. It check each value to see if it's empty and if it is we consider that as an error. We also recheck the validity of the email address.

When we find an error we set the value of $errmsg. We will print this value so the user can fix the error.

If everything is okay the value of $errmsg will be blank. So we continue processing the input.


// ... previous validation code

if($errmsg == '')
{
if(get_magic_quotes_gpc())
{
$subject = stripslashes($subject);
$message = stripslashes($message);
}

$to = "email@yourdomain.com";
$subject = '[Contact] : ' . $subject;
$msg = "From : $sname \r\n " . $message;
mail($to,
$subject,
$msg,
"From: $email\r\nReturn-Path: $email\r\n");

// ... more code here
?>

Some web host set the PHP directive magic_quotes_gpc to 'on' which runs addslashes() to every GET, POST, and COOKIE data so we got an extra work to strip the slashes from the input.

Because the addslashes() function only add slashes before single quote ( ' ), double quote ( " ), backslash ( \ ) and NULL, we only need to worry about the $subject and $message. This is because (usually ) only these two can contain such characters. However, we can't be sure if magic_quotes_gpc is On or Off so we have to check it's value first using the get_magic_quotes_gpc() function

After finishing all that boring job of validating the input we finally come to the last, and the most important step, sending the message using the mail() function.

The first parameter we pass to the mail() function is the receiver's email address. The second is the email subject. The third is the message itself and the fourth is an additional headers.

I'm sure you already understand the purpose of the first three parameters so I'll just discuss about the fourth one, the additional parameter ( additional headers )

"From: $email\r\nReply-To: $email\r\nReturn-Path: $email\r\n"

Each headers are separated by the "\r\n" ( newline ) characters. The first two ( From and Reply-To ) is self explanatory. But what about the third one ( Return-Path )?

The reason is some spam filter will check the Return-Path header and compare it with the From header. If these two don't match then the email is considered as spam and you're email won't get delivered ( or sent to the spam folder ). So it's better to play safe and put Return-Path header when we want to send an email to make sure it gets delivered.

Using PHP To Backup MySQL Database

There are at least three ways to backup your MySQL Database :

Execute a database backup query from PHP file.
Run mysqldump using system() function.
Use phpMyAdmin to do the backup.

Execute a database backup query from PHP file

Below is an example of using SELECT INTO OUTFILE query for creating table backup :


include 'config.php';
include 'opendb.php';

$tableName = 'mypet';
$backupFile = 'backup/mypet.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

include 'closedb.php';
?>

To restore the backup you just need to run LOAD DATA INFILE query like this :


include 'config.php';
include 'opendb.php';

$tableName = 'mypet';
$backupFile = 'mypet.sql';
$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);

include 'closedb.php';
?>

It's a good idea to name the backup file as tablename.sql so you'll know from which table the backup file is

Run mysqldump using system() function

The system() function is used to execute an external program. Because MySQL already have built in tool for creating MySQL database backup (mysqldump) let's use it from our PHP script


include 'config.php';
include 'opendb.php';

$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
include 'closedb.php';
?>

Use phpMyAdmin to do the backup

This option as you may guessed doesn't involve any programming on your part. However I think i mention it anyway so you know more options to backup your database.

To backup your MySQL database using phpMyAdmin click on the "export" link on phpMyAdmin main page. Choose the database you wish to backup, check the appropriate SQL options and enter the name for the backup file.

MySQL Update and Delete

There are no special ways in PHP to perform update and delete on MySQL database. You still use mysql_query() to execute the UPDATE or DELETE statement.

For instance to update a password in mysql table for username phpcake can be done by executing an UPDATE statement with mysql_query() like this:

Example : update.php
Source code : update.phps
include 'library/config.php';
include 'library/opendb.php';

mysql_select_db('mysql')
or die('Error, cannot select mysql database');

$query = "UPDATE user SET password = PASSWORD('newpass')". "WHERE user = 'phpcake'";

mysql_query($query) or die('Error, query failed');
include 'library/closedb.php';
?>

There is one important thing that you should be aware of when updating and deleting rows from database. That is data integrity.

If you're using InnoDB tables you can leave the work of maintaining data integrity to MySQL . However when you're using other kind of tables you need to enforce the data integrity manually.

To make sure that your update and delete queries will not break the data integrity. You have to make appropriate update and delete queries for all tables referencing to the table you update or delete.

For example, suppose you have two tables, Class and Student. The Student table have a foreign key column, cid which references to the class_id column in table Class. When you want to update a class_id in Class table you will also need to update the cid column in Student table to maintain data integrity.

Suppose i want to change the class_id of Karate from 3 to 10. Since there is a row in Student table with cid value of 3, I have to update that row too.

$query = "UPDATE Class SET class_id = 10 WHERE class_id = 3";
mysql_query($query);

$query = "UPDATE Student SET cid = 10 WHERE cid = 3";
mysql_query($query);

Below are the data in Table and Student class before an update query : Table Class
class_id class_name
1
Silat
2 Kungfu
3 Karate
4 Taekwondo


Table Student
student_id student_name cid
1 Uzumaki Naruto 1
2 Uchiha Sasuke 3
3 Haruno Sakura 2


Now the content of Table and Student class after the update query are : Table Class
class_id class_name
1
Silat
2 Kungfu
10 Karate
4 Taekwondo


Table Student
student_id student_name cid
1 Uzumaki Naruto 1
2 Uchiha Sasuke 10
3 Haruno Sakura 2


You can go as far as creating your own functions in PHP to ensure the data integrity. I have done this before and I hope you don't do it. Save yourself the headache and just write appropriate queries to maintain your data integrity whenever you update / delete rows from a table.

This means that whenever you make a query to update / delete always consult your database design to see if you need to update / delete another table to maintain data integrity. Your code will be more portable like this.


Using LOCK TABLES

When your web application is used by more than one user using LOCK TABLES before any update / delete query is a safe bet. This will make sure that only one user change the table at a time.

Using the above update code examples again, suppose there are two users. The first one want to update one row in Class table and the second want to delete it

$query = "LOCK TABLES Class WRITE, Student WRITE";
mysql_query($query);

$query = "DELETE FROM Class WHERE class_id = 3";
mysql_query($query);

$query = "DELETE FROM Student WHERE class_id = 3";
mysql_query($query);

$query = "UNLOCK TABLES";
mysql_query($query);

The update queries above can be rewritten as :

$query = "LOCK TABLES Class WRITE, Student WRITE";
mysql_query($query);

$query = "UPDATE Class SET class_id = 10 WHERE class_id = 3";
mysql_query($query);

$query = "UPDATE Student SET cid = 10 WHERE cid = 3";
mysql_query($query);

$query = "UNLOCK TABLES";
mysql_query($query);

By issuing the LOCK TABLES all other users are blocked from reading and writing to the tables. So you're update / delete query will continue to completion without any worries that the intended table already changed by another user

Using Paging

Ever use a Search Engine? I'm sure you have, lots of time. When Search Engines found thousands of results for a keyword do they spit out all the result in one page? Nope, they use paging to show the result little by little.

Paging means showing your query result in multiple pages instead of just put them all in one long page. Imagine waiting for five minutes just to load a search page that shows 1000 result. By splitting the result in multiple pages you can save download time plus you don't have much scrolling to do.

To show the result of a query in several pages first you need to know how many rows you have and how many rows per page you want to show. For example if I have 295 rows and I show 30 rows per page that mean I'll have ten pages (rounded up).



For the example I created a table named randoms that store 295 random numbers. Each page shows 20 numbers.

Example: paging.php
Source code :paging.phps

include 'library/config.php';
include 'library/opendb.php';

// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query = " SELECT val FROM randoms " .
" LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

// print the random numbers
while($row = mysql_fetch_array($result))
{
echo $row['val'] . '
';
}

// ... more code here
?>

Paging is implemented in MySQL using LIMIT that take two arguments. The first argument specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the first row is 0 ( not 1 ).

When paging.php is called for the first time the value of $_GET['page'] is not set. This caused $pageNum value to remain 1 and the query is :

SELECT val FROM randoms LIMIT 0, 20

which returns the first 20 values from the table. But when paging.php is called like this http://www.php-mysql-tutorial.com/examples/paging/paging.php?page=4
the value of $pageNum becomes 4 and the query will be :

SELECT val FROM randoms LIMIT 60, 20

this query returns rows 60 to 79.

After showing the values we need to print the links to show any pages we like. But first we have to count the number of pages. This is achieved by dividing the number of total rows by the number of rows to show per page :

$maxPage = ceil($numrows/$rowsPerPage);

// ... the previous code

// how many rows we have in database
$query = "SELECT COUNT(val) AS numrows FROM randoms";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';

for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " $page ";
}
}

// ... still more code coming
?>

The mathematical function ceil() is used to round up the value of $numrows/$rowsPerPage.

In this case the value of total rows $numrows is 295 and $rowsPerPage is 20 so the result of the division is 14.75 and by using ceil() we get $maxPage = 15

Now that we know how many pages we have we make a loop to print the link. Each link will look something like this:

5

You see that we use $_SERVER['PHP_SELF'] instead of paging.php when creating the link to point to the paging file. This is done to avoid the trouble of modifying the code in case we want to change the filename.


We are almost complete. Just need to add a little code to create a 'Previous' and 'Next' link. With these links we can navigate to the previous and next page easily. And while we at it let's also create a 'First page' and 'Last page' link so we can jump straight to the first and last page when we want to.



// ... the previous code

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " [Prev] ";

$first = " [First Page] ";
}
else
{
$prev = ' '; // we're on page one, don't print previous link
$first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " [Next] ";

$last = " [Last Page] ";
}
else
{
$next = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;

// and close the database connection
include '../library/closedb.php';

// ... and we're done!
?>

Making these navigation link is actually easier than you may think. When we're on the fifth page we just make the 'Previous' link point to the fourth. The same principle also apply for the 'Next' link, we just need to add one to the page number.

One thing to remember is that we don't need to print the 'Previous' and 'First Page' link when we're already on the first page. Same thing for the 'Next' and 'Last' link. If we do print them that would only confuse the one who click on it. Because we'll be giving them the exact same page.


We got a problem here...

Take a look at this slightly modified version of paging.php. Instead of showing 20 numbers in a page, I decided to show just three.

See the problem already?

Those page numbers are running across the screen! Yuck!

This call for a little modification to the code. Instead of printing the link to each and every page we will just saying something like "Viewing page 4 of 99 pages".

Than means we havel remove these code :

// ... the previous code

$nav = '';

for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " $page ";
}
}

// ... the rest here
?>

And then modify this one

// ...

// print the navigation link
echo $first . $prev . $nav . $next . $last;

// ...
?>



Into this

// ...

// print the navigation link
echo $first . $prev .
" Showing page $pageNum of $maxPage pages " . $next . $last;

// ...
?>

Source : http://www.php-mysql-tutorial.com/

Convert MySQL Query Result To Excel

Using PHP to convert MySQL query result to Excel format is also common especially in web based finance applications. The finance data stored in database are downloaded as Excel file for easy viewing. There is no special functions in PHP to do the job. But you can do it easily by formatting the query result as tab separated values or put the value in an HTML table. After that set the content type to application/vnd.ms-excel

Example : convert.php
Source : convert.php, students.txt

include 'library/config.php';
include 'library/opendb.php';

$query = "SELECT fname, lname FROM students";
$result = mysql_query($query) or die('Error, query failed');

$tsv = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
$tsv[] = implode("\t", $row);
$html[] = "" .implode("", $row) . "";
}

$tsv = implode("\r\n", $tsv);
$html = "

" . implode("\r\n", $html) . "";

$fileName = 'mysql-to-excel.xls';
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$fileName");

echo $tsv;
//echo $html;

include 'library/closedb.php';
?>

In the above example $tsv is a string containing tab separated values and $html contain an HTML table. I use implode() to join the values of $row with tab to create a tab separated string.

After the while loop implode() is used once again to join the rows using newline characters. The headers are set and the value of $tsv is then printed. This will force the browser to save the file as mysql-to-excel.xsl

Try running the script in your own computer then try commenting echo $tsv and uncomment echo $html to see the difference.

Get Data From MySQL Database

Using PHP you can run a MySQL SELECT query to fetch the data out of the database. You have several options in fetching information from MySQL. PHP provide several functions for this. The first one is mysql_fetch_array()which fetch a result row as an associative array, a numeric array, or both.

Below is an example of fetching data from MySQL, the table contact have three columns, name, subject and message.

Example : select.php
Source code : select.phps, contact.txt

include 'config.php';
include 'opendb.php';

$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "Name :{$row['name']}
" .
"Subject : {$row['subject']}
" .
"Message : {$row['message']}

";
}

include 'closedb.php';
?>

The while() loop will keep fetching new rows until mysql_fetch_array() returns FALSE, which means there are no more rows to fetch. The content of the rows are assigned to the variable $row and the values in row are then printed. Always remember to put curly brackets when you want to insert an array value directly into a string.

In above example I use the constant MYSQL_ASSOC as the second argument to mysql_fetch_array(), so that it 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 . Personally I think it's more informative to use $row['subject'] instead of $row[1].

PHP also provide a function called mysql_fetch_assoc() which also return the row as an associative array.

include 'config.php';
include 'opendb.php';

$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
echo "Name :{$row['name']}
" .
"Subject : {$row['subject']}
" .
"Message : {$row['message']}

";
}

include 'closedb.php';
?>

You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

include 'config.php';
include 'opendb.php';

$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_NUM))
{
echo "Name :{$row[0]}
" .
"Subject : {$row[0]}
" .
"Message : {$row[0]}

";
}

include 'closedb.php';
?>

Using the constant MYSQL_NUM with mysql_fetch_array() gives the same result as the function mysql_fetch_row().

There is another method for you to get the values from a row. You can use list(), to assign a list of variables in one operation.

include 'config.php';
include 'opendb.php';

$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);

while(list($name,$subject,$message)= mysql_fetch_row($result))
{
echo "Name :$name
" .
"Subject : $subject
" .
"Message : $row

";
}

include 'closedb.php';
?>

In above example, list() assign the values in the array returned by mysql_fetch_row() into the variable $name, $subject and $message.

Of course you can also do it like this

include 'config.php';
include 'opendb.php';

$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);

while($row = mysql_fetch_row($result))
{
$name = $row[0];
$subject = $row[1];
$message = $row[2];


echo "Name :$name
" .
"Subject : $subject
" .
"Message : $row

";
}

include 'closedb.php';
?>

Source : http://www.php-mysql-tutorial.com/

Insert Data To MySQL Database

Inserting data to MySQL is done by using mysql_query() to execute INSERT query. Note that the query string should not end with a semicolon. Below is an example of adding a new MySQL user by inserting a new row into table user in database mysql :

Example : insert.php
Source code : insert.phps

include 'library/config.php';
include 'library/opendb.php';

mysql_select_db($mysql);
$query = "INSERT INTO user (host, user, password, select_priv, insert_priv, update_ priv) VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y', 'Y')";

mysql_query($query) or die('Error, insert query failed');

$query = "FLUSH PRIVILEGES";
mysql_query($query) or die('Error, insert query failed');

include 'library/closedb.php';
?>

In the above example mysql_query() was followed by die(). If the query fail the error message will be printed and the script's execution is terminated. Actually you can use die() with any function that might not execute properly. That way you can be sure that the script won't continue to run when an error occured.

In a real application the values of an INSERT statement will be form values. As a safe precaution always escape the values using addslashes() if get_magic_quotes_gpc() returns false. Below is an example of using form values with INSERT. It's the same as above except that the new username and password are taken from $_POST :

Example : adduser.php
Source code : adduser.phps








if(isset($_POST['add']))
{
include 'library/config.php';
include 'library/opendb.php';

$username = $_POST['username'];
$password = $_POST['password'];

$query = "INSERT INTO user (host, user, password, select_priv, insert_priv, update_ priv) VALUES ('localhost', '$username', PASSWORD('$password'), 'Y', 'Y', 'Y')";
mysql_query($query) or die('Error, insert query failed');

$query = "FLUSH PRIVILEGES";
mysql_query($query) or die('Error, insert query failed');

include 'library/closedb.php';
echo "New MySQL user added";
}
else
{
?>

Username
Password

}
?>



Source : http://www.php-mysql-tutorial.com/

Create MySQL Database With PHP

To create a database use the mysql_query() function to execute an SQL query like this

include 'config.php';
include 'opendb.php';

$query = "CREATE DATABASE phpcake";
$result = mysql_query($query);

include 'closedb.php';
?>

Please note that the query should not end with a semicolon.

PHP also provide a function to create MySQL database, mysql_create_db(). This function is deprecated though. It is better to use mysql_query() to execute an SQL CREATE DATABASE statement instead like the above example.

If you want to create MySQL database using PHP mysql_create_db() function you can do it like this :

include 'config.php';
include 'opendb.php';

mysql_create_db('phpcake');

include 'closedb.php';
?>

If you want to create tables in the database you just created don't forget to call mysql_select_db() to access the new database.

Note: some webhosts require you to create a MySQL database and user through your website control panel (such as CPanel). If you get an error when trying to create database this might be the case.
Creating the Tables

To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

Example : contact.php
Source code : contact.phps

include 'config.php';
include 'opendb.php';

$query = 'CREATE DATABASE phpcake';
$result = mysql_query($query);

mysql_select_db('phpcake') or die('Cannot select database');

$query = 'CREATE TABLE contact( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'cname VARCHAR(20) NOT NULL, '.
'cemail VARCHAR(50) NOT NULL, '.
'csubject VARCHAR(30) NOT NULL, '.
'cmessage TEXT NOT NULL, '.
'PRIMARY KEY(cid))';

$result = mysql_query($query);

include 'closedb.php';
?>

Of course when you need to create lots of tables it's a good idea to read the query from a file then save in $query variable instead of writing the query in your script.

include 'config.php';
include 'opendb.php';

$queryFile = 'myquery.txt';

$fp = fopen($queryFile, 'r');
$query = fread($fp, filesize($queryFile));
fclose($fp);
$result = mysql_query($query);

include 'closedb.php';
?>


Deleting a Database

As with creating a database, it is also preferable to use mysql_query() and to execute the SQL DROP DATABASE statement instead of using mysql_drop_db()

include 'config.php';
include 'opendb.php';

// ... do something here

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

// ... probably do something here too

include 'closedb.php';
?>


Source : http://www.php-mysql-tutorial.com/

Connect to MySQL Database

Opening a connection to MySQL database from PHP is easy. Just use the mysql_connect() function like this

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'petstore';
mysql_select_db($dbname);
?>

$dbhost is the name of MySQL server. When your webserver is on the same machine with the MySQL server you can use localhost or 127.0.0.1 as the value of $dbhost. The $dbuser and $dbpass are valid MySQL user name and password. For adding a user to MySQL visit this page : MySQL Tutorial

Don't forget to select a database using mysql_select_db() after connecting to mysql. If no database selected your query to select or update a table will not work.


Sometimes a web host will require you to specify the MySQL server name and port number. For example if the MySQL server name is db.php-mysql-tutorial.com and the port number is 3306 (the default port number for MySQL) then you you can modify the above code to :


$dbhost = 'db.php-mysql-tutorial.com:3306';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'petstore';
mysql_select_db($dbname);
?>

It's a common practice to place the routine of opening a database connection in a separate file. Then everytime you want to open a connection just include the file. Usually the host, user, password and database name are also separated in a configuration file.

An example of config.php that stores the connection configuration and opendb.php that opens the connection are :

Source code : config.phps , opendb.phps

// This is an example of config.php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'phpcake';
?>



// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
?>

So now you can open a connection to mysql like this :

include 'config.php';
include 'opendb.php';

// ... do something like insert or select, etc

?>

Closing the Connection

The connection opened in a script will be closed as soon as the execution of the script ends. But it's better if you close it explicitly by calling mysql_close() function. You could also put this function call in a file named closedb.php.

Source code : closedb.phps

// an example of closedb.php
// it does nothing but closing
// a mysql database connection

mysql_close($conn);
?>

Now that you have put the database configuration, opening and closing routines in separate files your PHP script that uses mysql would look something like this :

include 'config.php';
include 'opendb.php';

// ... do something like insert or select, etc

include 'closedb.php';
?>


Source : http://www.php-mysql-tutorial.com/