Jump to content

Archived

This topic is now archived and is closed to further replies.

jrcarr

Creating Tables with information in them using PHP

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Can anyone give me the proper syntax using PHP to do this?  Thanks

Jack

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.