creating tables script *solved*

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
suthie
Forum Commoner
Posts: 68
Joined: Sat Jun 09, 2007 10:46 am

creating tables script *solved*

Post 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?
Last edited by suthie on Sun Jun 10, 2007 4:13 pm, edited 1 time in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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:
.Stealth
Forum Commoner
Posts: 57
Joined: Wed Jan 10, 2007 12:15 pm
Location: Manchester, England

Post 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();
}
suthie
Forum Commoner
Posts: 68
Joined: Sat Jun 09, 2007 10:46 am

Post 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)
)";
Post Reply