Page 1 of 2

[SOLVED] CREATE TABLE PROBLEM

Posted: Fri Aug 03, 2007 11:57 pm
by dream2rule
Hello Friends,

I am unable to add a table to the database currently set in the connection parameter.

When i echo it on screen, the table to be created(query) displays correctly but it does not add the table to the database.

And when i add the same table(which is being displayed) manually in phpmyadmin, it creates a table successfully without any errors.

Page: mysql_1.php

Code: Select all

class database
{
	//**Atributes for the class database
	var $db_HOST = "localhost"; //server name 
	var $db_USER = "root"; //username
 	var $db_PASS = ""; //password
 	var $banco   = "test2"; //database name
 	//var $CONST_ERRO = "Sorry could not connect to the database"; 
 	var $dbc;
	
	function database()
        {
	//a constructor to a class connect_db()
     	$this->connect_db();
	}
	
	// Methods/Queries to be executed for a particular database
	//function connect_db($server_name,$db_user_name,$db_pass,$db_name)
  	function connect_db()
	{
  		//Method to connect to a database
     	       $this->dbc = mysql_connect($this->db_HOST,$this->db_USER,$this->db_PASS);
    	       $m = mysql_select_db($this->banco,$this->dbc);
    	       if(!empty($m))
		{
			echo "<br>Connection to the Database $this->banco Successfull<br>";		
		}
		
		if(empty($m))
			echo "<br>Sorry could not connect to the Database";
		return($dbc);
	}  
        
        function create_db($db_name)
	{
		//creates a new database.
		
                $sql="CREATE DATABASE ".$db_name;
		echo $sql."<br>";
		$tmp=$this->query_db($sql);
		if(!empty($tmp))
		{
			echo "<br>Database <b>$db_name</b> Created Successfully.";
		}
		else 
		{
			echo("<br>Database cannot be created.");
		}
	}

function add_table($tblname,$num_fields,$fields)
	{
		//adding a table into the database
		
		$sql="CREATE TABLE ".$tblname."(<br>&nbsp;";
		if(!empty($num_fields))
		{
			$sql.=$fields."<br>)";
		}
		echo $sql."<br>";
		$tmp=$this->query_db($sql);
		if(isset($tmp))
		{
			echo "<br>Table <b>$tblname</b> Created Successfully.";
		}
		else 
		{
			echo("<br>Table cannot be created.");
		}
	}
}
Calling the function create_db() and add_table() in another php page: mysql_2.php

Code: Select all

include("mysql_1.php"); 

$c = new database(); //defining the object for class database 

$c->create_db(test2); //Creating a new database

//creating a table mytable within the newly created database
$c->add_table("mytable",6,"id smallint(5) NOT NULL auto_increment,LastName varchar(30) NOT NULL,FirstName varchar(30) NOT NULL,Address varchar(255) NOT NULL,Age int(3) NOT NULL,email varchar(255) NOT NULL,PRIMARY KEY(id)");
Can someone help me with this? Have been struggling with it since a week now but in vain... :( :( :(

Thanks and Regards,
Dream2rule

Posted: Sat Aug 04, 2007 12:52 am
by Benjamin
Compare the $sql being echoed with what you have placed into your function. And view the source this time.

Posted: Sat Aug 04, 2007 1:16 am
by dream2rule
astions wrote:Compare the $sql being echoed with what you have placed into your function. And view the source this time.
Viewed the source but actually the problem lies here

Code: Select all

function add_table($tblname,$num_fields,$fields)
        {
                //adding a table into the database
               
                $sql="CREATE TABLE ".$tblname."(<br>&nbsp;";
                if(!empty($num_fields))
                {
                        $sql.=$fields."<br>)";
                }
                echo $sql."<br>";
                [b]$tmp=mysql_query($sql); [/b]             
                if(!empty($tmp))
                {
                        echo "<br>Table <b>$tblname</b> Created Successfully.";
                }
                else
                {
                        echo("<br>Table cannot be created.");
                }
        }
The mysql_query($sql) does not seem to work. :( Unable to figure it out why this is happening because when i add the same table which is being displayed when i echo the $sql, its getting added. :( :( :(

Posted: Sat Aug 04, 2007 1:20 am
by Benjamin
Sigh...

Code: Select all

$sql="CREATE TABLE ".$tblname."(<br>&nbsp;";
See anything wrong with that?

Posted: Sat Aug 04, 2007 1:24 am
by dream2rule
astions wrote:Sigh..

Code: Select all

$sql="CREATE TABLE ".$tblname."(<br>&nbsp;";
See anything wrong with that?
By giving that, the table being displayed is:

CREATE TABLE mytable(
id smallint(5) NOT NULL auto_increment,LastName varchar(30) NOT NULL,FirstName varchar(30) NOT NULL,Address varchar(255) NOT NULL,Age int(3) NOT NULL,email varchar(255) NOT NULL,PRIMARY KEY(id)
)TYPE=MyISAM;

When i copy and paste the same above table in phpmyadmin, a table mytable is being created. :(

Posted: Sat Aug 04, 2007 1:29 am
by Benjamin
I give up.

Posted: Sat Aug 04, 2007 1:34 am
by dream2rule
I give up
SIGH.. :( :( :( :( :(


I would be obliged if anyone here can help me out..

Regards

Posted: Sat Aug 04, 2007 1:59 am
by Benjamin

Code: Select all

echo htmlentities("CREATE TABLE ".$tblname."(<br>&nbsp;");

Posted: Sat Aug 04, 2007 7:59 am
by superdezign
Hehe.

SQL != HTML.

Posted: Sat Aug 04, 2007 11:08 am
by iknownothing
superdezign wrote:Hehe.

SQL != HTML.
I'm not sure if he's talking about the above post (htmlentities) or not, but I'll continue with what he's on about.. Why are you attempting to display something that is not to be displayed??

Should look more like this:

Code: Select all

//adding a table into the database 

function add_table($tblname,$fields) { 
     
        $sql="CREATE TABLE $tblname ($fields)";
        if(mysql_query($sql)) {             
                echo "<br>Table <b>" . $tblname . "</b> Created Successfully."; 
        } 
        else { 
                echo("<br>Table cannot be created."); 
        } 
}

Posted: Sat Aug 04, 2007 11:29 am
by superdezign
iknownothing wrote:
superdezign wrote:Hehe.

SQL != HTML.
I'm not sure if he's talking about the above post (htmlentities) or not, but I'll continue with what he's on about.. Why are you attempting to display something that is not to be displayed??
The point of both posts is that HTML cannot be in an SQL statement.

Posted: Sat Aug 04, 2007 11:58 am
by bdlang
superdezign wrote: The point of both posts is that HTML cannot be in an SQL statement.
Technically, that's not true. :)

Posted: Sat Aug 04, 2007 12:01 pm
by bdlang
To dream2rule I say this, since all posts thus far have apparently not given you the hint: you're formatting the CREATE TABLE statement as if it were something to display to the end user. Thus, SQL != HTML. Sure, it looks good when the user views the page (and you can copy and paste the output into phpMyAdmin), but MySQL does not understand

Code: Select all

CREATE TABLE sometable (<br>&nbsp;
...

Posted: Sat Aug 04, 2007 12:04 pm
by bdlang
iknownothing wrote: Should look more like this (may or may not need '' around variables in SQL statement):
No, table and column names SHOULD NOT be surrounded by quotes, please don't confuse the OP any more than necessary.

Posted: Sat Aug 04, 2007 1:06 pm
by superdezign
bdlang wrote:
superdezign wrote: The point of both posts is that HTML cannot be in an SQL statement.
Technically, that's not true. :)
Damnit, you got me. :P