CODEWITHSUNDEEP
phpmysql
Cl'
Cl'

Reputation: 1653

php can't create new database tables via query

Here is the code that should create 2 new tables in MySQL if they do not exist CMS and PAGES, however what is occurring is that CMS is being created but PAGES is ignored and is not created.

Here is the php function responsible for creating tables

private function buildDB() {

#lets create cms table if one does not exist
$make_cms = <<<MySQL_QUERY
            CREATE TABLE IF NOT EXISTS cms (
            title VARCHAR(150),
            bodytext TEXT,
            date VARCHAR(100)
)
MySQL_QUERY;

return mysql_query($make_cms);

#lets create pages table if one does not exist
$make_pages = <<<MySQL_QUERY2
              CREATE TABLE IF NOT EXISTS pages (
              pid int NOT NULL AUTO_INCREMENT, PRIMARY KEY (pid),
              title VARCHAR(150),
              text TEXT,
              date VARCHAR(100)
)
MySQL_QUERY2;

return mysql_query($make_pages);

}

And there ya go that's the function. I ones again will note part one of it works so there for $make_cms does its job and actually makes a table called CMS while the function $make_pages does nothing and fails to create PAGES table.

Upvotes: 1

Views: 1174

Answers (4)

Roadmaster
Roadmaster

Reputation: 5357

The query itself seems OK, so one explanation may be that either pages exists already, or the user somehow has no permission to create that particular table.

You can modify your code so it shows what the MySQL server said when running the query:

result = mysql_query($make_pages);

if (!$result) {
    echo('Invalid query: ' . mysql_error());
}

Upvotes: 1

Duke
Duke

Reputation: 1731

change "text TEXT," to text1 TEXT and it should work! remember sql is not case sensitive.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270775

You're returning from the function before beginning the second CREATE TABLE statement. You'll therefore never reach the second statement.

// Don't return here!
return mysql_query($make_cms);

Instead assign the value and return only if FALSE:

$success_cms = mysql_query($make_cms);
// Return only on error of first table creation
if (!$success) {
  return FALSE;
}
// Then create the second table...

Upvotes: 6

Umbrella
Umbrella

Reputation: 4788

If you use echo mysql_error() to output the error, it will tell you. I would guess it is because you have a field named text, which is a reserved word. Try escaping your field names in tics ( ` ) or avoiding reserved words.

#lets create pages table if one does not exist
$make_pages = <<<MySQL_QUERY2
          CREATE TABLE IF NOT EXISTS pages (
          `pid` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`pid`),
          `title` VARCHAR(150),
          `text` TEXT,
          `date` VARCHAR(100)
)
MySQL_QUERY2;

Upvotes: 1

Related Questions