Creating MySQL tables with PHP

[Zoey]

Alright, I must have read through the syntax for creating a table in mysql a thousand and one times, but I cannot figure out what is wrong with my code.  I'm hoping another pair of eyes can help me.

[quote]
$database=mysql_connect("localhost","username","password");

$result=mysql_query("CREATE TABLE `poll` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `question` varchar(255) default NULL,
  `bgcolor` varchar(20) default NULL,
  `fontsize` varchar(10) default NULL,
  `font` varchar(50) default NULL,
  `fontcolor` varchar(20) default NULL,
  `orientation` varchar(100) default NULL,
  `tbgcolor` varchar(20) default NULL,
  `tfontsize` varchar(10) default NULL,
  `tfontcolor` varchar(20) default NULL,
  `tfont` varchar(50) default NULL,
  `votes` int(11) default '0')");
 
if ($result) {
echo "Table Created!";
}

else print "Error";
[/quote]

No matter what I do, I keep getting "Error".  And I know the table hasn't already been created because when I plug it into this code:

[quote]

$result = mysql_query("DESCRIBE poll");
 
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("%s %s", $row[0], $row[1]);
  echo"<br>";
  }
 
  mysql_free_result($result);
[/quote]

I get a blank page, even though when I test it on a table that I know already exsists, it gives me the feedback I'm looking for (the names of all the columns in the specified table).

Can someone please help?

(And unfortunatly, no, I don't have access to my server itself, so I do need to create the tables in PHP)

[marcus]

[code]
if ($result) {
echo "Table Created!";
}

else print "Error";
[/code]

change to

[code]
if ($result) {
echo "Table Created!";
}else{
print "Error";
};
[/code]

[Zoey]

Nope, still getting Error.

[gmwebs]

Have you checked that your SQL syntax is correct? Try executing the query in PHPMyAdmin and see if it creates the table. At this stage, don't know what is generating the error you speak of - PHP or MySQL.

Also, try adding the following...

[code]
<?php

$result=mysql_query("CREATE TABLE `poll` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `question` varchar(255) default NULL,
  `bgcolor` varchar(20) default NULL,
  `fontsize` varchar(10) default NULL,
  `font` varchar(50) default NULL,
  `fontcolor` varchar(20) default NULL,
  `orientation` varchar(100) default NULL,
  `tbgcolor` varchar(20) default NULL,
  `tfontsize` varchar(10) default NULL,
  `tfontcolor` varchar(20) default NULL,
  `tfont` varchar(50) default NULL,
  `votes` int(11) default '0')") OR DIE (mysql_error()); //This will print out the MySQL error

?>
[/code]

[Zoey]

No database selected

Why would it be giving me that?  I'm only connecting to the one database, and the username and password and all that are correct.. copied and pasted from another page that works.

[gmwebs]

Ok, try this...

[code]
<?php

$connection = mysql_connect("servername.somedomain.com",
                            "dbUsername",
                            "dbPassword");

mysql_select_db("dbName", $connection);

$sql = "CREATE TABLE `poll` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `question` varchar(255) default NULL,
  `bgcolor` varchar(20) default NULL,
  `fontsize` varchar(10) default NULL,
  `font` varchar(50) default NULL,
  `fontcolor` varchar(20) default NULL,
  `orientation` varchar(100) default NULL,
  `tbgcolor` varchar(20) default NULL,
  `tfontsize` varchar(10) default NULL,
  `tfontcolor` varchar(20) default NULL,
  `tfont` varchar(50) default NULL,
  `votes` int(11) default '0')";

$result = mysql_query($sql, $connection) OR DIE (mysql_error());

?>
[/code]

I always find it better to split my query string from the actual mysql_query()

[Zoey]

Hm, okay that got it to connect, but now I'm getting:

Incorrect table definition; there can be only one auto column and it must be defined as a key

[gmwebs]

Yeah that is because you are missing a rather important SQL statement! When a field is set to auto_increment, like an id column, then it has to be defined as a primary key.

[code]
<?php

$connection = mysql_connect("servername.somedomain.com",
                            "dbUsername",
                            "dbPassword");

mysql_select_db("dbName", $connection);

$sql = "CREATE TABLE `poll` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `question` varchar(255) default NULL,
  `bgcolor` varchar(20) default NULL,
  `fontsize` varchar(10) default NULL,
  `font` varchar(50) default NULL,
  `fontcolor` varchar(20) default NULL,
  `orientation` varchar(100) default NULL,
  `tbgcolor` varchar(20) default NULL,
  `tfontsize` varchar(10) default NULL,
  `tfontcolor` varchar(20) default NULL,
  `tfont` varchar(50) default NULL,
  `votes` int(11) default '0') PRIMARY KEY  (`id`)";

$result = mysql_query($sql, $connection) OR DIE (mysql_error());

?>
[/code]

MySQL create table reference - [url=http://dev.mysql.com/doc/refman/5.0/en/create-table.html]http://dev.mysql.com/doc/refman/5.0/en/create-table.html[/url]