SQL query failing

[gaston9x19]

How is this query "invalid syntax"?


CREATE TABLE `test_test` (
`id` int(4) NOT NULL auto_increment,
`link` varchar(16) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

INSERT INTO `test_test` VALUES (2, 'Legal_Forms');
INSERT INTO `test_test` VALUES (1, 'E-Mail_Order');
INSERT INTO `test_test` VALUES (3, 'Templates');


It was created with phpMyAdmin's export feature, it can't have a syntax error. I'm running the query with this PEAR:DB-based script:


<?php

require("db_connect.php");

if(empty($_POST['query'])){


print "<form action=\"".$_SERVER['PHP_SELF']."\" method=\"post\">\n<div align=\"center\"><textarea name=\"query\" rows=\"15\" cols=\"60\">\n";

?>
CREATE TABLE `test_test` (
`id` int(4) NOT NULL auto_increment,
`link` varchar(16) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

INSERT INTO `test_test` VALUES (2, 'Legal_Forms');
INSERT INTO `test_test` VALUES (1, 'E-Mail_Order');
INSERT INTO `test_test` VALUES (3, 'Templates');
<?php
print "</textarea><br/>\n<input type=\"submit\" /></div>\n</form>\n";


}else{
//POST data is present


if(!get_magic_quotes_gpc()){
$_POST['query']=addslashes($_POST['query']);
}
$customQuery=$db_object->query($_POST['query']);
if(!DB::isError($customQuery)){
print "<h3 align=\"center\">Query successful!</h3>\n";

print "<table align=\"center\" width=\"60%\" border=\"0\"><tr><td>\n";
print "<pre>";
echo htmlspecialchars(stripslashes($_POST['query']));
print "</pre>\n";
print "</td></tr></table>\n";

}else{
print "<h3 align=\"center\">Could not execute the query!</h3>\n";
print "<table align=\"center\" width=\"60%\" border=\"0\"><tr><td>\n";
print "Database error or bad SQL query:<br/>\n";
print $customQuery->getMessage()."<br>\n";
print "<pre>";
echo htmlspecialchars(stripslashes($_POST['query']));
print "</pre>\n";
print "</td></tr></table>\n";
}//end if query success
print "<br/><div align=\"center\"><input type=\"button\" value=\"Go Back\" onClick=\"window.location.href='".$_SERVER['PHP_SELF']."';\" /></div>\n";

}//end if POST data
?>


And it keeps coming back with DB::isError($customQuery) being true, not writing to the db, and the only error message is: "DB Error: syntax error". You can get useful data back if you change the query to "SELECT * FROM test_users ORDER BY id", but not even all SELECT statements work. "SELECT username FROM `test_users` WHERE id='3'" fails, for instance. What's going on, what am I doing wrong?

Complicated, I know, but I've got to get something that will read the contents of a SQL dump into a SQL query to recreate the database, and WORK.

P.S. Just a thought, does PEAR require only one SQL statement per query? I got a working model with a couple of different queries here (http://gaston9x19.modcentral.us/test/testSQL.php). Some work, some don't.

[NogDog]

Don't know about Pear, but I know the PHP mysql_query function only allows a single query statement per call.

[gebezis]

$query="create table test_test (id int(4) NOT NULL auto_increment primery, link varchar(16) default NULL) type=MyISAM AUTO_INCREMENT=4;";

sql_query($query);

$query="insert into test_test values (2, 'Legal_Foerms')";
sql_query($query);
$query="insert into test_test values (1, 'E-Mail_Order')";
sql_query($query);
$query="insert into test_test values (3, 'Templates')";
sql_query($query);




This should work, and shuold be put in the php script, not outside.

[Stephen Philbin]

Aye. It's a right pain in the arse. Something like mysql_query_to_array() would be a godsend. Y'know, just run a bunch of queries in the parenthesis and for each query run, the result resource is added as an array element.

Actually, that might not be a bad idea for an object/class. :D

[AdamGundry]

Perhaps something like this? :cool:

function mysql_query_to_array($query, $linkID = false)
{
$query = explode(';', $query);
$results = array();

if ($linkID)
foreach ($query as $value)
$results[] = mysql_query($value, $linkID);
else
foreach ($query as $value)
$results[] = mysql_query($value);

return $results;
}Adam

[gaston9x19]

Aight, thanks, I'll try it and see what happens :)

[gaston9x19]

That's better, some queries work, some don't. Check it out at http://gaston9x19.modcentral.us/test/testSQL.php and here's the scource code, I really don't think "INSERT INTO `test_test` VALUES (2, 'legal_forms');" is improper syntax:


<?php


$host="localhost";
$user="username";
$pass="password";
$database="database";
$db_engine="mysql";


require_once 'DB.php'; //require the PEAR::DB classes.

$datasource = $db_engine.'://'.$user.':'.$pass.'@'.$host.'/'.$database;

$db_object = DB::connect($datasource, TRUE);
/* assign database object in $db_object, if the connection fails $db_object will contain
the error message. */

if(DB::isError($db_object)) {
die($db_object->getMessage()); // If $db_object contains an error print out the
} // error and exit.

$db_object->setFetchMode(DB_FETCHMODE_ASSOC);




if(empty($_POST['query'])){

print "<form action=\"".$_SERVER['PHP_SELF']."\" method=\"post\">\n<div align=\"center\"><textarea name=\"query\" rows=\"15\" cols=\"60\" readonly=\"true\">\n";

?>
DROP TABLE IF EXISTS `test_test`;

CREATE TABLE `test_test` (
`id` int(4) NOT NULL auto_increment,
`link` varchar(16) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

INSERT INTO `test_test` VALUES (2, 'Legal_Forms');
INSERT INTO `test_test` VALUES (1, 'E-Mail_Order');
INSERT INTO `test_test` VALUES (3, 'Templates');
<?php
print "</textarea><br/>";

print "<input type=\"button\" value=\"Switch to INSERT\" style=\"font-weight:bold;\" onClick=\"this.form.query.value='DROP TABLE IF EXISTS `test_test`;\\r\\n\\r\\nCREATE TABLE `test_test` (\\r\\n `id` int(4) NOT NULL auto_increment,\\r\\n `link` varchar(16) default NULL,\\r\\n PRIMARY KEY (`id`)\\r\\n) TYPE=MyISAM AUTO_INCREMENT=4 ;\\r\\n\\r\\nINSERT INTO `test_test` VALUES (2, \\'Legal_Forms\\');\\r\\nINSERT INTO `test_test` VALUES (1, \\'E-Mail_Order\\');\\r\\nINSERT INTO `test_test` VALUES (3, \\'Templates\\');';\" /> ";
print "<input type=\"button\" value=\"Switch to SELECT *\" onClick=\"this.form.query.value='SELECT * FROM `test_users` ORDER BY `id`';\" />\n";
print "<input type=\"button\" value=\"Switch to SELECT\" onClick=\"this.form.query.value='SELECT username FROM `test_users` WHERE id=\'3\'';\" />\n";

print "<br/> <br/>\n<input type=\"submit\" /></div>\n</form>\n";

}else{
//POST data is present


if(!get_magic_quotes_gpc()){
$_POST['query']=addslashes($_POST['query']);
}
$query=explode(";", $_POST['query']);
foreach($query as $this){
if(!empty($this)){ $this=$this.";"; }
$customQuery=$db_object->query($this);
if(!DB::isError($customQuery)){
print "<h3 align=\"center\">Query successful!</h3>\n";

print "<table align=\"center\" width=\"60%\" border=\"1\"><tr><td>\n";
print "<pre>";
echo htmlspecialchars(stripslashes($this));
print "</pre>\n";
print "</td></tr></table>\n";

}else{
print "<h3 align=\"center\"><span style=\"color:#F00;\">Could not execute the query!</span></h3>\n";
print "<table align=\"center\" width=\"60%\" border=\"1\" bordercolor=\"#FF0000\"><tr><td>\n";
print "Database error or bad SQL query:<br/>\n";
print $customQuery->getMessage()."<br>\n";
print "<pre>";
echo htmlspecialchars(stripslashes($this));
print "</pre>\n";
print "</td></tr></table>\n";
}//end if query success
}//end foreach

print "<br/><div align=\"center\"><input type=\"button\" value=\"Go Back\" onClick=\"window.location.href='".$_SERVER['PHP_SELF']."';\" /></div>\n";

}//end if POST data
?>

[gaston9x19]

Any idea why it's not executing the INSERT statements, even after parsing into an array with explode()?