Yes I am yet another newbie to the world of PHP and MySQL and I can't get MySQL to make the table. I've been given full permissions from the admins of the site i'm using to host what will hopefully be a massive player database, but so far it's been a crash and burn. I can connect to the database fine and the version of MySQL is 4.0.12. It's probably something simple, but right now it's hurting my brain. Can anyone help me out? Thanks in advance.

Here's the error I get. It's a bit cut off for some reason but anyway:
MySql Error on: 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 'INDEX CHAR(100), year INDEX INT(4), team CHAR(3), G INT(6), AVG

Here's my code

$database = "basebal1_mbml1";
$user = "user";
$pass = "pass";
$hostname = "localhost";

$connection = mysql_pconnect($hostname, $user, $pass);

if (mysql_select_db("basebal1_mbml1")==TRUE)
{
print "DB selected<br>";
}
else
{
print "DB not selected";

}

echo mysql_get_server_info();

mysql_query("CREATE TABLE hitting(playername INDEX CHAR(100), year INDEX INT(4), team CHAR(3), g INT(6), avg DECIMAL(4,3), ab INT(6), h INT(6), doub INT(6), trip INT(6), hr INT(6), bb INT(6), k INT(6), sb INT(6), cs INT(6), r INT(6), rbi INT(6), slg DECIMAL(4,3), obp DECIMAL(4,3));", $connection) or die ("<br>MySql Error on : "mysql_error());

mysql_close($connection);

    When putting an index on a BLOB or TEXT column you must always specify the length of the index, up to 255 bytes. You also need to specify your indexes like they were columns, and add the commas.

    CREATE TABLE hitting (
	playername CHAR(100), 
	INDEX(playername(100)), 
	year INT(4),
	INDEX(year), 
	team CHAR(3), 
	g INT(6), 
	avg DECIMAL(4,3), 
	ab INT(6), 
	h INT(6), 
	doub INT(6), 
	trip INT(6), 
	hr INT(6), 
	bb INT(6), 
	k INT(6),
	sb INT(6), 
	cs INT(6), 
	r INT(6), 
	rbi INT(6), 
	slg DECIMAL(4,3), 
	obp DECIMAL(4,3)
)

      Thanks a ton. As you can tell i'm still getting used to such simple syntax. Nice to see it was really only the placement and use of the INDEX that was screwing thigns up.

        Write a Reply...