Displaying Column type

[GigaG]

I am running php 5.2.1 and mysql 5.0.38. I am looking to ultimately create a dowhile loop that will automatically create a form based on what is in the mysql database. For example, if the database has a field named Date_Due and the "Type" is DATE, i would like the script to create the appropriate input box / text area for the field. I would like to know what php syntax I would need to use in order to grab the "Type" field from mysql, or at least what the mysql query would be and I'm sure I could get the php syntax working.

Thank you


MCP ACA-I CTP

 

[vacunita]

This is actually more of a MYSQL question, so it would be better posted here: forum436

but the basic query is just a simple "describe".

$qry="describe mytablename";

With that you get a result set that gives you the field name, the type of field etc...

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[GigaG]

as I stated in the question... I am looking for how to inject the type into a variable and would be both since I am looking to do it with php. i know how to describe a table.. I'm looking for how to grabe that "Type" which would be DATE, or DATETIME or TEXT

MCP ACA-I CTP

 

[vacunita]

Well once you run the query, you can use the standard mysql functions from PHP to grab the information like you would a normal query.

$qry="describe mytablename";
$results=mysql_query($qry) or die(mysql_error());
$i=0;
while($row=mysql_fetch_array($results)){
$field[$i]['name']=$row['field'];
$field[$i]['type']=$row['type'];
$i++;
}

echo "<pre>";
print_r($field);
echo "</pre>";

Try that small piece of code.

The name of the field would be in $field[x]['name'] and the type in $field[x]['type'].

where X is the number of the column you want. 0 is the first field, 1 is the second field etc...




the name of the fiorst field of your

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[GigaG]

I understand i think what you are doing here , but I must be missing something... nothing is populating other than the following:

Array
(
[0] => Array
(
[name] =>
[type] =>
)

[1] => Array
(
[name] =>
[type] =>
)

[2] => Array
(
[name] =>
[type] =>
)

[3] => Array
(
[name] =>
[type] =>


below is my script...

$query="Describe jos_components";
$results=mysql_query($query) or die (mysql_error());
$i=0;
while($row=mysql_fetch_row($results)){
$field[$i]['name']=$row['field'];
$field[$i]['type']=$row['type'];
$i++;

}
echo"<pre>";
print_r($field);
echo"</pre>";

Am I suppose to fill the $row part in with what i am looking for....??

thank you for the quick response


MCP ACA-I CTP

 

[vacunita]

I used [red]mysql_fetch_array[/red] for a reason. And that's because you can address the columns by name. If you want to use [red]mysql_fetch_row[/red] you need to address them by number. So it would be:

while($row=mysql_fetch_row($results)){
$field[$i]['name']=$row[[red]0[/red]];
$field[$i]['type']=$row[[red]1[/red]];

Ohh and if you do use mysql_fetch_array. make sure the column names have the first letter capitalized.

$row['[blue]F[/blue]ield'] and $row['[blue]T[/blue]ype']

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DonQuichote]

I would not use an extra describe query here. If you get a result set from the mysql_query() function, you can use the mysql_fetch_field() function to get about any info you want.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[GigaG]

Thank you both for the response. Yes I did end up figuring the row[1] out a few min ago... I was being a dumbass. I will also look into the mysql_fetch_field() thank you guys

MCP ACA-I CTP

 

[vacunita]

I would not use an extra describe query here. If you get a result set from the mysql_query() function, you can use the mysql_fetch_field() function to get about any info you want

Assuming you were actually running a query to get data, then yes you can use fetch_field, but as I understand it, he isn't. So he's just running a describe query to get the table structure.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[southbeach]

This PHP script should get you started:

<?php
$conn = mysql_connect('localhost', 'root', 'root');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydbname');
$tables = mysql_query("show tables");

echo '<table border="1">';
			echo '
			<tr style="background-color: #000; color: #FFF;">
				<td>Table Name</td>
				<td>Field Name</td>
				<td>Blob</td>
				<td>Max Length</td>
				<td>Multi-Key</td>
				<td>Not Null</td>
				<td>Numeric</td>
				<td>Primary</td>
				<td>Table</td>
				<td>Type</td>
				<td>Default</td>
				<td>Unique</td>
				<td>Unsigned</td>
				<td>Zero Fill</td>
				<td>Value / Content</td>
			</tr>';

while ($tlist = mysql_fetch_array($tables)) {

	$qstr = 'select * from ' . $tlist[0];
	$result = mysql_query($qstr);
	if (!$result) {
		die('Query failed: ' . mysql_error());
	}
	
	while ($rows = mysql_fetch_assoc($result)) {
		/* get column metadata */
		$i = 0;
		while ($i < mysql_num_fields($result)) {
			// echo "Information for column $i:<br />\n";
			$meta = mysql_fetch_field($result, $i);
			if (!$meta) {
				echo "No information available<br />\n";
			}
			echo '
			<tr>
				<td>' . $tlist[0] . '</td>
				<td>' . $meta->name . '</td>
				<td>' . $meta->blob . '</td>
				<td>' . $meta->max_length . '</td>
				<td>' . $meta->multiple_key . '</td>
				<td>' . $meta->not_null . '</td>
				<td>' . $meta->numeric . '</td>
				<td>' . $meta->primary_key . '</td>
				<td>' . $meta->table . '</td>
				<td>' . $meta->type . '</td>
				<td>' . $meta->def . '</td>
				<td>' . $meta->unique_key . '</td>
				<td>' . $meta->unsigned . '</td>
				<td>' . $meta->zerofill . '</td>
				<td>' . $rows[$meta->name] . '</td>
			</tr>';
			$i++;
		}
	}
}
echo '</table>';
mysql_free_result($result);
?>