Page 1 of 1

using php to create table in mysql

Posted: Sun Oct 27, 2002 9:45 am
by jamal
hi,
I am stuk. I want to create 3 tables using the below script.
But instead of having the 3 tables, I only had the last one(occurrence).
Can anyone help me why it was like that please??




<?
$usr = "";
$pwd = "";
$db = "onlampDB";
$host = "localhost";

# connect to database
$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }

# setup SQL statement
# create links table
$SQL = " CREATE TABLE page (";
$SQL = $SQL . " page_id int(10) unsigned NOT NULL auto_increment, ";
$SQL = $SQL . " page_url varchar(200) NOT NULL default '', ";
$SQL = $SQL . " PRIMARY KEY (page_id), ";
$SQL = $SQL . " TYPE=MyISAM);";

$SQL = "CREATE TABLE word (";
$SQL = $SQL . " word_id int(10) unsigned NOT NULL auto_increment, ";
$SQL = $SQL . " word_word varchar(50) NOT NULL default '', ";
$SQL = $SQL . " PRIMARY KEY (word_id), ";
$SQL = $SQL . " TYPE=MyISAM);";

$SQL = " CREATE TABLE occurrence (";
$SQL = $SQL . " occurrence_id int(10) unsigned NOT NULL auto_increment, ";
$SQL = $SQL . " word_id int(10) unsigned NOT NULL default '0', ";
$SQL = $SQL . " page_id int(10) unsigned NOT NULL default '0', ";
$SQL = $SQL . " PRIMARY KEY (occurrence_id));";



# execute SQL statement
$result = mysql_db_query($db,"$SQL",$cid);

# check for error
if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); }

# display result for table creation query
# this should be 1 on a success
echo ("Links Results RESULT = $result\n\n");

mysql_close($cid);

Posted: Sun Oct 27, 2002 11:26 am
by volka
one mysql_query per statement.
i.e.

Code: Select all

...
$SQL = ' CREATE TABLE page ('; 
$SQL .= ' page_id int(10) unsigned NOT NULL auto_increment, '; 
$SQL .= " page_url varchar(200) NOT NULL default '', "; 
$SQL .= ' PRIMARY KEY (page_id), '; 
$SQL .= ' TYPE=MyISAM)';
$result = mysql_db_query($db,"$SQL",$cid) or die(mysql_error());

$SQL = 'CREATE TABLE word ('; 
$SQL .= ' word_id int(10) unsigned NOT NULL auto_increment, '; 
$SQL .= " word_word varchar(50) NOT NULL default '', "; 
$SQL .= ' PRIMARY KEY (word_id), '; 
$SQL .= ' TYPE=MyISAM)'; 
$result = mysql_db_query($db,"$SQL",$cid) or die(mysql_error());

$SQL = " CREATE TABLE occurrence ("; 
$SQL .= ' occurrence_id int(10) unsigned NOT NULL auto_increment, '; 
$SQL .= " word_id int(10) unsigned NOT NULL default '0', "; 
$SQL .= " page_id int(10) unsigned NOT NULL default '0', "; 
$SQL .= ' PRIMARY KEY (occurrence_id) )'; 
$result = mysql_db_query($db,"$SQL",$cid) or die(mysql_error());
...