Does anybody know a fast (performance wise) way to do a large search and replace over a set of array keys. Here's my problem.

I am playing around with a class to speed up some db development work that I do. (This is really about arrays...I promise) I have a function that pulls out a result set and puts them into an array. I would like that array to have 'table.field' style keys. I have the code that does this but I am reunning a loop inside of a loop which on a large result set is going to take a little time. Any ideas on how to accomplish this faster?

//Sets the result variable
function setResult($result)
{
    if (is_resource($result)) {

    //Store the result and create the result table
    $this->_result = $result;
    $this->_resultTable = array();

    //Make sure it is a valid result
    if (mysql_num_rows($result) > 0) {
        $this->_fieldNameRevMap = array();

        for ($i = 0; $i < mysql_num_fields($result); $i++) {
            //Create the reverse index (n to table.field)
            $this->_fieldNameRevMap[$i] = mysql_field_table($result, $i) . '.' . mysql_field_name($result, $i);
        }

        //make sure the result pointer is at the begining
        //Then we can begin building our array
        mysql_data_seek($result, 0);
        while ($temp_arr = mysql_fetch_array($result, MYSQL_NUM)) {


//-------This is the code in question
                $temp_arr2 = array();
                foreach ($temp_arr as $index => $value) {
                    $temp_arr2[$this->_fieldNameRevMap[$index]] = $value;
                }
                $this->_resultTable[] = $temp_arr2;
//-------

        }
    }
}
}

thanks

    This is entirely off the top of my head (but then, a lot of things are off the top of my head....), and may not be at all suitable for your purposes - but it does retain the "table/field" information in the records.

    It's to add another dimension to the result arrays, indexed by table.

    while($row=mysql_fetch_assoc($result))
    {$resultTable['MyTable']=$row;}

    It means instead of referring to $resultTable['MyTable.myfield'] you'd be referring to $resultTable['MyTable']['myfield'] instead, but it does mean you wouldn't have to do those extra loops.

    I did have another idea wherein re-keying is deferred until the field is actually requested (when $record['table.field'] is wanted, look to see if it is set, if not, create it from $record['field']), but that would be a sight more complicated (probably want to turn record arrays into record objects with suitable methods to handle all the deferral stuff transparently.) Like I say, probably more complicated.

    Gotta go, batteries running flat.

      I don't think that'll work. Occasionally I qill be running queries where more then one table will be in the result set...so the fields in the row could be

      table1.id
      table1.name
      table2.name
      table2.type

      So I would never know for sure what to use for the table name on each record since it changes. I would still have to look at each field individually to find the table name.

      What I have been looking into is first determining the name of each field, then extracting all the results, and then hope that there was some function that would have PHP internally rename the keys instead of having to go through my loops.

      I previously was using your second idea, but sometimes I want to grab the entire result table at once, in which case I could still defer renaming the fields, (That was the original idea behind $this->_fieldNameRevMap.) but by deferring I would have just postponed the inevitable and everything would still be taking as long. Although I might feel better about since it would be spread out over several functions 🙂.

      Isn't optimizing code great?

      Thanks for giving it a once over. If you have any other thoughts let me know.

        Well, apart from rewrting yr queries to be of the form

        select field as "mytable.field" from mytable ...

        Shsh, this phone's tiny...

          That would be cumbersome. Slow code is not all that bad 🙂. Thanks for the ideas

            Write a Reply...