It is effective, it is free and it is powerful.

KBleivik
Site Admin
Posts: 88
Joined: Tue Jan 31, 2006 3:10 pm
Location: Moss Norway
Contact:

It is effective, it is free and it is powerful.

Postby KBleivik » Sat Dec 02, 2006 3:27 pm

In this post we give an short introduction to the power tools, MySQL, PHP and phpMyadmin. Here is a short list of the advantages:
  1. MySQL is based on the relational database concept, Structured Query Language (SQL). More advanced databases like Oracle, Sybase and Ms SQL are based on the same concept. Note that even if you may think of a table in a database as a collection of rows (records) and columns (fields) the data structure is more like a cloud. It is the query that gives structure to what is pulled from the database. There is a large theory of how you shall make a efficient database. Here are some points (KW's)
    • Indexing. An index is like the index of the book. Indexing makes the database more efficient.
    • Database normalization. This is a large subject. It is about good database design and construction.
    • Make queries as minimalistic as possible. Avoid SELECT * from ... if that is not what you are seeking.
    • Do not make more than one entry in a column. If you think of making more than one entry in a column, like name and email, that is an indication that you shall make an unique email table. That is in the end one of the main ideas in a relational database.
  2. The difference between Java Script and PHP is that Java Script is interpreted by the clients web browser while PHP is interpreted by the web server. One of the big selling points of PHP is that you can mix HTML code and PHP code in the same file. PHP is also getting more and more a true object oriented language and that makes it very efficient. Since PHP is an interpreted language it is more flexible at run time than a compiled language. PHP has a growing community, it has a lot of class libraries and extensions and it is developing fast to a true object oriented language. It seems that PHP is the preferred language for MySQL and it is very simple to write advanced content management systems by combining PHP with MySQL like this forum. This forum a phpBB solution, is a very good example of a content management system made in PHP and MySQL.
  3. phpMyAdmin is a tool that makes it easier to administer your MySQL database. I has tools to import and export content to and from the database. You avoid writing MySQL command line instructions.
For more information see the following headlines on my site OopSchool in my signature:
  • Optimize, structure and document your code. Cache your pages where possible.
  • Syndication and importing content to your site.
We end this post by giving an example of how object oriented programming can be used to make a database connection class. The class is included in the SPLIB library that follows with Fuecks (2005) "The PHP Anthology: Object Oriented PHP Solution, Volume 1 and 2" that is hereby varmly reccomended.

  1. The MySQL class

    <?php
    /**
    * @package SPLIB
    * @version $Id: MySQL.php,v 1.1 2003/12/12 08:06:07 kevin Exp $
    */
    /**
    * MySQL Database Connection Class
    * @access public
    * @package SPLIB
    */
    class MySQL {
    /**
    * MySQL server hostname
    * @access private
    * @var string
    */
    private $host;

    /**
    * MySQL username
    * @access private
    * @var string
    */
    private $dbUser;

    /**
    * MySQL user's password
    * @access private
    * @var string
    */
    private $dbPass;

    /**
    * Name of database to use
    * @access private
    * @var string
    */
    private $dbName;

    /**
    * MySQL Resource link identifier stored here
    * @access private
    * @var string
    */
    private $dbConn;

    /**
    * Stores error messages for connection errors
    * @access private
    * @var string
    */
    private $connectError;

    /**
    * MySQL constructor
    * @param string host (MySQL server hostname)
    * @param string dbUser (MySQL User Name)
    * @param string dbPass (MySQL User Password)
    * @param string dbName (Database to select)
    * @access public
    */
    public function MySQL ($host,$dbUser,$dbPass,$dbName) {
    $this->host=$host;
    $this->dbUser=$dbUser;
    $this->dbPass=$dbPass;
    $this->dbName=$dbName;
    $this->connectToDb();
    }

    /**
    * Establishes connection to MySQL and selects a database
    * @return void
    * @access private
    */
    private function connectToDb () {
    // Make connection to MySQL server
    if (!$this->dbConn = @mysql_connect($this->host,
    $this->dbUser,
    $this->dbPass)) {
    trigger_error('Could not connect to server');
    $this->connectError=true;
    // Select database
    } else if ( !@mysql_select_db($this->dbName,$this->dbConn) ) {
    trigger_error('Could not select database');
    $this->connectError=true;
    }
    }

    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    public function isError () {
    if ( $this->connectError )
    return true;
    $error=mysql_error ($this->dbConn);
    if ( empty ($error) )
    return false;
    else
    return true;
    }

    /**
    * Returns an instance of MySQLResult to fetch rows with
    * @param $sql string the database query to run
    * @return MySQLResult
    * @access public
    */
    public function & query($sql) {
    if (!$queryResource=mysql_query($sql,$this->dbConn))
    trigger_error ('Query failed: '.mysql_error($this->dbConn).
    ' SQL: '.$sql);
    return new MySQLResult($this,$queryResource);
    }
    }

    /**
    * MySQLResult Data Fetching Class
    * @access public
    * @package SPLIB
    */
    class MySQLResult {
    /**
    * Instance of MySQL providing database connection
    * @access private
    * @var MySQL
    */
    private $mysql;

    /**
    * Query resource
    * @access private
    * @var resource
    */
    private $query;

    /**
    * MySQLResult constructor
    * @param object mysql (instance of MySQL class)
    * @param resource query (MySQL query resource)
    * @access public
    */
    public function MySQLResult(& $mysql,$query) {
    $this->mysql=& $mysql;
    $this->query=$query;
    }

    /**
    * Fetches a row from the result
    * @return array
    * @access public
    */
    public function fetch () {
    if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) {
    return $row;
    } else if ( $this->size() > 0 ) {
    mysql_data_seek($this->query,0);
    return false;
    } else {
    return false;
    }
    }

    /**
    * Returns the number of rows selected
    * @return int
    * @access public
    */
    public function size () {
    return mysql_num_rows($this->query);
    }

    /**
    * Returns the ID of the last row inserted
    * @return int
    * @access public
    */
    public function insertID () {
    return mysql_insert_id($this->mysql->dbConn);
    }

    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    public function isError () {
    return $this->mysql->isError();
    }
    }
    ?>
  2. An example 13.php (File: SPLIB/Database/MySQL.php)

    <?php
    // Include the MySQL database connection class
    require_once('SPLIB/Database/MySQL.php');

    // A class which aggregates the MySQL class
    class Articles {
    var $db;
    var $result;
    // Accept an instance of the MySQL class
    function Articles(& $db) {
    // Assign the object to a local member variable
    $this->db = & $db;
    $this->readArticles();
    }
    function readArticles() {
    // Perform a query using the MySQL class
    $sql = "SELECT * FROM joke LIMIT 0,5";
    $this->result = & $this->db->query($sql);
    }
    function fetch() {
    return $this->result->fetch();
    }
    }

    // Create an instance of the MySQL class
    $db = & new MySQL('localhost','root','mypasswd','ijdb');
    // Create an instance of the Article class, passing it the MySQL object
    $articles = & new Articles($db);

    while ( $row = $articles->fetch() ) {
    echo ( '<pre>' );
    print_r($row);
    echo ( '</pre>' );
    }
    ?>

Source:
Harry Fuecks (2005): The PHP Anthology Object Oriented PHP Solutions Volume I chapter 2.
Kjell Gunnar Bleivik
Make it simple, as simple as possible but no simpler: | DigitalPunkt.no |

Return to “MySQL, PHP and phpMyAdmin”

Who is online

Users browsing this forum: No registered users and 1 guest