Php/pdo - Only Inserts 5 Tables

[MySQL_Narb]

I'm making an installation page for my script I plan on releasing. The page allows users to type in their database details, and then after verifying the connection - it attempts to create all the tables and insert default values. Although, it only creates the first 5 tables and doesn't insert the data for the last created table (forums).

 

$db->query("
-- Generation Time: Sep 22, 2012 at 06:54 PM
-- Server version: 5.1.57
-- PHP Version: 5.2.17

SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";

CREATE TABLE `banned_ips` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(20) COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

CREATE TABLE `cats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(40) COLLATE latin1_german2_ci NOT NULL,
`type` int(11) NOT NULL,
`pos` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=2 ;

INSERT INTO `cats` VALUES(1, 'Staff', 1, 1);
INSERT INTO `cats` VALUES(2, 'General', 0, 2);

CREATE TABLE `config` (
`maintenance` int(11) NOT NULL DEFAULT '0',
`floodlimit` int(11) NOT NULL DEFAULT '0',
`lastset` int(11) NOT NULL,
`postcount` int(11) NOT NULL,
`reportforum` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO `config` VALUES(0, 15, 1340316455, 0, 2);

CREATE TABLE `dailyscreenshots` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`filename` varchar(54) NOT NULL,
`caption` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `forums` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent` int(11) NOT NULL,
`pos` int(11) NOT NULL,
`title` varchar(30) COLLATE latin1_german2_ci NOT NULL,
`description` varchar(100) COLLATE latin1_german2_ci NOT NULL,
`icon` int(2) NOT NULL,
`type` int(11) NOT NULL,
`double_posting` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=3 ;

INSERT INTO `forums` VALUES(1, 1, 1, 'Staff Center', 'All the staff-related or very delicate issues can be resolved here.', 0, 4, 1);
INSERT INTO `forums` VALUES(2, 1, 2, 'Reports', 'All automated reports go here.', 0, 4, 0);
INSERT INTO `forums` VALUES(3, 2, 1, 'Success!', 'You\\''ve successfully installed RCS. Wewt!', 9, 1, 1);

CREATE TABLE `login_attempts` (
`failed` int(11) NOT NULL,
`ip` varchar(15) COLLATE latin1_german2_ci NOT NULL,
`time` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`creator` varchar(12) NOT NULL,
`receiver` varchar(12) NOT NULL,
`title` varchar(50) NOT NULL,
`message` text NOT NULL,
`ip` text NOT NULL,
`date` datetime NOT NULL,
`status` int(11) NOT NULL,
`opened` int(11) NOT NULL,
`lastreply` varchar(12) NOT NULL,
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(35) NOT NULL,
`category` int(1) NOT NULL,
`content` text NOT NULL,
`username` varchar(12) NOT NULL,
`date` varchar(20) NOT NULL,
`ip` varchar(20) NOT NULL,
`icon` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

CREATE TABLE `online_users` (
`cookie` varchar(35) COLLATE latin1_german2_ci NOT NULL,
`time` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

CREATE TABLE `polls` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`poll_title` varchar(50) COLLATE latin1_german2_ci NOT NULL,
`poll_question` text COLLATE latin1_german2_ci NOT NULL,
`date` varchar(13) COLLATE latin1_german2_ci NOT NULL,
`closed` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

CREATE TABLE `poll_options` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`belongs` int(11) NOT NULL,
`option` varchar(100) COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(12) COLLATE latin1_german2_ci NOT NULL,
`content` text COLLATE latin1_german2_ci NOT NULL,
`thread` int(11) NOT NULL,
`date` datetime NOT NULL,
`status` int(11) NOT NULL,
`lastedit` varchar(55) COLLATE latin1_german2_ci NOT NULL,
`ip` varchar(20) COLLATE latin1_german2_ci NOT NULL,
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

CREATE TABLE `recoveries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`cancel` int(11) NOT NULL,
`a1` varchar(35) NOT NULL,
`a2` varchar(35) NOT NULL,
`a3` varchar(35) NOT NULL,
`a4` varchar(35) NOT NULL,
`a5` varchar(35) NOT NULL,
`a6` varchar(35) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `replies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(12) NOT NULL,
`conversation` int(11) NOT NULL,
`content` varchar(2000) NOT NULL,
`ip` varchar(20) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `reports` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`reported` varchar(15) COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

CREATE TABLE `stories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) COLLATE latin1_german2_ci NOT NULL,
`content` text COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=2 ;

CREATE TABLE `threads` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent` int(11) NOT NULL,
`title` varchar(50) COLLATE latin1_german2_ci NOT NULL,
`content` text COLLATE latin1_german2_ci NOT NULL,
`username` varchar(12) COLLATE latin1_german2_ci NOT NULL,
`date` datetime NOT NULL,
`qfc` varchar(14) COLLATE latin1_german2_ci NOT NULL,
`lastpost` datetime NOT NULL,
`lastposter` varchar(12) COLLATE latin1_german2_ci NOT NULL,
`lastedit` varchar(55) COLLATE latin1_german2_ci NOT NULL,
`lastbump` int(11) NOT NULL,
`ip` varchar(15) COLLATE latin1_german2_ci NOT NULL,
`sticky` int(11) NOT NULL,
`lock` int(11) NOT NULL,
`status` int(11) NOT NULL,
`moved` tinytext COLLATE latin1_german2_ci NOT NULL,
`hidden` int(11) NOT NULL,
`timestamp` int(11) NOT NULL,
`autohiding` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

CREATE TABLE `tracking` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` varchar(12) NOT NULL,
`ip` varchar(20) NOT NULL,
`date` date NOT NULL,
`time` int(11) NOT NULL,
`tracking_id` varchar(12) NOT NULL,
`status` int(11) NOT NULL,
`a1` varchar(35) NOT NULL,
`a2` varchar(35) NOT NULL,
`a3` varchar(35) NOT NULL,
`a4` varchar(35) NOT NULL,
`a5` varchar(35) NOT NULL,
`a6` varchar(35) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(12) NOT NULL,
`password` tinytext NOT NULL,
`age` varchar( NOT NULL,
`country` varchar(20) NOT NULL,
`email` varchar(35) NOT NULL,
`reg_date` varchar(20) NOT NULL,
`acc_status` int(11) NOT NULL,
`ip` varchar(20) NOT NULL,
`lastpost` int(11) NOT NULL,
`lastlogin` int(11) NOT NULL,
`cookie` varchar(35) NOT NULL,
`forum_mute` int(11) NOT NULL,
`mute_time` int(11) NOT NULL,
`updated` int(11) NOT NULL,
`invited` int(11) NOT NULL,
`lastbump` int(11) NOT NULL,
`messages` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `users` VALUES(1, 'Report', '43e55dc74d83bad897cb49b784c49e418304f8544b81ed015f77617dbe80c3edf5f6e702b25f721d82a962a10f6764897dd7b24b5e3cf9bd1e2a6b43e', '13-18', '225', 'fdsfdsf@gmail.com', 'Jun/22/2012', 4, '209.82.161.101', 1348344761, 1340362924, '". hash(sha512, time()) ."', 0, 0, 1, 0, 0, 0);

CREATE TABLE `votes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`option_id` int(11) NOT NULL,
`poll` int(11) NOT NULL,
`username` varchar(12) COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;");

Edited September 22, 2012 by MySQL_Narb

[Jessica]

I wasn't aware of any way to run multiple queries in one statement using PHP. Are you capturing errors anywhere?

[jcbones]

You should probably be using mysqli::multi_query()

[darkfreaks]

i think this is more what the OP is looking for to use PDO to create Tables

 

http://php.net/manua...transaction.php

Edited September 23, 2012 by darkfreaks

[Monkuar]

Screw using that bloated PDO crap, nonsense.

 

 

They're is an error somewhere if it just randomly stopped. Enable error reporting, put this at the top of your php file

 

error_reporting(-1);

 

Try it again, copy your stuff here. This can be done easily w/o the need of PDO or another function, those tables should be able to run just fine stock.

Edited September 23, 2012 by Turd

[Adam]

What do you have against PDO?

[DavidAM]

@Turd - I don't know about PDO, but the other sql extensions I have worked with (mysql and mysqli) do not promote SQL errors to PHP errors. You have to actually use the sql extension's error function to get the error message.

 

@OP - The third INSERT statement on that fifth table looks invalid to me. I don't think you have escaped the single-quote correctly (unless that was a cut-n-paste issue with the post).

 

INSERT INTO `forums` VALUES(3, 2, 1, 'Success!', 'You\\''ve successfully installed RCS. Wewt!', 9, 1, 1);

 

Shouldn't that be 'You\'ve successfully ...?

[Christian F.]

David: Almost, it should have been 'You\\\'ve successfully..., which would be translated into what you wrote above, inside the string.

 

Had this been anything other than MySQL, then the query might have needed to be constructed as 'You\'\'ve successfully.... Though MySQL does not use single quotes for escaping characters, as some other DB systems does.

[DavidAM]

David: Almost, it should have been 'You\\\'ve successfully..., which would be translated into what you wrote above, inside the string.

 

Had this been anything other than MySQL, then the query might have needed to be constructed as 'You\'\'ve successfully.... Though MySQL does not use single quotes for escaping characters, as some other DB systems does.

 

How do you figure? We have here a PHP string, using double quotes; with an SQL string, using single quotes; and the need to embed a single-quote inside the SQL string.

 

For mySql, that would be :

$sql = "INSERT INTO `forums` VALUES(3, 2, 1, 'Success!', 'You\'ve successfully installed RCS. Wewt!', 9, 1, 1);"

 

Since the backslash is inside of a double-quoted string and is immediately before a single-quote, it does not have any special meaning in PHP. So it must not be escaped for PHP. It is being escaped, once, for mySQL. Unless the PDO extension requires additional escaping for some strange reason, this is all that is needed.

[PFMaBiSmAd]

I'm making an installation page for my script I plan on releasing.

 

Then you should have the 3-e's in your code - error checking (check if something worked or failed), error reporting/logging (output a user message and log all the pertinent information about the error), and error recovery (take a predictable execution path when an error occurs to prevent follow-on errors) logic in your code,

 

If you are writing a script that you plan on releasing or the installation script for it, your code should be bullet-proof/fool-proof. If you already had error checking, error reporting/logging, and error recovery logic in your code, your code would have told you who was executing your script when it failed, what about it failed, when it failed, and where in your files/code it failed, so that you can find and fix why it failed.

 

Using the 3-e's not only helps you debug your code, but it also helps after the fact to find and fix things like legitimate visitors doing something your logic didn't take into account or hackers trying (succeeding) to break into your script.

 

Even if you found a method that will execute multiple queries at one time, you would never do that in a real script, since that prevents you from using the 3-e's to find exactly what didn't work when your code runs. You would execute each query separately and only move onto the next query if the previous step worked.

[Christian F.]

David: Ah, you're right. Took a second look at the OP's code block, and noticed the use of double quotes. Assumed it was single quotes, when I read your post. Sorry about the confusion.