Help please. My CREATE TABLE not working

Databases
1

I’m trying to create a table with the name passed to the function in a variable but all I get is a table called $newtab. Can anyone advise me?

Thanks,
Mike

function tablecreation($newtab)
  {
    $query = '
      CREATE TABLE $newtab
      (
       booknumber INT(6),
       PRIMARY KEY(booknumber)
      )
            ';
    $result = mysql_query($query)
             or die('no create');
     return;
  };
2

The products are books (there are more than 7,000) and they can have common keywords, eg., photography, architecture, fashion, etc., etc. I intend just to have eg. a photography table with the numbers of the books which have photography as one of their keywords. Get it?

Mike

3

:confused:
And the keywords?

4

Scallio dank je.
I used the 2nd option.

Being an sql newbie I’m experimenting with this and that.

The 1 column table is because I have products (books) in the main table and each one can have several keywords. The idea is to have a keyword table with just the booknumbers to make the link for searches etc. Hope that’s clear.

Mike

5

You need to either use double quotes


function tablecreation($newtab)
  {
    $query = "
      CREATE TABLE $newtab
      (
       booknumber INT(6),
       PRIMARY KEY(booknumber)
      )";
    $result = mysql_query($query) or die('no create');
  };

Or single quotes with string concatenation


function tablecreation($newtab)
  {
    $query = '
      CREATE TABLE ' . $newtab . '
      (
       booknumber INT(6),
       PRIMARY KEY(booknumber)
      )';
    $result = mysql_query($query) or die('no create');
  };

I prefer the second option, but none of the options is “better” than the other, just different.

PS. Note that INT(6) only makes sense if you use the zerofill option. Otherwise just specify INT.
PPS. What is the table for? A table with just one column indicates to me that you might be doing something wrong.

6

I get, but that’s not the way I would do it, since I don’t think scripts should be allowed to create tables dynamically (also, it’s not 3NF).

How you’d normally do this is something like

Table: categories


id | name
-----------------
1  | photography
2  | architecture
3  | fashion

PRIMARY KEY: id

Table: books


id | name
-----------------------------------
1  | How to make beautiful pictures
2  | Taking pictures of buildings
3  | Dress to impress!

PRIMARY KEY: id
Table: book_categories


book_id | category_id
---------------------
1       | 1
2       | 1
2       | 2
3       | 3

PRIMARY KEY: book_id, category_id

7

For those of you that don’t speak Dutch, “dank je” means “thank you” :slight_smile:

Ah, I had the nagging feeling something wasn’t quite right when I typed it.
Thank you for pointing it out, I’ll keep it in mind! :slight_smile:

8

sorry, but it is

not every poor design fails normalization, you know

there’s nothing wrong from a normalization point of view in having multiple tables like that

it’s a very poor and awkward design, but it doesn’t fail 3NF

p.s. yes, i’m tired of normalization getting blamed for poor designs