mySQL Query help

[jack.mitchell]

Hi Everyone, I am trying to create some tables automatically using this code:

 function initial_create_tables() {

                $query = "

                        CREATE TABLE dContent (
                        dContentID int NOT NULL AUTO_INCREMENT,
                        title text,
                        text text,
                        linkName text,
                        specialInclude int,
                        PRIMARY KEY(dContentID)
                        );

                        CREATE TABLE sContent (
                        sContentID int NOT NULL AUTO_INCREMENT,
                        description text,
                        value text,
                        inputType int,
                        PRIMARY KEY(sContentID)
                        );

                        CREATE TABLE admin (
                        adminID int NOT NULL AUTO_INCREMENT,
                        adminUser text,
                        adminPass text,
                        PRIMARY KEY(adminID)
                        ); ";


                if ($this->mysqli->query($query)) {

                        return "Table creation success!";

                }

                else {

                        return $this->mysqli->error;

                }

        }

If I plug this directly into mySQL as a copy pasta then it executes no problem, however trying to run this as a php script throws:

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 sContent ( sContentID int NOT NULL AUTO_INCREMENT, descr' at line 8

Does anyone have any ideas, I assume it's something to do with the space I have been the two table queries?

TIA!

 

[visibleman]

Have you tried each query individually?

 

[GravyMonster]

I'm an MSSQL guy, so apologies if this is completely wrong, but does the user your website connects to the database with have create privileges?

I'd guess logging directly in to mysql gives you better access rights

 

[Beansprout]

I'm 99% sure you have to send individual queries

 

[fez]

You need to send each query separately, it wont allow the creation of multiple tables in one go.

 

[Goksly]

For the love of God, make the primary key column 'id'.... I *hate* typing <tablename>id, or <tablename><columnname> arrrrrrrrrrggggggggggggghhhhhhh!

 

[fez]

Its pretty common to make the primary key have more than just ID as its name. When you start joining tables and using the results in a programming language its much easier to just alias the tables and use the column names without having to alias the id column on one table and remember which out of the many joined tables is the one with just ID.

 

[Viper²]

mysql_query() doesn't support multiple queries.

 

[jack.mitchell]

Alright cheers guys, altered it to:

function initial_create_tables() {

    echo("<b>Creating tables:</b><br />");

    if (!$this->table_exists("dContent")) {

      $query = "

        CREATE TABLE dContent (
        dContentID int NOT NULL AUTO_INCREMENT,
        title text,
        text text,
        linkName text,
        specialInclude int,
        PRIMARY KEY(dContentID)
        ); ";

      if ($this->mysqli->query($query)) {

        echo("dContent created<br />");

      }

      else {

        echo($this->mysqli->error . "<br />");

      }

    }

    if (!$this->table_exists("sContent")) {

      $query = "

        CREATE TABLE sContent (
        sContentID int NOT NULL AUTO_INCREMENT,
        description text,
        value text,
        inputType int,
        PRIMARY KEY(sContentID)
        ); ";

      if ($this->mysqli->query($query)) {

        echo("sContent created<br />");

      }

      else {

      }

    }

    if (!$this->table_exists("admin")) {

      $query = "

        CREATE TABLE admin (
        adminID int NOT NULL AUTO_INCREMENT,
        adminUser text,
        adminPass text,
        PRIMARY KEY(adminID)
        ); ";


      if ($this->mysqli->query($query)) {

       echo("admin created<br />");

        }

      else {

        echo($this->mysqli->error . "<br \>");

      }

    }

  }

I would change the ID columns but they are used heavily in my code and it just lets me know what i'm referring to and when. There should be very very few reasons to ever modify the database manually so I don't see it being an issue.
echo($this->mysqli->error . "<br \>");

 

[Goksly]

Its pretty common to make the primary key have more than just ID as its name. When you start joining tables and using the results in a programming language its much easier to just alias the tables and use the column names without having to alias the id column on one table and remember which out of the many joined tables is the one with just ID.

That's where column aliases come in. Obviously when you join them you just alias the columns but most of the time the id columns are just used for joins or update / delete... so can be just referred to as id.

Personal pref, but most guides I've read say its bad practice to prefix.