Create a new table in the database

Question

I have a form in a php file. When sending the form, I call a test.php file that checks the validity of the data received and inserts them into a table of my database. I would also like to create a new table in the database, with the name $category_ $username. The file is the following:

<?php

if(isset($_POST['mySubmit'])) {
    $db = mysqli_connect('localhost','root','','DBsito');
    if (!$db)
    {
        die('Could not connect to database: ' . mysqli_error());
    }

    $db_select = mysqli_select_db($db, 'DBsito');

    //Salva il nome del file
    $imagename = $_FILES['icona']['name'];
    //tipo del file
    $imagetype = $_FILES['icona']['type'];
    $imagetemp = $_FILES['icona']['tmp_name'];
    
    //Path dell'upload
    $imagePath = "img/upload/";
    
    if(is_uploaded_file($imagetemp)) {
        if(move_uploaded_file($imagetemp, $imagePath . $imagename)) {
            echo "Sussecfully uploaded your image.";
        }
        else {
            echo "Failed to move your image.";
        }
        
    }
    else {
        echo "Failed to upload your image.";
    }

    $categoria = mysqli_real_escape_string($db, $_POST['categoria']);
    $username = mysqli_real_escape_string($db, $_POST['utente']);

    $result = mysqli_query($db, "SELECT categoria.nome_categoria, categoria.user_utente FROM categoria WHERE BINARY categoria.nome_categoria = BINARY '$categoria' AND BINARY categoria.user_utente = BINARY '$username' ");

    if(!empty($categoria) && mysqli_num_rows($result)) {
        $name_error = "Categoria già esistente!";

    }
    else if (!empty($categoria)){
        $query = "INSERT INTO categoria (nome_categoria, user_utente, icona) values ('$categoria','$username', '$imagename')";
        $db->query("CREATE TABLE '$categoria'_'$username'");

        // sql to create table
        $sql = "CREATE TABLE $categoria'_'$username (
        )";
        
        if ($db->query($sql) === TRUE) {
        echo "Table MyGuests created successfully";
        } else {
        echo "Error creating table: " . $db->error;
        }

        if(!mysqli_query($db, $query)){
            die("DAMMIT");
        }
        else{ 
            { header("Location: confermaCategoria.php"); }
        }
        mysqli_query($db, $query);
    }
    else {
        $name_error = "";
    }
    mysqli_close($db);

}
?>

The data is inserted into the existing table in the database, but I cannot create the new table. How can I do? Where am I wrong?

You should never create a new table on user input. Also , your queries are wide open to SQL injection. Use prepared statements instead of variables in your query string. — Gert B., Aug 31 '21 at 15:02
Just **don't**! You will have issues managing these tables with only few records in. Better have one table for that information for all users with proper indexes to users table. — Justinas, Aug 31 '21 at 15:07
And your create table statement simply has errors in syntax. Name is `CREATE TABLE fooCategory'_'barUsername`. And there muse be columns defined — Justinas, Aug 31 '21 at 15:09
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 31 '21 at 15:34

Barmar · Accepted Answer · 2021-08-31T15:47:53.830

0

You have incorrect quotes around and in the table name. You also have to specify at least one column in the table. I've made up a couple of columns, you should replace them with the names and types you need.

$sql = "CREATE TABLE `{$categoria}_{$username}` (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    col1 VARCHAR(100)";

You also need to add validation of $categoria and $username to protect against SQL injection. You can't use a prepared statement with parameters for table/column names, so you must validate them yourself. There's also little point in using mysqli_real_escape_string(), because escaping doesn't work in these names.

In general, if you're creating table names dynamically like this, you have a poor database design. Dynamic information should be in table data, not table/column names.

edited Aug 31 '21 at 15:47

answered Aug 31 '21 at 15:08

Barmar

741,623
53
500
612

I tried to do this but no tables are created in my database. Why? – asbarabungi Aug 31 '21 at 15:31
You need to list all the columns in the table when creating it. – Barmar Aug 31 '21 at 15:46
I can't see it anyway, even if I insert the columns – asbarabungi Aug 31 '21 at 15:56
Is it getting an error when you execute the query? If not, then the table must be created. – Barmar Aug 31 '21 at 15:57
i tried to insert this check and it actually returns me DAMMIT: if(!mysqli_query($db, $sql)){ die("DAMMIT"); } ` – asbarabungi Aug 31 '21 at 16:19
Change it to `die(mysqli_error($db))` to see the reason. – Barmar Aug 31 '21 at 16:20
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''user_utente' VARCHAR(100), 'nome_categoria' VARCHAR(50), ...' at line 2 – asbarabungi Aug 31 '21 at 16:22
My code is: `$sql="CREATE TABLE `{$categoria}_{$username}`( 'user_utente' VARCHAR(100), 'nome_categoria' VARCHAR(50), 'nome_stanza' VARCHAR(50) ) "; – asbarabungi Aug 31 '21 at 16:23
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/236614/discussion-between-asbarabungi-and-barmar). – asbarabungi Aug 31 '21 at 16:25

Create a new table in the database

1 Answers1