get column type from query result

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

get column type from query result

Post by mcccy005 »

I want to get the data type from each mysql column in a result from an sql query in PHP.

Code: Select all

$result=$db_connection->query($sql_query);
$temp_column;
$temp_result_field=array( );
$temp_result_set=array( );

for($i=0; $i<$result->num_rows; $i++)
{
     $row=$result->fetch_row( );
     for($j=0; $j<sizeof($row); $j++)
     {
          $temp_result_field[$j]=new result_cell(stripslashes($row[$j]), <<GET DATA TYPE FROM $row[$j] HERE>>);
     }
     $result_set[$i] = $temp_result_field;
}
I've included the entire piece of code so you understand exactly what I'm talking about. What I'm trying to avoid is double-querying the database; once with the query; and then again using the DESCRIBE function.

Would the PHP function gettype( ) by any chance work with the data I get from the database???

Thanks
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

gettype() wouldn't work, because all values returned will be of string type.

Have a look at mysql_field_type()
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Jenk wrote:gettype() wouldn't work, because all values returned will be of string type.

Have a look at mysql_field_type()
Hmm. While I agree that mysql_field_type() will return the type of MySQL defined field, it will not necessarily return the PHP data type, nor will it return the actual column makeup (VARCHAR, etc). If you want to go the route of returning either, you may want to create a wrapper function or class that maps each type.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

It's all good - mysql_field_type( ) ought to do the trick.
I just need to know if data types are 'SMALLINT', 'INT', LARGEINT etc etc etc so I can classify them in PHP as 'numerical'; and the same again re: text; date/times and 'bool'.

Thanks
Post Reply