Using PHP to alter a table

[Wickersty]

Hi.

I'm trying to have my PHP code add a field to a table. I'm not sure what I'm doing wrong, so if you could take a look at this code and perhaps let me know, I'd appreciate it.

Here's what I've written:

$query_add_field=mysql_query("ALTER TABLE 'wickersty'.'team_roster' ADD '$field_name' TEXT NOT NULL"

When I run the code, I get this error ("w0303" is the value contained in $field_name, mentioned above):

You have an error in your SQL syntax near 'w0303' TEXT NOT NULL' at line 1

Many thanks, guys...

Jeff

 

[Marklar]

You need the word COLUMN after ADD.

$query_add_field=mysql_query("ALTER TABLE 'wickersty'.'team_roster' ADD COLUMN '$field_name' TEXT NOT NULL"

If you want to specify where it goes use:

$query_add_field=mysql_query("ALTER TABLE 'wickersty'.'team_roster' ADD COLUMN '$field_name' TEXT NOT NULL AFTER 'another_field'"

This places it AFTER the specified column. To put it before, use FIRST.

 

[Wickersty]

Thanks, but I still get a mySQL error:

You have an error in your SQL syntax near 'wickersty.team_roster ADD COLUMN w0303 TEXT NOT NULL' at line 1

Very strange. I've been browsing the 'Net for any hints at what might still be wrong, but haven't found anything yet. If someone knows why it's failing, I'd love to know!

Thanks,

Jeff

 

[Marklar]

I'm a bit of a novice in this area myself, but seeing as no-one else has helped, I'll take a guess based on what I've found in the manuals.

I think the problem is with the wickersty.team_roster bit of the statement. I couldn't find any info that included the . syntax but there is a similar function called mysql_db_query where you specify the database and then the table (if that's what you're doing) but that is comma seperated. The mysql_query is only used when you already have a connected database, otherwise it will try and call mysql_connect() with no arguments.

I'd try it without the wickersty. bit.

Good luck It's not what you know, it's who knows it that counts.

 

[flugh]

There's a program out here called "phpMyAdmin" (

http://phpmyadmin.sf.net).

Aside from being handy for working on MySQL dbs, it will also show the SQL commands it executes to make stuff happen. Great learning tool

ALTER TABLE `testtable` ADD `bar` TEXT NOT NULL AFTER `newfoo` ;

That's what it gave me for doing something similar to what you need. So try:

$query_add_field=mysql_query("ALTER TABLE 'team_roster' ADD '$field_name' TEXT NOT NULL AFTER '$other_field'");

--
JR

 

[skiflyer]

I don't think it matters in this particular case... but there is a distinction to be made between the apostrophe and backtick.

' is the apostrophe
` is the backtick (on the same key as the tilde)

I was under the impression in mySQL the backtick was to specify table names, but from your phpmyadmin, it looks like it may be used to separate all user named fields from the rest of the syntax.

(In php it shells out, so it's a big difference there)

-Rob

 

[sleipnir214]

The backtick is used to tell MySQL that an ambiguous word or a word containing special characters is being used as a table or column name.

For example, this will fail:

SELECT * from select where foo = 3

This will work:

SELECT * from `select` where foo = 3

As a general rule, if you are using table and column names that require the backtick, then you are choosing the wrong table and column names.

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

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[flugh]

You know, I didn't even notice the backticks. That's the PHP code I copied and pasted from phpMyAdmin. The second set was entered mostly by hand. I will pay closer attention in the future

sleipnir214: thanks for that explanation about backticks. Haven't heard that mentioned before. --
JR

 

[Wickersty]

Figured it out. How silly...

The key is not having single quotes around the table name at all...

The correct code is:

$query_add_field=mysql_query("ALTER TABLE team_roster ADD '$field_name' TEXT NOT NULL"

Thanks for all the discussion guys, and maybe this solution will be of some use to some of you

Jeff