Having issues creating new table

kjdix

Im trying to resolve an issue with code that has been written by someone else. Currently the code is to delete a table and then create a new one based on information gathered in a query. It seems to gather the information fine but then states a syntax error when trying to create the table.
Code is as below.

$query="drop table aslevelsgrid_2008";
$result=mysql_query($query);
echo mysql_error();

$query="select distinct exam_name from exams_2008 where level like 'AS marks'";
$result=mysql_query($query);
echo mysql_error();

$query="create table aslevelsgrid_2008 (row_id int not null auto_increment, person_id int ";
//$query="create table aslevelsgrid_2008 (row_id int not null auto_increment, person_id int, 'varchar (255)', PRIMARY KEY (`row_id`))";	
while ($row=mysql_fetch_array($result)) {
	$query.=", '$row[exam_name]' varchar (255)";
}
$query.=", PRIMARY KEY ('row_id'))  ";
//echo $query;
$createresult=mysql_query($query);
echo "<p>Creating AS level grid table...";
echo mysql_error();

Error as below

Unknown table 'aslevelsgrid_2008'
Creating AS level grid table...You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Biology' varchar (255), 'Business Studies' varchar (255), 'Chemistry' varchar (' at line 1

Any ideas?

tomhath

'Business Studies' has a blank in it

Lars_Berg

Use `instead of ' when escaping the column names

$query . = ", `" .  $row["exam_name"] . "` varchar (255)";

Weedpacket

Instead of dropping the table and recreating it, couldn't you just "Truncate Table aslevelsgrid_2008"? It's not like the new table is going to have a different structure to the old one, is it?

That would be ... disturbing.

kjdix

Still no luck
I now have code

$query="drop table aslevelsgrid_2008";
$result=mysql_query($query);
echo mysql_error();

$query="select distinct exam_name from exams_2008 where level like 'AS marks'";
$result=mysql_query($query);
echo mysql_error();


$query="create table aslevelsgrid_2008 (row_id int not null auto_increment, person_id int";
//$query="create table aslevelsgrid_2008 (row_id int not null auto_increment, person_id int, $row[exam_name] varchar(255), PRIMARY KEY ('row_id') )";	
while ($row=mysql_fetch_array($result)) {
$query.=", `" .  $row["exam_name"] . "` varchar (255)";
	//$query.=", '$row[exam_name]' varchar(255)";
}
$query.=", PRIMARY KEY (row_id) )  ";
//echo $query;
$createresult=mysql_query($query);
echo "<p>Creating AS level grid table...";
echo mysql_error();

and get result

Unknown table 'aslevelsgrid_2008'
Creating AS level grid table...Incorrect column name ''

Adding 96 students to the AS level grid...Table 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable 'school.aslevelsgrid_2008' doesn't existTable

I know that business studies has a blank in it but it needs to be able to use this information in a table field.

tomhath

Looks like you might have a blank exam_name in your table. What does $query contain before you run the query?