0

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.

Improve this question
3
  • Please post your complete error (obviously), and the var_dump of the $sql Commented Jan 15, 2013 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? Commented Jan 15, 2013 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. Commented Jan 15, 2013 at 15:48

5 Answers 5

Reset to default
2

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...

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I did echo my $sql, and everything came out as it should. I'll try the double quotes anyway.
If you did echo the $sql, add that to your questionk, and show us the output.
0

You apparenty have an extra trailing comma:

CREATE TABLE $table (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY(id),
       col1 INT,
       col2 INT,
--             ^ here
       )
Improve this answer

2 Comments

I wasn't sure how to get rid of that since I'm using a loop.
@user1978550: look up implode
0

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

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

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...?
0

If you are trying to get rid of the trailing slash you can also do this by using a counter.

$fieldsCount = count($listingFields);
foreach($listingFields as $key => $listing)
{ // create the insert statement (do not add comma at the end)
    $query .=" ".$listing[0];
    if ( $key+1 != $fieldsCount )
    {
        $query .=',';
    }
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.