importing data

[imarockstar]

I need to import a huge ass amount of data ... this is my code ..

 

<?php

if ( $data == 'import' ) {


$sql = "CREATE TABLE IF NOT EXISTS `adsense` (
  `id` int(3) NOT NULL auto_increment,
  `code` longtext NOT NULL COMMENT 'adsense code',
  `comment` mediumtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;";

mysql_query($sql) or die(mysql_error());

?>

 

 

obviously the above is not allot of code ... but i cant seem to figure out the best way to import a .sql file .. is there a way I can import a file with my above code ..

 

[jordanwb]

You want to take a .sql file that phpMyAdmin would make and run the queries?

[premiso]

Whats a "huge ass" amount of data.

 

If it is really significant, I would import it via the mysql console, or via phpMyAdmin, if the file is less than 2mbs.

 

Since browsers tend to timeout after 90seconds if no data is sent to the screen. There are ways around this, but that is just too unpredictable.

[jordanwb]

Whats a "huge ass" amount of data.

 

I'd say thousands of rows. If I'm not mistaken you can have phpMyAdmin compress it into a gz file which works very well with text files.

[imarockstar]

ok sorry ... my sarcasm has thrown off this thread lol ..

 

basically what I am needing the script to do is import dummy data. Its not really "huge" way under 1 meg. But its just allot, length wise,and I thought if i could import a sql file through php is would be more easy than to write out the data within the above php code.

 

what would be the best way in doing this ?

 

b

[imarockstar]

This is my script i am using :

 

<?php

if ( $data == 'import' ) {


$sql = "CREATE TABLE IF NOT EXISTS `adsense` (
  `id` int(3) NOT NULL auto_increment,
  `code` longtext NOT NULL COMMENT 'adsense code',
  `comment` mediumtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;";

mysql_query($sql) or die(mysql_error());

?>

 

as you can see there is only 1 table creation query. below is a little of what I want to import.

Its allot of data and when I put it into the above query it does not work so i was just wandering if i could "include" the sql file instead of having it actually in the above query ?

 

 

 

 

CREATE TABLE IF NOT EXISTS `adsense` (
  `id` int(3) NOT NULL auto_increment,
  `code` longtext NOT NULL COMMENT 'adsense code',
  `comment` mediumtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `adsense` (`id`, `code`, `comment`) VALUES
(1, '<script type="text/javascript"><!--\r\ngoogle_ad_client = "pub-2277278167426579";\r\n/* 468x60, created 11/3/08 */\r\ngoogle_ad_slot = "3760018615";\r\ngoogle_ad_width = 468;\r\ngoogle_ad_height = 60;\r\n//-->\r\n</script>\r\n<script type="text/javascript"\r\nsrc="http://pagead2.googlesyndication.com/pagead/show_ads.js">\r\n</script>', 'This is the ad that goes in the top and the bottom of the page. \r\n\r\nAd Dimensions:\r\n480 x 60\r\n\r\nYou can change the height if you want, but leave the width no longer than 468. '),
(2, '<script type="text/javascript"><!--\r\ngoogle_ad_client = "pub-2277278167426579";\r\n/* 160x600, created 11/3/08 */\r\ngoogle_ad_slot = "5480775252";\r\ngoogle_ad_width = 160;\r\ngoogle_ad_height = 600;\r\n//-->\r\n</script>\r\n<script type="text/javascript"\r\nsrc="http://pagead2.googlesyndication.com/pagead/show_ads.js">\r\n</script>', 'this is the ad that goes on the right hand  side of the page. its in the sidebar includes file.\r\n\r\nAd Dimensions:\r\n160 x 600\r\n\r\nDo not change the width of the ad, however you may change the height.');

CREATE TABLE IF NOT EXISTS `headerimages` (
  `id` int(2) NOT NULL auto_increment COMMENT 'image id',
  `imagename` varchar(255) NOT NULL default '' COMMENT 'user named image',
  `filename` varchar(255) NOT NULL default '' COMMENT 'name of file',
  `active` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='images used for the home page header' AUTO_INCREMENT=16 ;

INSERT INTO `headerimages` (`id`, `imagename`, `filename`, `active`) VALUES
(10, 'header 2', 'g_mainpik2.jpg', 0),
(11, '', 'g_mainpik3.jpg', 0),
(12, 'header 4', 'g_mainpik4.jpg', 0),
(13, 'header 5', 'g_mainpik5.jpg', 0),
(14, 'header 6', 'g_mainpik6.jpg', 0),
(15, 'header 7', 'g_mainpik7.jpg', 0),
(9, 'header 1', 'g_mainpik1.jpg', 0);

CREATE TABLE IF NOT EXISTS `homeblocks` (
  `id` int(3) NOT NULL auto_increment,
  `metatitle` text NOT NULL,
  `metadesc` text NOT NULL,
  `title` varchar(255) NOT NULL default '' COMMENT 'title of block',
  `pagetitle` varchar(255) NOT NULL default '',
  `topcopy` mediumtext NOT NULL,
  `bottomcopy` mediumtext NOT NULL,
  `text` mediumtext NOT NULL COMMENT 'block text',
  `rss` mediumtext NOT NULL,
  `rssactive` int(1) NOT NULL default '0' COMMENT 'tells us if there is a rss feed on the page',
  `active` int(1) NOT NULL default '0',
  `home` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

 

 

[bluesoul]

I guess the obvious question is why not just use phpMyAdmin rather than a wheel reinvention?

[imarockstar]

because this is a installer script. they go through the process of entering in there DB info an then when they get to the import page this script will place the dummy data into the database.

 

1) user downloads my script

2) user uploads files to there server

3) user navigates to install.php and goes through the steps

    a) enter there db info

    b) enter admin username and password

    c) clicks the import data button

        **this is where i need to import the dummy data

4) user deletes the install directory

5) website is ready to go

 

here is a link to the site i am working on :

 

http://franklinspirko.com/sites/rssnewsite/install/

 

that might give you a better idea of whats going on

 

b

 

 

[premiso]

Create the sql file on the server with each insert on a new line.

 

Read the file in with file

 

Do a foreach loop and run mysql_query on each element of the file array.

 

<?php
$createTableSQL[] = "CREATE TABLE IF NOT EXISTS `adsense` (
  `id` int(3) NOT NULL auto_increment,
  `code` longtext NOT NULL COMMENT 'adsense code',
  `comment` mediumtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;";

$createTableSQL[] = "CREATE TABLE IF NOT EXISTS `headerimages` (
  `id` int(2) NOT NULL auto_increment COMMENT 'image id',
  `imagename` varchar(255) NOT NULL default '' COMMENT 'user named image',
  `filename` varchar(255) NOT NULL default '' COMMENT 'name of file',
  `active` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='images used for the home page header' AUTO_INCREMENT=16 ;";

foreach ($createTableSQL as $sql) {
    mysql_query($sql);
}

$sqlFile = file('createSQL.sql');

echo "Populating Tables...Please Wait";
$i=0;
foreach ($sqlFile as $sql) {
         mysql_query($sql);
         if ($i % 100 == 0) {
               echo ".";
         }
         $i++;
}
echo "Tables populated!<br />";
?>

 

I would put the table creators in side the script as an array of strings and loop through them first since they do not have the " and '.

 

You should be fine doing that. You can even add a "Please Wait" deal like in the sqlFile example above.

 

Hopw that helps get you rolling.