AmenKa
07-18-2008, 07:16 AM
This is a PHP script, however the error is MySQL syntax so I will post here.
I am creating a rating system for someone and I want the script to check to make sure the MySQL table needed is there. If it isn't, the script creates the table.
It generates an error (
Error in query: CREATE TABLE `ratings` ( `id` int(11) NOT NULL auto_increment, `fivestar` int(11), `fourstar` int(11), `threestar` int(11), `twostar` int(11), `onestar` int(11), PRIMARY KEY (`id`) ) TYPE=MyISAM; INSERT INTO `ratings` VALUES (1, 0, 0, 0, 0, 0);. 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 `ratings` VALUES (1, 0, 0, 0, 0, 0)' at line 1
) that claims my sytax is off, however if i copy that exact code and execute it in the MySQL server it works just fine.
A working (or not-working) example is at http://www.shadowtechstudios.net/shadowtechstudios.net/projects/ratingsystem/0.1.0.php .
The page with all code is as follows:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Rating System</title>
<!-- Version 0.1.0 of this script. -->
</head>
<body>
<form action="0.1.0.php" method="post">
This dudle is worth
<select name="rating">
<option value="5">5</option>
<option value="4">4</option>
<option value="3">3</option>
<option value="2">2</option>
<option value="1">1</option>
</select>
stars.
<input type="submit" name="submit" value="Submit" />
</form>
<?php
/*
0.1.0 Will automatically create the tables needed by this script if they are not there. It will also grab the rating and spit it back out.
*/
// Get the user's rating.
$rating = $_POST['rating'];
// Database Information.
$host = "***********";
$user = "*******";
$pass = "****************";
$db = "*****";
// Table in case I need var tables in the future...
$table = "ratings";
// Open connection.
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// Select database.
mysql_select_db($db) or die ("Unable to select database!");
// Command to create tables if needed.
$createtable = "CREATE TABLE `".$table."` ( `id` int(11) NOT NULL auto_increment, `fivestar` int(11), `fourstar` int(11), `threestar` int(11), `twostar` int(11), `onestar` int(11), PRIMARY KEY (`id`) ) TYPE=MyISAM; INSERT INTO `".$table."` VALUES (1, 0, 0, 0, 0, 0);";
// Test for existing table.
$testtable = "SELECT * FROM ".$table;
// Execute test.
$tablecheck = @mysql_query($testtable);
// See if any rows were returned.
if (!$tablecheck) {
// There are no rows, create the table.
mysql_query($createtable) or die ("Error in query: $createtable. ".mysql_error());
}
else {
// If there are rows in the table, then we will assume the table is good.
}
// Free memory.
mysql_free_result($tablecheck);
// Close connection.
mysql_close($connection);
echo ( $rating );
?>
</body>
</html>
Thank you in advance for any helpful information!
I am creating a rating system for someone and I want the script to check to make sure the MySQL table needed is there. If it isn't, the script creates the table.
It generates an error (
Error in query: CREATE TABLE `ratings` ( `id` int(11) NOT NULL auto_increment, `fivestar` int(11), `fourstar` int(11), `threestar` int(11), `twostar` int(11), `onestar` int(11), PRIMARY KEY (`id`) ) TYPE=MyISAM; INSERT INTO `ratings` VALUES (1, 0, 0, 0, 0, 0);. 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 `ratings` VALUES (1, 0, 0, 0, 0, 0)' at line 1
) that claims my sytax is off, however if i copy that exact code and execute it in the MySQL server it works just fine.
A working (or not-working) example is at http://www.shadowtechstudios.net/shadowtechstudios.net/projects/ratingsystem/0.1.0.php .
The page with all code is as follows:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Rating System</title>
<!-- Version 0.1.0 of this script. -->
</head>
<body>
<form action="0.1.0.php" method="post">
This dudle is worth
<select name="rating">
<option value="5">5</option>
<option value="4">4</option>
<option value="3">3</option>
<option value="2">2</option>
<option value="1">1</option>
</select>
stars.
<input type="submit" name="submit" value="Submit" />
</form>
<?php
/*
0.1.0 Will automatically create the tables needed by this script if they are not there. It will also grab the rating and spit it back out.
*/
// Get the user's rating.
$rating = $_POST['rating'];
// Database Information.
$host = "***********";
$user = "*******";
$pass = "****************";
$db = "*****";
// Table in case I need var tables in the future...
$table = "ratings";
// Open connection.
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// Select database.
mysql_select_db($db) or die ("Unable to select database!");
// Command to create tables if needed.
$createtable = "CREATE TABLE `".$table."` ( `id` int(11) NOT NULL auto_increment, `fivestar` int(11), `fourstar` int(11), `threestar` int(11), `twostar` int(11), `onestar` int(11), PRIMARY KEY (`id`) ) TYPE=MyISAM; INSERT INTO `".$table."` VALUES (1, 0, 0, 0, 0, 0);";
// Test for existing table.
$testtable = "SELECT * FROM ".$table;
// Execute test.
$tablecheck = @mysql_query($testtable);
// See if any rows were returned.
if (!$tablecheck) {
// There are no rows, create the table.
mysql_query($createtable) or die ("Error in query: $createtable. ".mysql_error());
}
else {
// If there are rows in the table, then we will assume the table is good.
}
// Free memory.
mysql_free_result($tablecheck);
// Close connection.
mysql_close($connection);
echo ( $rating );
?>
</body>
</html>
Thank you in advance for any helpful information!