Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
MySQL version 5, PHP version 5 and mysqli

There's a great deal of code and data that uses MySQL version 3 and PHP version 4 and it works very well. Many users are NOT rushing to upgrade. But MySQL has now progressed on to MySQL version 5, and PHP is at version 5 too. This article considers some of the new facilities offered, and some of the upgrade issues involved.

MYSQL 3 -> 4 -> 5

MySQL version 3 had many years of life as "the current and latest" and was installed on many servers under many different operating systems. A great deal of software was written to make use of it. But times moved on and its "lean, mean and efficient" approach was becoming just TOO lean and mean; people wanted additional facilities that were available in other databases and MySQL at version 3 needed to be enhanced to catch up.

MySQL version 4.0 was described as an "enabling release" - not in itself providing a huge range of new facilities, but rather marking the way forward with a rewrite of much of the internal code. Some of the facilities such as InnoDb tables which used to be optional were rolled in as standard, giving facilities such as transaction handling in the default download / configuration.

At MySQL 4.1, further updates were introduced including a new security / login model that meant that all client programs had to be upgraded to access the data, unless a user account was created with a specifically-stated old style password.

MySQL 5.0 became generally available in October 2005. It adds stored procedures, Triggers, Views, a Data Dictionary and more.

PHP 3 ->4 -> 5

PHP 3 is now ancient history by computing standards. It was very much a structured programming language and it provided an excellent embedded scripting system for web pages.

In PHP 4, an object oriented model was added but being based on some of the older code, that model was good rather than excellent. PHP 4 went through quite a number of releases and there's still the occasional maintenance release. From 4.0 to 4.4, PHP really grew beyond all recognition.

The OO model has been fixed in PHP 5. The fixing of the OO model lead to a specific code incompatibility in relation to the copying of objects; it doesn't effect too many people BUT it was sufficient to require a change in the major release number ... and the opportunity was taken to make a number of other changes at the same time.

THE PHP / MYSQL DRIVER ISSUE

With the new client security model at MySQL 4.1, the MySQL drivers built in to PHP4 will only function if you have old passwords set on an account; if you need to set up such a password, the command you need in MySQL is something like

SET PASSWORD FOR "wwweb"@"localhost"
         = OLD_PASSWORD("paddington")

With the coming of the new security model, the MySQL authors also updated their open source license so that it's no longer compatible with the PHP distribution license, and as a result PHP version 5 doesn't ship with drivers for the current version of MySQL - but it does ship with the older version still if you're happy to use that, and it can be used with modern versions of MySQL if you're prepared to use the OLD_PASSWORD.

Looking forward, PHP now ships with a new class that defines the interface to MySQL - it's known as "mysqli" and that stands for MySQL Improved. It does NOT include the MySQL drivers - remember, it can't due to the licensing issues, but if you build PHP5 on a system on which MySQL 5 is already installed, you can include the recent MySQL drivers from the MySQL distribution. It's a very neat solution to the licensing issue.

MySQL functions with names starting mysql are the old style functions. Functions with names starting with mysqli are the new style functions. The names are NOT simply interchangeable - call sequences vary. If you're writing code in PHP that's to be portable between different database anyway, we suggest you use a wrapper as we teach on our PHP course.

RECOGNISING PROBLEMS

Here are some sample outputs if you get things wrong - the cause isn't always obvious!

1. You're trying to talk to a recent MySQL database using the old style routines, through a login which does NOT have an old style password set.


2. You're trying to use the new style routines in a version of MySQL which has been built with the old style routines, or has no MySQL support at all!


3. You're trying to use the new style routines but with the old sequence of parameters to them


This is what it SHOULD look like:


Original code using mysql functions will work under PHP 5 and MySQL 5 if PHP is built with the mysql drivers AND The MySQL 5 trainee account has the old password set.

Working example - mysql
New code using mysqli functions will work if the PHP has been built to use the new functions on a system on which MySQL had already been installed.

Working example - mysqli

See also MySQL training course

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

MySQL 5 and PHP 5
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [2172] PHP4 v PHP5 - Object Model Difference - (2009-05-11)
  [1766] Diagrams to show you how - Tomcat, Java, PHP - (2008-08-22)
  [1754] Upgrade from PHP 4 to PHP 5 - the TRY issue - (2008-08-15)
  [1455] Connecting to MySQL 5 from PHP on Mac OSX Leopard - (2007-12-03)
  [1417] What software version do we teach? - (2007-10-31)
  [1131] MySQL - Password security (authentication protocol) - (2007-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

Using MySQL Databases in PHP Pages
  [4483] Moving from mysql to mysqli - simple worked example - (2015-05-03)
  [4378] What FGW passengers want to talk about / and PHP programming to find out - (2015-01-01)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2628] An example of an injection attack using Javascript - (2010-02-08)
  [2561] The future of MySQL - (2010-01-03)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [947] What is an SQL injection attack? - (2006-11-27)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [915] Paging through hundreds of entries - (2006-11-05)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [666] Database design - get it right from first principles - (2006-04-02)
  [647] Checking for MySQL errors - (2006-03-15)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [515] MySQL - an FAQ - (2005-12-03)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

Interfacing Applications to MySQL Databases
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2381] Checking the database connection manually - (2009-08-28)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [663] Python to MySQL - (2006-03-31)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2019: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-my ... ysqli.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard