[SOLVED] CREATE TABLE PROBLEM

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

[SOLVED] CREATE TABLE PROBLEM

Post 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
Last edited by dream2rule on Mon Aug 06, 2007 1:45 am, edited 3 times in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Compare the $sql being echoed with what you have placed into your function. And view the source this time.
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post 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. :( :( :(
Last edited by dream2rule on Sat Aug 04, 2007 1:21 am, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Sigh...

Code: Select all

$sql="CREATE TABLE ".$tblname."(<br>&nbsp;";
See anything wrong with that?
Last edited by Benjamin on Sat Aug 04, 2007 1:58 am, edited 3 times in total.
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post 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. :(
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I give up.
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post by dream2rule »

I give up
SIGH.. :( :( :( :( :(


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

Regards
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

echo htmlentities("CREATE TABLE ".$tblname."(<br>&nbsp;");
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Hehe.

SQL != HTML.
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post 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."); 
        } 
}
Last edited by iknownothing on Sat Aug 04, 2007 1:12 pm, edited 2 times in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

superdezign wrote: The point of both posts is that HTML cannot be in an SQL statement.
Technically, that's not true. :)
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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;
...
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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
Post Reply