Cannot Drop Table ...

[xProteuSx]

I am very new to php/MySQL so I am playing around with the basic functions and I cannot get the DROP TABLE statement to work.  My code is as follows:

[code]
<?php
$sql = "
DROP TABLE users
CREATE TABLE users (
users_id int(6) NOT NULL auto_increment,
users_handle varchar(20) default NULL,
users_password varchar (20) default NULL,
users_email varchar (40) default NULL,
users_datejoined timestamp NOT NULL,
users_visits int (6) default '0',
users_lastvisit timestamp NOT NULL,
users_questionsanswered int(6) default '0',
users_correctanswers int(6) default '0',
users_percentcorrect float default '0',
users_totalscore int (6) default '0',
users_pagesviewed int(8) default '0',
users_visitbonus int(6) default '0',
users_activity int(6) default '0',
PRIMARY KEY(users_id),
UNIQUE(users_handle)
) TYPE=MYISAM;"
?>
[/code]

What I am trying to do is to drop the previous 'users' table and create a new one in its place.  I tried everything I can think of, I believe, including "DROP TABLE users;"  I don't know what to do anymore.  Some help would be duly appreciated.  Thanks guys.

[btherl]

You will certainly need a ";" at the end of the drop table, eg "DROP TABLE users;"

It might be better to do two seperate queries, one to drop the table and one to create the table.  That will make error handling much simpler.

[xProteuSx]

When I add the ';' this is the error that I get:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE users ( users_id int(6) NOT NULL auto_incremen

You're absolutely right about two queries, however.  The problem is, I am unsure of how to do two separate queries.  I will try the following:

[code]
<?php
$sql = "DROP TABLE users";
$sql = "CREATE TABLE users (
users_id int(6) NOT NULL auto_increment,
users_handle varchar(20) default NULL,
users_password varchar (20) default NULL,
users_email varchar (40) default NULL,
users_datejoined timestamp NOT NULL,
users_visits int (6) default '0',
users_lastvisit timestamp NOT NULL,
users_questionsanswered int(6) default '0',
users_correctanswers int(6) default '0',
users_percentcorrect float default '0',
users_totalscore int (6) default '0',
users_pagesviewed int(8) default '0',
users_visitbonus int(6) default '0',
users_activity int(6) default '0',
PRIMARY KEY(users_id),
UNIQUE(users_handle)
) TYPE=MYISAM;"
?>
[/code]

Does that seem correct?  Thanks a bunch.

[xProteuSx]

Must not be correct, because it did not work ...  Any suggestions?

[btherl]

Is that your whole code right there?  That won't do anything :)

[code=php:0]$sql = "DROP TABLE users";
$result = mysql_query($sql) or die("Error on $sql: " . mysql_error());[/code]


You will also need to connect to the database first using mysql_connect(), using the information given by your hosting provider.

[xProteuSx]

Well, if you are worried that the code is not connecting to the MySQL server then rest assured that the connection is fine, but I have not included the connection code in this snippet.  I know that the connection is good because if I manually drop the table via phpMySQLadmin and run this code, it does generate a users table.  I just can't seem to get the DROP command to work.

[.josh]

he wasn't providing you with a connection script. he was providing you with the php function to actually run your query.