Computer Science Canada

[Tutorial] MySQL & PHP
Author:  rdrake [ Tue Mar 20, 2007 8:15 pm ]
Post subject:  [Tutorial] MySQL & PHP
Been a while since I wrote this, posting it here.

This tutorial is intended to teach you how to connect to a MySQL server and select a database. In the future, I'll add more parts to it as I learn and remember more about MySQL. It's been a while since I've used it.

Introduction
Using MySQL with PHP and Apache can provide a very robust, dynamic, and powerful combination. PHP goes very well together with MySQL with support built right in in version 4.

It is generally a good idea to keep these following variables set in a separate file which you can include when needed if you need to access the same server/database in your web application:

php:
<?
$server = "localhost";
$databse = "db";
$username = "root";
$password = "";
?>
Doing things that way makes it so you only have to change one line of code instead of several.

Connecting to a Server
Always remember to include the db.php file with the following line in any script which needs to initiate a connection with the server:
php:
<?
include("db.php");
?>
Forgetting to include that line could lead to many errors.

The code for connecting to a server is as so:
php:
$connection = mysql_connect($server, $username, $password) or die("Could not connect to server!");
php:
$connection
Used by the next line for selecting the database. Not always needed unless you really want it, but still recommended to have.
php:
mysql_connect($server, $username, $password)
Connects to the database using the server location, username, and password provided in the db.php file.
php:
die("Could not connect to server!")
Informs the user of a database failure and stops the script from throwing out more errors.

Selecting Database
Selecting the database is really easy. Just use the following line of code:
php:
$db = mysql_select_db($database, $connection) or die("Can't select database!");
php:
$db
Allows for addressing the database later on.
php:
mysql_select_db($database, $connection)
Selects the database for the connection specified in Connecting to a Server. The $connection part is optional, but it allows for connections to multiple servers if required. If none is specified, the most recent connection is assumed.
php:
die("Can't select database!")
Again added to reduce the errors and make sure more errorous code is not executed.

Executing Statements & Queries
The code for executing queries on the server is very simple. First we must build the query with the following line:
php:
$query = "";
Then we must execute it with the following line:
php:
$result = mysql_query($query) or die("Could not execute query!");
php:
$result
Allows us to use the result obtained later in our script.
php:
mysql_query($query)
Executes the pre-built query.
php:
die("Could not execute query!")
Again used to reduce errors. If something doesn't go right in a script, you don't want your data being screwed up, right?

An example of this in action adds some data into the table 'users':
php:
$query = "INSERT INTO users VALUES (\'datatoadd\')";
$result = mysql_query($query);
Number of Rows in a Table
This section explains how to obtain how many rows there are in a row.

Firstly, as always, we must connect to the server and select a database:
php:
// Include the db.php file
include("db.php");

// Connect to the server
$connection = mysql_connect($server, $username, $password) or die("Could not connect to server!");

// Select the database
$database = mysql_select_db($db, $connection) or die("Could not select database!");
Then we must build our query and execute it.
php:
// Build the query
$query = "SELECT * FROM users";

// Finally execute the query
$result = mysql_query($query);
We use mysql_num_rows() to find out the number of rows in the result.
php:
// mysql_num_rows() can be used to find what we want
$num_rows = mysql_num_rows($result);
Finally we can output the number of rows found for us to see.
php:
// Echo out the number of rows
echo $num_rows;
It's just that easy.
Author:  geekhut [ Tue Oct 09, 2007 9:06 pm ]
Post subject:  Re: [Tutorial] MySQL & PHP
I have been using a simple database class for the last two years now and I find there is nothing simpler / faster.

The Class - mysql.class.php
code:

<?php
class mysql {
        private $linkid;
        private $host;
        private $user;
        private $pswd;
        private $db;
        private $result;
        private $querycount;

        function __construct($host, $user, $pswd, $db) {
                $this->host = $host;
                $this->user = $user;
                $this->pswd = $pswd;
                $this->db = $db;
        }

        function connect() {
                try {
                        $this->linkid = @mysql_connect($this->host, $this->user, $this->pswd);
                        if (! $this->linkid)
                        throw new Exception("Could not connect to mysql server");
                }
                catch (Exception  $e){
                        die($e->getMessage());
                }
        }

        function select() {
                try {
                        if (! @mysql_select_db($this->db, $this->linkid))
                        throw new Exception("Could not connect to mysql database");
                }
                catch (Exception $e){
                        die($e->getMessage());
                }
        }

        function query($query) {
                try {
                        $this->result = @mysql_query($query,$this->linkid);
                        if (! $this->result)
                        throw new Exception("the database query failed");
                }
                catch (Exception $e) {
                        echo($e->getMessage());
                }
                $this->querycount++;
                return $this->result;
        }

        function affectedRows() {
                $count = @mysql_affected_rows($this->linkid);
                return $count;
        }

        function numRows() {
                $count = @mysql_num_rows($this->result);
                return $count;
        }

        function fetchObject() {
                $row = @mysql_fetch_object($this->result);
                return $row;
        }

        function fetchRow() {
                $row = @mysql_fetch_row($this->result);
                return $row;
        }
        function fetchArray() {
                $row = @mysql_fetch_array($this->result);
                return $row;
        }

}
?>


How to use it - login.inc.php
code:

function login() {
# Create a new instance - specifying host, user, password, and database
$db = new mysql("localhost", "compsci", "topsecret", "blog");
$db->connect();
$db->select();
$uname = mysql_real_escape_string($_POST['uname']);
$passwd = mysql_real_escape_string($_POST['passwd']);
# Query
$db->query("SELECT * FROM users WHERE uname='$uname' AND passwd='$passwd' ");
$row = $db->fetchObject();
$first_name = $row->first_name;
$last_name = $row->last_name;

$count = $db->numRows();
     if ($count < 1) {
          echo "<div class='message">Username / Password do not match</div>";
          return home();
     } else {
          $_SESSION['authenticated'] = md5($first_name.$last_name);
          echo "<div class='message">Welcome $first_name $last_name</div>";
          return home();
     }

}

This is fun because you can quickly run multiple queries to lookup other values in a relational database.
Author:  geekhut [ Tue Dec 18, 2007 12:13 am ]
Post subject:  Re: [Tutorial] MySQL & PHP
Don't forget to sanitize your post vars with mysql_real_escape_string() or a ' or '1' = 1 can easily bypass a server not running magic_quotes!
code:

$uname = mysql_real_escape_string($_POST['uname']);
$passwd = mysql_real_escape_string($_POST['passwd']);
Author:  Aziz [ Tue Dec 18, 2007 10:01 am ]
Post subject:  RE:[Tutorial] MySQL & PHP
What about doing it with PEAR and PDO :O (this topic is 9 months old, geekhut)

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy