Solved

Add field if does not exist (mysql, php)

Posted on 2008-10-02
6
1,942 Views
Last Modified: 2013-12-12
Hello,

The problem with my code is that some databases do not contain fields for "extra1Width" and "extra2Width".

This causes a database error.

How can I automatically ADD these fields if they do not already exist to avoid the error?

The value should be a smallint and should be 0 by default.


$adminSetting .= '
	extra1Width = ' . quote_smart($_POST['extra1Width']) . ',
	extra2Width = ' . quote_smart($_POST['extra2Width']) . ',
 
 
    $postupdate = 'UPDATE
                        cms_vars
                    SET
                        Item1 = ' . quote_smart($_POST['Item1']) . ','
			.$adminSetting.'
                        Item2 = ' . quote_smart($_POST['Item2']) . ','
                    WHERE
                        id = 1';
 
    $postupdateer = mysql_query($postupdate);

Open in new window

0
Comment
Question by:hankknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 22624992
How many databases would you possibly need to make this upgrade to?  You might want to consider the risks of a web / PHP application being able to change your database structure.
0
 
LVL 16

Author Comment

by:hankknight
ID: 22625235
I have about 70 databases.  Alternatively I could just check to see if the fields exists before doing the query to avoid the error.  How could I do that?
0
 
LVL 13

Expert Comment

by:AielloJ
ID: 22625587
My first reccomendation would be to do the updates to all the databases if possible.  If not, the easiest way to check if the field exists would be to do a SELECT * FROM the table with a rowlimit of 1.  Then check for the existence of the column name in the recordset using the isset function.  I consider this a definite second best solution to doing the database column updates if at all practical.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
Hube02 earned 500 total points
ID: 22630046

//See if a column exists
 
  $columns = array();
  $table = 'MyTable'
  $query = 'SHOW COLUMNS FROM '.$table.';';
  if ($results = mysql_query($query)) {
   if (mysql_num_rows($results) > 0) {
    while($row = mysql_fetch_row($results)) {
     columns[] = $row[0];
    }
   }
  }
  if (in_array('myColumn', $columns)) {
   echo 'column exists';     
  }
 
// add a column query
  $column = 'myColumn'
  $query = 'ALTER TABLE '.$table.' ADD COLUMN '.$column.' smallint DEFAULT "0";';

Open in new window

0
 
LVL 18

Expert Comment

by:Hube02
ID: 22630077
Should have left a comment...

You could turn the above into a function and call it to check for the columns that are needed before trying to use those columns.

As long as you specify the exact columns and do not depend on user input for the column names you should be safe. Never alter a table based on user input unless you are sure you know what that user input it. Only alter a database based on specific requirements.
0
 
LVL 18

Expert Comment

by:Hube02
ID: 22632932
just as an aside...

Altering a database in this way is a more common thing that one would expect. Many upgrades of applications perform these types of checks, especially those that do not require you to run an installation script to make the update.

If an installation/update script is provided then in many cases the database changes are done in the update. However, if an installation/update script is not used then in all likelihood, if database changes are included in the update, there could be many places where tables are altered to meet the new requirements of the application.

Security should always be considered when performing these changes to ensure that random alterations cannot occur due to user input.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php hashing methods 3 50
WordPress:  Reset File Permission after server migration 5 62
Doubt with angularJs with PHP 4 19
PHP, Curl and XML 13 13
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question