[Tutorial] MySQL & PHP

rdrake · **Posted:** Tue Mar 20, 2007 8:15 pm

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.

geekhut · **Posted:** Tue Oct 09, 2007 9:06 pm

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:

This is fun because you can quickly run multiple queries to lookup other values in a relational database.

geekhut · **Posted:** Tue Dec 18, 2007 12:13 am

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']);

Aziz · **Posted:** Tue Dec 18, 2007 10:01 am

What about doing it with PEAR and PDO :O (this topic is 9 months old, geekhut)

	Computer Science Canada Programming C, C++, Java, PHP, Ruby, Turing, VB Username: Password: Register
Wiki Blog Search Turing Chat Room Members