Using PHP5 With MySQL

MySQL Syntax and Commands

Command Description
CREATE Creates new database or table.
ALTER Modify existing tables.
SELECT Chooses the data you want.
DELETE Erases the data from your table.
DESCRIBE Lets you know the structure and specifics of the table.
INSET INTO tablename VALUES Puts values into the table.
UPDATE Lets you modify data already in the table.
DROP Deletes an entire table or database.

More Commonly Used Functions

Functions Description
mysql_connect ("hostname", "user", "password") Connects to the MySQL Server.
mysql_select_db ("database name") Equivalent to the MySQL command USE; make the selected database the active one.
mysql_query ("query") Used to send any type of MySQL command to the server.
mysql_fetch_rows ("results variables from query") Used to return a row of the entire results of a database query.
mysql_fetch_array ("results variables from query") Used to return several rows of the entire results of a database query.
mysql_error() Show the error message that has been returned directly from the MySQL server.

Connecting to the MySQL Server

Example,
$host = "localhost";
$user = "sroyit";
$pass = "sroyitpass";
$connect = mysql_connect ($host, $user, $pass);

or

$connect = mysql_connect ("localhost", "sroyit", "sroyitpass");

Creating a Database and Table

Example:

$connect = mysql_connect ("localhost", "sroyit", "sroyitpass") or die ("Deny, check your server connection!");
$create = mysql_query ("CREATE DATABASE IF NOT EXISTS movie_site") or die(mysql_error());
mysql_select_db ("movei_site");
$movie_create = " CREATE TABLE movie (
movie_id int(11) NOT NULL auto_increment,
movie_name varchar(255) NOT NULL,
movie_type tinyint(2) NOT NULL default 0,
movie_year int(4) NOT NULL default 0,
movie_leadactor int(11) NOT NULL default 0,
movie_director int(11) NOT NULL default 0,
PRIMARY KEY (movie_id),
KEY movie_type (movie_type, movie_year) ) ";
$result = mysql_query ($movie_create) or die (mysql_error());
?>

Inserting Rows in a Created Table

Example:

$connect = mysql_connect ("localhost", "sroyit", "sroyitpass") or dia ("Deny, check your server connection!");
mysql_select_db ("movie_site");
$movie_insert = "INSERT INTO movie (movie_id, movie_name, movie_type,
movie_year, movie_leadactor, movie_director)
VALUES (1,'Sumon',5,2003,1,2),
(2,'Matrix',5,2003,1,2),
(3,'Patriot',5,2003,1,2),
(4,'Tarminator',5,2003,1,2),
(5,'Titanic',5,2003,1,2),
(7,'After the sunset',5,2003,1,2)
";
$result = mysql_query ($movie_insert) or die (mysql_error());
?>

Configuring MySQL

Testing the Installation of MySQL

C:\apache\mysql\bin>mysqld
Or
C:\apache\mysql\bin>mysqld --install
Service successfully installed

C:\apache\mysql\bin>NET START MySQL
The MySQL service was started successfully.

C:\apache\mysql\bin>mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 3.23.47-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

C:\apache\mysql\bin>NET STOP MySQL
The MySql service is stopping.
The MySql service was stopped successfully.

Configuring the Installation

C:\apache\mysql\bin>NET START MySQL
The MySql service is starting.
The MySql service was started successfully.


C:\apache\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.47-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql
|
| sroyit |
| test |
+----------+
3 rows in set (0.00 sec)

mysql> USE mysql;
Database changed

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)

Changing Password

C:\apache\mysql\bin>mysqladmin -u root reload

C:\apache\mysql\bin>mysqladmin -u root password root

C:\apache\mysql\bin>mysql -h localhost -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.47-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

A Simple Tutorial

What do I need?

In this tutorial we assume that your server has activated support for PHP and that all files ending in .php are handled by PHP. On most servers, this is the default extension for PHP files, but ask your server administrator to be sure. If your server supports PHP, then you do not need to do anything. Just create your .php files, put them in your web directory and the server will automatically parse them for you. There is no need to compile anything nor do you need to install any extra tools.

Your first PHP-enabled page

Create a file named hello.php and put it in your web server's root directory (DOCUMENT_ROOT) with the following content:



PHP Test


echo '

Hello World

'
;
?>

Use your browser to access the file with your web server's URL, ending with the "/hello.php" file reference. When developing locally this URL will be something like http://localhost/hello.php or http://127.0.0.1/hello.php but this depends on the web server's configuration. f everything is configured correctly, this file will be parsed by PHP and the following output will be sent to your browser:




PHP Test


Hello World



This program is extremely simple and you really did not need to use PHP to create a page like this. All it does is display: Hello World using the PHP echo() statement. Note that the file does not need to be executable or special in any way. The server finds out that this file needs to be interpreted by PHP because you used the ".php" extension, which the server is configured to pass on to PHP.

Make a call to the phpinfo() function and you will see a lot of useful information about your system and setup such as available predefined variables, loaded PHP modules, and configuration settings. Get system information from PHP:

(); ?>

Something Useful

We are going to check what sort of browser the visitor is using. For that, we check the user agent string the browser sends as part of the HTTP request. This information is stored in a variable. Variables always start with a dollar-sign in PHP. The variable we are interested in right now is $_SERVER['HTTP_USER_AGENT']. $_SERVER is a special reserved PHP variable that contains all web server information. It is known as an autoglobal (or superglobal).

Example 2-3. Printing a variable (Array element)

echo $_SERVER['HTTP_USER_AGENT']; ?>

A sample output of this script may be:

Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)

Introduction

What is PHP?

PHP (recursive acronym for "PHP: Hypertext Preprocessor") is a widely-used Open Source general-purpose scripting language that is especially suited for Web development and can be embedded into HTML.

Example 1-1. An introductory example


Example



echo "Hi, I'm a PHP script!";
?>


The PHP code is enclosed in special start and end tags that allow you to jump into and out of "PHP mode".

This is going to be ignored.


echo 'While this is going to be parsed.'; ?>

This will also be ignored.


What can PHP do?

Anything. PHP is mainly focused on server-side scripting, so you can do anything any other CGI program can do, such as collect form data, generate dynamic page content, or send and receive cookies. But PHP can do much more.

There are three main areas where PHP scripts are used.

  • Server-side scripting. You need three things to make this work, 1.the PHP parser (CGI or server module), 2. a webserver and 3. a web browser. You need to run the webserver, with a connected PHP installation. You can access the PHP program output with a web browser, viewing the PHP page through the server.
  • Command line scripting. You can make a PHP script to run it without any server or browser. You only need the PHP parser to use it this way. This type of usage is ideal for scripts regularly executed using cron (on *nix or Linux) or Task Scheduler (on Windows). These scripts can also be used for simple text processing tasks.
  • Writing desktop applications. PHP is probably not the very best language to create a desktop application with a graphical user interface, but if you know PHP very well, and would like to use some advanced PHP features in your client-side applications you can also use PHP-GTK to write such programs. You also have the ability to write cross-platform applications this way. PHP-GTK is an extension to PHP, not available in the main distribution. If you are interested in PHP-GTK, visit its own website.

PHP can be used on all major operating systems, including Linux, many Unix variants (including HP-UX, Solaris and OpenBSD), Microsoft Windows, Mac OS X, RISC OS, and probably others. PHP has also support for most of the web servers today. This includes Apache, Microsoft Internet Information Server, Personal Web Server, Netscape and iPlanet servers, Oreilly Website Pro server, Caudium, Xitami, OmniHTTPd, and many others. For the majority of the servers PHP has a module, for the others supporting the CGI standard, PHP can work as a CGI processor.

With PHP you are not limited to output HTML. PHP's abilities includes outputting images, PDF files and even Flash movies (using libswf and Ming) generated on the fly. You can also output easily any text, such as XHTML and any other XML file. PHP can autogenerate these files, and save them in the file system, instead of printing it out, forming a server-side cache for your dynamic content.

One of the strongest and most significant features in PHP is its support for a wide range of databases. Writing a database-enabled web page is incredibly simple. The following databases are currently supported:

Adabas D InterBase PostgreSQL
dBase FrontBase SQLite
Empress mSQL Solid
FilePro (read-only) Direct MS-SQL Sybase
Hyperwave MySQL Velocis
IBM DB2 ODBC Unix dbm
Informix Oracle (OCI7 and OCI8)
Ingres Ovrimos

We also have a database abstraction extension (named PDO) allowing you to transparently use any database supported by that extension. Additionally PHP supports ODBC, the Open Database Connection standard, so you can connect to any other database supporting this world standard.

PHP also has support for talking to other services using protocols such as LDAP, IMAP, SNMP, NNTP, POP3, HTTP, COM (on Windows) and countless others. You can also open raw network sockets and interact using any other protocol. PHP has support for the WDDX complex data exchange between virtually all Web programming languages. Talking about interconnection, PHP has support for instantiation of Java objects and using them transparently as PHP objects. You can also use our CORBA extension to access remote objects.

PHP has extremely useful text processing features, from the POSIX Extended or Perl regular expressions to parsing XML documents. For parsing and accessing XML documents, PHP 4 supports the SAX and DOM standards, and you can also use the XSLT extension to transform XML documents. PHP 5 standardizes all the XML extensions on the solid base of libxml2 and extends the feature set adding SimpleXML and XMLReader support.

At last but not least, we have many other interesting extensions, the mnoGoSearch search engine functions, the IRC Gateway functions, many compression utilities (gzip, bz2, zip), calendar conversion, translation...

There was an error in this gadget