Create table syntax help

[r00tk1LL]

I am trying to create a dynamic table with php/msql and I keep running into a syntax problem. I need to use a variable for the table name but if I do this:

$table = 'CREATE TABLE `$var` ('
        . ' `id` INT(15) NOT NULL AUTO_INCREMENT PRIMARY KEY, '
        . ' `pics` TEXT NOT NULL, '
        . ' `thumbs` TEXT NOT NULL'
        . ' )'
        . ' ENGINE = myisam;';

The table is created called $var literally

 

Now if I use double quotes like this:

$table = "CREATE TABLE `$var` ('
        . ' `id` INT(15) NOT NULL AUTO_INCREMENT PRIMARY KEY, '
        . ' `pics` TEXT NOT NULL, '
        . ' `thumbs` TEXT NOT NULL'
        . ' )'
        . ' ENGINE = myisam;";

 

The value of the variable is seen, but no table is created

 

I need to find a way to create this table with the value that is stored in $var.

Can someone help me out?

[cooldude832]

dynamic table creation is a no no unless you can justify it.

[hitman6003]

You have to escape from single quotes when you want the value of a variable to be included...

 

<?php
$table = 'CREATE TABLE `' . $var . '` ('
        . ' `id` INT(15) NOT NULL AUTO_INCREMENT PRIMARY KEY, '
        . ' `pics` TEXT NOT NULL, '
        . ' `thumbs` TEXT NOT NULL'
        . ' )'
        . ' ENGINE = myisam';
?>

 

With that said, as was stated above, there is always a better way of doing something than creating a table based on user input.  Not only does it open up gaping security holes, but it clutters your database.

[r00tk1LL]

What I'm trying to do here is allow users to upload pictures to a dir, then create a table that sorts their pictures. I.E. Once a user uploads a picture, an original an thumbnail are copied to a dir, then a table is created with their username and it will store the pic url's, date created, and picture comment text

 

Is there a better way?

[cooldude832]

yes used a centralized table with a field for the UserID of the pics owner.

Tables are meant to hold data that can be compared aganist its self (on a row by row basis) and data that will have all the relative same use in output.  For example if you wanted to store  a schedule that had location, time, and people you would probably want to make your locatiosn be 1 table and people another then a table for the schedule that would link to the other ones.  The key to any good table strucure is linking tables together and not overloaded/underloading tables.  Some times storing a link to another tablse entry of something is best, others it by be best just to store those entries duplicated in table B.

[r00tk1LL]

Can you give an example of linking tables, syntax wise?

[keeB]

CREATE TABLE users (
    userId primary key int auto_increment not null,
    .
    .
)

CREATE TABLE images (
   imageId primary key int auto_increment not null,
   userId int,
   path text,
   description text,
   .
   .
)

 

Then, to query

SELECT * FROM users u
INNER JOIN images i on i.userId = u.userId
where i.userId='$userId'

 

 

Something like that.

 

You can read up on relational/normalized database creation, which is what you're trying to do.

[r00tk1LL]

Ok, I'm working on centralizing the pics table right now. I will keep this thread open until I get it all working.