MySQL Class problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

MySQL Class problem

Post by Nay »

For the past day I've been making (well actually just to play around) a MySQL connecter/iterator class. Anyhow the connecting class and iterating class are seperate classes. MySQL is the parent class while Iterator is a child. The problem is that something like:

Code: Select all

$mysql = new MySQL();
$mysql->selectDatabase('test');
$result = mysql_query('SELECT * FROM test');
works fine but:

Code: Select all

$mysql = new MySQL();
$mysql->selectDatabase('test');
$result = new Iterator('SELECT * FROM test');
tells me that the resource is not valid. As in $this->mysql_link is not being passed when the Iterator inherits from MySQL when instantiated.

I have done a previous class - which I unfortunately lost it in a format -> I swear I backed it up but oh well. Ouch 8O...

I've been scratching my head and tearing my hair for a day so maybe I'm missing something. Please do advise. If you do pick up and tips/bugs for me - also do tell. Thanks.

-Nay

The code:

Code: Select all

<?php

// MySQL class to handle MySQL database connections and database selections

Class MySQL {

   var $mysql_link;
   var $mysql_db;

   // Constructor
   function MySQL($flag = 0) {
   		// return (void)
		// (Optional) (int) $flag = Flag 0 (False) or 1 (True) to select the
		//                    default MySQL database after connecting to MySQL

   		// Require Config file
   		require('class_config.php');

   		$Config = new Config();

   		// Set local variables
   		$mysql_host = $Config->getVar('mysql_host');
   		$mysql_user = $Config->getVar('mysql_user');
   		$mysql_pass = $Config->getVar('mysql_pass');

   		// If unable to get variable values:
   		if( !empty($mysql_host) && !empty($mysql_user) && !empty($mysql_pass) ) {
   			die('Could not get requied variables for connection in MySQL::MySQL().');
   		}

   		// Connect to MySQL
   		$tmp_link = mysql_connect($mysql_host, $mysql_user, $mysql_pass);

   		// If failed to connect to MySQL
   		if( empty($tmp_link) ) {
   			die('Could not connect to MySQL in MySQL:MySQL(). MySQL returned:<br />' . mysql_error());
   		}

   		// Set connection link
   		$this->mysql_link = $tmp_link;

   		// If flag is set, select default database
   		if($flag == 1) {
   			// Get default database name, and select
   			$mysql_df_db = $Config->getVar('mysql_df_db');
   			$this->selectDatabase($mysql_df_db);
   		}

   }

   function selectDatabase($db) {
   		// return (bool)
   		// (str) $db = The name of the database to select

		if( empty($db) ) {
			die('Error: Argument not given or is empty for MySQL::selectDatabase()');
		}

		// Select database
		$tmp_select = mysql_select_db($db, $this->mysql_link);

		// If unable to select database:
		if( empty($tmp_select) ) {
			die('Error: Could not select database ' . $db . ' in MySQL::selectDatabase(). MySQL returned: <br />' . mysql_error());
		}

		// Set selection
		$this->mysql_db = $tmp_select;

		return $true;
   }

}

// Iterator Class, child class of MySQL - to handle any MySQL queries

Class Iterator extends MySQL {

	// Class variables
	var $last_query;
	var $last_result;
	var $last_rows; // Number of rows of last query
	var $last_array; // From mysql_fetch_array($last_result);

	// Variable for Iterator::nextResult();
	var $counter = -1;

	// Constructor
	function Iterator($query = '') {

		// return (void)
		// (Optional) (str) $query = The MySQL query string to query.

		// Check if connected to MySQL
		if( empty($this->mysql_link) ) {
			die('Unable to instantiate Iterator class - no MySQL connection present.');
		}

		// If the query is not empty, query and fetch result array
		if( !empty($query) ) {
			$this->queryFetch($query);
		}

	}

	function queryFetch($query) {
		// return(int)
		// (str) $query = MySQL query string (eg: 'SELECT name FROM admins')

		// Check is query is empty
		if( empty($query) ) {
			die('Query given for Iterator::queryFetch is empty');
		}

		// Query given string
		$tmp_result = mysql_query($query, $this->mysql_link);

		// If query failed to execute/returned error
		if( empty($tmp_result) ) {
			die('MySQL query failed in Iterator::queryFetch() for query:<br /> ' . $query . '<br />MySQL returned:<br />' . mysql_error());
		}

		// Check if it is an 'SELECT' query:
		if ( preg_match('^SELECT', $query) ) {
			// If so, fetch a result array, get number of rows

			// Set number of rows
			$this->last_rows = mysql_num_rows($tmp_result) or die('Could not get number of rows in Iterator::queryFetch()');

			// Set result array
			while( $tmp_row = mysql_fetch_assoc($tmp_result) or die('Error getting result row in Iterator:queryFetch()') ) {
				$this->last_array[] = $tmp_row;
			}
		}

		return $tmp_result;

	}

	function nextResult() {
		// return (bool)

		// If there is no result array to iterate:
		if( empty($this->last_array) ) {
			die('Iterator->last_array is empty. Unable to continue with function Iterator::nextResult()');
		}

		// Add reference and add to counter
		$counter =& $this->counter;
		$counter++;

		// Determine return true or false, for use in while loop (eg: while($row = $Iterator->nextResult) { print_r($row); }
		if( empty($this->last_array[$counter]) ) {
			// If there is no more results left
			return false;
		} else {
			// Else return a row
			return $this->last_array[$counter];
		}

	}

}

?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

In your iterator constructor you have to call the constructor for the parent class explicitely.

Anyway, i'd do it a little different

An Iterator is an interface with methods:
boolean hasNext(); // returns true if the iteration has more elements
Object nextElement(); // returns the next element in the iteration

Now i'd make a MySqlExtended class that is able to return an iterator for the resultset.

function getIterator() {
return new Iterator() {
private $cursor=0;
public hasNext() {
return (mysql_num_rows($result) > $cursor);
}
public nextElement() {
$next = mysql_fetch_assoc($result);
$cursor++;
return $next;
}
}


And then in my code:
$mysql = new MySqlextend(.....);
// perform a query etc...

// iterate through the resultset
$it = $mysql.Iterator();
while ($it.hasNext()) {
$row = $it.nextElement();
// do stuff with $row
}
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

In your iterator constructor you have to call the constructor for the parent class explicitely.
I don't really get that. You mean call $this->MySQL() in my Iterator constructor? Wouldn't that be erasing the point of seperating the MySQL connector and iterator?

Anyhow, the other thing is I don't understand the private and public variables there. Isn't that PHP5? Or in PHP4 - at least for functions? Since I have used static variables.

And also the . in:

Code: Select all

$it = $mysql.Iterator();
while ($it.hasNext()) {
$row = $it.nextElement();
In my previous class, I was - somehow, able to do:

Code: Select all

require('class_mysql.php');

$mysql = new MySQL();
$iterator = new Iterator();

$mysql->selectDatabase('test');

$query = 'SELECT * FROM tests';
$result = $iterator->queryFetch($query);

while ( $rows = $iterator->getRow() ) {
   print_r($rows);
}
I appriciate your opinion on it. So then I'd want to ask a usual question, what's the difference and which would be considered more proper/efficient/secure?

Thanks,

-Nay
Post Reply