Page 1 of 1

Create MySql table via php?

Posted: Sun Sep 15, 2002 11:20 pm
by dlynch
Hey,

Glad I found this forum.

I just started with php/MySQL this week after a year or two of knowing I "should" be getting into it.

I'm doing well, but have a hitch I can't get around.

It seems that there are no mysql_whatever statements that will create a MySQL table. I have seen how to create a new database, how to append and delete and modify tables, but nothing on how to create a new table within a database.

I tried mysql_query(), but was unsuccessful. When I copied the exact query into PhpMyAdmin using copy and paste, it created the table just fine. I got no error messages or messages of any kind, but when I try to create a new table, it never shows up.

My SAMS Learning PHP in 24 Hours book shows nothing that allows me to do it, other than creating the table within MySQL.

I'd find it hard to believe that it just can't be done... any help would be appreciated!


By the way... here's the code snippet I'm using to try to create the table.... I connect ok, and when I just try to add data to the table (after I create it in PhpMyAdmin) it works fine.....:

$link = mysql_connect($server, $un, $pw);
if(! $link)
die("Couldn't connect to MySQL.");
print "<p><p>Successfully connected to MySQL server.<p><p>";

mysql_query( "CREATE TABLE students
(id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
lname VARCHAR (40),
fname VARCHAR (40) ,
address VARCHAR (40),
city VARCHAR (40),
province VARCHAR (5),
postalcode VARCHAR(6),
age TINYINT (2),
sex VARCHAR (1),
grade TINYINT (1))", $link );

I also tried it with a ; after the grade field, before the last ).... the book says to leave the ; off the query when using mysql_query.

Posted: Mon Sep 16, 2002 1:58 am
by twigletmac
Try using the mysql_error() function to check for any error messages being returned from the database. I'm fairly certain that the error is being caused by you not selecting a database to put the table in (based on your posted code) so try this:

Code: Select all

// Make the connection to the database
$link = mysql_connect($server, $un, $pw) or die(mysql_error()); 

// now you have to choose a database
@mysql_select_db($your_database) or die(mysql_error());

// put the SQL statement into a variable so you can print it out if necessary
$sql = "CREATE TABLE students 
(id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY (id), 
lname VARCHAR (40), 
fname VARCHAR (40) , 
address VARCHAR (40), 
city VARCHAR (40), 
province VARCHAR (5), 
postalcode VARCHAR(6), 
age TINYINT (2), 
sex VARCHAR (1), 
grade TINYINT (1))";

// Run the query
mysql_query($sql) or die(mysql_error().'&lt;p&gt;'.$sql.'&lt;/p&gt;');
mysql_select_db() is the function used to select the database that you wish to work with.

Mac

Posted: Mon Sep 16, 2002 7:40 am
by dlynch
Hmmm.... I thought I had done that at one point, but I'll have to make sure. I'll scream if it was something so simple, because I worked on it for a couple hours....

Appreciate the reply....

DL

Of course it worked...

Posted: Mon Sep 16, 2002 7:48 am
by dlynch
I can't believe I missed something so simple.... thanks again!

Posted: Mon Sep 16, 2002 7:51 am
by twigletmac
S'alright, it's always the simple mistakes that cause the most pain unfortunately :) . The best way to guard against this kinda thing is to use

Code: Select all

or die(mysql_error())
statements after all your mysql_connect/_select_db and _query statements, saves a lot of headaches.

Mac