PHP & MySQL

PHP will work with virtually all database software, including Oracle and Sybase but most commonly used is freely available MySQL database.

What you should already have ?

• You have gone through MySQL tutorial to understand MySQL Basics.

• Downloaded and installed a latest version of MySQL.

• Created database user guest with password guest123.

• If you have not created a database then you would need root user and its password to create a database.

We have divided this chapter in the following sections −

• Connecting to MySQL database − Learn how to use PHP to open and close a MySQL database connection.

• Create MySQL Database Using PHP − This part explains how to create MySQL database and tables using PHP.

• Delete MySQL Database Using PHP − This part explains how to delete MySQL database and tables using PHP.

• Insert Data To MySQL Database − Once you have created your database and tables then you would like to insert your data into created tables. This session will take you through real example on data insert.

• Retrieve Data From MySQL Database − Learn how to fetch records from MySQL database using PHP.

• Using Paging through PHP − This one explains how to show your query result into multiple pages and how to create the navigation link.

• Updating Data Into MySQL Database − This part explains how to update existing records into MySQL database using PHP.

• Deleting Data From MySQL Database − This part explains how to delete or purge existing records from MySQL database using PHP.

• Using PHP To Backup MySQL Database − Learn different ways to take backup of your MySQL database for safety purpose.

PHP interaction

PHP started out as a small open source project that evolved as more and more people found out how useful it was. Rasmus Lerdorf unleashed the first version of PHP way back in 1994.
 PHP is a recursive acronym for "PHP: Hypertext Preprocessor".
 PHP is a server side scripting language that is embedded in HTML. It is used to manage dynamic content, databases, session tracking, even build entire e- commerce sites.
 It is integrated with a number of popular databases, including MySQL, PostgreSQL, Oracle, Sybase, Informix, and Microsoft SQL Server.
 PHP is pleasingly zippy in its execution, especially when compiled as an Apache module on the Unix side. The MySQL server, once started, executes even very complex queries with huge result sets in record-setting time.
 PHP supports a large number of major protocols such as POP3, IMAP, and LDAP. PHP4 added support for Java and distributed object architectures (COM and CORBA), making n-tier development a possibility for the first time.
 PHP is forgiving: PHP language tries to be as forgiving as possible.
 PHP Syntax is C-Like.

How to Install Apache Web Server on Windows

Click Here!

What is a Web Server?

A web server is software that listens for requests and returns data (usually a file). When you type “www.mysite.com”, the request is forwarded to a machine running web server software which returns a file back to your browser, e.g. the contents of index.html. The browser might then make further requests based on the HTML content, e.g. CSS, JavaScript, and graphic files.

Since the web server sits between your browser and the requested file, it can perform processing that is not possible by opening an HTML file directly. For example, it can parse PHP code which connects to a database and returns data.

You can use your host’s web server for testing, but uploading will become tiresome and changes could go live before they had been fully tested. What you need is a local web server installation.

Why Apache?

In general, I would recommend using the web server software that your web host uses. Unless you are creating ASP.NET applications on Microsoft IIS, your host is likely to use Apache: the most widespread and fully-featured web server available. It is open-source project so it does not cost anything to download or install.

The following instructions describe how to install Apache on Windows. Mac OSX comes with Apache and PHP, although you might need to enable them. Most Linux users will have Apache pre-installed or available in the base repositories.

All-in-One packages

There are some excellent all-in-one Windows distributions that contain Apache, PHP, MySQL and other applications in a single installation file, e.g. XAMPP (including a Mac version), WampServer andWeb.Developer. There is nothing wrong with using these packages, although manually installing Apache will help you learn more about the system and its configuration options.

The Apache Installation Wizard

An excellent official .msi installation wizard is available from the Apache download page. This option is certainly recommended for novice users or perhaps those installing Apache for the first time.

Manual Installation

Manual installation offers several benefits:

• backing up, reinstalling, or moving the web server can be achieved in seconds
• you have more control over how and when Apache starts
• you can install Apache anywhere, such as a portable USB drive (useful for client demonstrations).

Step 1: configure IIS, Skype and other software (optional)

If you have a Professional or Server version of Windows, you may already have IIS installed. If you would prefer Apache, either remove IIS as a Windows component or disable its services.

Apache listens for requests on TCP/IP port 80. The default installation of Skype also listens on this port and will cause conflicts. To switch it off, start Skype and choose Tools > Options > Advanced > Connection. Ensure you untick “Use port 80 and 443 as alternatives for incoming connections”.

Step 2: download the files

We are going to use the unofficial Windows binary from Apache Lounge. This version has performance and stability improvements over the official Apache distribution, although I am yet to notice a significant difference. However, it is provided as a manually installable ZIP file fromwww.apachelounge.com/download/

You should also download and install the Windows C++ runtime from Microsoft.com. You may have this installed already, but there is no harm installing it again.

As always, remember to virus scan all downloads.

Step 2: extract the files

We will install Apache in C:Apache2, so extract the ZIP file to the root of the C: drive.

Apache can be installed anywhere on your system, but you will need to change the configuration file paths accordingly…

Step 3: configure Apache

Apache is configured with the text file confhttpd.conf contained in the Apache folder. Open it with your favourite text editor.

Note that all file path settings use a ‘/’ forward-slash rather than the Windows backslash. If you installed Apache anywhere other than C:Apache2, now is a good time to search and replace all references to “c:/Apache2″.

There are several lines you should change for your production environment:

Line 46, listen to all requests on port 80:

Listen *:80

Line 116, enable mod-rewrite by removing the # (optional, but useful):

LoadModule rewrite_module modules/mod_rewrite.so

Line 172, specify the server domain name:

ServerName localhost:80

Line 224, allow .htaccess overrides:

AllowOverride All

Step 4: change the web page root (optional)

By default, Apache return files found in its htdocs folder. I would recommend using a folder on an another drive or partition to make backups and re-installation easier. For the purposes of this example, we will create a folder called D:WebPages and change httpd.conf accordingly:

Line 179, set the root:

DocumentRoot "D:/WebPages"

and line 204:

<Directory "D:/WebPages">

Step 5: test your installation

Your Apache configuration can now be tested. Open a command box (Start > Run > cmd) and enter:

cd Apache2bin
httpd -t

Correct any httpd.conf configuration errors and retest until none appear.

Step 6: install Apache as a Windows service

The easiest way to start Apache is to add it as a Windows service. From a command prompt, enter:

cd Apache2bin
httpd -k install

Open the Control Panel, Administrative Tools, then Services and double-click Apache2.2. Set the Startup type to “Automatic” to ensure Apache starts every time you boot your PC.

Alternatively, set the Startup type to “Manual” and launch Apache whenever you choose using the command “net start Apache2.2″.

Step 7: test the web server

Create a file named index.html in Apache’s web page root (either htdocs or D:WebPages) and add a little HTML code:

<html>
<head><title>testing Apache</title></head>
<body><p>Apache is working!</p></body>
</html>

Ensure Apache has started successfully, open a web browser and enter the address http://localhost/. If all goes well, your test page should appear.

In general, most problems will be caused by an incorrect setting in the httpd.conf configuration file. Refer to the Apache documentation if you require further information.

• See also:

• How to Install PHP
• How to Install MySQL
• MySQL: the Pros and Cons of MyISAM Tables
• MySQL: the Pros and Cons of InnoDB Tables
• How to Use MySQL Foreign Keys for Quicker Database Development

Have you installed Apache? Do you use an alternative web server?

How to Install PHP on Windows

Hopefully, you now have a working local installation of Apache on your Windows PC. In this article we will install PHP 5 as an Apache 2.2 module.

Why PHP?

PHP remains the most widespread and popular server-side programming language on the web. It is installed by most web hosts, has a simple learning curve, close ties with the MySQL database, and an excellent collection of libraries to cut your development time. PHP may not be perfect, but it should certainly be considered for your next web application. Both Yahoo and Facebook use it with great success.

Why Install PHP Locally?

Installing PHP on your development PC allows you to safely create and test a web application without affecting the data or systems on your live website. This article describes PHP installation as a module within the Windows version of Apache 2.2. Mac and Linux users will probably have it installed already.

All-in-One packages

There are some excellent all-in-one Windows distributions that contain Apache, PHP, MySQL and other applications in a single installation file, e.g. XAMPP (including a Mac version), WampServer andWeb.Developer. There is nothing wrong with using these packages, although manually installing Apache and PHP will help you learn more about the system and its configuration options.

The PHP Installer

Although an installer is available from php.net, I would recommend the manual installation if you already have a web server configured and running.

Manual Installation

Manual installation offers several benefits:

• backing up, reinstalling, or moving the web server can be achieved in seconds (see 8 Tips for Surviving PC Failure) and
• you have more control over PHP and Apache configuration.

Step 1: download the files

Download the latest PHP 5 ZIP package from www.php.net/downloads.php

As always, virus scan the file and check its MD5 checksum using a tool such as fsum.

Step 2: extract the files

We will install the PHP files to C:php, so create that folder and extract the contents of the ZIP file into it.

PHP can be installed anywhere on your system, but you will need to change the paths referenced in the following steps.

Step 3: configure php.ini

Copy C:phpphp.ini-recommended to C:phpphp.ini. There are several lines you will need to change in a text editor (use search to find the current setting).

Define the extension directory:

extension_dir = "C:phpext"

Enable extensions. This will depend on the libraries you want to use, but the following extensions should be suitable for the majority of applications (remove the semi-colon comment):

extension=php_curl.dll
extension=php_gd2.dll
extension=php_mbstring.dll
extension=php_mysql.dll
extension=php_mysqli.dll
extension=php_pdo.dll
extension=php_pdo_mysql.dll
extension=php_xmlrpc.dll

If you want to send emails using the PHP mail() function, enter the details of an SMTP server (your ISP’s server should be suitable):

[mail function]
; For Win32 only.
SMTP = mail.myisp.com
smtp_port = 25

; For Win32 only.
sendmail_from = my@emailaddress.com

Step 4: add C:php to the path environment variable

To ensure Windows can find PHP, you need to change the path environment variable. From the Control Panel, choose System, (then “Advanced system settings” in Vista), select the “Advanced” tab, and click the “Environment Variables” button.

Scroll down the System variables list and click on “Path” followed by the “Edit” button. Enter “;C:php” to the end of the Variable value line (remember the semi-colon).

Now OK your way out. You might need to reboot at this stage.

Step 5: configure PHP as an Apache module

Ensure Apache is not running (use “net stop Apache2.2″ from the command line) and open its confhttpd.conf configuration file in an editor. The following lines should be changed:

Line 239, add index.php as a default file name:

DirectoryIndex index.php index.html

At the bottom of the file, add the following lines (change the PHP file locations if necessary):

# PHP5 module
LoadModule php5_module "c:/php/php5apache2_2.dll"
AddType application/x-httpd-php .php
PHPIniDir "C:/php"

Save the configuration file and test it from the command line (Start > Run > cmd):

cd Apache2bin 
httpd -t

Step 6: test a PHP file

Create a file named index.php in Apache’s web page root (either htdocs or D:WebPages) and add this code:

<?php phpinfo(); ?>

Ensure Apache has started successfully, open a web browser and enter the address http://localhost/. If all goes well, a “PHP version” page should appear showing all the configuration settings.

See also:

• How to Install Apache
• How to Install MySQL
• MySQL: the Pros and Cons of MyISAM Tables
• MySQL: the Pros and Cons of InnoDB Tables
• How to Use MySQL Foreign Keys for Quicker Database Development

Best of luck!

If you enjoyed reading this post, you’ll love Learnable; the place to learn fresh skills and techniques from the masters. Members get instant access to all of SitePoint’s ebooks and interactive online courses, likePHP & MySQL Web Development for Beginners.