Jump to content

Archived

This topic is now archived and is closed to further replies.

Zoey

Creating MySQL tables with PHP

Recommended Posts

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)

Share this post


Link to post
Share on other sites
[code]
if ($result) {
echo "Table Created!";
}

else print "Error";
[/code]

change to

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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()

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.