getting the "real" mysql_field_type

[michaellunsford]

Okay, using mysql_field_type, "TEXT" comes across as "BLOB" -- soooo, how do I tell TEXT and a real BLOB apart?

puzzling

[craygo]

You could ask it :)

This will loop thru the fields and give info for each.

[code]$i=0;
$res = mysql_query('select * from testing') or die (mysql_error());;
while ($i < mysql_num_fields($res)) {
   echo "Information for column $i:<br />\n";
   $meta = mysql_fetch_field($res, $i);
      echo "<pre>
blob:        $meta->blob
max_length:  $meta->max_length
multiple_key: $meta->multiple_key
name:        $meta->name
not_null:    $meta->not_null
numeric:      $meta->numeric
primary_key:  $meta->primary_key
table:        $meta->table
type:        $meta->type
default:      $meta->def
unique_key:  $meta->unique_key
unsigned:    $meta->unsigned
zerofill:    $meta->zerofill
</pre>";
$i++;
}[/code]

Obviously you could just shorten the code for one field but the $meta->blob will return a 0 for no blob and 1 for a blob field.

Also $meta->type will return actual field type blob, string, int ect..

Ray

[michaellunsford]

ahh... now who would have guessed that mysql_fetch_field had anything to do with field information. I thought it fell in there somewhere between mysql_fetch_array and mysql_fetch_row. Ha!

thanks for the help!

[michaellunsford]

NOPE, didn't work. It's still returning "blob" for type "text"

ideas?

[michaellunsford]

found another way around. Flags will show if the field is binary -- which is sufficient for my purposes.

[code]if(strpos(mysql_field_flags($result, key($myrow)),"binary"))[/code]

[michaellunsford]

Ughh... stuck again!

date and time fields area also flagged as binary.

Anyone have an idea? I'm really stuck here. I need to separate the true "BLOB" fields from a "TEXT" field (both of which mysql_field_type returns "blob" for)

phpmyadmin can tell the difference -- so there must be a way.

[litebearer]

Try this (use those portions that apply to your specific needs). I use it to test data content as well as duplicating/backing up table. And even to export the data to an excel file.


(demo link is near bottom of this reply)


[code]<?php
#################################################################
#     this small script can be used to
#
#        get and display a database table's structure (field names and field type)
#        get and display the field contents in an html table
#        get and store the field names and contents to an excel file
#
#

##################################
#    set the database variables

$database="YOURDATABASENAME";
$location = "localhost";  
$username = "YOURUSERNAME";  
$password = "YOURPASSWORD";  
$db_table = "edselford_1963";

######################################
#    set the function switches
#    0 means use
#    1 means do NOT use

$display_table_structure = 0;
$display_table_contents = 0;
$copy_table_data = 0;
$csv_name = $db_table . "_" . time();
$how_many = 10; // use this value to limit the number of records returned in the data query (0 means no limit)

######################################
#    define the function(s)

// This function gets the field names
function my_db_get_table_field_names($table) {
    $sql = "SHOW COLUMNS FROM `$table`";
    $field_names = array();
    $result2 = mysql_query($sql);
    for($i=0;$i<mysql_num_rows($result2);$i++){
        $row = mysql_fetch_array($result2);
        $name = $row['Field'];
        array_push($field_names, $name);
    }
    return $field_names;
}



############################################
# connect to the database

mysql_connect ($location, $username, $password);
@mysql_select_db($database) or die( "Unable to select database");
$result0 = mysql_query("SHOW COLUMNS FROM $db_table");

if (!$result0) {
  echo 'Could not run query: ' . mysql_error();
  exit;
}


####################################################
#    get the field names into an array and count them

$new_array = my_db_get_table_field_names($db_table);
$fields = count($new_array);

###################################################
#    get all the data and count the records

if ($how_many == 0) {
    $result = mysql_query( "SELECT * FROM $db_table" )
    or die("SELECT Error: ".mysql_error());
    $num_rows = mysql_num_rows($result);
}else{
    $result = mysql_query( "SELECT * FROM $db_table LIMIT $how_many" )
    or die("SELECT Error: ".mysql_error());
    $num_rows = mysql_num_rows($result);
}

##############################################################
#    display the general information

echo "This information is for the " . $database . " database. Table " . $db_table . "<br><br>";
echo "There are " . $fields . " columns/fields  and " . $num_rows . " records.<br><br>";

###########################################################
#    display the table structure

if ($display_table_structure == 0) {
        echo "<hr>Table structure for " . $db_table . ": <br><br>";
        echo "<table border=1> <tr><td>Field #</td><td>Field Name</td><td>Field Type</td></tr>";
    $result9 = mysql_query("SHOW FIELDS FROM $database.$db_table");
    $i = 0;
    while ($row = mysql_fetch_array($result9)){
        echo "<tr><td>" . $i . "</td><td>" . $row['Field'] . "</td><td>" . $row['Type'] . "</td></tr>";
    $i = $i +1;
    }
        echo "</table>";
}

##############################################################
#    display the table contents

if ($display_table_contents == 0) {
    echo "<hr>Table data for " . $db_table . ": <br><br>";
    print "<table width='100%' border=1>\n";
    $i = 0;
    print "<tr>\n";
    for ($i=0;$i<$fields;$i++) {
      print "\t<td><font face=arial size=1/>$new_array[$i]</font></td>\n";
    }
    while ($get_info = mysql_fetch_row($result)){
        print "<tr>\n";
        foreach ($get_info as $field)
            print "\t<td><font face=arial size=1/>$field</font></td>\n";
            print "</tr>\n";
    }
    print "</table>\n";
}
##################################################
# remove the comment tags to enable the excel file creation
/*

if ($copy_table_data ==0) {
    ?>
    <meta http-equiv="refresh" content="2;url=http://nstoia.com/mysqlexcel.php">
    <?PHP
}
*/

?>[/code]


demo is here [a href=\"http://nstoia.com/display000.php\" target=\"_blank\"]http://nstoia.com/display000.php[/a]


Hope it helps.

Lite...

[michaellunsford]

Ahhhhhhhhh.... the refman speaks...

$whattype=mysql_fetch_assoc(mysql_query("DESCRIBE table_name field_name"));
echo $whattype['Type'];