Jump to content


Photo

Creating Tables with information in them using PHP


  • Please log in to reply
14 replies to this topic

#1 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 03:15 AM

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
Jack Carr

#2 jwk811

jwk811
  • Members
  • PipPipPip
  • Advanced Member
  • 714 posts

Posted 28 October 2006 - 03:27 AM

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

#3 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 04:14 AM

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

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');

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

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`));

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:

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

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
Jack Carr

#4 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 02:28 PM

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

Jack
Jack Carr

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 28 October 2006 - 02:52 PM

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

INSERT INTO db2.website_catalogitems
SELECT * FROM db1.website_catalogitems

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 03:56 PM

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
Jack Carr

#7 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 28 October 2006 - 04:06 PM

<?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());
?>

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#8 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 04:31 PM

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?

<?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());

?>


Then continue for all tables needed?

Jack
Jack Carr

#9 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 28 October 2006 - 05:29 PM

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..
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#10 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 06:22 PM

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


Jack Carr

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 28 October 2006 - 06:33 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 07:55 PM

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
Jack Carr

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 28 October 2006 - 07:58 PM

It's the fastest way known to man (or woman) of loading data.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 28 October 2006 - 08:00 PM

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
Jack Carr

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 28 October 2006 - 08:07 PM

http://dev.mysql.com.../load-data.html

The syntax will depend on how the csv file is created in the first place
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users