NYCPHP Meetup

[Chris Snyder]

It's easier than you think-- here's how I'd do it:

$query = "SELECT * FROM articles WHERE  blah blah blah ORDER BY pmid DESC';
$result = mysql_query($query);
if ( !$result ) {
    exit( "Database error on query: $query -- ".mysql_error() );
}
$records = array();
while ( $array = mysql_fetch_assoc($result) ){
    $records[$array['pmid']] = $array;
}

And that's it -- that will give you an array $records, which is an array 
of associative arrays matching the records in the result set, keyed on pmid.

Arguably, it's still not as simple as $array = 
db_fetch_result_set($query); -- but then, there's nothing stopping you 
from writing that function and using it throughout your code.

    csnyder



Michael Myers wrote:

>
> 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...
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk