Error in SQL Syntax

Tazerenix · January 8, 2010

Ok well according to my tutorials this is the correct syntax for my sql. Here is my source:

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  lang="en-US" xml:lang="en-US" xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Eobots Install Wizard</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<meta name="Keywords" content="" />
<meta name="Description" content="" />
<link rel="stylesheet" href="style.css" type="text/css" />
</head>

<body onload="javascript:void(0)" onunload="javascript:void(0)">

<div id="wrapper">
<h1 class="border">Eobots Install Wizard</h1>
<p>
Feel free to use the same DB details as the forum uses. It wont affect anything<br />
* = Required Field<br />
<form action="install.php" method="post">
<table>
<tr>
<td><label for="dbhost">* Database Host:</label></td>
<td><input type="text" name="dbhost" /></td>
</tr>
<tr>
<td><label for="dbuser">* Database User:</label></td>
<td><input type="text" name="dbuser" /></td>
</tr>
<tr>
<td><label for="dbpass">* Database Password:</label></td>
<td><input type="password" name="dbpass" /></td>
</tr>
<tr>
<td><label for="dbname">* Database Name:</label></td>
<td><input type="text" name="dbname" /></td>
</tr>
<tr>
<td><br /></td>
</tr>
<tr>
<td><label for="admin_user">* Admin Username:</label></td>
<td><input type="text" name="admin_user" /></td>
</tr>
<tr>
<td><label for="admin_pass">* Admin Password:</label></td>
<td><input type="password" name="admin_pass" /></td>
</tr>
<tr>
<td><label for="admin_pass_c">* Confirm Admin Password:</label></td>
<td><input type="password" name="admin_pass_c" /></td>
</tr>
<tr>
<td><input type="submit" value="Submit" /></td>
</tr>
</table>
</form>
</p>
</div>

</body>
</html>

install.php:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  lang="en-US" xml:lang="en-US" xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Eobots Install Wizard</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<meta name="Keywords" content="" />
<meta name="Description" content="" />
<link rel="stylesheet" href="style.css" type="text/css" />
</head>

<body onload="javascript:void(0)" onunload="javascript:void(0)">

<div id="wrapper">
<h1 class="border">Eobots Install Wizard</h1>
<p>
<?php

if ((!isset($_POST['dbhost'])) || (!isset($_POST['dbuser'])) ||(!isset($_POST['dbpass'])) || (!isset($_POST['dbname'])) || (!isset($_POST['admin_user'])) || (!isset($_POST['admin_pass'])) || (!isset($_POST['admin_pass_c'])))
{
echo "Error: One of the required fields was left blank";
}
else
{
$dbhost = $_POST['dbhost'];
$dbuser = $_POST['dbuser'];
$dbpass = $_POST['dbpass'];
$dbname = $_POST['dbname'];
$admin_user = $_POST['admin_user'];
$admin_pass = $_POST['admin_pass'];
$admin_pass_c = $_POST['admin_pass_c'];

$admin_user = stripslashes($admin_user);
$admin_pass = stripslashes($admin_pass);
$admin_pass_c = stripslashes($admin_pass_c);
$admin_user = strip_tags($admin_user);
$admin_pass = strip_tags($admin_pass);
$admin_pass_c = strip_tags($admin_pass_c);
	if ((strlen($admin_user) < 5) || (strlen($admin_user) > 20))
	{
		echo "Error: The username must be between 5 and 20 characters";
	}
	else if ((strlen($admin_pass) < 5) || (strlen($admin_pass) > 20))
	{
		echo "Error: The password must be between 5 and 20 characters";
	}
	else if ($admin_pass !== $admin_pass_c)
	{
		echo "Error: Your Passwords do not match";
	}
	else
	{
		$tempcon = mysql_connect($dbhost, $dbuser, $dbpass);
		if (!$tempcon)
		{
			echo "Could not connect to the mysql server at $dbhost using username $dbname and password $dbpass<br />";
			echo mysql_error();
		}
		else
		{
			echo "Successful connection to mysql database";
			if (!mysql_select_db($dbname, $tempcon))
			{
				echo "Error: Database Name incorrect";
			}
			else
			{
				unset($tempcon);
				echo "Writing dbconfig.php...";
				$dbconfig = '
				<?php
				\$dbhost = \"' . $dbhost . '\";
				\$dbuser = \"' . $dbuser . '\";
				\$dbpass = \"' . $dbpass . '\";
				\$db = \"' . $dbname . '\";
				\$con = mysql_connect(\$dbhost, \$dbuser, \$dbpass);
				?>';
				$file = fopen("../dbconfig.php", "x");
				if (!fwrite($file, $dbconfig))
				{
					echo "Error Writing dbconfig.php";
				}
				else
				{
					echo "dbconfig.php has been written successfully";
				}
				fclose($file);
				$time = date("l dS \of F Y h:i:s A");
				$sql = file_get_contents('install.sql');
				if (!mysql_query($sql))
				{
					echo "Error installing database<br />";
					echo mysql_error();
				}
				else
				{
					echo "Successful database installation";
				}
			}
		}	
	}
}



?>
</p>
</div>

</body>
</html>

install.sql (this is the problem)

CREATE TABLE `seoseblog_users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL default '',
`password` varchar(20) NOT NULL default '',
`email` varchar(30) default '',
`joined_on` varchar(50) default '',
`status` int(11) default 0,
PRIMARY KEY(`user_id`)
)

CREATE TABLE `seoseblog_posts` (
`post_id` int(11) NOT NULL AUTO_INCREMENT,
`post_title` varchar(30) NOT NULL default '',
`post_author` int NOT NULL default 0,
`post_content` varchar(1024) default '',
`posted_on` varchar(50) default '',
PRIMARY KEY(`post_id`)
)

CREATE TABLE `seoseblog_comments` (
`comment_id` int(11) NOT NULL AUTO_INCREMENT,
`comment_author` varchar(30) NOT NULL default '',
`comment_email` varchar(30) default '',
`comment_website` varchar(50) default '',
`comment_content` varchar(256) default '',
PRIMARY KEY(`omment_id`)
)

INSERT INTO `seoseblog_users` (username, password, joined_on) VALUES ('$admin_user', '$admin_pass_c', '$time');

INSERT INTO `seoseblog_posts` (post_title, post_author, post_content, posted_on) VALUES ('Successful Installation', 'Tazerenix', 'Eobots Seose Blog has been installed successfully', '$time');

And here are the error's i am getting:

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 `seoseblog_posts` ( `post_id` int(11) NOT NULL AUTO_INCREMENT, ' at line 11

cags · January 8, 2010

So far as I can tell there's nothing wrong with the actual SQL per se. I believe the error is because you don't have semicolons after the CREATE TABLE queries.

Tazerenix · January 8, 2010

i've tried with and without semicolons and it still doesnt work =/

cags · January 8, 2010

What exactly did you try because...

CREATE TABLE `seoseblog_users` (
   `user_id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(20) NOT NULL default '',
   `password` varchar(20) NOT NULL default '',
   `email` varchar(30) default '',
   `joined_on` varchar(50) default '',
   `status` int(11) default 0,
   PRIMARY KEY(`user_id`)
);

CREATE TABLE `seoseblog_posts` (
   `post_id` int(11) NOT NULL AUTO_INCREMENT,
   `post_title` varchar(30) NOT NULL default '',
   `post_author` int NOT NULL default 0,
   `post_content` varchar(1024) default '',
   `posted_on` varchar(50) default '',
   PRIMARY KEY(`post_id`)
);

...seems to work fine for me.

Mchl · January 8, 2010

mysql_query can not run multiple queries. You need to split string loaded from your file into individual queries.

cags · January 8, 2010

mysql_query can not run multiple queries. You need to split string loaded from your file into individual queries.

D'oh, my bad I only really looked at the SQL, never occurred to me the OP was trying to run it all at once via mysql_query.

Tazerenix · January 9, 2010

is there any function that does?

Mchl · January 9, 2010

Not in ext/mysql. There is mysqli_multi_query in ext/mysqli, but using it is not that trivial.

Tazerenix · January 9, 2010

hmm thanks, it looks alright, i think ill just manually install the tables and such one by one though

ps: How do phpbb and joomla and such do it?

cags · January 9, 2010

I just downloaded phpBB to have a look, it creates an array of statements then loops through them one at a time using mysql_query.

Tazerenix · January 9, 2010

ahh ok. cool

Sign In

Error in SQL Syntax

Recommended Posts

Tazerenix

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

Tazerenix

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

Tazerenix

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

Tazerenix

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

Tazerenix

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information