Creating a MySql table with a php variable as a name

Question

I'm trying to write a basic function creating a table with a name as a parameter.

When I execute the function I get this error : "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 '( id int(10) unsigned NOT NULL AUTO_INCREMENT, nom VARCHAR (255), questio' at line 1"

I looked at many forum posts and it seems like this syntax worked for some people, but not for meeee ! Here's my code :

function connexion_bd() {
    $serv ="localhost";
    $username = "root";
    $pwd = "blah_blah";
    $bd ="project";
    $connex = mysqli_connect($serv, $username, $pwd, $bd);  
    if (! $connex) {
        page_erreur(ERR_CONNEX, mysqli_connect_error($connex)); exit; 
    }
    return $connex;
}

function creer_jeu_bdd($nom) {
    $connex = connexion_bd();

    $nom = mysqli_real_escape_string($connex,$nom);

    $req = "CREATE TABLE IF NOT EXISTS ".$nom." ( 
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        nom VARCHAR (255),
        question text NOT NULL,
        reponse1 VARCHAR(256) NOT NULL DEFAULT ``,
        reponse2 VARCHAR(256) NOT NULL DEFAULT ``,
        reponse3 VARCHAR(256) NOT NULL DEFAULT ``,
        bonne_reponse TINYINT(2) UNSIGNED NOT NULL,
        foreign key (nom) references jeux(nom),
        primary key(`id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
    $resultat = mysqli_query($connex, $req);

    if (!$resultat) {
        page_erreur(ERR_REQUETE, mysqli_error($connex)); 
    } elseif (mysqli_num_rows($resultat) > 0) {
        page_erreur(ERR_LOGIN, ''); 
    }
    mysqli_close($connex);
    exit;
}

Thanks a lot.

$nom is empty. Also, `mysqli_real_escape_string` us **absolutely** useless here — Your Common Sense, May 05 '20 at 21:27
What value does `$nom` have? And the double backticks for the default are wrong. You probably meant double single quotes. Se ["When to use single quotes, double quotes, and backticks in MySQL"](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql?r=SearchResults&s=1|294.5770) — sticky bit, May 05 '20 at 21:29
Any time you find yourself with enumerated column names (above,say, 2), consider whether your schema design is optimal — Strawberry, May 05 '20 at 21:29

score 0 · Answer 1 · answered May 05 '20 at 21:29

Your default for your varchar have to be char, so use single quotes and niot backticks

Please check When to use single quotes, double quotes, and backticks in MySQL

 $req = "CREATE TABLE IF NOT EXISTS ".$nom." ( 
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        nom VARCHAR (255),
        question text NOT NULL,
        reponse1 VARCHAR(256) NOT NULL DEFAULT '',
        reponse2 VARCHAR(256) NOT NULL DEFAULT '',
        reponse3 VARCHAR(256) NOT NULL DEFAULT '',
        bonne_reponse TINYINT(2) UNSIGNED NOT NULL,
        foreign key (nom) references jeux(nom),
        primary key(`id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8; ";

Creating a MySql table with a php variable as a name

1 Answers1