Creating Tables with information in them using PHP

[jrcarr]

I didn't know if this should be under PHP help or MySQL help.  Anyway...

I've exported a set of tables with several fields in each, most of those field are poplulated.  Now, how would I use php to create those same tables, fields and populate them in another database? I know how to do it using phpmyadmin, but I want to be able to do it from an installation page script.  Thanks

Jack

[jwk811]

you could make up a mysql_query for the table and then add all the information in with a different mysql_query by taking out all the information from the table and then INSERTing it into the new one.. do you know what i mean? do you know how to do this? if not let me know and i will try to help you further.. i dont know if there is a simpler way to do this but this should work

[jrcarr]

Here is an example of one of the tables with the fields and the info that needs to be inserted.

[code]
CREATE TABLE `website_catalogitems` (
  `item_id` int(11) NOT NULL auto_increment,
  `catalog_id` varchar(50) NOT NULL default '',
  `item` varchar(100) NOT NULL default '',
  `info` text NOT NULL,
  `price` decimal(5,2) NOT NULL default '0.00',
  `thumbimage` varchar(100) NOT NULL default '',
  `image` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `website_catalogitems`
--

INSERT INTO `website_catalogitems` VALUES (1, '1', 'Sample Product', 'Birds', 10.99, '82d3bafaeef1a6446a8a7fd26cabird.jpg', '7c20a1390f2b080b0151ec6fb31bird.jpg');
[/code]

I'm assuming that I would start with something like:

[code]CREATE TABLE website_catalogitems (`item_id` int(11) NOT NULL auto_increment, `catalog_id` varchar(50) NOT NULL default '', `item` varchar(100) NOT NULL default '', `info` text NOT NULL, birth DATE, `price` decimal(5,2) NOT NULL default '0.00', `thumbimage` varchar(100) NOT NULL default '',`image` varchar(100) NOT NULL default '', PRIMARY KEY  (`item_id`));[/code]

I don't know about the proper syntax and what to do with the ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; But then I'm guessing that I would run a query like:

[code]INSERT INTO `website_catalogitems` VALUES (1, '1', 'Sample Product', 'Birds', 10.99, '82d3bafaeef1a6446a8a7fd26cabird.jpg', '7c20a1390f2b080b0151ec6fb31bird.jpg');[/code]

But again, I'm unsure of exact syntax to make it work.  Then I would do the same basic thing with the other tables that I'm needing to load.  I'm also figuring that I would need to check to see if the table is already there, before trying to create and populate it, maybe drop the table if it is there and then re-create it.

Does this give you some better idea of what I'm trying to do?  Thanks for the reply

Jack

[jrcarr]

Can anyone give me the proper syntax using PHP to do this?  Thanks

Jack

[Barand]

As long as the table in the new db is same as that in the old (substitute your database names for db1, db2)

[code]
INSERT INTO db2.website_catalogitems
SELECT * FROM db1.website_catalogitems
[/code]

[jrcarr]

Well that only works if both databases are on the same domain/server.  But, I wanting to be able to create an installation routine that creates these tables on anyone's site.  So I can't just copy from one database to another.  Thanks for your reply

Jack

[.josh]

[code]
<?php
  $conn = mysql_connect('localhost','username','password') or die(mysql_error());
  $db = mysql_select_db('dbname',$conn) or die(mysql_error());

  $sql = "put your query string here";
 
  $result = mysql_query($sql, $conn) or die(mysql_error());
?>
[/code]

[jrcarr]

So, if I have this right, I would need to do something like the following to add a couple tables and then insert the info into them?

[code]<?php
  $conn = mysql_connect('localhost','username','password') or die(mysql_error());
  $db = mysql_select_db('dbname',$conn) or die(mysql_error());

  $sql = "CREATE TABLE website_catalogitems (`item_id` int(11) NOT NULL auto_increment, `catalog_id` varchar(50) NOT NULL default '', `item` varchar(100) NOT NULL default '', `info` text NOT NULL, birth DATE, `price` decimal(5,2) NOT NULL default '0.00', `thumbimage` varchar(100) NOT NULL default '',`image` varchar(100) NOT NULL default '', PRIMARY KEY  (`item_id`));
";
  $result = mysql_query($sql, $conn) or die(mysql_error());

  $sql = "INSERT INTO `website_catalogitems` VALUES (1, '1', 'Sample Product', 'Birds', 10.99, '82d3bafaeef1a6446a8a7fd26cabird.jpg', '7c20a1390f2b080b0151ec6fb31bird.jpg');";
    $result = mysql_query($sql, $conn) or die(mysql_error());


  $sql = "CREATE TABLE different_name (Other fields list)";
    $result = mysql_query($sql, $conn) or die(mysql_error());

  $sql = "INSERT INTO `different_name` VALUES (different values for the fields in this table);";
    $result = mysql_query($sql, $conn) or die(mysql_error());

?>

[/code]

Then continue for all tables needed?

Jack

[.josh]

yeah, that should work.  although, depending on the end goal and stuff, there might be a more efficient way of doing that. for instance, if you were wanting to have a form field in which you put in the queries, you could just create a loop for it, etc..

[jrcarr]

Thanks.  What I'm trying to create is an installation routine to help users with the installation of a program I'm creating.  The install.php script will basically do the following:

1.  Display a form to collect their DB info, (name,user,password,server), default URL....

2.  When form is submitted, it will create a setting.php file with the collected information and certain defaults.

3.  Then continuing after writing the file,  include ("settings.php") to get the info supplied and connect to the database and create the needed tables and fill them with just default info.


The information going into the tables is the same on every installation and isn't dependent on anything in the form except for the DB connection info.  The rest of the program will add, edit, delete the info in the tables.  I'm pretty good or getting that way on working with DB information, but I have never needed to create tables from a script before.  I've always used phpMyadmin to handle that part.

Again, Thanks
Jack

[Barand]

Extract the data from your tables into csv files and ship those with the scripts.

After you create the tables use a "LOAD DATA INFILE" queries to populate the tables

[jrcarr]

Is this faster or have some other advantage to the method above?  I haven't researched the LOAD DATA INFILE command, so don't know anything about it.

Jack

[Barand]

It's the fastest way known to man (or woman) of loading data.

[jrcarr]

and using the example table and date in the third post on this topic, what would be the syntax to use this command.  Thanks

Jack

[Barand]

http://dev.mysql.com/doc/refman/4.1/en/load-data.html

The syntax will depend on how the csv file is created in the first place