Creating a mySQL TABLE using php

[Anon]

I've tried to create a mysql table using php but have not been successful. Can someone please give me a good example to follow? I tried this:

// Create MySQL database.
$link = mysql_pconnect($host, $user, $password);
mysql_select_db ($databaseName);

// Create Table.
mysql_query("CREATE TABLE $tableName(.....)");

but I keep getting a Table does not exist error. Any help appreciated. email me please.

Thanks,
Marcel.

[Anon]

You can't just stick a string variable between two quotes in a function call.

Try building a $sql_statement var first, and executing that:

$sql_statement="create table ".$tablename."(.....)";
print $sql_statement."\n"; # for debugging
mysql_query($sql_statement);

[Anon]

Actually you can stick a variable between double quotes on a function call. You have to be careful with php3 when dealing with object properties and arrays, though.

The only thing I see possibly wrong, Marcel, is that $tableName isn't defined.

[Anon]

Thanks for the help Rod and Vincent, here's what I did to 'fix' it...let me know what you think.
<PRE>
// Create Table.
$stmt = "CREATE TABLE $tableName(
BUSINESS VARCHAR(255),
NAME VARCHAR(255),
EMAIL VARCHAR(255),
HOMEPAGE VARCHAR(255),
STREET VARCHAR(255),
CITY VARCHAR(255),
STATE VARCHAR(255),
COUNTRY VARCHAR(255),
ROWID INT PRIMARY KEY AUTO_INCREMENT)");

if (!mysql_query(sprintf($stmt, $tableName), $link)) {
printf(sprintf("Error in executing %s stmt.", $stmt));
printf(sprintf("Error: %d %s", mysql_errno($link), mysql_error($link)));
exit();
}
</PRE>

So in essence I used Vincents idea. 🙂...which is what I had originally, but as Rod said, I should be able to use either method with success? Please note I am using PHP4...🙂

Thanks again and I hope this helps someone else....
Marcel.

[Anon]

Either method should work.
I usually build a statement first, so I can print it to the screen for debugging.

Ofcourse, this is not mandatory....
Once you are satisfied that your code builds the correct statement you can skip the seperate building of the statement, and build it directly in the functioncall.

Once you have built your statement, you don't need to use the sprintf() function, just use the variable directly.
(In fact, have a look at what the manual says about printf and sprintf, and then rethink that last bit of code....)

[Anon]

I see what you mean. Also, that little bit of code had a typo...it should have read:

if (!mysql_query(sprintf($stmt, $tableName), $link)) {
DisplayErrMsg(sprintf("Error in executing %s stmt.", $stmt));
DisplayErrMsg(sprintf("Error: %d %s", mysql_errno($link), mysql_error($link)));
exit();
}

Where DisplayErrMsg() was declared in a global file which I included in the preceding code.

Thanks again,
Marcel.