create mysql tables in array

[dujmovicv]

Greetings!

I'm not sure if it's allowed to run 2 mysql queries to create 2 tables with php, I've tried to do that with this array :

<?
$table_fields = array (
                table_name => array(1 => "table_users", 2 => "table_links"),
                field_1 => array(1 => "userID", 2 => "linkID"),
                field_2 => array(1 => "username", 2 => "url"),
                field_3 => array(1 => "real_name", 2 => "clicks"),
                field_4 => array(1 => "password", 2 => "title"),
                field_5 => array(1 => "level", 2 => "date"),
                vartype_1 => array(1 => "int", 2 => "int"),
                vartype_2 => array(1 => "varchar(15)", 2 => "varchar(350)"),
                vartype_3 => array(1 => "varchar(35)", 2 => "int(11)"),
                vartype_4 => array(1 => "varchar(35)", 2 => "varchar(50)"),
                vartype_5 => array(1 => "int(2)", 2 => "CURRENT_DATE")
                );
                
                for ($i = 1; $i <= 2; $i++)
                    {
                    $sql = "CREATE TABLE".$table_fields[table_name][$i]."
                            (".
                            $table_fields[field_1][$i]." ".$table_fields[vartype_1][$i]." NOT NULL AUTO_INCREMENT,
                            PRIMARY KEY(".$table_fields[field_1][$i].",".
                            $table_fields[field_2][$i]." ".$table_fields[vartype_2][$i].",".
                            $table_fields[field_3][$i]." ".$table_fields[vartype_3][$i].",".
                            $table_fields[field_4][$i]." ".$table_fields[vartype_4][$i].",".
                            $table_fields[field_5][$i]." ".$table_fields[vartype_5][$i]."
                            )";
                    
                    if (mysql_query($sql,$con))
                        {
                        table_ok('Table '.$table_fields[table_name][$i].' created successfully!');
                        }
                    else
                        {
                        error('Error creating table '.$table_fields[table_name][$i].'!');
                        }
                    
                    }
?>

the script is however calling the function error, and reports :

Error creating table table_users!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 'varchar(15), real_name varchar(35), password varchar(35), level int(2) )' at line 3

I've checked the syntax 100 times and it seems OK to me.... Is it something else I missed?

[Dan Grossman]

It's because you're missing the closing parentheses for the PRIMARY KEY( part

You can call mysql_query as many times as you want in a script

But I have no idea why you would want to create this convoluted thing instead of just writing the two CREATE TABLE queries

35 lines of code for the equivalent of

mysql_query("CREATE TABLE table_users (user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, username varchar(15), real_name varchar(35), password varchar(35), level int(2))");

mysql_query("CREATE TABLE table_links (linkID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, url varchar(350), clicks int(11), title varchar(50), date DATE)"); 

[dujmovicv]

It's because you're missing the closing parentheses for the PRIMARY KEY( part

You can call mysql_query as many times as you want in a script

But I have no idea why you would want to create this convoluted thing instead of just writing the two CREATE TABLE queries

35 lines of code for the equivalent of

mysql_query("CREATE TABLE table_users (user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, username varchar(15), real_name varchar(35), password varchar(35), level int(2))");

mysql_query("CREATE TABLE table_links (linkID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, url varchar(350), clicks int(11), title varchar(50), date DATE)"); 

I should have checked 101st time!
Actually I will have a huge number of tables with the same structure that's why I decided to call the query in a loop!
Thanks a lot!

[Dan Grossman]

Fair enough. No problem, a fresh set of eyes usually do the trick for catching typos