Jump to content

Creating Tables with information in them using PHP


jrcarr

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
Link to comment
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
Link to comment
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
Link to comment
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
Link to comment
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
Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.