Jump to content


Photo

Creating MySQL tables with PHP


  • Please log in to reply
7 replies to this topic

#1 Zoey

Zoey
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 24 October 2006 - 09:06 PM

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.

$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";


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:


$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);


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)

#2 marcus

marcus
  • Members
  • PipPipPip
  • Advanced Member
  • 1,842 posts
  • LocationRochester, NY

Posted 24 October 2006 - 09:10 PM

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

else print "Error";

change to

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


#3 Zoey

Zoey
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 24 October 2006 - 09:15 PM

Nope, still getting Error.

#4 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 24 October 2006 - 09:37 PM

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...

<?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

?>


#5 Zoey

Zoey
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 24 October 2006 - 09:40 PM

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.

#6 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 24 October 2006 - 09:51 PM

Ok, try this...

<?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());

?>

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

#7 Zoey

Zoey
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 24 October 2006 - 10:00 PM

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

#8 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 24 October 2006 - 10:08 PM

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.

<?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());

?>

MySQL create table reference - http://dev.mysql.com...eate-table.html




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users