Handling data returned from fetch_assoc()

Question

I connected to the db, pull the data using fetch_assoc() (fetch_all(NUMB) is not available on the machine we are working with else this would be less of an issue). I get the returned data and load it into an array. However, it ends up in as an numeric array embedded inside another assoc array. What is the best way to have get the returned data in one, single, simple ASSOC array?

/**
* @Param $conn object [required], $tableName string [required]
* @Return $clmNames array
*/
public function getColumnName($conn = NULL, $tableName = NULL )
{
    if ( !is_object( $conn ) || !is_string( $tableName ) )
    {
        //params are not of correct type. This will eventually throw an error instead of printing to screen
        echo("Error: getColumnName returning false");
        return false;
    }

    //create SQL query
    $stmt = "SELECT column_name FROM information_schema.columns WHERE table_name =  '". $tableName ."' ORDER BY ordinal_position";
    //create query
    $query = $conn->query($stmt);

    //execute query
    $tmp = array();
    while ($row = $query->fetch_assoc())
    {
        //load all returned rows into an array
        $tmp[] = $row;
    }

    //clear memory
    $query->free();unset($query);

    //reCreate Array as a single assoc array instead of a double enbedded one.
    $clmNames = array();
    foreach( $tmp as $tmpKey=>$tmpValue )
    {
        foreach( $tmpValue as $tmpValueKey=>$tmpValueValue )
        {
            $clmNames[] = $tmpValueValue;
        }
    }

    return  $clmNames;
}

The above returns the data correctly. However, I would like to be able to remove the entire foreach dealing with $tmp and $clmNames if possible.

Answer 1

First off, there is no need to check if $conn is an object, simply use the proper procedure for checking if the connection was successful.

if( ! $conn ) {//MySQL
if( $conn->connect_error ) {//MySQLi
try {
    $conn = new PDO( $dsn, $user, $pass );
} catch( PDOException $e ) {
    die( $e->getMessage() );
}

And what is $tableName if not a string? The only other value I see is NULL, in which case you can use an is_null() check instead.

Anyways, now that we are done with that, lets look at the actual problem. The reason you are getting a numerical array of associative arrays is because that is the way you have it coded. You are appending a new array element on to the end of the $tmp array foreach $row in the fetch_assoc() table. Because you used an empty assignment [], this element uses the internal iterator to determine its position in the array, which is always the next available numeric index.

If you want to combine all of these arrays together into one associative array, then you will have to merge them somehow. The easiest way would be to use array_merge(), but then you risk overwriting keys from a previous row. This means if you have identical keys in each row array, then only the last row will be available. This, as you can imagine, would be rather pointless. That leaves us with merging it ourselves. Or does it? Since it appears that you only want the insides of the $row array, then you can just do it all at once, for example:

while( $row = $query->fetch_assoc() ) {
    foreach( $row  AS $value ) {
        $clmNames[] = $value;
    }
}

The above should, if I understand your code correctly, result in the same thing. I'm not sure if its the "best" solution, but it removes two loops, an unnecessary array, and makes your code a little more obvious in what it is trying to do.

A couple more things before I go, try only looping over information once. As shown above, if you find yourself looping over information just to create a new array to loop over it again, then chances are you can do whatever you needed to the first time and avoid having that second loop altogether. Finally, lets look at your foreach loops. If you compare yours to mine, you will notice I am not assigning the key pointer in mine. This is because it is unnecessary unless I am planning on using that value. Avoid assigning variables you are not going to use.

Hope this helps!

Answer 2

using

while( $row = $query->fetch_assoc() ) {

 statement relating $row 

}

The loop Will Escape the first OR the Zeroth Index in the Query as While loop loops till the End of the Array is reached the $row is assigned automatically the first value and is per-incremented so you wont help Array(first_query);

if you want it to retrieve values from your db use a array such that

$a2=array();
    $a2[0]=mysqli_fetch_assoc($my_sql_query);   //fetches the first missing value
    while( $row = $query->fetch_assoc() ) {
     statement relating $row 
    $a2[]=$row;
}
echo json_decode($a2);