Page 1 of 1

mysql error

Posted: Wed Apr 27, 2011 2:58 pm
by hamdan92
Hello

i am beginner in php and i am trying to insert information into existing table in mysql database and creating new table at the same time but get an error :

Code: Select all

$sql=("INSERT INTO persons(profileNumber,name,age,dType)
VALUES
('$_POST[profileNumber]','$_POST[name]','$_POST[age]','$_POST[dType]')".
"CREATE TABLE $_POST[profileNumber]
(
billid varchar(20),
PRIMARY KEY(billid),
medname varchar(20),
time1 varchar(20),
time2 varchar(20),
time3 varchar(20),
gltest varchar(20),
testtime varchar(20),
)");
i get this error :

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 'CREATE TABLE 4532 ( billid varchar(20), PRIMARY KEY(billid), medname varchar' at line 3


please help
thanks

Re: mysql error

Posted: Wed Apr 27, 2011 3:45 pm
by CrowderSoup
The reason is that you don't separate your queries with a semi-colon: ';'.

This should do it:

Code: Select all

$sql= "INSERT INTO persons(profileNumber,name,age,dType)
          VALUES
             ('$_POST[profileNumber]','$_POST[name]','$_POST[age]','$_POST[dType]');
          CREATE TABLE $_POST[profileNumber]
          (
             billid varchar(20),
             PRIMARY KEY(billid),
             medname varchar(20),
             time1 varchar(20),
             time2 varchar(20),
             time3 varchar(20),
             gltest varchar(20),
             testtime varchar(20),
          )";
All I did was added a ; after the insert statement to separate it from the create statement.

Re: mysql error

Posted: Thu Apr 28, 2011 1:26 pm
by hamdan92
CrowderSoup wrote:The reason is that you don't separate your queries with a semi-colon: ';'.

This should do it:

Code: Select all

$sql= "INSERT INTO persons(profileNumber,name,age,dType)
          VALUES
             ('$_POST[profileNumber]','$_POST[name]','$_POST[age]','$_POST[dType]');
          CREATE TABLE $_POST[profileNumber]
          (
             billid varchar(20),
             PRIMARY KEY(billid),
             medname varchar(20),
             time1 varchar(20),
             time2 varchar(20),
             time3 varchar(20),
             gltest varchar(20),
             testtime varchar(20),
          )";
All I did was added a ; after the insert statement to separate it from the create statement.
i tried that before but still getting an error :

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 'CREATE TABLE 2222 ( billid varchar(20), P' at line 4

thanks

Re: mysql error

Posted: Thu Apr 28, 2011 2:40 pm
by CrowderSoup
Thats because you're not allowed to use only numbers as table names. If you do, you have put ticks around it like this:

Code: Select all

CREATE TABLE `2222`

Re: mysql error

Posted: Thu Apr 28, 2011 2:50 pm
by hamdan92
CrowderSoup wrote:Thats because you're not allowed to use only numbers as table names. If you do, you have put ticks around it like this:

Code: Select all

CREATE TABLE `2222`
i tried using letters but still getting the same error :

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 'CREATE TABLE hamdan ( billid varchar(20), ' at line 4

Re: mysql error

Posted: Thu Apr 28, 2011 3:29 pm
by mikosiko
I'm guessing that you are using the PHP mysql API, if that is the case you should know that mysql API doesn't support multi-queries.

you have 2 options:
a) separate your INSERT and CREATE (and by the way... why you need to have the CREATE in your code?) in 2 queries and execute them separately or
b) use the mysqli API that support multi-queries but you also should know that it is more difficult to program the proper error controls in your code using multi-queries.