Connecting to MySQL

MySQL Tutorial contributed by Azekeal.

MySQL? Right then, firstly, SQL stands for structured query language

Anyway, here’s a quick PHP class for mysql connections:

<?php
// helper mysql function

class DB
{
// fill in these values for your s
var $host = "localhost";
var $db = "database";
var $user = "username";
var $pass = "p4Ssw0rD";

var $link;
var $sys;

function DB()
{
// calls the shutdown function at the end
register_shutdown_function( array( &$this, "_DB" ) );
$this->connected = false;
}
function connect()
{
$this->connected = true;

// connect to mysql
$this->link = mysql_connect($this->host, $this->user, $this->pass)
or die("Could not connect : ($this->host, $this->user) " . mysql_error());
mysql_select_db($this->db) or die("Could not select database");
}

function query($SQL, $singleResult = false, $silent = false)
{
global $DB;

// if not already connected, connect to mysql
if(!$DB->connected)
$DB->connect();

// run the query
$result = mysql_query($SQL);
if($result === false)
{
$returnError = mysql_error();
if(!$silent) error("Query failed : $returnError ($SQL)", __LINE__, __FILE__);
return array();
}

// make an array of data
$data = array();
while ($line = @mysql_fetch_array($result, MYSQL_ASSOC))
$data[] = $line;

@mysql_free_result($result);

// return the data
return ($singleResult===true && isset($data[0]) ? $data[0] : $data);
}
function _DB() // destructor
{
// close the connection
if($this->connected)
@mysql_close($this->link);
}
}

$DB = new DB();
?>

Save this as db.php

4 basic commands you need for sql:

SELECT, INSERT, DELETE, CREATE TABLE

key:
<word> – replace with name of something relevent (IE <table_name> -> mytable)
[ blah....] – stuff between square brackets is optional (IE select … [WHERE a=b])
( x | y ) – must have one of the options specified (IE where a=b (AND|OR) c=d..)

SELECT (*|<column_name>) FROM <table_name> [ WHERE var1=value1 [ (AND|OR)
var2=value2 ] ] [ORDER BY <column_name>] [LIMIT [<offset>,] <num_items> ]

To make a query – first include the file that you paste the above code into (include “db.php”, and then just us the following:

$results_array = DB::query("SELECT stuff FROM Tables_OR_whatever");

*data is returned in the array as follows:

  $results_array =
  Array(
  [0] => Array(
  "stuff" => "value1"

  ),

  [1] => Array(...),
  ...
  )

i.e. the first result from that query would be in $results_array[0]['stuff'];

* the number of results can be found by using count($results_array)

* if the query has no return data, the $results_array will be an empty array

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.