create MySQL table in php....

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

create MySQL table in php....

Post by lizlazloz »

hi, php noobie here.... I'm trying to create a MySQL table in php, with the user being able to select the table settings etc. I've searched but couldnt find what i was looking for. anyway, here's the code I've got:

Code: Select all

<html> 
    <head> 
        <title>Creating a table</title> 
    </head> 
    <body> 
    <?php  

        if( !$_POST['fields'] and !$_POST['db'] )  
        {  
            $form ="<form action="create_table.php" method="post">";  
            $form.="How many fields are needed in the new table?";  
            $form.="<br><input type="text" name="fields">";  
            $form.="<input type="submit" value="Submit">";  
            echo($form);  
        }  

        elseif( !$_POST['db'] )  
        {  
            $form ="<form action="create_table.php" method="post">";  
            $form.="Database: <input type="text" name="db"><br>";  
            $form.="Table Name: <input type"text" name="table"><br>";  

            for ($i = 0 ; $i <$_POST['fields']; $i++)  
            { 
                $form.= "<br/>Column Name: <input type="text" name="name[$i]">";  
                $form.="Type: <select name="type[$i]">";  
                $form.="<option value="char">char</option>";  
                $form.="<option value="int">int</option>";  
                $form.="</select> ";  
                $form.="Size:<input type="text" name="size[$i]">";  
            }  
            $form.="<input type="submit" value="Submit">";  
            $form.="</form>"; echo($form);  
        }  

        else  
        {  
            $conn = @mysql_connect("localhost", "shaun", "password")  
            or die("Err:Conn");  
            $rs = @mysql_select_db($_POST['db'], $conn) or die("Err:Db");  
            $sql = "create table $_POST[table] (";  
            for ($i = 0; $i < count($_POST[name]); $i++)  
            {  
                $sql .= "$name[$i] $type[$i]";  
                if($_POST['type'][$i] =="char")
                {  
                    if($_POST['size'][$i] != "")  
                    {  
                        $sql.="($_POST[size][$i])";  
                    }  
                }  
                if(($i+1) != count($_POST['name']) ) 
                {  
                    $sql.=",";  
                }  
            }  
            $sql .= ")";  
            echo("SQL COMMAND: $sql <hr>");  
            $result = mysql_query($sql,$conn) or die("Err:Query");  

            if ($result)  
            {  
                echo("RESULT: table "$_POST[table]" has been created");  
            }  
        }  
    ?>  
    </body> 
</html>
it gives my the MySQL command as something like: "create table my_table ( , (Array[1]), (Array[2]))", which isnt write, and it obviously can't execute that query.

so, what is wrong with my code?

please help, thanks.[/mysql_man]
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

it gives my the MySQL command as something like: "create table my_table ( , (Array[1]), (Array[2]))", which isnt write, and it obviously can't execute that query.
Well, actually it is right but it isn't what you want! 8)
You've made a logical error: you either should split the code in two separate scripts OR use the method $_SERVER['PHP_SELF'] instead of POST. I'd use the first one, being a noobie.... :wink:

The logic is as follows:
1. you show a HTML-form with one/more fields
2. user enters values in these fields and hits 'SUBMIT'-button
3. the values are returned to the server and you can get them like:

Code: Select all

$tablename = $_POST&#1111;'tablename'];
4. now you can use them to create / edit a MySQL-db.

In the way you wrote the script, the variables aren't known yet: the variables haven't been posted yet!
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

huh? i dont really understand you... i thought i had done what you are suggesting....

when the user clicks the submit button, the page is reloaded and the variables are'posted', right? so i can get them back?
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

OK, I've been a bit unclear once again. Sorry, let me try to explain it.

I suppose the name of the script you've shown us here is "create_table.php", the same script which is called in the form-definition

Code: Select all

$form ="<form action="create_table.php" method="post">";
If this assumption is true, you should call it like this:

Code: Select all

$form ="<form action=$_SERVER['PHP_SELF'] method="post">";
In that way the server reloads the page, with the variables known. Try it out, it should work..... I sure hope I haven't made a typo here :roll:
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

ok I'll give it a go, thanks.
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

hmm... now the page doesnt load at all, it's just blank....
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

Woops! Just noticed you forgot to close the form in the first if-statement:

Code: Select all

if( !$_POST['fields'] and !$_POST['db'] ) 
        { 
            $form ="<form action="create_table.php" method="post">"; 
            $form.="How many fields are needed in the new table?"; 
            $form.="<br><input type="text" name="fields">"; 
            $form.="<input type="submit" value="Submit">"; 
            echo($form); 
        } 


?>
You need to add

Code: Select all

$form.="</form>";
in there, I think. Does that work now?
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

ooh, didnt notice that...

..but no, it made no difference.

(php is hard:( )
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

(php is hard:( )
Well, I think it's like all programming: writing the script is easy, but finding that missing comma is a drag :D

I'll try your script on my PC....hold on!
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

if i dont put the 's around PHP_SELF i get it to work, but it still doesnt make $sql as it is supposed to, i still have the same problem i started off with...
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

I was just about to post such a remark... :(
The error seems to be in the sql-code....
We'll get it anyhow! :twisted:
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

well i'm almsot out of ideas.... ah well i just gotta keep trying stuff...
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

Found it! There are a few errors here.

One
You're trying to get the data in an array, i guess:

Code: Select all

for ($i = 0 ; $i <$_POST['fields']; $i++) 
{ //I've cut some code here
$form.="Size:<input type="text" name="size[$i]">";
}
but what it does create is an output like:

Code: Select all

&lt;input type="text" name="size&#1111;1]"&gt;
This does not result in the data put into an array called "size" at position "1", but the data is stored with the variable-name "size[1]". In theory you should be able to refer to it with "$_POST['size[1]']" but I'm not sure what effect these double '[' will have. Better leave them out and use something like:

Code: Select all

for ($i = 0 ; $i &lt;$_POST&#1111;'fields']; $i++) 
{ //I've cut some code here
$form.="Size:&lt;input type="text" name="size$i"&gt;";
}
The same goes for name[$i] and text[$i] ofcourse.

Two
There are three 'stages' in this form: the first one is

Code: Select all

<?php
 if( !$_POST['fields'] and !$_POST['db'] )  
{
     // first stage here
}
?>
the second one is

Code: Select all

<?php
elseif( !$_POST['db'] )  
{
     //second stage here
}
?>
and finally the third one is

Code: Select all

<?php
else
      { 
       $conn = @mysql_connect("localhost", "shaun", "password")
       //I've left code out here  
       // third stage here
      }
?>
When you want to use variables in the third stage, which were entered in the first stage, you need to write them in the second stage as "hidden form-fields". Euh, do I understand that last sentence?? 8O No.
Well, more about that later. Back to the testing....
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

ok, i'll see what i can do...
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

8) 8) 8) 8O 8) :D
I got it to work correct.
I admit having the neat coding messed up a bit, but who cares...
It looks like this:

Code: Select all

<html>
    <head>
        <title>Creating a table</title>
    </head>
    <body>
    <?php 

        if( !$_POST['fields'] and !$_POST['db'] ) 
        { 
            $form ="<form action=$_SERVER[PHP_SELF] method="post">";
            $form.="How many fields are needed in the new table?"; 
            $form.="<br><input type="text" name="fields">"; 
            $form.="<input type="submit" value="Submit">"; 
            $form.="</form>";
            echo($form); 
        } 

        elseif( !$_POST['db'] ) 
        { 
            $form ="<form action=$_SERVER[PHP_SELF] method="post">";
            $form.="Database: <input type="text" name="db"><br>"; 
            $form.="Table Name: <input type"text" name="table"><br>"; 

            for ($i = 0 ; $i <$_POST['fields']; $i++) 
            {
                $form.= "<br/>Column Name: <input type="text" name="name$i">"; 
                $form.="Type: <select name="type$i">"; 
                $form.="<option value="char">char</option>"; 
                $form.="<option value="int">int</option>"; 
                $form.="</select> "; 
                $form.="Size:<input type="text" name="size$i">"; 
            } 
				$form.="<input type="hidden" name="fields" value="$_POST[fields]">";
            $form.="<input type="submit" value="Submit">"; 
            $form.="</form>"; echo($form); 
        } 

        else 
        { 
            $conn = @mysql_connect("localhost", "name", "password") 
            or die("Err:Conn"); 
            $rs = @mysql_select_db($_POST['db'], $conn) or die("Err:Db"); 
           $sql = "create table $_POST[table] ("; 
            for ($i = 0; $i < $_POST['fields']; $i++) 
            { 
					$name="name".$i;$type="type".$i;$size="size".$i;
                $sql .= "$_POST[$name] $_POST[$type]"; 
                if($_POST[$type] =="char")
                { 
                    if($_POST[$size] != "") 
                    { 
                        $sql.="($_POST[$size])"; 
                    } 
                } 
                if(($i+1) != count($_POST['name']) )
                { 
                    $sql.=","; 
                } 
            } 
            $sql .= ")";

            echo("SQL COMMAND: $sql <hr>"); 
            $result = mysql_query($sql,$conn) or die("Err:Query"); 

            if ($result) 
            { 
                echo("RESULT: table "$_POST[table]" has been created"); 
            } 
        } 
    ?> 
    </body>
</html>
and it returned this message:

Code: Select all

SQL COMMAND: create table test (vgzuv char(5),vg char(5),) RESULT: table "test" has been created
I suppose this means I'm stuck with a MySQL-table of no use, regarding those crappy names I thought of... :lol:
Post Reply