Cannot create a table in DB

WaltP · #1 26-Apr-2006, 02:21

Using the same code idea from two other PHP scripts that work I can't get a table created in a third DB. Each DB was created in the same way. Using the code:

PHP Code:


## connect to the mySQL server    
    $dbserver = mysql_connect($hostname,$username,$password);
    
    ## choose the active DB
    mysql_select_db ($dbname, $dbserver);


    ## create the SQL statement to delete the table
    $strSQL = "DROP TABLE $tablename";
    if (!(mysql_query ($strSQL, $dbserver)))
    {
        print "Table [$tablename] was not dropped <br />";
    }
    else
    {
        print "Table [$tablename] dropped <br />";
    }
    


    ## create the SQL statement to add a table
    $strSQL = "CREATE TABLE $tablename (";
    $strSQL = $strSQL . "show VARCHAR(24),";
    $strSQL = $strSQL . "mon  VARCHAR( 2),";
    $strSQL = $strSQL . "day  VARCHAR( 2),";
    $strSQL = $strSQL . "year VARCHAR( 2),";
    $strSQL = $strSQL . "ttl  VARCHAR(30),";
    $strSQL = $strSQL . "cmt  VARCHAR(40),";
    $strSQL = $strSQL . "len  VARCHAR( 2),";
    $strSQL = $strSQL . "num  VARCHAR( 8),";
    $strSQL = $strSQL . "lbl  VARCHAR( 8),";
    $strSQL = $strSQL . "side VARCHAR( 1),";
    $strSQL = $strSQL . "id INT AUTO_INCREMENT PRIMARY KEY AUTO_INCREMENT )";

    print "SQL=$strSQL<br />";
    if (!(mysql_query ($strSQL, $dbserver)))
    {
        print "Table [$tablename] was not created <br />";
    }
    else
    {
        print "Table [$tablename] created <br />";
        ...

Execution displays:

Code:

Table [otr] was not dropped
SQL=CREATE TABLE otr (show VARCHAR(24),mon VARCHAR( 2),day VARCHAR( 2),
  year VARCHAR( 2),ttl VARCHAR(30),cmt VARCHAR(40),len VARCHAR( 2),num 
  VARCHAR( 8),lbl VARCHAR( 8),side VARCHAR( 1),id INT AUTO_INCREMENT 
  PRIMARY KEY AUTO_INCREMENT )
Table [otr] was not created

Anyone have any ideas why the table might not be created?

Is there any way to get the reason for a failure? All I can find so far is the function returns TRUE and FALSE.

LuciWiz · #2 26-Apr-2006, 02:27

You already have a table named [otr] in that Database. To recreate it, you must first delete it (DROP TABLE otr).

LuciWiz · #3 26-Apr-2006, 02:29

Ah, I see you have code for dropping the table... back to the drawing board

[edit]
I don't see you checking whether the connection was successful (the mysql_connect call). In case that failed, wouldn't the other calls fail too?
[/edit]

WaltP · #4 26-Apr-2006, 16:09

Quote:

Originally Posted by LuciWiz

Ah, I see you have code for dropping the table... back to the drawing board

[edit]
I don't see you checking whether the connection was successful (the mysql_connect call). In case that failed, wouldn't the other calls fail too?
[/edit]

Good point -- but no cigar.

Code:

Connected to localhost/waltp_otr
Selected waltp_otr/Resource id #2
Table [otr] was not dropped
SQL=CREATE TABLE otr (show VARCHAR(24),mon VARCHAR( 2),day VARCHAR( 2),year VARCHAR( 2),ttl VARCHAR(30),cmt VARCHAR(40),len VARCHAR( 2),num VARCHAR( 8),lbl VARCHAR( 8),side VARCHAR( 1))
Table [otr] was not created

PHP Code:


## start of PHP section

    ## define variables -- $ defines var name
    $username = "waltp_otr";
    $password = "*******";
    $hostname = "localhost";
    $dbname   = "waltp_otr";

    $tablename= "otr";
    $num = 0;

    ## connect to the mySQL server    
    if (!($dbserver = mysql_connect($hostname,$username,$password)))
    {
        print "Connection to $hostname/$username was not made<br />";
    }
    else
    {
        print "Connected to $hostname/$username <br />";

        ## choose the active DB
        if (!(mysql_select_db ($dbname, $dbserver)))
        {
            print "Error selecting $dbname/$dbserver <br />";
        }
        else
        {
            print "Selected $dbname/$dbserver <br />";
    

            ## create the SQL statement to delete the table
            $strSQL = "DROP TABLE $tablename";
            if (!(mysql_query ($strSQL, $dbserver)))
            {
                print "Table [$tablename] was not dropped <br />";
            }
            else
            {
                print "Table [$tablename] dropped <br />";
            }

            ## create the SQL statement to add a table

            $strSQL = "CREATE TABLE $tablename (";
            $strSQL = $strSQL . "show VARCHAR(24),";
            $strSQL = $strSQL . "mon  VARCHAR( 2),";
            $strSQL = $strSQL . "day  VARCHAR( 2),";
            $strSQL = $strSQL . "year VARCHAR( 2),";
            $strSQL = $strSQL . "ttl  VARCHAR(30),";
            $strSQL = $strSQL . "cmt  VARCHAR(40),";
            $strSQL = $strSQL . "len  VARCHAR( 2),";
            $strSQL = $strSQL . "num  VARCHAR( 8),";
            $strSQL = $strSQL . "lbl  VARCHAR( 8),";
            $strSQL = $strSQL . "side VARCHAR( 1))";

            print "SQL=$strSQL<br />";
            if (!(mysql_query ($strSQL, $dbserver)))
            {
                print "Table [$tablename] was not created <br />";
            }
            else
            {
                print "Table [$tablename] created <br />";

The reason the DROP TABLE failed is because the DB is newly created and the is no table.

Is there any way to get more detailed information from a failure?

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
making table of LCM using arrays	azncrazycooler	CPP / C++ Forum	5	01-Dec-2005 03:52
C league table	Networkedd	C Programming Language	3	13-Mar-2005 17:20
Hash Table & Graph	Kay Chan	CPP / C++ Forum	7	08-Oct-2004 07:44
Can't seem to create db	Tigress7	MySQL / PHP Forum	3	19-Aug-2003 09:19
[Tutorial] MySQL Basics	nniehoff	MySQL / PHP Forum	15	23-Mar-2003 19:42

Cannot create a table in DB

Re: Cannot create a table in DB

Re: Cannot create a table in DB

Re: Cannot create a table in DB