"Casting" query to array

Question

I have a function that generates a dropdown based on a query or array that is passed to it (it does more than that, but it's immaterial to this question). I currently have both working fine, but there is a lot of redundancy in the function to make both queries and arrays work and I'd like to streamline it.

What I'm wondering if there is a way to "cast" or convert a query result to be treated as an associative array instead of a resource without having to loop over the query results. By the way, we're currently on ms sql with no plans to move to another RDBMS.

EDIT: Here's my code (a bit modified from production, but you'll get the gist).

function phpSelect($varName, &$queryName,$valueField, $displayField, $selectedValue, $returnFullSelect = true, $additionalAttributes = "", $distinctOnly = false){
/**
 * Creates select dropdown from SQL results and selects given value
 */
    $thisSelect = '';
    $resultsArray = array();

    //If they request a full tag, start it here//
    if($returnFullSelect){
        $thisSelect = "<select name=\"$varName\" id=\"$varName\" " .$additionalAttributes .'>
        ';
        $thisSelect .= '    <option value=""> </option>
        ';// clean up - decide if we should put a blank option first or not
    }

    if(is_resource($queryName) ){  // use this for queries
        if( mssql_num_rows($queryName) == 0) return '<!-- Query passed was empty -->';//If the query is empty, just return empty string
        mssql_data_seek($queryName,0);
        //new dBug($queryName);
        $row_thisQuery = mssql_fetch_assoc($queryName);
        $totalRows_thisQuery = mssql_num_rows($queryName);


        do {
            $tempAlreadyExists = array_search(trim(strtoupper($row_thisQuery[$valueField])),$resultsArray);
            if(!$distinctOnly or !is_numeric($tempAlreadyExists) ){ // if Distinct is set to false or this value is not already in the array, add to the array, otherwise, skip it
                $thisOption = '<option value="' .$row_thisQuery[$valueField] .'"';
                if( is_string($selectedValue) ){ //perform case insensitive check
                    if( strtoupper( trim($row_thisQuery[$valueField]) ) == strtoupper( trim($selectedValue) ) ){
                        $thisOption .=" selected";
                    }
                } else {
                    if($row_thisQuery[$valueField] == $selectedValue){
                        $thisOption .=" selected";
                    }
                }
                $thisOption .='>';
                $thisOption .= $row_thisQuery[$displayField];
                $thisOption .= '</option>
                    ';
                $thisSelect .= $thisOption;

                array_push($resultsArray,trim( strtoupper($row_thisQuery[$valueField]) )  );
            } //End checking for distinct values
        } while ($row_thisQuery = mssql_fetch_assoc($queryName)); 

        if($returnFullSelect){
            $thisSelect .= '</select>
            ';
        }
        return $thisSelect;

    } elseif( is_array($queryName) ){  // use this for arrays

        foreach ($queryName as $i => $values) {
            $thisOption = '<option value="' .$values[$valueField] .'"';
            if($values[$valueField] == $selectedValue){
                $thisOption .=" selected";
            }
            $thisOption .='>';
            $thisOption .= $values[$displayField];
            $thisOption .= '</option>
                ';
            $thisSelect .= $thisOption;


        }


        //If they request a full tag, end it here//
        if($returnFullSelect){
            $thisSelect .= '</select>
            ';
        }
        return $thisSelect;
    } else { // didn't pass a query or array, return failure
        return false;   
    }
}

Answer 1

No. What you have to understand is that the result resource doesn't contain all of the result rows. The result resource is essentially an iterator—each time you call mssql_fetch_*() PHP asks the database driver for the next row (I don't know the specifics of the implementation but presumably this is to avoid loading potentially thousands of rows of data into memory all at once and let the database worry about holding on to the results until you're ready to use them). For that reason you can't just "cast" it to an array, since it doesn't actually contain all the data you want in your array.

Even if there was a mssql_fetch_all_rows_as_array() function you would still, behind the scenes, be iterating over the result set, so you shouldn't be afraid of doing that iteration in your PHP code. If this is something you have to do often in your code consider writing a helper function to do it so you don't have to write (and debug) the same code in many places.

Answer 2

there is nothing wrong in looping over the query results. to get data into array, you HAVE to loop over it. even if you don't see the loop itself.

So, even if you get some "magic" method that seemingly "cast" your resource to array, there WILL be a loop inside.

I see no reason though to these 2 functions have too much redundant code. May be you have your mistake somewhere else.

Answer 3

You might consider something like this (this is a rough and purely conceptual definition):

interface DropdownDataProvider
{
    public hasNext();
    public getNext();
}

class DBDataProvider implements DropdownDataProvider
{
    // ...
}


class ArrayDataProvider implements DropdownDataProvider
{
    // ...
}

and finally your dropdown could be modelled as such:

class Dropdown
{
    public function __construct(DropdownDataProvider $provider)
    {
        // ...
    }

    // ...
}

Such an approach would allow you to access the query result 'cursor style' as intended, without any messing around packing the thing into appropriate arrays. It'll also allow you swap in new drop-down data sources without too much faffing around.

Hope this helps.

Answer 4

Without lopping through here is no way to cast it all, however this is how you would do it looping, which is generally how everyone does it.

$arr=array();
while($row = mssql_fetch_assoc($result))
    $arr[]=$row;