help with function for getting arrays of SQL results

[martincrumlish]

Hi,

I have made a function to make it more convenient for me to run SQL queries and produce an associative array from the result. However, I am having some problems with the function.

Here's the function code:

function getarray($q){
	if(!$result = mysql_query($q)){
		echo mysql_error();
	}
	else{
		$item = array();
		$item = mysql_fetch_assoc($result);
		return $item;
	}
}

and I am calling it like this:

$row = getarray("SELECT * FROM events");
for($i = 0;$i<=sizeof($row);$i++){
	$eventtime = date("D, jS M y",$row['eventtime']);
	echo '<tr><td>'.$eventtime.'</td></tr>';
	echo '<tr><td>'.$row['event'].'</td></tr>';
}

The database contains 4 rows but the code above is returning 9 lines in the HTML and the values of each 1 hold the values of the 4th row in the database.

Any ideas?

Thanks.

[paulnaj]

Hi Martin,

You've got to index the $row inside the loop ...

 
$row = getarray("SELECT * FROM events"); 
for($i = 0;$i<=sizeof($row);$i++){ 
    $eventtime = date("D, jS M y",$row[$i]['eventtime']); 
    echo '<tr><td>'.$eventtime.'</td></tr>'; 
    echo '<tr><td>'.$row[$i]['event'].'</td></tr>'; 
}

Just to say though ... there doesn't seem to be much gain from the getarray method ... why not just ...

$res = @mysql_query("SELECT * FROM events") 
   or die('error message');
while($row = mysql_fetch_row($res)){
    $eventtime = date("D, jS M y",$row['eventtime']); 
    echo '<tr><td>'.$eventtime.'</td></tr>'; 
    echo '<tr><td>'.$row['event'].'</td></tr>'; 
}

... if the number of rows is large, this much more efficient.

Paul.

[Shrike]

Your logic is a little incorrect. Rather than counting the number of columns in the for() loop, you should use a while() loop, and use the recordset's internal pointer to loop through the recordset. Below is your code slightly reimplemented (and shoved in a class since I like classes).

<?php 

class SqlIterator
{
	var $result;

function sqlIterator($sql)
{
	if(!$this->result = mysql_query($sql))
	{
		echo mysql_error();
		exit;
	}
}

function getRow()
{
	return mysql_fetch_array($this->result);
}
}

$sqliterator = new SqlIterator("SELECT * FROM events");
while($row = $sqliterator->getRow())
{
	$eventtime = date("D, jS M y",$row['eventtime']); 
	echo '<tr><td>'.$eventtime.'</td></tr>'; 
	echo '<tr><td>'.$row['event'].'</td></tr>'; 
}

?>

[martincrumlish]

Thanks for the help!

I am kind of putting this function together for the reason of learning more about functions as much as anything else.