Index   -   Next  >

PHP With MySQL

PHP provides database connectivity

• Via ODBC - Open DataBase Connectivity
  • Generic - works with all databases supporting ODBC
  • Limited features
  • Possible performance overhead
• Via native database API's
  • Access to specific database features
  • Good performance
  • Large number of supported databases
    • Oracle
    • IBM DB2
    • IBM Informix
    • Microsoft SQL Server
    • Sybase
    • Ingres
    • PosgreSQL
    • MySQL

Let's have a closer look at MySQL

'The world's most popular open source database'

An Open Source, client/server RDBMS for SME's and beyond. Developed to provide a high performance, highly scalable, SQL database engine, MySQL was originally low on features but has grown up to be an enterprise level engine in a remarkably short period of time. This is in part due to it's incredible popularity attracting significant input from the open source community.

Being open source MySQL is available for just about any platform. If your platform is not available as a binary you can always compile it from source. Some sysops prefer to build on our server to optimise performance.

Good bits:

• ANSI SQL92 and ODBC level 0-2 standards
• Excellent International language support
• Wide programming language support
• Highly scalable
  • large tables
  • high speed
  • robust
• Easy to use
  • standard SQL with helpful extras
  • command line interface
  • lots of tools available
  • lots of code available
• Highly configurable access control
  • high security

Not so good bits:

• None

Bits not present when I first began to play with MySQL 3:
(studb.cms.gre.ac.uk is version 14.12 distribution 5.0.67)

• A Graphical User Interface
• Transactions
• Sub-selects
• Stored procedures
• Triggers
• Foreign keys (referential integrity)
• Views

MySQL was originally low on bells an whistles, which was part of it's appeal. If you are from an advanced database background MySQL versions up to 4 may have appeared to be lacking features.

All of this changed with the arrival of MySQL Version 5.0 in 2005 which introduced triggers and stored procedures. It is now possible to enforce referential integrity by using InnoDB tables instead of MyISAM tables. The PHP MySQL 'improved' API provides transactions, rollbacks. The situation continues to evolve with new releases of both MySQL and PHP.

The MySQL Community Server remains open source and free of charge but the Enterprise and Cluster releases take MySQL head-to-head with the established big players. This is arguably why Oracle were so keen to aquire MySQL when in April 2009 Sun Microsystems succumbed to the economic recession.

• Command line interface
  • Like most software from a *nix background the original user interface is text based
• Graphical user interfaces
  • Mysql Workbench - 3 tools in one
    • design and develop
    • administer
    • migrate
  • Navicat - cross db administration
• Web based GUI tools
  • phpMyAdmin - so popular it appears to have killed off all competition apart from...
  • MySQL Query Form - my homegrown lightweight alternative

Access control in MySQL

Access control is managed through a MySQL database called mysql.
This has tables to manage server access control from aspects such as contacting the server down to fine grained access of specific columns in specific tables. Some of the more important tables are listed below...

• user - controls access of named users to the MySQL server
• db - controls who has access to each database on the server
• host - restricts access by hostname
• tables_priv - controls table privileges per user within a database
• columns_priv - controls column privileges per user within a table

Note that the MySQL server user names are not same entities as the operating system user names, although they may be identical strings. MySQL has a totally separate authentication system including its own password function.

The MySQL user called 'root' has all privileges and manages user access.
The db table is provided here as an example as it is reasonably easy to follow.
The SQL statement: SHOW COLUMNS FROM db produces:

Index   -   Next  >

. .