Query Problems

donald3

I have the following code, that I believe seems to be my problem, $newTableName is predefined

$sql="CREATE TABLE $newTableName (date INT(2), event TEXT, description TEXT, where TEXT, time TEXT, cost TEXT, ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID))";

If you could explain my problem I would appreciate it!!!

bastien

TIME as col name

http://www.mysql.com/doc/en/Reserved_words.html

donald3

Well, I already have an existing table with time as a field, I tried your suggestion, but it didn't do the trick....

Lars_Berg

What about where? That's reserved.

dzekic

date, time and where are all keywords and idea is that you can't use them for column names... I don't get how you made table with 'time' column work...

Anyway... try put something like "order_date", "where_from", etc...

donald3

I changed the Where to wheres, Date to dates, and time to times and I still get that error...here's the code again....

mysql_query("CREATE TABLE $newTableName(
event_date INT,
event TEXT,
description TEXT,
event_location TEXT,
event_time TEXT,
cost TEXT,
event_ID INT NO NULL AUTO_INCREMENT,
PRIMARY KEY(event_ID))") or die ("Couldn't process query");

When the script is executed, I get the Couldn't process query echoed.

Any sugguestions?? (thanks for those who have tried so far!!!) oh, I copied some code from a php site and it worked great! but when I edit the code it doesn't like it, so something IS wrong with my code....I just don't know what!!!!

bastien

PHP sometimes has issues with dynamic variables in the code...try concatenating it like below....

mysql_query("CREATE TABLE".$newTableName."(
event_date INT,
event TEXT,
description TEXT,
event_location TEXT,
event_time TEXT,
cost TEXT,
event_ID INT NO NULL AUTO_INCREMENT,
PRIMARY KEY(event_ID))") or die ("Couldn't process query");

donald3

Okay, I really appreciate you helping me! But unfortunately this isn't working still, and its starting to bug me! I copied your code and pasted it into my php code in place of the old...(was this the wrong thing to do?)...

bastien

to show the errors

donald3

Now, i'm getting an error...what I am trying to do is just create a new table named $newTableName which is entered in and passed in....I don't know if there is anything I can tell you, but I am getting an error about this....

bastien

numeric or ortherwise...might need a closing bracket in the line

PRIMARY KEY(event_ID))") or die ("error is ".mysql_error());

need to see a mysql error to see what the problem is...

could also try to echo out the sql statement to see if all appears as you want/need it to...

Jeepsta

I just ran your SQL on my server at home and the SQL appears to be good. One thing to remember though.......try to stay away from reserved words for table names, it causes problems.

Just a suggestion but have you tried echoing out the variable to make sure that it is actually a value and not null?

Also, I would try creating a $query variable to set up the actual query.

$query = "CREATE TABLE ".$newTableName." (dates INT(2), event TEXT, description TEXT, wheres TEXT, times TEXT, cost TEXT, ID INT NOT NULL AUTO_INCREMENT,PRIMARY KEY (ID))";

$result = mysql_query($query,$link);

where $link is the link identifier.

Note the spaces after TABLE and before (dates...... maybe the concatenation is running those words plus the variable together. Just a thought

Zach

donald3

HEY! That code did the trick! Thank you for helping me! I appreciate your time....
Thanks, Donald

zalath

On a side note, mySQL allows reserved words for table names, just as long as you backtick them (I tested this):

create table time(where varchar(10));

Works like a charm. But bad practice, nonetheless.