With PHP / sqlite3, how can I add a column if it does not exist in a table?

Question

I'm looking for a way to add a column to a table only if it does not exist yet in a PHP script using sqlite3. It looks like SQL alone cannot help me here, for example with something like

ALTER TABLE items ADD COLUMN IF NOT EXISTS new_col INTEGER;

Well, I was thinking about hacks like trying to run a query and, if it fails, conclude the column doesn't exist yet :

if ($db->querySingle('SELECT new_col FROM items') === FALSE) {
    // False means the query failed
    $db->exec('ALTER TABLE items ADD COLUMN new_col INTEGER');
}

update_stuff_with_new_column($db);

I guess it should work, but what if the query failed for another reason (say, a transaction is running) ?

There are a few other solutions I can think of, but they all look the same : "if the query fails, the column probably does not exist, create it". Is there a better way to do it ?

This is a script to transform an ugly database into a nicer one. Well, actually, I have a few different database files, but they don't have exactly the same schema, some of them already have the new_col column, but with obsolete values, while others don't have the column at all. I'm trying to merge them all. Yeah, that's rather ugly, but, that's life :/ — Fabien
– Fabien, Commented Jul 4, 2012 at 10:05

Doug Currie · Accepted Answer · 2012-07-20 13:55:53Z

2

You can find the SQL of the CREATE TABLE statement in the sqlite_master table; you'll need to parse it to see if new_col is defined yet:

sqlite> create table items (col1, col2);
sqlite> select sql from sqlite_master where type = 'table' and name = 'items';
CREATE TABLE items (col1, col2)
sqlite> alter table items add column new_col;
sqlite> select sql from sqlite_master where type = 'table' and name = 'items';
CREATE TABLE items (col1, col2, new_col)

Addendum:

Additional ways to see if a column already exists in a table include:

Try to prepare (sqlite3_prepare_v2) a statement of the form

select ColumnToCheck from TableToCheck;

and see if it gives an error (thanks to Igor Tandetnik!).

Or use the

PRAGMA table_info(myTable)

documented here.

edited Jul 20, 2012 at 13:55

answered Jul 4, 2012 at 16:49

Doug Currie

41.4k1 gold badge104 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fabien Over a year ago

Hmm, I'll try this way, seems promising. Thanks :)

Rohit Ramani · Accepted Answer · 2013-05-01 07:18:39Z

php file 
===========
<pre>
<?php


define(DB_USER,'root');
define(DB_PASS,'');
define(DB_NAME,'DATABASENAME');

//Load the class

/* Creating Connection for database where we want to make changes using sql file */

$con = mysql_connect('localhost',DB_USER,DB_PASS) or die ("error in connecting $sitename ");
mysql_select_db(DB_NAME,$con) or die("error in databse selction in $sitename");



/* getting content from sql file where we have updated fields */
$fp= file_get_contents('blank_hotel_version_1.0.sql', true);




/* getting list of create table query from sql file content */

$pattern_gettable = '/CREATE TABLE IF NOT EXISTS (.*?) ;/is';

preg_match_all($pattern_gettable,$fp,$tables);

foreach($tables[0] as $value):
    mysql_query($value,$con) or die(mysql_error());
endforeach;





//exit;


/* getting Database table list */

$tables_db ="*";
if($tables_db == '*')
{
    $tables_db = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
        $tables_db[] = $row[0];
    }
}
else
{
    $tables_db = is_array($tables_db) ? $tables_db : explode(',',$tables_db);
}



foreach($tables_db as $tablename):
     $pattern = "CREATE TABLE IF NOT EXISTS `{$tablename}`";

    foreach($tables[0] as $value):

        if(strpos($value,$pattern)=== false):

        else:

            /*getting columns  list from perticular table query of sql file */

            $pattern_cloumn="/CREATE TABLE IF NOT EXISTS `{$tablename}` \((.*?) PRIMARY KEY/is";
            preg_match_all($pattern_cloumn,$value,$columns);


            /*Replacing comma with # so that while we exploade we can easily make array of columns othere wise it make problem for enum(..) datatype or comment */
            $pattern_cm="/\n/ims";
            $columns[1][0]= preg_replace($pattern_cm,"#",$columns[1][0]);
            $colmn=explode("#",$columns[1][0]);
            array_shift($colmn);


            /*Getting name of columns currently we have columns with sql query like "`id` int(11) not null," and we get column name like 'id' into $columnnames array */
            $columnnames=array();
            foreach($colmn as $cols):
                $pattern = '/`(.*?)`/is';
                preg_match_all($pattern, $cols ,$namecols);

                array_push($columnnames,$namecols[1][0]);
            endforeach;


            /*Getting column names from database table */
            $column_query="SHOW COLUMNS FROM {$tablename}";
            $result_clm=mysql_query($column_query);
            $colum_db_array=array();
            while($row_clm=mysql_fetch_array($result_clm))
            {
                array_push($colum_db_array,$row_clm['Field']);
            }


            foreach($columnnames as $key=>$value_cols)
            {
                /*if column not exists in database table then we add column into table */

                if(!in_array($value_cols,$colum_db_array) && $value_cols!="")
                {

                    if(substr($colmn[$key],strlen($colmn[$key])-2)=="',")
                    {
                        $query_cl="ALTER TABLE {$tablename} ADD ".substr($colmn[$key],0,strlen($colmn[$key])-1);
                    }
                    else
                    {
                        $query_cl="ALTER TABLE {$tablename} ADD ".substr($colmn[$key],0,strlen($colmn[$key])-1);
                    }

                    mysql_query($query_cl) or die(mysql_error());
                }
            }
        endif;


    endforeach;


endforeach;


exit;

?>
</pre>

Sql File 
========
<pre>



-- --------------------------------------------------------

--
-- Table structure for table `advertising`
--

CREATE TABLE IF NOT EXISTS `advertising` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `title_tag` varchar(255) NOT NULL,
  `alt_tag` varchar(255) NOT NULL,
  `d_order` int(11) NOT NULL,
  `is_visible` varchar(11) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `advertising`
--


-- --------------------------------------------------------

--
-- Table structure for table `advertising_enquiry`
--

CREATE TABLE IF NOT EXISTS `advertising_enquiry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `advertising_id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `website` varchar(255) NOT NULL,
  `enquiry` text NOT NULL,
  `budget` varchar(255) NOT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

--
-- Dumping data for table `advertising_enquiry`
--


-- --------------------------------------------------------

--
-- Table structure for table `agents`
--

CREATE TABLE IF NOT EXISTS `agents` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `agent_name` varchar(150) NOT NULL,
  `contact` varchar(1000) NOT NULL,
  `cat_id` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `agents`
--


-- --------------------------------------------------------

--
-- Table structure for table `amenities`
--

CREATE TABLE IF NOT EXISTS `amenities` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `am_name` varchar(200) NOT NULL,
  `type` enum('H','R','RE') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=234 ;

--
-- Dumping data for table `amenities`
--



</pre>

This may help you thanks

Collectives™ on Stack Overflow

With PHP / sqlite3, how can I add a column if it does not exist in a table?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related