Thrudigital

Important: newer versions of each of the below-mentioned software may be available.

PHP as a programming language and MySQL as a database system are the most common technologies used for developing websites. The easiest way to install these systems is to use an Apache web server (currently at v2.0). The advantages of the 2.0 version are: hybrid multithread/multiprocessor mode; support of multiprotocols; stream filtering; support of IPv6; new API module. Also, there are other new modules: mod_auth_db, mod_auth_digest, mod_charset_lite, mod_dav, mod_file_cache. Also, mod_ssl is the part of the Apache distribution.

Sponsored links

After installing the Apache web server, it is necessary to install the PHP core files. This package is called php5 and is freely available. In addition, it is necessary to install the php5-mysql package which contains functions necessary to access the MySQL database servers. The phpMyAdmin tool is recommended for installation as well, since it allows the user to create, modify or delete databases, tables or rows, run any SQL command, manage keys within tables, update privileges, export data into various formats, and communicate in 43 languages.

The MySQL databases package is called mysql. Contrary to common belief, MySQL, it can be used for databases as large as 100gb. Of course, there are other useful packages, for example, mysql-administrator, mysql-client, mysql-Max (which supports transaction tables), mysql-query-browser, and so on.

PHP and MySQL run easily on both Linux and Windows servers. If you’re trying to install the web server with PHP and MySQL under Windows, the best option is probably WAMP. It’s easy-to-install and you don’t have to do anything apart from clicking “OK” or “Next”. Installation under Linux is generally similar to installing any other package.

After installing the necessary modules, you should also be able to connect to the MySQL server from the shell.

Snapshots

The MySQL Administrator

The MySQL Administrator and health of MySQL

The backup of MySQL

Status of MySQL via shell

If your server is running properly, you should be able to ping it

Executing MySQL queries in PHP

First, it is necessary to create a connection between your application and server:

$link = mysql_connect($server, $user, $passwd)
or die (”Could not connect to mysql because “.mysql_error());

mysql_select_db($dbase)
or die (”Could not select database because “.mysql_error());

If everything is alright, the script will continue. Say that we want to execute a simple select command. It could look like this one:

$result = mysql_query(”select * from some_table where id = ‘$some_variable’ order by id desc limit 5 “, $link)
or die (”Could not read data because “.mysql_error());

Naturally, MySQL commands can be more complex and it’s up to the programmer to design the application. At the end of each script we should close the MySQL connection, like this:

mysql_close();

Sometimes you need to close one connection while keeping a second connection live (if you’re using 2 or more databases for instance). In this scenario it is necessary to use the argument within the mysql_close function. If we wanted to close our $link connection, then it would look like this:

mysql_close($link);

Errors

Basically, there are two types of errors: system and command. The first kind of error is generated by the server. For instance say that your script’s runtime is more than 10 seconds where operations with the MySQL database are interrupted by operations which work with data or files. Sometimes the MySQL connection may be lost: it’s not a fatal error, and all that is required is to press F5 (refresh). Naturally, you can test the script for potential problems by creating a simple function which tests the connection. If it’s not live any more, simply call the reconnect function.

Command errors should be eliminated during the application’s development. Such errors usually occur as the result of referring to invalid names of columns in MySQL tables or invalid usage of the where, order, join, group by clauses.

The third type of error can be called “logical errors”. These errors are not common and this is why I don’t view them as normal errors. For example, if the programmer opens a connection after executing every single MySQL query, it’s a logical error. The same applies to closing connections without any apparent reason. Also, selecting 1,000 rows in the situation when only 5 are necessary is a big logical error. The update and delete command can destroy the whole database as well, if the where clause is used incorrectly. Say that we want to update a few records, but the programmer forgot to use the where clause. In this case every single row in our table will be affected. Another example of logical error is saving duplicate data. This has a lot to do with MySQL optimisation.

Examples of functions

The select function:

$result = mysql_query(”select * from $table_cats where publicy = ‘YES’ order by id desc;”, $link)
or die (”Could not read data because “.mysql_error());

if (mysql_num_rows($result))
while ($qry = mysql_fetch_array($result))
$path[$qry[id]] = $qry[fname];

The insert function:

$result = mysql_query(”insert into $table_poll values (’NULL’ , ‘$poll_text’ , ‘0′ , ‘0′);”, $link)
or die (”Could not insert data because “.mysql_error());

The delete function:

$result = mysql_query(”delete from $table_wban where id = ‘$del’;”, $link)
or die (”Could not delete data because “.mysql_error());

The update function:

$result = mysql_query(”update $table_comms set text = ‘$text’, who = ‘$who’ where id=’$id’;”, $link)
or die (”Could not update data because “.mysql_error());