how to install multiple tables to a mysql database with php pdo

Question

I am creating a CMS and have created a self installer that creates the tables in the database.

I am using this php code which everything works great on wamp but the script quites after 10 files on the server.

if (!$db = new PDO('mysql:host='.$dbhost.'; dbname='.$dbname, $dbuser, $dbpass)){$msg = 'e|Could Not Connect.';}            
        elseif ($db = new PDO('mysql:host='.$dbhost.'; dbname='.$dbname, $dbuser, $dbpass)){
        $sql = '';
        foreach(glob('sql/*') as $file) {$sql .= file_get_contents($file);}     
        $db->exec($sql);    
        }

There are a total of 48 txt files in sql/ which look like this:

--
-- Table structure for table `block_grids`
--

CREATE TABLE IF NOT EXISTS `block_grids` (
  `block_grid_id` int(12) NOT NULL AUTO_INCREMENT,
  `block_grid_name` varchar(25) NOT NULL,
  `row_items_small` tinyint(2) NOT NULL,
  `row_items_medium` tinyint(2) NOT NULL,
  `row_items_large` tinyint(2) NOT NULL,
  `equalize_block_grid` tinyint(1) NOT NULL,
  PRIMARY KEY (`block_grid_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The server is set to 200mMiBs for the upload and the total of the 10 files executed is only 161KiBs. Not quite sure where to go with it...

The code above concatenates all the files together which works fine on wamp. I tried running each file individully with this code but it quite after only 4 on every attempt:

if (!$db = new PDO('mysql:host='.$dbhost.'; dbname='.$dbname, $dbuser, $dbpass)){$msg = 'e|Could Not Connect.';}            
    elseif ($db = new PDO('mysql:host='.$dbhost.'; dbname='.$dbname, $dbuser, $dbpass)){
    $sql = '';
      foreach(glob('sql/*') as $file) {
      $sql = file_get_contents($file);
      $db->exec($sql);
      }     

    }

when you said it works fine on wamp, you mean u have a 2nd server if so where, who hosts — Drew
– Drew, Commented Jun 19, 2015 at 21:35
I designed it on my comp with wamp and installed it on the net on a shared hostmonster account — petebolduc
– petebolduc, Commented Jun 19, 2015 at 21:37

Drew · Accepted Answer · 2015-06-19 21:55:04Z

1

As you are on a host now and not wamp, the following may not be possible in a hosted environment but give it a shot. (note the .cnf modification may not be possible)

show variables like '%wait_timeout%';
show global variables like '%wait_timeout%';

You would run at a mysql prompt:

set global wait_timeout=600

This is 10 minutes, a pathetically long time. You choose the value based on your needs not my nonsense.

any new sessions get this value.

mirror change in your my.cnf. file. That becomes effective on mysql restart.

Any one transaction in a reporting job (or otherwise) is naturally of concern. Think of any non-wrapped statement outside of a transaction as a transaction.

or as a work around make many teeenie files

answered Jun 19, 2015 at 21:55

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

petebolduc Over a year ago

Thanks @Drew Pierce for you input. The purpose of the auto installer is to allow for a quick installation of the script by a non programmer type. It was my intent to make a video show how to create the db in cpanel. The script identifies if the db tables are not installed and auto brings the to the install set up where they enter the db user/pass after which it takes them to a series of forms about their biz and so on. I'm looking for a work around to allow this process to occur.

Drew Over a year ago

If we knew the contents sequentially we could help

petebolduc Over a year ago

I am not quite sure what you are asking for... the sequential of the sql files?

Drew Over a year ago

Sequence rather ... of statements.

Drew Over a year ago

One way is to create a log table, insert a row with a string and timestamp BEFORE and AFTER EVERY sql command. Then u can focus on the last entry in the log that never got an AFTER.

|

user3824994 · Accepted Answer · 2015-06-19 21:54:53Z

0

By default PHP stops after 30 seconds. You can try this:

set_time_limit(300);

in your setup.php

answered Jun 19, 2015 at 21:54

user3824994

9 Comments

petebolduc Over a year ago

Thanks @WebDevel for your input. as explained in the comment to @Drew Pierce the purpose of the auto installer is to allow for a quick installation of the script by a non programmer type I did try sleep(1) in the second example to no avail.

user3824994 Over a year ago

As I understand you have a setup-php file, right? set_time_limit() have to be in this file at the very top of it. This will tell php not to stop after 30seconds. If this will not work, you can splitt your insert in multiple steps (step1 create table1, step2 insert content1, step3 create table2, step4 insert content2, ...). But I would prefer to use set_time_limit().

petebolduc Over a year ago

I tried the set_time_out(300) with the same results. I also have tried the multiple steps from the same folder but in reading your comment I thought about splitting the sql files into multiple folders and executing each in sequence which I am going to try now.

user3824994 Over a year ago

You wrote that the server stops afeter 10 files. Maybe there is something wrong with the 11th file? Don't need to move the files in different folders. You can redirect the script to it self after each insert with the info in $_GET which number will be the next file to import.

petebolduc Over a year ago

I removed the 11th file completely and tried different combinations of files as well. I did try the different folder thing... no luck there and my most recent attempt was to call each file $sql = file_get_contents('sql/block-grid-item.txt'); $db->exec($sql); which quite after four... at a loss for sure

|

Collectives™ on Stack Overflow

how to install multiple tables to a mysql database with php pdo

2 Answers 2

7 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related