Jump to content


Photo

get a mysql feild type


  • Please log in to reply
16 replies to this topic

#1 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 28 August 2006 - 01:21 AM

hi again

I just need to know how to get a mysql feild type eg varchar, image, date, text etc. how to pull it out of a feild using SQL or some way in PHP.

thanks again


DONT LAY AN EGG OVER IT

#2 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 28 August 2006 - 01:29 AM

Use the "SHOW COLUMNS FROM tablename" MySQL query, then loop through the result and look at the Type column.

http://dev.mysql.com...ow-columns.html

#3 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 28 August 2006 - 01:32 AM

install phpmyadmin ok
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#4 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 29 August 2006 - 12:55 AM

here is the show colums syntax

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

ok i can pull out the colums names using this by taking the info out of the result in to an array

i dont understand how i can pull the type out is the result multi dimensional if so please can somone give me an example

i just need the type of the column so my software can check if its an image or audio feild ect and compensate.
DONT LAY AN EGG OVER IT

#5 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 29 August 2006 - 07:05 AM

<?php
   $sql = "show columns from test";
   $result = mysql_query($sql);
   // formatted for your pleasure
   echo "<table>";
   while ($list = mysql_fetch_row($result)) {
      echo "<tr><td align = 'right'><b>name:</b></td>";
      echo "<td align = 'left'>$list[0]</td>"; // the 0 position in the array is the name
      echo "<td align = 'right'><b>type:</b>";
      echo "<td align = 'left'>$list[1]</td></tr>"; // the 1 position in the array is the type
   }
   echo "</table>";
?>

... though, if you are basing a filetype on the column's type.. that doesn't make a whole lot of sense.  that opens up lots of potential bugs.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#6 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 29 August 2006 - 11:59 AM

http://us2.php.net/m...-field-type.php

#7 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 29 August 2006 - 03:22 PM

the reason i needed the type was to see if the feild is blob etc so i can compensate on the input form that is generated by my software.

DONT LAY AN EGG OVER IT

#8 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 29 August 2006 - 03:22 PM

Thanx alot RESOLVED
DONT LAY AN EGG OVER IT

#9 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 29 August 2006 - 05:08 PM

jenk- just so you know, mysql_field_type() returns what php thinks the column type is, based on the data retrieved from the column. This does not necessarily make it the same data type as what you actually have in your database.  Your column type could be something else and php could get it wrong.  therefore you should do it my way, as you are getting the datatype directly from sql, not php.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#10 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 31 August 2006 - 05:50 AM

yeh because im trying to make a peice of software that makes the website so i dont wana get it wrong because the database is what the software bases the generated site on
DONT LAY AN EGG OVER IT

#11 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 31 August 2006 - 05:51 AM

thnx has gone a long way since last week i got it in a object know and am building it
DONT LAY AN EGG OVER IT

#12 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 31 August 2006 - 05:51 AM

a class that is

DONT LAY AN EGG OVER IT

#13 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 31 August 2006 - 08:02 AM

jenk- just so you know, mysql_field_type() returns what php thinks the column type is, based on the data retrieved from the column. This does not necessarily make it the same data type as what you actually have in your database.  Your column type could be something else and php could get it wrong.  therefore you should do it my way, as you are getting the datatype directly from sql, not php.

Actually, no it doesn't. It is a direct shortcut to the C function which is used within MySQL. It returns whatever value is in the TYPE column when you run a DESC `table` statement.

#14 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 31 August 2006 - 08:07 AM

really? well then maybe you should add a note to the manual then, correcting the other notes.

http://us3.php.net/mysql_field_type
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#15 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 31 August 2006 - 08:10 AM

I've no need to. The information is readily available because others have posted it ;)

#16 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 31 August 2006 - 08:11 AM

where? i'm not being snide. i just don't see it..
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#17 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 31 August 2006 - 10:54 PM

It is, without a doubt, at best - a guess by php using the mysql_field_type function.  MySQL has several data types...like int, tinyint, bigint, and so forth...that mysql_field_type will generalize into one...in the listed examples, they area all listed as "int".

Here is the exact C code from the php source code:

switch(field_type) {
		case FIELD_TYPE_STRING:
		case FIELD_TYPE_VAR_STRING:
			return "string";
			break;
#ifdef MYSQL_HAS_TINY
		case FIELD_TYPE_TINY:
#endif
		case FIELD_TYPE_SHORT:
		case FIELD_TYPE_LONG:
		case FIELD_TYPE_LONGLONG:
		case FIELD_TYPE_INT24:
			return "int";
			break;
		case FIELD_TYPE_FLOAT:
		case FIELD_TYPE_DOUBLE:
		case FIELD_TYPE_DECIMAL:
#ifdef FIELD_TYPE_NEWDECIMAL
		case FIELD_TYPE_NEWDECIMAL:
#endif
			return "real";
			break;
		case FIELD_TYPE_TIMESTAMP:
			return "timestamp";
			break;
#ifdef MYSQL_HAS_YEAR
		case FIELD_TYPE_YEAR:
			return "year";
			break;
#endif
		case FIELD_TYPE_DATE:
#ifdef FIELD_TYPE_NEWDATE
		case FIELD_TYPE_NEWDATE:
#endif
			return "date";
			break;
		case FIELD_TYPE_TIME:
			return "time";
			break;
		case FIELD_TYPE_SET:
			return "set";
			break;
		case FIELD_TYPE_ENUM:
			return "enum";
			break;
#ifdef FIELD_TYPE_GEOMETRY
		case FIELD_TYPE_GEOMETRY:
			return "geometry";
			break;
#endif
		case FIELD_TYPE_DATETIME:
			return "datetime";
			break;
		case FIELD_TYPE_TINY_BLOB:
		case FIELD_TYPE_MEDIUM_BLOB:
		case FIELD_TYPE_LONG_BLOB:
		case FIELD_TYPE_BLOB:
			return "blob";
			break;
		case FIELD_TYPE_NULL:
			return "null";
			break;
		default:
			return "unknown";
			break;

As you can see, it is a guess.  The only way to get a 100% accurate answer as to what type of field it is is to use "SHOW COLUMNS FROM tablename" and see what the "type" column in the result contains.

If you still don't beleive me, use this SQL:

CREATE TABLE `proof` (
  `a` varchar(255) default NULL,
  `b` tinyint(4) default '0',
  `c` text,
  `d` date default NULL,
  `e` smallint(6) default '0',
  `f` mediumint(9) default '0',
  `g` int(11) default '0',
  `h` bigint(20) default '0',
  `i` float default '0',
  `j` double default '0',
  `k` decimal(10,0) default '0',
  `l` datetime default NULL,
  `m` timestamp NULL default NULL,
  `n` time default NULL,
  `o` year(4) default NULL,
  `p` char(255) default NULL,
  `q` tinyblob,
  `r` tinytext,
  `s` blob,
  `t` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Once you have that table in your db, execute this php code:

$conn = mysql_connect("hostname", "username", "password");
mysql_select_db("dbname");

$query = "SELECT * FROM proof";

$result = mysql_query($query);

$fields = mysql_num_fields($result);
$rows  = mysql_num_rows($result);
$table  = mysql_field_table($result, 0);
echo "<pre>Your '" . $table . "' table has " . $fields . " fields and " . $rows . " record(s)\n";
echo "The table has the following fields:\n";
for ($i=0; $i < $fields; $i++) {
   $type  = mysql_field_type($result, $i);
   $name  = mysql_field_name($result, $i);
   $len  = mysql_field_len($result, $i);
   $flags = mysql_field_flags($result, $i);
   echo $type . " " . $name . " " . $len . " " . $flags . "\n";
}

$query = "SHOW COLUMNS FROM proof";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	print_r($row);
}

Which will return:

Your 'proof' table has 20 fields and 0 record(s)
The table has the following fields:
string a 255 
int b 4 
blob c 65535 blob
date d 10 binary
int e 6 
int f 9 
int g 11 
int h 20 
real i 12 
real j 22 
unknown k 11 
datetime l 19 binary
timestamp m 19 unsigned zerofill binary
time n 8 binary
year o 4 unsigned zerofill
string p 255 
blob q 255 blob binary
blob r 255 blob
blob s 65535 blob binary
blob t 16777215 blob binary
Array
(
    [Field] => a
    [Type] => varchar(255)
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => b
    [Type] => tinyint(4)
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => c
    [Type] => text
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => d
    [Type] => date
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => e
    [Type] => smallint(6)
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => f
    [Type] => mediumint(9)
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => g
    [Type] => int(11)
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => h
    [Type] => bigint(20)
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => i
    [Type] => float
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => j
    [Type] => double
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => k
    [Type] => decimal(10,0)
    [Null] => YES
    [Key] => 
    [Default] => 0
    [Extra] => 
)
Array
(
    [Field] => l
    [Type] => datetime
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => m
    [Type] => timestamp
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => n
    [Type] => time
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => o
    [Type] => year(4)
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => p
    [Type] => char(255)
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => q
    [Type] => tinyblob
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => r
    [Type] => tinytext
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => s
    [Type] => blob
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => t
    [Type] => mediumblob
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users