Build 2 new tables at once with php

Question

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

Don't update your question to incorporate answers. Mark the answer as correct if it worked or comment on why it didn't work. If you have a problem with the new code, start a new question. This makes the answers no longer relevant to your question. — mellamokb, Oct 07 '11 at 01:14
It only half-worked. Only the first table gets created. Sorry for editing the original, I just wasn't sure where to show the work. — Thomas, Oct 07 '11 at 01:15
A good way to show new information is by adding a `**EDIT**` or `**UPDATE**` section to the bottom of your question and show the changes and new problems so the original is preserved and relevant. If it gets too complicated, the best thing to do at this point is start a new question with the new problem addressed. If you do start a new question, you can always link to this one to provide some context. — mellamokb, Oct 07 '11 at 01:16
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. — mellamokb, Oct 07 '11 at 01:23

score 2 · Accepted Answer · edited May 23 '17 at 12:27

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):

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? — Thomas, Oct 07 '11 at 01:13
See my comment on the question. Don't incorporate answers to your question because it makes the answers irrelevant and confuses the question. Put the question back to normal or add the updated code as an **UPDATE** section to your question. We can continue this conversion via comments, or start a new question if it gets too complicated. — mellamokb, Oct 07 '11 at 01:15
Can you dump the queries that are getting executed, i.e. `var_dump($value)`, and make sure they look ok. Also, make sure any error messages are getting reported with `error_reporting(1)` and check the contents of `mysql_error()` after every query. — mellamokb, Oct 07 '11 at 01:19
Actually it looks like the problem is you are saying `return`, so after the first iteration it will immediately return the value back to the caller... — mellamokb, Oct 07 '11 at 01:23

score 1 · Answer 2 · answered Oct 07 '11 at 00:45

1

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

answered Oct 07 '11 at 00:45

Ole

776
7
10

Build 2 new tables at once with php

2 Answers2