[SOLVED] query help ... headache here ...

[imarockstar]

what am i doing wrong here ? do i need a separate query for each table creation and data insert ?

 

 

$sql2 = "

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

";

 

error:

 

 

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 '; INSERT INTO `headerimages` (`id`, `imagename`, `filename`, `active`) VALUES (' at line 7

 

 

[rhodesa]

Yes, you can only do one statement at a time.

$sql2 = "
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";
mysql_query($sql2) or die("Failed to create table: ".mysql_error());

$sql3 = "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)";
mysql_query($sql3) or die("Failed to insert: ".mysql_error());

[PFMaBiSmAd]

mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

 

And since you should be doing error checking, error reporting, logging, and error recovery in your code, you would want to make sure the first query executed successfully before attempting the second query.

[imarockstar]

ok kool . thats what I thought. ust hoping there was a short kut so I did not have like 20 different queries ...

 

thanks

[PFMaBiSmAd]

Taking short cuts in programming often takes twice as long to accomplish anything because programming is unforgiving and literal since computers only do what their programs tell them.

 

Those that take short cuts in programming often trip and fall down before they get where they were going.

[rhodesa]

If you wanted to have one big SQL file, and run each query in it, you could try splitting on the semi-colon followed by a new line:

 

<?php
  $sql = file_get_contents('queries.sql');
  if(!($queries = preg_split('/\s*;\s*\n\s*/',$sql)))
    die("No queries found");
  foreach($queries as $query){
    if(!strlen(trim($query))) continue;
    print "<pre>$query</pre>";
    if(mysql_query($query)){
      print "Status: Success<br>";
    }else{
      print "Status: Failed<br>Error: ".mysql_error();
      exit;
    }
    print "<hr>";
  }
?>