Query Function Not working Right

[mikesta707]

Ok, so here is the deal. I am currently building a class that will do queries for me. So far, it is going ok, But i ran into a problem. I built a function to do simple SELECT queries, that dont have any conditions, IE

$query = "SELECT * FROM tablename ORDER BY columnname DESC";

 

The function builds the query perfectly, and then stores the result in a class variable. The class is pretty long, but here are the relevant parts of the class

 

the variables of the class

class superQuery{
//define the variables in class
var $debugmode;//sets whether or not to output error messages. Defaults to false
var $resource;//the mysql resource of the class
var $num_rows;//Number of rows returned from a query. 
var $info;//array with info from query

//error messages
var $errors = array(
'required_unset' => 'ERROR: Attempt to access function without passing required parameters', 
'sql' => 'ERROR: Invalid MYSQL query. Error Message Below', 
'array_verify' => 'ERROR: Attempt to pass arrays of unequal or invalid length to query. Script Terminated', 
'sqland' => 'ERROR: Attempt to pass non And Or value into And Or parameter. Script Terminated', 
'non_array' => 'ERROR: Attempt to pass non-array value into array parameter. Script Terminated'
);

 

the function itself

function query_whereAll($table, $orderBy=null, $select = "*"){//Selects everything from a table.
	$array = array($table);
	$this->isempty($array);//just makes sure that the table var is not empty. dont ask why i put it into an array and passed it

	//set the beginning of the sql
	$sql = "SELECT ".$select." FROM ".$table." ";

	if ($orderBy != null){
		if (is_array($orderBy)){
			$keyArray = array_keys($orderBy);
			$order = $keyArray[0];
			$by = $orderBy[$order];
			$sql .= " ORDER BY ".$order." ".$by;
		}
		else {
			$this->error($this->errors['non_array']);
			}
	}
	$this->resource = mysql_query($sql);
	if (!$this->resource){
		$this->error($this->errors['sql'].' '.mysql_error());
	}
	$this->num_rows = mysql_num_rows($this->resource);
	$this->info = mysql_fetch_assoc($this->resource);
}

 

Now this function builds the query correctly, and my server doesn't return any mysql error. However, the problem is when I use the class, IE in the following

$topic = new superQuery(true);
$topic->query_whereAll('topics', array('lastreply' => 'DESC'));
$i = 0;

while ($i < $topic->num_rows) {
$id = mysql_result($topic->resource, $i, 'id');
$topic = mysql_result($topic->resource, $i, 'topic');
$date = mysql_result($topic->resource, $i, 'date');
$user = mysql_result($topic->resource, $i, 'user');
$text = mysql_result($topic->resource, $i, 'text');
//other stuff
}

 

What happens is that the variables id and topic get the right data, but I get the error "Invalid Mysql resource" for the date variable, user variable and text variable. What gets me is that it actually works perfectly fine for the first two variables, but doesn't work any more for the last three. The column names are all perfectly fine, and the query that happens goes without error, so I am at a loss at this point to what could be going on.

 

my server php version is 5.2.8

my mysql version is 5.0.67

 

And yes, I understand that I can just do the query and it would work fine (actually this is what i did before) but i am trying to update my site using a lot more objects and classes.

 

Can anyone see what is going wrong?

[Ken2k7]

Well yeah, you're overwriting the variable $topic in the loop so it no longer is a superQuery. =)

[JonnoTheDev]

This is an awful implementation for an object. It has no relation to any other system entity and would have been more efficient as a general function.

 

i.e. you are setting a database resource inside the object and then accessing in the global scope with:

// encapsulated in class
function query_whereAll() {
   // ......
   $this->resource = mysql_query($sql);
}

// global scope
$date = mysql_result($topic->resource, $i, 'date');

This needs to be thought out a bit more

[Ken2k7]

neil.johnson, if this is PHP 4, then it's the only way because all class fields are public. If PHP 5, I agree.

[JonnoTheDev]

neil.johnson, if this is PHP 4, then it's the only way because all class fields are public. If PHP 5, I agree.

 

I think the post states php 5 but this isn't the point.

 

The object should be responsible for returning the result set or a sub class method with access to the mysql resource. If you create a mysql resource within the object and then use mysql functions outside of the object scope using a class variable is just backwards. Like spaghetti code.

 

i.e.

 

<?php

public function query($sql) {
	if($this->resource = mysql_query($sql, $this->connection)) {
		$this->numrows = mysql_num_rows($this->resource);
	}
	else {
		// throw query error
	}
}


public function resultSet() {
	$this->value = mysql_fetch_array($this->resource);
	return $this->value;	
}
?>

[Ken2k7]

I don't think resultSet() is a good name for that function because you'll have to keep calling it. The class should have a function to trigger some sort of loop.

 

Example:

// inside the class

// @return bool
function hasNext () {
     // some code
}

// @return array
function getRecord () {
     // gets the current set of results
}

// outside the class
while ($class->hasNext()) {
     $result = $class->getRecord();
     // some more code
}

 

I left out a lot, but that's just to trigger some thinking on your part.

[mikesta707]

Yeah i realized what I was doing was pretty stupid haha. Instead I just had my object return the mysql resource instead of what I had before, kinda like what neil.johnson wrote.

 

thanks alot of your input!