CODEWITHSUNDEEP
phpmysql
musicweb
musicweb

Reputation: 15

How can I modify a column in a table if it exists after checking all tables in my database?

This is the code I have now:

 <?php
// your connection
mysql_connect("localhost","user","password");
mysql_select_db("database");

// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
foreach ($row as $key => $table)
{
$fields = mysql_list_fields($table);
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {$field_array[] = mysql_field_name($fields, $i);}

if (in_array('customer_id', $field_array))
{
$result = mysql_query("ALTER TABLE " . $table . " MODIFY customer_id INT(11), CHANGE VARCHAR(12)");
}

    echo $key . " =&gt; " . $table . " CONVERTED<br />";
}
}
?>

The code runs, but no changes are made. I get a line like this for each table it queries:

0 => $table CONVERTED

Also get this in the server log:

PHP Notice:  Undefined variable: field_array in /home/bruce/public_html/cat/admx/tables.php on line 16

Any help on this?

Upvotes: 0

Views: 38

Answers (1)

Michiel Overtoom
Michiel Overtoom

Reputation: 1619

I found a few problems with your program:

  • Unnecessary inside enumeration.
  • Missing database parameter in call to mysql_list_fields().
  • Initialize field_array to an empty array before filling it with the fields of the currently processed table.
  • Usage of deprecated functions and no identation ;-(

I made some corrections:

<?php
$host = "localhost";
$user = "youruser";
$password = "yourpassword";
$dbname = "yourdatabasename";

$link = mysql_connect($host, $user, $password);
if (!$link) 
    die("Could not connect: ".mysql_error());

$db = mysql_select_db($dbname);
if (!$db)
    die ("Can't select database $dbname: ".mysql_error());

$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res)) {
    $table = $row[0];
    $fields = mysql_list_fields($dbname, $table);
    $columns = mysql_num_fields($fields);
    $field_array = array();
    for ($i = 0; $i < $columns; $i++)
        $field_array[] = mysql_field_name($fields, $i);
    if (in_array("customer_id", $field_array)) {
        $sql = "ALTER TABLE " . $table . " MODIFY customer_id INT(11), CHANGE VARCHAR(12)";
        $result = mysql_query($sql);
        echo "$table - altered<br>\n";
        }
    else
        echo "$table - no changes necessary<br>\n";
    }
?>

Let me know if it works better ;-)

Upvotes: 1

Related Questions