Hey,
I just installed MySQL, PHP, and Apache on my computer.. and when I go into phpMyAdmin and type this in:

CREATE TABLE users (
username varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default '',
PRIMARY KEY (ID)
) TYPE=MyISAM;

I get this error:


Error

SQL-query :

CREATE TABLE users ( username varchar(20) NOT NULL default '', password varchar(20) NOT NULL default '', PRIMARY KEY (ID) ) TYPE=MyISAM

MySQL said:


Key column 'ID' doesn't exist in table


How do I fix this?

You didnt ask it to create a column that was id, you specified you wanted to make the uncreated id column an primary key though.

Code:CREATE TABLE users (
id int(20) NOT NULL'',
username varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default '',
PRIMARY KEY (ID)
) TYPE=MyISAM;



Matt

thank you! I've been trying to fix that for like an hour. I'm a newbie at mysql

wait, I still get an error

Here we go, this should work:mysql_query("DROP TABLE IF EXISTS users");
$query = "CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(30) NULL,
password varchar(20) NULL) TYPE=MyISAM;";
$result = mysql_query($query);


Matt

I still get this:


Error

SQL-query :

mysql_query("DROP TABLE IF EXISTS users")

MySQL said:


You have an error in your SQL syntax near 'mysql_query("DROP TABLE IF EXISTS users")' at line 1

Make this a php page and run it:

install.phpmysql_query("DROP TABLE IF EXISTS users");
$query = "CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(30) NULL,
password varchar(20) NULL) TYPE=MyISAM;";
$result = mysql_query($query);

Originally posted by Studio-10
I still get this:


Error

SQL-query :

mysql_query("DROP TABLE IF EXISTS users")

MySQL said:


You have an error in your SQL syntax near 'mysql_query("DROP TABLE IF EXISTS users")' at line 1


Assuming that you issued this command from PHPMyAdmin, I think the only problem here is that you forgot the ; at the end of the line.

To make the query from a PHP script as ballingtonma suggested (and as you'll eventually want to learn to do anyway), you'll first have to make the script open a connection to MySQL AND select the database where you want to create the table:

<?

$host = "localhost";
$username = "your_username";
$userpassword = "your_password";
$database_name = "name_of_database";

$connection_to_MySQL = mysql_connect($host, $username, $userpassword);

mysql_select_db($database_name));

mysql_query("DROP TABLE IF EXISTS users");

$sql = "CREATE TABLE users (
id int(20) DEFAULT '0' NOT NULL auto_increment,
username varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default ''
PRIMARY KEY (ID)
);";

$result = mysql_query($sql);

mysql_close($connection_to_MySQL);

?>

All of the functions here are included in PHP. The variable names are conventions or made up stuff.
And to help troubleshoot errors, try this version:

<?

$host = "localhost";
$username = "your_username";
$userpassword = "your_password";
$database_name = "name_of_database";

$connection_to_MySQL = mysql_connect($host, $username, $userpassword);
if (!$connection_to_MySQL) die("couldn't connect to MySQL");

if (!mysql_select_db($database_name)) die("couldn't select database $database_name");

mysql_query("DROP TABLE IF EXISTS users");

$sql = "CREATE TABLE users (
id int(20) DEFAULT '0' NOT NULL auto_increment,
username varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default ''
PRIMARY KEY (ID)
);";

$result = mysql_query($sql);
if (!$result) die("couldn't execute query: \"$sql\"");

mysql_close($connection_to_MySQL);

?>