creating a table/getting error

Here is the error I get:


Error in executing CREATE TABLE %s (cityname VARCHAR (20), KEY index_name
(cityname(10)), dow1 VARCHAR (9), max1 TINYINT, min1 TINYINT, sky1 VARCHAR (20), fcstdays TINYINT)
stmt

error: 1064 You have an error in your SQL syntax near ' dow1 VARCHAR
(9), max1 TINYINT, min1 TINYINT,&nbs' at
line 1


Here is the actual code for this page:

<?php



require ("common.php3");

//CREATE TABLE STATEMENT BASED ON NUMBER OF FORECAST DAYS


$one = "CREATE TABLE %s (cityname VARCHAR (20), KEY index_name (cityname(10)),";

for ($i = 1; $i <= $fcstdays; $i++):
$one = $one . "&nbsp;" . "dow$i" . "&nbsp;" . "VARCHAR (9)," . "&nbsp;" .
"max$i" . "&nbsp;" . "TINYINT," . "&nbsp;" .
"min$i" . "&nbsp;" . "TINYINT," . "&nbsp;" .
"sky$i" . "&nbsp;" . "VARCHAR (20)," . "&nbsp;";
endfor;

$stmt = $one . "&nbsp;" . "fcstdays" . "&nbsp;" . "TINYINT" . ")";


$hostName ="xxxx";
$userName ="xxxx";
$password ="xxxx";
$databaseName ="xxxx";

//FUNCTION TO CREATE ERROR MESSAGE

function printError ($errorMesg)
{
printf("<BR> %s <BR>n", $errorMesg);
}

// OPEN CONNECTION WITH THE DATABASE SERVER

if (!($link=mysql_connect($hostname, $userName, $password))) {
printError(sprintf("error connecting to host %s, by users %s", $hostName, $userName));
exit();
}

// SELECT THE DATABASE

if (!mysql_select_db($databaseName, $link)) {
printError(sprintf("Error in selecting %s database", $databaseName));
printError(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)));
exit();
}

// CREATE THE TABLE

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

printf("<BR> Created Table %s.%s <BR>n", $databaseName, $tablename);

?>

Any idea?, somebody help, been stearing at screen for hours now

first of all you can't give table name with '%s' value.

and cityname should be NOT NULL field becoz that is unique or index field.


your query should be something like this..


CREATE TABLE test(cityname VARCHAR (20)not null, KEY index_name
(cityname(10)), dow1 VARCHAR (9), max1 TINYINT, min1 TINYINT, sky1 VARCHAR (20), fcstdays TINYINT);

just create a query like above with concatenation.




------------------
SR -
shiju.dreamcenter.net

"The fear of the LORD is the beginning of knowledge..."