Page 1 of 1

creating tables script *solved*

Posted: Sun Jun 10, 2007 11:59 am
by suthie
i am trying to create four tables. my script creates the first table "userdata" and the third "prayerlog" but for some reason not the second or fourth. here is my code:

Code: Select all

<?php

// set your infomation.
$dbhost='localhost';
$dbusername='david';
$dbuserpass='mypassword';
$dbname='test';

// connect to the mysql database server. 
$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass); 
echo "success in database connection.";

// select the specific database name we want to access.
if (!mysql_select_db($dbname)) die(mysql_error()); 
echo "success in database selection."; 

// add a table to the selected database
$result="CREATE TABLE userdata (
userid int(20) NOT NULL auto_increment,
username VARCHAR(25), 
password VARCHAR(25), 
email VARCHAR(30),
link1 VARCHAR(25),
link2 VARCHAR(25),
link3 VARCHAR(25),
link4 VARCHAR(25),
userimage VARCHAR(200),
PRIMARY KEY  (userid),
UNIQUE KEY id (userid)
)";

if (mysql_query($result)){
echo "success in table creation."; 
} else {
echo "no table created.";
}
 
// add a table to the selected database
$result = "CREATE TABLE dailyvibe (
  postid int(20) NOT NULL auto_increment,
  timestamp varchar(30) NOT NULL,
  userlink varchar(30) NOT NULL,
  entry longtext NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
)";

if (mysql_query($result)){
echo "success in table creation."; 
} else {
echo "no table created.";
}
 
// add a table to the selected database
$result = "CREATE TABLE prayerlog (
  postid int(20) NOT NULL auto_increment,
  timestamp varchar(30) NOT NULL,
  userlink varchar(30) NOT NULL,
  entry longtext NOT NULL,
  reply bool,
  replytext longtext NOT NULL,
  PRIMARY KEY  (postid),
  UNIQUE KEY id (postid)
)";

if (mysql_query($result)){
echo "success in table creation."; 
} else {
echo "no table created.";
}


// add a table to the selected database
$result = "CREATE TABLE publicvibe (
  postid int(20) NOT NULL auto_increment,
  timestamp varchar(30) NOT NULL,
  userlink varchar(30) NOT NULL,
  entry longtext NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
)";

if (mysql_query($result)){
echo "success in table creation."; 
} else {
echo "no table created.";
}

?>
why is this happening?

Posted: Sun Jun 10, 2007 1:28 pm
by superdezign
No idea.

Have you tried these queries in MySQL rather than through PHP to see where the error is?

And, FYI, I believe that once a key is a primary key, it is automatically unique. Otherwise, it couldn't be the primary key. :wink:

Posted: Sun Jun 10, 2007 1:32 pm
by .Stealth
first of all, this:

Code: Select all

// connect to the mysql database server.
$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);
echo "success in database connection.";
is going to echo the success weather the mysql_connect is successful or not.


and as people have said in your other topics, for feedback on the error use mysql_error()

this will tell you why it didnt work.

Code: Select all

// add a table to the selected database
$result = "CREATE TABLE dailyvibe (
  postid int(20) NOT NULL auto_increment,
  timestamp varchar(30) NOT NULL,
  userlink varchar(30) NOT NULL,
  entry longtext NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
)";

if (mysql_query($result)){
echo "success in table creation.";
} else {
echo "no table created." . mysql_error();
}

Posted: Sun Jun 10, 2007 4:13 pm
by suthie
the problem was i was trying to make column "id" primary when there was no column "id" in either of those tables. this is the fixed code:

Code: Select all

// add a table to the selected database
$result = "CREATE TABLE publicvibe (
  postid int(20) NOT NULL auto_increment,
  timestamp varchar(30) NOT NULL,
  userlink varchar(30) NOT NULL,
  entry longtext NOT NULL,
  PRIMARY KEY  (postid),
  UNIQUE KEY id (postid)
)";