Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Oct 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mysql syntax error

    Hello,

    If anyone can help... much appreciated.

    I am trying to create a simple html form to submit a table into a datebase with pre-populated columns. Upon submisson, I get a syntax error. I have tried using single quotes and not using single quotes for the table name, which is where the error is originating. I believe it is due to using $ but I cannot figure out another way to get the table name from my html form.

    My html form code:

    [CODE]
    <html>
    <form action="insert.php" method="post">
    Project Name: <input type="text" name="Project" size="50"><br>
    <input type="Submit">
    </form>
    <br>
    </html>
    [CODE]

    my inpsert.php code:

    [CODE]
    <?php
    $username = "root";
    $password = "XXXXX";
    $database = "Project_Data";

    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");

    $Project=$_POST['Project'];

    $query="CREATE TABLE $Project (
    'Id' tinyint(1) NOT NULL auto_increment,
    'Description' varchar(50) NOT NULL default '',
    'File' varchar(50) NOT NULL default '',
    PRIMARY KEY (Id)
    ) TYPE=MyISAM;

    INSERT INTO 'pageheaders' VALUES ($Project);";
    $result=mysql_query($query);
    if(!$result)
    {
    print 'DB error ' . mysql_error();
    } else

    Print "$Project has been successfully added to the database.";

    mysql_close();


    ?>
    [CODE]

    The sql syntax error message:

    DB 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 'Project ( 'Id' tinyint(1) NOT NULL auto_increment, 'Description' varchar(50)' at line 1

    Or with single quotes for table name:

    DB 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 ''First Project' ( 'Id' tinyint(1) NOT NULL auto_increment, 'Description' var' at line 1

    Thanks in advance for any help.

    - Hal W

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Code:
    $Project=$_POST['Project'];
    
    $query="CREATE TABLE $Project (
    'Id' tinyint(1) NOT NULL auto_increment,
    'Description' varchar(50) NOT NULL default '',
    'File' varchar(50) NOT NULL default '',
    PRIMARY KEY (Id)
    ) TYPE=MyISAM;
    Column names shouldn't be wrapped by any quotes. Also, you have to validate the values from external inputs from GET/POST etc to check whether they are allowed to be used in queries. For example, the table name shouldn't contain any spaces or quotes.

    btw, the logic to make a table for each "Project" is obviously bad. You may have a single table named projects and then insert the name given by the user to that table, after validation.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New to the CF scene
    Join Date
    Oct 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your response.

    I managed to fix the issue.

    Code:
    <?php
    $username = "root";
    $password = "xxxxx";
    $database = "Project_Data";
    
    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
      
    $Project=$_POST['Project'];
    
    $query="CREATE TABLE `$Project` (
      `Id` tinyint(1) NOT NULL auto_increment,
      `Description` varchar(50) NOT NULL default '',
      `File` varchar(50) NOT NULL default '',
       PRIMARY KEY (Id)
    ) TYPE=MyISAM;
    
    ;";
    $result=mysql_query($query);
    if(!$result)
    {
       print 'DB error ' . mysql_error();
    } else
    
    Print "$Project has been successfully added to the database.";
    
    mysql_close();
    
    
    ?>
    Using grave accent ` instead of single quote ' seems to have fixed it. The table was created successfully. I don't even know how I figured that one out.

    Thank you. - Hal W

  • #4
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    You don't need to use those backticks UNLESS you are using mysql's reserved words in the place of your table/column names (which is not a good practice anyway)
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Quote Originally Posted by abduraooft View Post
    You don't need to use those backticks UNLESS you are using mysql's reserved words in the place of your table/column names (which is not a good practice anyway)
    Or UNLESS you use invalid characters in your names (such as spaces, periods, minus signs, etc.).

    I'm not sure why people want to do that, but you can.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •