CODEWITHSUNDEEP
phpmysql
Thomas
Thomas

Reputation: 5099

Build 2 new tables at once with php

Im attempting to construct to new mysql tables at once:

 public function connect() {
    mysql_connect($this->host,$this->username,$this->password) or die("Could not connect. " . mysql_error());
    mysql_select_db($this->table) or die("Could not select database. " . mysql_error());
    return $this->buildDB();
  }
    private function buildDB() {
        $sql = <<<MySQL_QUERY
    CREATE TABLE IF NOT EXISTS projects (
    title       VARCHAR(300),
    projectid   VARCHAR(100)
    )
    CREATE TABLE IF NOT EXISTS owners (
    projectid   VARCHAR(100),
    owners      VARCHAR(150)
    )
    MySQL_QUERY;

        return mysql_query($sql);
      }

This maybe sort of a novice question, but I am having trouble seeing why this wont work - any ideas?

EDIT: Updated to incorporate @mellamokb idea about splitting up the queries

  private function buildDB() {
    $sql = <<<MySQL_QUERY
CREATE TABLE IF NOT EXISTS projects (
title       VARCHAR(300),
tags        VARCHAR(390),
description TEXT,
created     VARCHAR(100),
projectimg  VARCHAR(150),
savethumb   VARCHAR(150),
owners      VARCHAR(150),
projectid   VARCHAR(100),
projecturl  VARCHAR(150)
);

CREATE TABLE IF NOT EXISTS owners (
projectid   VARCHAR(100),
owners      VARCHAR(150)
)

MySQL_QUERY;

   $arr = split(";", $sql);
   foreach($arr as &$value) {
    return mysql_query($value);
   }

So, I updated the original question using split and then a foreach statement to iterate through each 'CREATE TABLE'. Unfortunately only the first table (projects) gets created. Any ideas what could be going on

Upvotes: 1

Views: 129

Answers (2)

mellamokb
mellamokb

Reputation: 56779

mysql_query cannot run multiple statements in a single call according to the docs: (emphasis mine)

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

You either need to split up the statements into separate calls to mysql_query, or make use of the mysqli library and mysqli_multi_query instead.

If you do go the splitting queries route with mysql_query, one easy way to do this is to terminate your statements with ; as normal in your single script, and then run a PHP command to split around the ; (like split or explode). Then loop through each of the output strings and execute them one at a time.

EDIT: Regarding your update: you need to move the return statement to outside the bottom of the loop, otherwise it returns to the caller of the function immediately after the first query.

Related SO questions (Remarkable what you can find with Google if you try, isn't it):

Upvotes: 2

Ole
Ole

Reputation: 794

That is 2 queries you're running there, and to my knowledge only MySQLi supports multi queries. You should check out http://php.net/mysqli

Upvotes: 1

Related Questions