what happens when you query a database with multiple tables and some of those tables have the same field.

I wrote the entire app not thinking of this and to go back and rewrite the code would me a nightmare.

SELECT * FROM leads AS l, eventtype AS et, accounts AS a, locations AS loc, referraltype AS rt
WHERE l.location_id = loc.location_id and l.account_id = a.account_id and l.eventtype_id = et.eventtype_id and l.referraltype_id= rt.referraltype_id and l.leads_id =3

I sat down and rewrote the tables over and over so this wouldn't happen and just my luck I get multiple tables with the same field.

"comments"

is there a way to get the leads.comments out of this query??

Thanks for the help

In your SELECT statement, instead of using *, you can explicitly name each column you want selected. To avoid field conflicts, you can prefix each field with the table name:

SELECT leads.comments, ....

yeah I know I could. I was hoping there would have been an easier way to do this. I have about 50 fields.

Maybe I should look at my tables before i start coding...

For future projects look at some available PHP/MySQL stuff that was released under the GPL..

Almost none of them have recurring field names for that reason (I had to find the same hack you did in a current project). Either all the names are different (like username and temp_username, etc) or they use the table as a prefix to the field...

Table : Contact
Field : Contact_Name or Cont_Name etc...

Yeah. Finding that AS command in MySQL saved my butt in a current project.

Rule 1 - never ever use select * in a program. You WILL eventually add columns to a table and break your program. Explicitly list the columns you want and your program will be fine even if you add additional columns to the table

Rule 2 - keep column names the same in two tables if the column is a foreign key. If you're joining the People table with key personid to the Address table with foreign key personid the column SHOULD be named the same in both tables.

Rule 3 - NEVER use the same column name across multiple tables UNLESS it's a foreign key.

If you want a really ugly hack you can usually extract the results of your query using the number of the field returned. In java it'd be something like:
personId = rs.getInt(1)
rather than
personId = rs.getInt("personid")

Perl does something very similar as I'm sure does PHP. The problem though is that if you select * you're never really sure what columns your getting and in what order. Even without changing your Db schema you could potentially break your code with a Db upgrade. Most Db's will also return meta data which gives you the name of the column so you could potentially use that to find the fields you need but it's probably more work than just following rule #1 in the first place.

Thank you all for the input.

I am using php.

$row->fieldname. so if the database does change I don't have to worry about if the field moves around in the table. I'm also using the table name now as a pre-fix (tablename_fieldname) I will go back a reweite the code so it has the prefix.

John