How can I see if a table has a certian field?

[tibberous]

Is there a way to check to see if a table has a specific field? I tried:

 

show columns from packages where `Key` like 'PRI'

 

But it gives an sql error.

 

So does:

 

show fields from packages where `Key` like 'PRI'

 

Any ideas?

[devstudio]

Simply: http://www.php.net/mysql_list_fields

 

*oops*  Looks like that has been deprecated.  Read Example #1 on that link for "new" way.

 

That example will show you the resulting array for the query, you can use a simple conditional statement to check to see if a particular field exists.

 

Best, Nathan

[JustinK101]

Try this:

 

$sql = "SELECT * FROM table_name";
$result = mysql_query($sql) or die(mysql_error());

for ($i = 0; $i < mysql_num_fields($result); $i++) {
  $fields[$i] = mysql_field_name($result, $i);
}

if(in_array("search_value", $fields)) {
    echo 'found it';
} else {
echo 'not found';
}

[Barand]

One way is directly query the information_schema database.

 

E.G. Here's a script that let's you input a column name and list all tables in current db containing that column.

 

<?php
mysql_connect ($host,$usr,$pwd);
mysql_select_db($dbname);

if (isset($_GET['column']))
{
    $ta = wherecolumn($_GET['column']);
    echo '<pre>', print_r($ta, true), '</pre>';
}

function wherecolumn($column)
{
    $sql = "SELECT C.TABLE_NAME 
            FROM information_schema.`COLUMNS` C
            WHERE C.TABLE_SCHEMA=DATABASE() 
            AND C.COLUMN_NAME='$column'";
    $tables = array();
    $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
    while ($row = mysql_fetch_row($res))
    {
        $tables[] = $row[0];
    }
    return $tables;
}
?>
<form>
Column <input type="text" name="column">
<input type="submit" name="sub" value="Find"> 
</form>