OOP php - database connection and run script

[zeezack]

Hi guys,

I am stuck with how to make this database oop conenction...this is one of my first scripts using oop... please help me improve my coding practice...

 

 

<?php

//define class
class database
{
//define variables
var $str_schema;
var $str_host;
var $str_user;
var $str_password;

var $res_connection;


// The constructor
function __construct($str_schema, $str_host, $str_user, $str_password)
{
	// Set-up the class variables from the parameters.
	$this->str_schema   = (string) $str_schema; // It's good practice to use type-casting.
	$this->str_host     = (string) $str_host;
	$this->str_user     = (string) $str_user;
	$this->str_password = (string) $str_password;
}

// The destructor
function __destruct()
{
	// Close a mysql connection we might've created earlier
	if ($this->res_connection)
	{
		  mysql_close($this->res_connection);
	}
}



/*methods*/

function connect(){
	// Connect to MySQL
	$this->res_connection = mysql_connect($this->str_schema, $this->str_user, $this->str_password);

	// Select desired database
	mysql_select_db($this->str_host, $this->res_connection);
}	

function query($sql){
	// Query SQL
	$this->$result = mysql_query($sql);
	return $this->$result;
}		


function fetch(){
	// Fetch result		
	$this->$row = mysql_fetch_assoc($this->$result);	
	return $this->$row;
}		

}




// Instantiate MySQL class, connect to MySQL and select database
$db = new database('localhost', 'BATMAN', 'user', 'password');
$db->connect();

// Perform a query selecting five articles
$sql = 'SELECT * FROM SPARE LIMIT 0,5';


$result = $db->query($sql); // Creates a MySQLResult object

// Display the results
while ($row = $result->fetch()) {
  // Display results here
  echo''.$row[0].'';
}

?>

[rhodesa]

First, SCHEMA is a DB2/MSSQL concept. In MySQL, there is just a DB to select. Here is how I would change your class:

 

<?php

//define class
class database
{
//define variables
var $str_host;
var $str_db;
var $str_user;
var $str_password;

var $res_connection;
var $res_result;


// The constructor
function __construct($str_host, $str_db, $str_user, $str_password)
{
	// Set-up the class variables from the parameters.
	$this->str_host     = (string) $str_host;
	$this->str_db       = (string) $str_schema;
	$this->str_user     = (string) $str_user;
	$this->str_password = (string) $str_password;
}

// The destructor
function __destruct()
{
	// Close a mysql connection we might've created earlier
	if ($this->res_connection)
	{
		  mysql_close($this->res_connection);
	}
}



/*methods*/

function connect(){
	// Connect to MySQL
	$this->res_connection = mysql_connect($this->str_host, $this->str_user, $this->str_password);
	if(!$this->res_connection)
	{
		return false;
	}

	// Select desired database
	return mysql_select_db($this->str_db, $this->res_connection);
}	

function query($sql){
	// Query SQL
	return $this->res_result = mysql_query($sql, $this->res_connection);
}		


function fetch(){
	// Fetch result		
	return mysql_fetch_assoc($this->res_result);	
}		

}




// Instantiate MySQL class, connect to MySQL and select database
$db = new database('localhost', 'BATMAN', 'user', 'password');
$db->connect();

// Perform a query selecting five articles
$sql = 'SELECT * FROM SPARE LIMIT 0,5';


$db->query($sql); // Creates a MySQLResult object

// Display the results
while ($row = $db->fetch()) {
  // Display results here
  print_r($row);
  echo''.$row[0].''; //Can't use 0 since we used fetch_assoc
}

?>

[zeezack]

function fetch(){

// Fetch result

return mysql_fetch_assoc($this->res_result);

}

 

 

is not a valid arguement?

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

[rhodesa]

The query is probably failing

 

function query($sql){
	// Query SQL
	$this->res_result = mysql_query($sql, $this->res_connection)
		or die("Query Failed");
}		


function fetch(){
	// Fetch result		
	return mysql_fetch_assoc($this->res_result);	
}

[zeezack]

oo ooo checked the query... but it is still not working... claims the query has failed...but I know the new query here works fine.

 

 

[zeezack]

Still need help here guys...what exactly is wrong ?

[Daniel0]

What does the error message say?

[zeezack]

It says query failed.

[Daniel0]

Well... MySQL must return an error try to change it to:

or die("Query Failed: " . mysql_error());

[zeezack]

Claims no database is selected?

[zeezack]

Ah cracked it ........ now is the following code - correct - good coding practice? etc....

 

 

 

 

 

<?php


//define class
class database
{
//define variables
var $str_host;
var $str_db;
var $str_user;
var $str_password;

var $res_connection;
var $res_result;


// The constructor
function __construct($str_host, $str_db, $str_user, $str_password)
{
	// Set-up the class variables from the parameters.
	$this->str_host     = (string) $str_host;
	$this->str_db       = (string) $str_db;
	$this->str_user     = (string) $str_user;
	$this->str_password = (string) $str_password;
}

// The destructor
function __destruct()
{
	// Close a mysql connection we might've created earlier
	if ($this->res_connection)
	{
		  mysql_close($this->res_connection);
	}
}



/*methods*/

function connect(){
	// Connect to MySQL
	$this->res_connection = mysql_connect($this->str_host, $this->str_user, $this->str_password) or die("Connection Failed: " . mysql_error());;
	if(!$this->res_connection)
	{
		return false;
	}

	// Select desired database
	return mysql_select_db($this->str_db, $this->res_connection);
}	


function query($sql){
	// Query SQL
	$this->res_result = mysql_query($sql, $this->res_connection) or die("Query Failed: " . mysql_error());; 
}		



function fetch(){
	// Fetch result		
	return mysql_fetch_assoc($this->res_result);	
}		

}




// Instantiate MySQL class, connect to MySQL and select database
$db = new database('localhost', 'mydb', 'user', 'password');
$db->connect();

// Perform a query selecting five articles
$sql = ' SELECT * FROM `BATMAN` LIMIT 0 , 30 ';


$db->query($sql); // Creates a MySQLResult object

// Display the results
while ($row = $db->fetch()) {
  // Display results here
  print_r($row);
  echo''.$row[0].''; //Can't use 0 since we used fetch_assoc
}

?>
?>

[rhodesa]

One thing I would do differently is pass the host/user/pass/db with the connect() function instead of the constructor. Then you can use them and don't have to save them in the class.

[AP81]

Just one tip:

 

Replace var with private. var is PHP4 and is redundant (but still supported in PHP5).  So you should protected/private/public in place of var.  Other than that it looks good.

 

I generally include a couple more functions: GetRowCount and GetRowsAffected.  It makes it handy to get the number of rows a query returns and to be able to find how many rows have been affected by a delete/insert.

 

Also there isn't a need to typecast all the variables to strings in the constructor.  They are all strings anyway.  No harm leaving it there though.

[zeezack]

Sorry guys did you mean like this?

 

Probably best to give code examples from now on. 

 

 

 

 

<?php


//define class
class database
{
//define variables
private $str_host;
private $str_db;
private $str_user;
private $str_password;

private $res_connection;
private $res_result;


// The constructor
function __construct($str_host, $str_db, $str_user, $str_password)
{
	// Set-up the class variables from the parameters.
	$this->str_host     = (string) $str_host;
	$this->str_db       = (string) $str_db;
	$this->str_user     = (string) $str_user;
	$this->str_password = (string) $str_password;
}

// The destructor
function __destruct()
{
	// Close a mysql connection we might've created earlier
	if ($this->res_connection)
	{
		  mysql_close($this->res_connection);
	}
}



/*methods*/

function connect($str_host, $str_db, $str_user, $str_password){
	// Connect to MySQL
	$this->res_connection = mysql_connect($this->str_host, $this->str_user, $this->str_password) or die("Connection Failed: " . mysql_error());;
	if(!$this->res_connection)
	{
		return false;
	}

	// Select desired database
	return mysql_select_db($this->str_db, $this->res_connection);
}	


function query($sql){
	// Query SQL
	$this->res_result = mysql_query($sql, $this->res_connection) or die("Query Failed: " . mysql_error());; 
}		



function fetch(){
	// Fetch result		
	return mysql_fetch_assoc($this->res_result);	
}		

}




// Instantiate MySQL class, connect to MySQL and select database
$db = new database('localhost', 'mydb', 'user', 'password');
$db->connect();

// Perform a query selecting five articles
$sql = ' SELECT * FROM `BATMAN` LIMIT 0 , 30 ';


$db->query($sql); // Creates a MySQLResult object

// Display the results
while ($row = $db->fetch()) {
  // Display results here
  print_r($row);
  echo''.$row[0].''; //Can't use 0 since we used fetch_assoc
}

?>
?>

[Cobby]

It could also be a good idea to define method scope as well. Do you understand the difference between public, protected and private? And I assume that the str_ at the start is reference to the variable being a string? I don't think that sort of practice is necessary, PHP is a loosely type language so unofficially specifying the variable type isn't need.

 

Same with the type casting when you set the class variables, its something that you can do, but isn't necessary. I haven't tried it, but if you pass, say an integer as the database username, PHP would convert it to a string anyway.

[zeezack]

I kind of do.. but how do you apply them and why in this case?

 

public - available to all classes

protected - availalbe only to the parent class

private - available to only the class that defines it.