mysqli fetch() passes by reference?

unsavory

I am using the following code to return a resultset from a prepared statement in mysqli.

$sql = 'SELECT L.LABEL_ID, L.LABEL_NAME, L.PARENT_ID, L.BREADCRUMB
				FROM LG_RP_LABELS L
				JOIN LG_RP_ARTICLE_LABELS A
				ON L.LABEL_ID = A.LABEL_ID
				WHERE A.ARTICLE_ID = ?';

	try {
		$dbh = new DbMySql();   
		$stmt = $dbh->prepare($sql);
		$stmt->bind_param('i', $this->articleId);		
		$stmt->bind_result($row['LABEL_ID'], 
							$row['LABEL_NAME'], 
							$row['PARENT_ID'], 
							$row['BREADCRUMB']);	
		$stmt->execute();

		$count = 0;			
		while ($stmt->fetch()) {
			$this->articleLabels[$count] = $row;
			$count++;
		}
		$stmt->free_result();			
		$dbh->close();

	} catch (DbConnectException $e) {		
	die ($e->getFormattedException());
	} catch (SqlException $e) {
	die ($e->getFormattedException());
	}

The problem is that if I loop over $this->articleLabels, every entry in the array is the same as the last fetch(). Which tells me it is not passing by value, but rather by reference and the pointer is just being moved with each fetch() call.

How do I get the actual values out of the fetch() call and not the reference?

paulnaj

Hi,

This is PHP5, I assume. If it is, then I'm guessing but surely ...

while ($stmt->fetch()) {
$this->articleLabels[$count] = $row;
$count++;
}

... should be ...

while ($row = $stmt->fetch()) {
$this->articleLabels[$count] = $row;
$count++;
}

... no?

Paul 🙂

unsavory

Well no. I am using the OOP interface to mysqli. The result is bound to the PHP variables here:

$stmt->bind_result($row['LABEL_ID'],  

                                $row['LABEL_NAME'],  

                                $row['PARENT_ID'],  

                                $row['BREADCRUMB']);

fetch() does not return a row of data. It returns either true, false or null.

The problem is that the bind seems to be by reference rather than by value. So how do I go about holding onto those values as the reference is updated in the loop?

paulnaj

I did say I was guessing :queasy: ... but I get your problem now!

One solution might be to extract the values from $row on each iteration, rather than getting the 'whole' $row in one go ...

<?php
// ...
	while ($stmt->fetch()) { 
		$this->articleLabels[$count] = array(
			'LABEL_ID' => $row['LABEL_ID'],   

			'LABEL_NAME' => $row['LABEL_NAME'],   

			'PARENT_ID' => $row['PARENT_ID'],   

			'BREADCRUMB' => $row['BREADCRUMB']);
		);
		$count++; 
	} 
// ...?>

It ain't 'purdy', but without knowing anymore about this setup, that's probably my lot.

Paul.

unsavory

Eeeek. Yes, that is actually what I resorted to having to do. And it works, but is very ugly and a pain in the royal rear to code for every select statement.

The funny thing is that if I use a mysqli query to do the same thing above (without the prepared statement), it works just fine. It's only when fetching from a result that it passes by reference.

I was just hoping someone else had experience with fetching an entire resultset from a prepared statement.

I still can't believe there is no fetch_all() for a mysqli result object.

paulnaj

Well, you could automate that ...

 <?php 
// ... 
    while ($stmt->fetch()) {  

		$tmp = array();
		foreach($row as $key => $value){
			$tmp[$key] = $value;
		}
        $this->articleLabels[$count++] = $tmp
    }  

// ...?>

unsavory

Ah... kudos.

That is much better. Thanks so much.