MySQL

**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/sha...stem/0.1.0.php .

The page with all code is as follows:

Code:

<!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!