NYCPHP Meetup

[Michael Myers]

As a newbie, I find it a little surprising that the mysql_fetch_() 
functions don't have a variant where any row of a result set is 
directly accessible via an associative array.

I am sure many users encounter query results where some column is 
guaranteed to have a unique value (eg. primary key), and could be used 
as a key in a PHP array of arrays. Instead, one must always proceed 
through the query result row-by-row.

There are so many different array capabilities in PHP, I wonder if I'm 
taking a reasonable course or if I haven't overlooked something. I have 
two MySQL result sets to handle when rendering a web page. One contains 
a set of Topics, each with one or more identifiers that point to 
relevant information. The other result set contains the information for 
each identifier.

I render the page according the alphabetical order of the topics. I 
dealt that within the SQL query. In order to directly access the data 
for each identifier and speed things up, I built an index where the key 
is the identifier and the value is the row number in the result object. 
It seemed less memory intensive to create a simple index rather than 
create a "keyed" version of the result object. (It also wasn't clear if 
I could directly modify the result object while looping through it).

Here's a snippet of my approach:

	// get the record of information for each identifier (pmid).
	$sql = 'SELECT pmid, doi, pii, pst, volume, issue, page, source, 
title, authors, affiliation
	        FROM articles
	        WHERE pmid IN ' . $pmid_set . 'ORDER BY pmid DESC';
	
	$refs = mysql_db_query("autolit", $sql);

	// pmid is a unique identifier to a record of information
	$pmid_index = array();
	$cnt = mysql_numrows($refs);
	for ($i = 0; $i < $cnt; $i++) {
		mysql_data_seek($refs, $i);
		$row = mysql_fetch_row($refs);
		$curr_pmid = $row['pmid'];
		$pmid_index[$curr_pmid] = $i;
	}
	reset($refs);

	// now, while looping through the rows of the Topics result set...

	$topic_name = $row['name'];
	$pmid = $row['pmid'];
	
	// use the index to find details for current pmid
	mysql_data_seek($refs, $pmid_index[$pmid]);
	$details = mysql_fetch_row($refs);
	
	$source = $details['source'];
	$title = $details['title'];
	$authors = $details['authors'];
	etc...