Getting structure of resultset in memory

Dave · 05-19-2008, 11:45 PM

After a SELECT query is executed, there is a resultset located in memory.

Without using 3rd-party software, is there a way to "capture" the "table structure" of the resultset into a text file for later reference?

Thanks for any help...

buggabill · 05-23-2008, 01:52 PM

I do not really know of a way to get the structure of a recordset, but you can get the structure of the tables involved by using the SHOW COLUMNS syntax from MySQL:

PHP Code:


			
<?php

    $user = "user";

    $pass = "pass";

    

    $link = mysql_connect("localhost", $user, $pass);

    mysql_select_db("test");

    

    $sql = 'SHOW COLUMNS FROM tester like "%"';

    

    $result = mysql_query($sql, $link);

    $fldarr = array();

    

    echo "<pre>";

    while($row = mysql_fetch_assoc($result))

    {

        print_r ($row)."\n";

        $fldarr[] = $row['Field'];

    }

    echo "</pre>";

    

    $first = true;

    $recrow = '';

    

    $fp = fopen("recfile.txt", "w+")    // Set the file up for writing here.

    foreach ($fldarr as $fld)

    {

        if (!$first)

        {

            $recrow .= ', '.$fld;

        }

        else

        {

            $recrow .= $fld;

            $first = false;

        }

    }

    

    fwrite($fp, $recrow);             // write your line

    fclose($fp);

    

    echo $recrow;

    

    mysql_free_result($result);

    mysql_close($link);

?>

If you notice, this syntax supports a LIKE phrase so that you can limit the number of columns retrieved.

This script will output something like this:

Code:

Array
(
    [Field] => id
    [Type] => int(10) unsigned
    [Null] => NO
    [Key] => PRI
    [Default] => 
    [Extra] => auto_increment
)
Array
(
    [Field] => testfield1
    [Type] => varchar(45)
    [Null] => NO
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => testfield2
    [Type] => varchar(45)
    [Null] => NO
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => testfield3
    [Type] => varchar(45)
    [Null] => NO
    [Key] => 
    [Default] => 
    [Extra] => 
)

id, testfield1, testfield2, testfield3

Hope this helps!

buggabill · 05-23-2008, 02:23 PM

I just thought of another way, but it would require that a result came back from your query. You could dump the first row of the result into an array using mysql_fetch_assoc and get the keys from the array using array_keys like this:

php Code:

.
.
.
    $sql = 'some query';

    $result = mysql_query($sql, $link);

    $row = mysql_fetch_assoc($result);

    $recset_arr = array_keys($row);
.
.
.

This would dump the key names of $row into an array that you could parse.

Dave · 05-24-2008, 12:53 PM

Buggabill --

Thanks so much for your replies. They are most helpful for me (a learner) on a number of levels...First, there are several new bits of code that I have not encountered previously (e.g., ".=" -- not easy for me to grasp, but I think I've gotten to "second grade" on it now). Secondly, the overall concept, though simple to you, is new to me and I've gone through everything step-by-step.

So I'm still at work on this -- just wanted to say thanks for now.

Dave