Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -3 down vote favorite

So, a snippet of my code which is resulting in an error is : 

$con = mysqli_connect('localhost', 'root', '', 'notesDB');

if(isset($_POST['tableName'])) {
    $tName = htmlentities($_POST['tableName']);

    $firstQuery = mysqli_query($con,"INSERT into notes(Title) VALUES( '$tName'); CREATE TABLE $tName(id int NOT NULL AUTO_INCREMENT, Title varchar(20) NOT NULL, Description varchar(100), PRIMARY KEY(id));");

    if($firstQuery){
        header("Location: create2.php");
    }
    else 
        echo mysqli_error($con);
}

The output of this is :

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 'CREATE TABLE test1(id int NOT NULL AUTO_INCREMENT, Title varchar(20) NOT NULL, D' at line 1

Well, the funny thing is that the exact code (except the variable - I just removed the $ sign) executed perfectly in phpMyAdmin.

Also, to prove that there is nothing really wrong with the php, the query executed without any error when it was only the INSERT query (and not the CREATE query).

share|improve this question
 
"Also, to prove that there is nothing really wrong with the php, the query executed without any error when it was only the INSERT query (and not the CREATE query)." That's not proof that the PHP is correct; it's just proof that the issue with the PHP is the part where you're generating the CREATE TABLE part of the SQL. –  Anthony Grist Nov 11 at 13:20
2  
You can't run 2 seperate queries in one mysqli_query call –  juergen d Nov 11 at 13:22
 
Updated my answer, kindly check it now –  altafhussain Nov 11 at 13:26
 
I'm actually a newbie :3 Thanks, though :) –  AbhiramH Nov 11 at 16:13
add comment

3 Answers

up vote 1 down vote accepted

mysqli_query can only perform one query at a time.

Try mysqli_multi_query instead.

As an aside creating tables on the fly is usually a sign of larger design issues. Schema should be relatively static while data should be dynamic.

share|improve this answer
 
Thanks a lot! What do you mean by static schema, exactly? I thought by keeping the column names the same across all the tables, the schema would be relatively static. This was a project to help me understand databases, PHP and SQL so I'm a complete noob. –  AbhiramH Nov 11 at 16:19
 
I think I understood what you meant. Thanks!! –  AbhiramH Nov 11 at 16:29
add comment
up vote 1 down vote

Your database architecture is wrong.
You shouldn't create tables on the fly. So, you have only register whatever new entity with simple regular INSERT query. And then use this entity's id to link records from another [already existing] table.

if(isset($_POST['tableName'])) {
    $stm = mysqli_prepare($con,"INSERT into notes(Title) VALUES(?)");
    $stm->bind_param("s",$_POST['tableName']);
    $stm->execute();
}
share|improve this answer
 
Thanks for the tip! I will do more research on this now! –  AbhiramH Nov 11 at 16:25
add comment
up vote 0 down vote

You are trying to run two separate queries at a time in the code, which you can't run like that. You have to run them separately like below:

$con = mysqli_connect('localhost', 'root', '', 'notesDB');

if(isset($_POST['tableName'])) {
    $tName = htmlentities($_POST['tableName']);

    $firstQuery = mysqli_query($con,"INSERT into notes(Title) VALUES( '$tName')");

$secondQuery = mysqli_query("CREATE        TABLE '$tName' (id int NOT NULL AUTO_INCREMENT, Title varchar(20) NOT NULL, Description varchar(100), PRIMARY KEY(id));");

    if($firstQuery || $secondQuery){
        header("Location: create2.php");
    }
    else 
        echo mysqli_error($con);
}
share|improve this answer
 
Thanks a lot for your answer! –  AbhiramH Nov 11 at 16:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.