Jump to content

Creating MySQL tables with PHP


Zoey

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.