Trying to export and import database

[garry27]

I've exported a database from phpadmin and I'm trying to import it onto a different server. The database connects fine but I get a mysql error from the first line of sql code. The source server is SQL version is 5.0.92 and the destination version is 5.0.77.

 

The database name I want to insert the data into is "h8861636".  Here's the sql code:

 

USE `h8861636`;

-- --------------------------------------------------------

--
-- Table structure for table `Blog`
--

CREATE TABLE IF NOT EXISTS `Blog` (
  `bid` int(10) unsigned NOT NULL auto_increment,
  `date` date NOT NULL,
  `title` varchar(80) collate latin1_general_ci NOT NULL,
  `uid` int(50) unsigned NOT NULL,
  `tid` int(10) unsigned NOT NULL,
  `mcid` int(10) unsigned default NULL,
  `iid` int(10) unsigned default NULL,
  `vid` int(10) unsigned default NULL,
  PRIMARY KEY  (`bid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=49 ;

--
-- Dumping data for table `Blog`
--

INSERT INTO `Blog` (`bid`, `date`, `title`, `uid`, `tid`, `mcid`, `iid`, `vid`) VALUES
(1, '2011-06-28', 'Welcome to the Site!', 1, 1, 1, 5, NULL),
(2, '2011-06-27', 'Post pictures of your reptiles!', 1, 2, 2, 0, NULL),
(3, '2011-06-27', 'Rankins Dragon Care', 1, 0, NULL, 0, 1),
(4, '2011-06-27', 'Corn Snake care', 1, 0, NULL, 0, 2),
(48, '2011-06-28', 'Leo blog', 1, 57, 58, 17, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `Comments`
--

CREATE TABLE IF NOT EXISTS `Comments` (
  `cid` int(10) unsigned NOT NULL auto_increment,
  `comment` varchar(1000) collate latin1_general_ci NOT NULL,
  `date` date NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `bid` int(10) unsigned NOT NULL,
  `iid` int(10) unsigned default NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `Comments`
--

INSERT INTO `Comments` (`cid`, `comment`, `date`, `uid`, `bid`, `iid`) VALUES
(1, 'this id comment 1', '2011-06-27', 2, 2, 6),
(2, 'This is comment 2', '2011-07-04', 3, 2, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `Image`
--

CREATE TABLE IF NOT EXISTS `Image` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `filename` varchar(50) collate latin1_general_ci NOT NULL COMMENT 'filename and extension only',
  `alt` varchar(50) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`iid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=18 ;

--
-- Dumping data for table `Image`
--

INSERT INTO `Image` (`iid`, `filename`, `alt`) VALUES
(1, 'uid2.jpg', 'Member''s profile picture'),
(2, 'uid3.jpg', 'Member''s profile picture'),
(3, 'uid4.JPG', 'Member''s profile picture'),
(4, 'uid6.JPG', 'Member''s profile picture'),
(5, 'bid1.jpg', 'Welcome to Reptile Care Sheets!'),
(6, 'cid1.jpg', 'Crested gecko'),
(17, 'bid48.jpg', 'Leopard Gecko');

-- --------------------------------------------------------

--
-- Table structure for table `MainContent`
--

CREATE TABLE IF NOT EXISTS `MainContent` (
  `mcid` int(10) unsigned NOT NULL auto_increment,
  `content` varchar(10000) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`mcid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=59 ;

--
-- Dumping data for table `MainContent`
--

INSERT INTO `MainContent` (`mcid`, `content`) VALUES
(1, 'Lorem ipsum dolor sit amet, reprehenderit donec vivamus vestibulum, aenean proin libero tellus fringilla, auctor convallis ac nec, amet vitae fusce volutpat leo. Sed ligula iaculis magna fusce, ex aenean turpis congue, sapien suscipit sit laoreet, rhoncus lacus sagittis nibh sed rutrum, amet eros in. Pellentesque aliquam libero fringilla aliquam semper risus, cursus ipsum ipsum amet urna ut, eget dignissim donec, varius dui quis nunc amet, odio diam. Mollis et commodo inventore vestibulum, tortor proin pede cras donec. Augue metus neque vel ac sed, turpis vitae sapiente, sit massa, odio massa ad et nunc, porta justo lacus lorem faucibus nisl. Porttitor integer maecenas curabitur felis pellentesque ridiculus, rutrum eros elementum varius a, enim bibendum ullamcorper, ipsum suspendisse proin. Libero sit nisl quisque wisi pellentesque vivamus, elit aliquam nulla ac et sed vehicula, cum consectetuer purus, nec dolor. Ut adipiscing sit. Viverra eleifend curabitur amet in in. Vitae in orci ridiculus, iaculis elementum integer convallis at ut ante, dolor libero semper, aenean at massa cum vel. Nunc integer tristique per pharetra in sodales, dignissim accumsan, pede ut arcu lorem, nibh vestibulum, duis at nulla risus ipsum pharetra fermentum.  Orci elit, vehicula in ullamcorper et erat nam, sem massa, rutrum fermentum malesuada velit vitae. Eget mi nec tortor, exercitation adipiscing montes sed mauris, sapien arcu nulla pellentesque nulla lorem duis. Lectus proin vel eu cum pellentesque, laoreet lectus vel eu arcu, etiam nulla. Lacus neque luctus fermentum. Leo nullam sit sit venenatis tincidunt, arcu parturient a est accumsan justo, quis aliquet nec gravida ac facilisi eleifend. Mattis nam eleifend eros potenti. Vel auctor ante phasellus nullam aenean. Leo volutpat arcu cras eleifend curabitur sagittis, enim gravida, mauris mi vel est neque sed tincidunt, velit ultrices non fermentum ligula nunc.  Pretium felis dictum id mauris arcu, fringilla proin mauris aut dis semper, semper in, libero arcu facilisi pretium nunc. Neque turpis faucibus mollis, quis vel mollis ultricies vestibulum, augue vulputate, eros nunc. Libero vel, in etiam maecenas cursus, a quis nulla eget proin, aliquet blandit vestibulum faucibus quis at suspendisse, congue interdum suspendisse pharetra netus. Magna interdum cras dui maecenas lorem ligula. Pulvinar sit lobortis suspendisse dictum curabitur, consectetuer libero justo accumsan quis, tincidunt ac venenatis diam ante sollicitudin eu, ut feugiat non eros id, lacus aliquid. Fringilla auctor, sed nibh etiam saepe velit viverra, pretium inceptos, nostra erat lectus habitasse varius elementum amet, et interdum lorem adipiscing volutpat aenean. Eu odio et at erat eu hac.'),
(2, 'Euismod ligula cum vestibulum, luctus velit laoreet, magna ullamcorper ac. Urna cras wisi sed scelerisque egestas orci, nam laoreet gravida in purus, sed felis aenean in ipsum. Sociis et ligula elementum odio eu wisi, id felis aliquet commodo, class semper vestibulum omnis a sodales dapibus. Et tortor orci in donec risus pellentesque, ipsum id egestas neque amet vitae pellentesque. Amet gravida rutrum pellentesque, nulla nulla, posuere odio orci fermentum nisl vestibulum, non in, ac in fermentum. Id integer eu vel imperdiet ut, suspendisse diam nibh, purus adipiscing ut, interdum nulla. Id et, lobortis sed aut massa suspendisse in, felis et adipiscing. Sed tincidunt iusto aliquam tellus, urna volutpat quis, suspendisse aliquam quis erat.  +   	'),
(3, 'This is my new crested gecko. Isn''t he great?'),
(58, 'Leo mc'),
(57, 'gh'),
(56, 'f'),
(55, 's'),
(54, 'g'),
(53, 'n'),
(52, 'tr'),
(51, 'blog test mc');

-- --------------------------------------------------------

--
-- Table structure for table `Teaser`
--

CREATE TABLE IF NOT EXISTS `Teaser` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `teaser` varchar(500) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`tid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=58 ;

--
-- Dumping data for table `Teaser`
--

INSERT INTO `Teaser` (`tid`, `teaser`) VALUES
(1, 'Welcome to the Reptile Pet Care online guide! This Webpage is currently under construction. Please take a look at the rest of the site and watch this space for futture announcements. '),
(2, 'This Blog is devoted to posting pictures of your pets.'),
(57, 'Leo teaser');

-- --------------------------------------------------------

--
-- Table structure for table `User`
--

CREATE TABLE IF NOT EXISTS `User` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `fname` varchar(20) collate latin1_general_ci NOT NULL,
  `lname` varchar(40) collate latin1_general_ci default NULL,
  `email` varchar(50) collate latin1_general_ci NOT NULL,
  `password` varchar(20) collate latin1_general_ci NOT NULL,
  `imageID` int(10) unsigned default NULL,
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `User`
--

INSERT INTO `User` (`uid`, `fname`, `lname`, `email`, `password`, `imageID`) VALUES
(1, 'Garry', 'McCabe', 'mccabegarry@hotmail.com', 'dsgdsg', NULL),
(2, 'Karen', 'Chung', 'uid2@domainname.com', 'rghdhdf', 1),
(3, 'Patrick', NULL, 'uid3@domainname.com', 'dfhdhd', 2),
(4, 'Sandy', NULL, 'uid4@domainname.com', 'gsrgg', 3),
(5, 'Marion', 'Moon', 'uid5@domainname.com', 'dgdggd', NULL),
(6, 'Jerome', 'Wallace', 'uid6@domainname.com', 'sgsfgs', 4);

-- --------------------------------------------------------

--
-- Table structure for table `Video`
--

CREATE TABLE IF NOT EXISTS `Video` (
  `vid` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(200) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`vid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `Video`
--

INSERT INTO `Video` (`vid`, `url`) VALUES
(1, 'http://youtu.be/CKxMht9nnhw'),
(2, 'http://youtu.be/qA3dIYp2K4U');

 

Here's the sql error from my php query:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS `Blog` ( `bid` int(10) unsigned NOT NULL auto_incr' at line 9

 

Anyone know where I'm going wrong please?

 

 

[Pikachu2000]

What error are you getting?

[xyph]

That's your second line. Your first line is

USE `h8861636`;

 

mysql_query() allows only one query per line.

 

You could try mysqli_multi_query() or splitting the query into single queries and looping.

[Pikachu2000]

How are you trying to perform this import? Through phpMyAdmin, or some other way?

[garry27]

That's your second line. Your first line is

USE `h8861636`;

 

mysql_query() allows only one query per line.

 

You could try mysqli_multi_query() or splitting the query into single queries and looping.

 

Do you have the code to do this as can't find it on the internet?

That's your second line. Your first line is

USE `h8861636`;

 

mysql_query() allows only one query per line.

 

You could try mysqli_multi_query() or splitting the query into single queries and looping.

 

Do you have the code to do this as can't find it on the internet?

[garry27]

How are you trying to perform this import? Through phpMyAdmin, or some other way?

 

No, I'm just trying to run it through a php query as I don't have access to phpAdmin on that server.

[xyph]

Well, you could use

http://php.net/manual/en/mysqli.multi-query.php

 

Manual is straightforward there. I'm not a huge fan of this function, as they haven't made it easy to grab errors your query might generate.

 

Otherwise you're going to have to use some sort of query parser to split the query up. You could use a RegEx like this

'/(?:
[^"\']|
(?:\'(??!\\\\\')[^\']|\\\\\')++\')|
(?:"(??!\\\\")[^"]|\\\\")++")
)+?;/x'

 

Which should split up the dump fine, then run and test each query.