[SOLVED] How to get MySQL table field attributes

[Chappers]

Hi,

 

I'm using:

<?php
$result = mysql_query("SHOW FIELDS FROM $tablename");

to get the field names from a MySQL table, which is working fine. What I would like to do now is to also get the attributes of each field, so for the field ID, it would return "int(10) NOT NULL auto_increment".

 

The reason for this is that I'm creating an admin page that allows me to create and delete tables from my MySQL database without having to log on to my CPanel, then the database, then Admin, then do it all from there. I can do it more quickly and simply from my website like this. At present I have written code to give me full control over the above and it works, but I'd like to be able to not only list each table and its fields, but also the field attributes so I can keep track of it all and see what might need modifying, etc. Which also makes me think: I can create tables with any fields and their respective attributes using my admin page, but is it also possible to modify the field attributes of any given table, and edit the fields themselves (or delete, etc.)?

 

Thanks in advance.

[revraz]

Describe?

 

http://dev.mysql.com/doc/refman/5.0/en/describe.html

[Chappers]

Thanks for that. I read the entry in the manual from the link you gave, and it's supposed to do exactly what I want. In practice though, it's just printing the field names over and over again for every entry in that table. For example, say three users have signed up and the table contains their details in 4 fields (id, username, password, date), then describe outputs "id username password date" three times - once for each entry. With no attributes of each field. Any idea why?

[Mchl]

COuld you show us your code?

[Chappers]

Of course, should have shown code. It's probably a mess to those who are much better at this than me, but it's the best I could do and it's for test purposes at the moment. I've used three different methods I've found for accessing field info, just to compare, and found they all output the same thing. Also, when I said before that it's printing the fields for each entry, I made a mistake in my while() and that was what caused that. Anyway, here's the whole lot:

 

<table align='center' width='100%' cellpadding='2' cellspacing='0' border='1'>
<?php
$result = mysql_query("SHOW TABLES FROM $db_name");
while ($row = mysql_fetch_row($result)) {
echo <<<END
<tr><td><b>Table:</b> $row[0]<br></td></tr>
END;

$result2 = mysql_query("SHOW FIELDS FROM $row[0]");
while ($row2 = mysql_fetch_row($result2)) {
echo <<<END
<tr><td>Field: $row2[0]<br></td></tr>
END;
}
$result3 = mysql_query("SHOW COLUMNS FROM $row[0]");
while ($row3 = mysql_fetch_row($result3)) {
echo <<<END
<tr><td>Column: $row3[0]<br></td></tr>
END;
}
$result4 = mysql_query("DESCRIBE $row[0]");
while ($row4 = mysql_fetch_row($result4)) {
echo <<<END
<tr><td>Describe: $row4[0]<br></td></tr>
END;
}
}
?>
</table>

 

All the above output the same thing, the field names without any of the attributes of each field, even though the manual states they should. I know I must be doing something wrong, but what?

[DarkWater]

You're only outputting one element of the array.  Did you expect the one element to contain several columns worth of data...?  Try running print_r() on the result row instead.

[Chappers]

As Roland of Gilead would say, I have forgotten the face of my father.

 

I didn't expect one element to contain several columns of data, I was thinking along the lines of an output with each field entry separated by a comma or something. I don't know. Just got confused. I worked it out just before coming back here to check for a reply after looking at the manual about Show Columns, when I realised I was only outputting 'Field' and not 'Type' or any of the others. I didn't realise it grabbed them like that, I still get cross-eyed with SQL at times. What can I say, must have some Irish in me. Also had to change mysql_fetch_row to mysql_fetch_array for it to work:

 

<?php
while ($row3 = mysql_fetch_array($result3)) {
$field = $row3['Field'];
$type = $row3['Type'];
$null = $row3['Null'];
$key = $row3['Key'];
$default = $row3['Default'];
$extra = $row3['Extra'];
}
?>