up vote 0 down vote favorite

A user is creating a table. The user enters the number of fields that will be in the table, and a form is generated based on the number they entered. They then enter the names of the columns and the type. I then create the table based on what they entered.

I can get the arrays to populate correctly, but my error message says I have a syntax error. I'm sure I did something wrong, but I tried to add a while loop inside the query since there is no set number of variables to be entered. This is what I have. If there's a better way to do it, I'm all ears.

    $sql = 'CREATE TABLE $table (
id INT NOT NULL AUTO_INCREMENT,  
PRIMARY KEY(id), ';

        while($numDone < $totalFields){
            $sql .= $colName[$x] . ' ' . $types[$x] . ', ';
            $x++;
            $numDone++;
        }
        $sql .= ')';
    $query1 = mysql_query($sql) or die(mysql_error());

**Solved I changed the single quotes to double quotes, used the dot operator for $table, and added an if statement for the comma. It's working now.

share|improve this question
Please post your complete error (obviously), and the var_dump of the $sql – Nanne Jan 15 at 7:43
2  
Do you realise that creating tables on user's request is extremely unusual practice and in general being a very bad idea? – Your Common Sense Jan 15 at 7:52
I understand that. I'm just putting together a data entry program to be used by one guy. I'll work on making it more secure later. – user1978550 Jan 15 at 15:48
feedback

4 Answers

up vote 0 down vote accepted

You apparenty have an extra trailing comma:

CREATE TABLE $table (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY(id),
       col1 INT,
       col2 INT,
--             ^ here
       )
share|improve this answer
I wasn't sure how to get rid of that since I'm using a loop. – user1978550 Jan 15 at 15:49
@user1978550: look up implode – Quassnoi Jan 15 at 16:40
feedback
up vote 0 down vote

You may have VARCHAR field entered without size like fieldname VARCHAR will return error instead it should be like fieldname VARCHAR(100) ? Trailing comma may also be the reason for error as Quassnoi commented.

share|improve this answer
I knew the trailing comma might be an issue, but I wasn't sure how to get rid of it. I guess some sort of if statement to make to known that it's the last one...? – user1978550 Jan 15 at 15:47
feedback
up vote 0 down vote

1

change the single quotes (') to double quotes (") for your query.

2

or use dot operator (.) to append php variable. 

$tableName = "mytable";
echo $query1 = "SELECT * FROM $tableName";
echo $query2 = 'SELECT * FROM $tableName';

// Output

SELECT * FROM mytable

SELECT * FROM $tableName

share|improve this answer
feedback
up vote 2 down vote

For one, this 

'CREATE TABLE $table'

will NOT fill in $table, but will be LITERALLY

CREATE TABLE $table

use " if you want variables to be shown. You would've spotted that if you'd just echo your $sql. There might be more, but probably easily discoverable trough mentioned debugging...

share|improve this answer
I did echo my $sql, and everything came out as it should. I'll try the double quotes anyway. – user1978550 Jan 15 at 15:45
If you did echo the $sql, add that to your questionk, and show us the output. – Nanne Jan 15 at 15:51
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.