php create mysql table with constraints help please

[webdevdea]

Ok I am a beginner obviously. I have this big sql file, I need to create the tables with constraints on the database… I have no problem most times but this is a really big deal.. could someone let me know the best way to get these tables created .. 

Included will be the sql file, and the php file I am working on to create the tables and such on the database.. I do not have access to the myphpAdmin for this site so I have to get it done with php.. 

 

My PHP file I am working on.. 

//# buildPlayer.sql
# builds and populates all databases 
######################################

######################################
# conventions
######################################
# primary key = table name . ID
# primary key always first fields
# all primary keys autonumbered
# all field names camel-cased
# only link tables use underscore
# foreign keys indicated although mySQL does not always enforce
# every table used as foriegn reference has a name field

<?php
// Make a MySQL Connection


// Create a MySQL table in the selected database
$sql = "DROP TABLE IF EXISTS badPlayer";
mysql_query($sql);
mysql_query("CREATE TABLE badPlayer (
  agentID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name varchar(30),
  specialty varchar(40),
  assignment varchar(40),
  description varchar(40),
  location varchar(20),
  age int
)")
or die(mysql_error());  

echo "Table dRplayer Created!";

//insert data into 
mysql_select_db("cis1110") or die(mysql_error());

mysql_query("INSERT INTO badPlayer VALUES (
  null,'Rahab','Electronics, Counterintelligence',
  'Raging Dandelion','Plant Crabgrass','Sudan', 27
)")
or die(mysql_error());

mysql_query("INSERT INTO badPlayerVALUES(
  null, 'Gold Elbow','Sabatoge, Doily design',
  'Dancing Elephant','Infiltrate suspicious zoo','London', 47
)")
or die(mysql_error());
mysql_query("INSERT INTO badPlayer VALUES(
  null,'Falcon','Counterintelligence',
  'Dancing Elephant','Infiltrate suspicious circus','London', 33
)")
or die(mysql_error());

mysql_query("INSERT INTO badPlayer VALUES(
  null,'Cardinal','Sabatoge',
  'Enduring Angst','Make bad guys feel really guilty','Lower Volta', 29
)")
or die(mysql_error());

mysql_query("INSERT INTO badPlayer VALUES(
  null,'Blackford','Explosives, Flower arranging',
  'Enduring Angst','Make bad guys feel really guilty','Lower Votla', 52
)")
or die(mysql_error());


echo "Data Inserted  table!";

The .sql file

######################################
# buildPlayer.sql
# builds and populates all databases 
######################################

######################################
# conventions
######################################
# primary key = table name . ID
# primary key always first fields
# all primary keys autonumbered
# all field names camel-cased
# only link tables use underscore
# foreign keys indicated although mySQL does not always enforce
# every table used as foriegn reference has a name field
######################################

######################################
#housekeeping
######################################

use ph_6;
DROP TABLE IF EXISTS badPlayer;
DROP TABLE IF EXISTS athlete ;
DROP TABLE IF EXISTS contract;
DROP TABLE IF EXISTS specialty;
DROP TABLE IF EXISTS athlete_specialty;
DROP TABLE IF EXISTS playerFirst;

######################################
#create badplayer table
######################################

CREATE TABLE badPlayer (
  agentID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name varchar(30),
  specialty varchar(40),
  assignment varchar(40),
  description varchar(40),
  location varchar(20),
  age int
); 

INSERT INTO badPlayer VALUES (
  null,'Rahab','Electronics, Counterintelligence',
  'Raging Dandelion','Plant Crabgrass','Sudan', 27
);

INSERT INTO badPlayerVALUES(
  null, 'Gold Elbow','Sabatoge, Doily design',
  'Dancing Elephant','Infiltrate suspicious zoo','London', 47
);

INSERT INTO badPlayer VALUES(
  null,'Falcon','Counterintelligence',
  'Dancing Elephant','Infiltrate suspicious circus','London', 33
);

INSERT INTO badPlayer VALUES(
  null,'Cardinal','Sabatoge',
  'Enduring Angst','Make bad guys feel really guilty','Lower Volta', 29
);

INSERT INTO badPlayer VALUES(
  null,'Blackford','Explosives, Flower arranging',
  'Enduring Angst','Make bad guys feel really guilty','Lower Votla', 52
);

DESCRIBE badPlayer;
SELECT * FROM badPlayer;

######################################
# build athlete table
######################################

CREATE TABLE athlete (
  athleteID int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) default NULL,
  operationID int(11) default NULL,
  birthday date,
  PRIMARY KEY  (athleteID),
  FOREIGN KEY (operationID) REFERENCES operation (operationID)
);    

INSERT INTO athlete VALUES(
  null, 'Bond', 1, '1961-08-30'
);

INSERT INTO athlete VALUES(
  null, 'Falcon', 1, '1975-05-23'
);

INSERT INTO agent VALUES(
  null, 'Cardinal', 2, '1979-01-27'
);

INSERT INTO athlete VALUES(
  null, 'Blackford', 2, '1956-10-16'
);

INSERT INTO athlete VALUES(
  null, 'Rahab', 3, '1981-9-14'
);

######################################
# build athleteAge view
######################################
DROP VIEW IF EXISTS athleteAgeView;
CREATE VIEW athleteAgeView AS
SELECT 
  name,
  birthday,
  operationID,
  CONCAT(
    YEAR(FROM_DAYS(DATEDIFF(NOW(), birthday))), ' years, ',
    MONTH(FROM_DAYS(DATEDIFF(NOW(), birthday))), ' months') as age
FROM athlete;

######################################
# build contract table
######################################

CREATE TABLE contract (
  contractID int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) default NULL,
  description varchar(50) default NULL,
  location varchar(50) default NULL,
  PRIMARY KEY  (`OperationID`)
);

INSERT INTO contract VALUES(
  null, 'Dancing Elephant', 
  'Infiltrate suspicious zoo', 'London'
);

INSERT INTO contract VALUES(
  null, 'Enduring Angst', 
  'Make bad guys feel really guilty','Lower Volta'
);

INSERT INTO contract VALUES(
 null, 'Furious Dandelion', 
 'Plant crabgrass in enemy lawns','East Java'
);

DESCRIBE contract;
SELECT * FROM contract;

######################################
# build athlete contract view
######################################

DROP VIEW IF EXISTS athleteOpView;
CREATE VIEW athleteOpView AS
SELECT
  athlete.name AS 'athlete',
  CONCAT(
    YEAR(FROM_DAYS(DATEDIFF(NOW(), birthday))), ' years, ',
    MONTH(FROM_DAYS(DATEDIFF(NOW(), birthday))), ' months')    as age,
  contract.name AS 'contract',
  contract.description AS 'task',
  contract.location AS 'location'
FROM
  athlete, contract
WHERE
  athlete.contractID = contract.contractID;

######################################
# build specialty table
######################################

CREATE TABLE specialty (
  specialtyID int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) default NULL,
  PRIMARY KEY  (SpecialtyID)
);   

INSERT INTO specialty VALUES(
  null, 'Electronics'
);

INSERT INTO specialty VALUES(
  null, 'Counterintelligence'
);

INSERT INTO specialty VALUES(
  null, 'Sabatoge'
);

INSERT INTO specialty VALUES(
  null, 'Doily Design'
);

INSERT INTO specialty VALUES(
  null, 'Explosives'
);

INSERT INTO specialty VALUES(
  null, 'Flower Arranging'
);

DESCRIBE specialty;
SELECT * FROM specialty;

######################################
# build athlete_specialty table
######################################

CREATE TABLE athlete_specialty (
  athlete_specialtyID int(11) NOT NULL AUTO_INCREMENT,
  athleteID int(11) default NULL,
  specialtyID int(11) default NULL,
  PRIMARY KEY (athlete_specialtyID),
  FOREIGN KEY (athleteID) REFERENCES agent (athleteID),
  FOREIGN KEY (specialtyID) REFERENCES specialty (specialtyID)
);

INSERT INTO athlete_specialty VALUES(
  null,1,2
);

INSERT INTO athlete_specialty VALUES(
  null,1,3
);

INSERT INTO athlete_specialty VALUES(
  null,2,1
);

INSERT INTO athlete_specialty VALUES(
  null,2,6
);

INSERT INTO athlete_specialty VALUES(
  null,3,2
);

INSERT INTO athlete_specialty VALUES(
  null,4,4
);

INSERT INTO athlete_specialty VALUES(
  null,4,5
);

DESCRIBE athlete_specialty;

######################################
# build athleteSpecialty view
######################################
DROP VIEW IF EXISTS athleteSpecialtyView;

CREATE VIEW athleteSpecialtyView as
SELECT 
  athlete.name as 'athlete',
  specialty.name as 'specialty'
FROM 
  athlete, specialty, athlete_specialty
WHERE athlete.athleteID = athlete_specialty.athleteID
  AND specialty.specialtyID = athlete_specialty.specialtyID;


######################################
# build playerFirst table
######################################

CREATE TABLE playerFirst (
  playerFirstID INT NOT NULL AUTO_INCREMENT,
  codeName VARCHAR(30),
  specialtyA VARCHAR(30),
  specialtyB VARCHAR(30),
  specialtyC VARCHAR(30),
  assignment VARCHAR(40),
  description VARCHAR(40),
  location VARCHAR(20),
  PRIMARY KEY (playerFirstID)
);

DESCRIBE playerFirst;

######################################
# build storedQuery table
######################################
DROP TABLE if exists storedQuery;
CREATE TABLE storedQuery (
  storedQueryID int(11) NOT NULL AUTO_INCREMENT,
  description varchar(30),
  text TEXT,
  PRIMARY KEY (storedQueryID)
);

INSERT INTO storedQuery VALUES (
  null,
  'agent info',
  'SELECT * FROM athlete'
);

INSERT INTO storedQuery VALUES (
  null,
  'athlete names',
  'SELECT name FROM athlete'
);

INSERT INTO storedQuery VALUES (
  null,
  'athlete and contracts',
  'SELECT athlete.name AS \'athlete\', contract.name AS \'contract\' FROM athlete, contract WHERE athlete.contractID = contract.contractID'
);

INSERT INTO storedQuery VALUES (
  null,
  'contract info',
  'SELECT * FROM contract'
);

INSERT INTO storedQuery VALUES (
  null,
  'athlete on contracts 2',
  'SELECT athlete.name AS \'athlete\', contract.name AS \'contracts\' FROM athlete, contract WHERE athlete.contractID = contract.contractID AND athlete.contractID = 2'
);

INSERT INTO storedQuery VALUES (
  null,
  'athlete and specialties',
  'SELECT athlete.name AS \'athlete\', specialty.name AS \'specialty\' FROM athlete, specialty, athlete_specialty WHERE athlete.athleteID = athlete_specialty.athleteID AND specialty.specialtyID = athlete_specialty.specialtyID'
);

[Ch0cu3r]

I do not have access to the myphpAdmin for this site so I have to get it done with php..

Cant you just download phpmyadmin, and configure it with your MySQL hostname, username and password and you should be good to go then.

 

if you cant get phpmyadmin then try this

$hostname = 'localhost';
$username = 'YOUR_MYSQL_USERNAME';
$password = 'YOUR_MYSQL_PASSWORD';
$database = 'YOUR_MYSQL_DATABASE';
$sqlfile = 'path/to/the/sql-your-file';

$command="mysql -h $hostname -u $username -p $password $database < $sqlfile";
exec($command);

Edited October 24, 2013 by Ch0cu3r

[webdevdea]

 

Cant you just download phpmyadmin, and configure it with your MySQL hostname, username and password and you should be good to go then.

 

if you cant get phpmyadmin then try this

$hostname = 'localhost';
$username = 'YOUR_MYSQL_USERNAME';
$password = 'YOUR_MYSQL_PASSWORD';
$database = 'YOUR_MYSQL_DATABASE';
$sqlfile = 'path/to/the/sql-your-file';

$command="mysql -h $hostname -u $username -p $password $database < $sqlfile";
exec($command);

I have my php admin on my computer but just for localhost not for the database I need to be working on.. 

<?php
// Make a MySQL Connection
$dbhost = "c10.db.host.com";
 $dbuser = "cis0";
 $dbpassword = "099";
 $dbdatabase = "cis_44kds"; 
 
mysql_connect("localhost", "cys", "aagg") or die(mysql_error());
mysql_select_db("csss") or die(mysql_error());







$hostname =  'cis1110.db.2177912.hostedresource.com';
$username = 'user';
$password = 'pard';
$database = 'c0';
$sqlfile = 'the.sql';

$command="mysql -h $hostname -u $username -p $password $database < $sqlfile";
exec($command);
?>

Edited October 25, 2013 by Zane

[Ch0cu3r]

Please don't say that is your actual mysql hostname, username and password in the code you posted above. If it is I strongly recommend you to change the password to your mysql server NOW! Never post your sensitive data such as usernames/password etc on the internet

 

I have my php admin on my computer but just for localhost not for the database I need to be working on..

It can be configured to use your remote mysql database too, not just localhost. However surely the web site you're working on also has a tool available for importing MySQL dump files. Pretty sure every webhost that provides MySQL databases will also provide phpmyadmin access in some form. Who is your webhost?

Edited October 24, 2013 by Ch0cu3r

[webdevdea]

I think its host gator, its not mine.. its a combined effort project for a club at school.. 

[Ch0cu3r]

host gator as in hostgator.com They provide phpmyadmin through cPanel

[Eccentricson]

Is your script returning errors?  What's the problem here?  

 

Here's how I connect to my database in my scripts:

 

 

$link = mysql_connect('host', 'username', 'password');
mysql_select_db('database_name');

I have this in a functions file, and just require that file in every other file I create for the site.   It's nice, short and quick.

 

Your table syntax looks correct, but are you receiving an error?

[webdevdea]

Its connecting, i just do not know the correct syntax to use when adding the above sql tables/table constraints, and data to the database.. I have no access to the host or site or anything like that .. I have the host name admin name admin password and database name that I am supposed to build the tables on.. I have some simple code I use for simple tables but this is pretty complex..