Recordsets/Joining tables

[redbullmarky]

hello all

im making a back office recruitment system using dreamweaver, and whilst its not going too badly, ive just hit a brick wall.

 

basically, i have two tables, Business Contacts (buscontacts) and Candidates (candidates). Both have fields fTitle (ie Mr, Mrs, Miss, etc), fForenames, fSurname.

 

ive written a query like:

SELECT buscontacts.fTitle,buscontacts.fForenames,buscontacts.fSurname, candidates.fTitle, candidates.fForenames, candidates.fSurname FROM mydatabase WHERE criteria matched

 

which basically selects the name of the interviewer/business contact and the name of the candidate they interviewed.

 

now - when i run this query using mysql from command line, it works and shows me what i want.

but in php/dreamweaver, the recordset ignores the duplicate fields. i tried to use:

$myrecordset['buscontacts.fForenames'] to distinguish it from $myrecordset['candidates.fForenames'] but that didnt work. how can i treat all the fields and display each data field as individual?

 

this is really doing my head in and ive searched around but not yet found an answer ... can anyone here help???

 

cheers

 

mark

 

[bilbot]

Try using the "as" when you have duplicate names:

 

SELECT buscontacts.fTitle as bus_title,buscontacts.fForenames as bus_forenames,buscontacts.fSurname as bus_surname, candidates.fTitle, candidates.fForenames, candidates.fSurname FROM mydatabase WHERE criteria matched

 

[redbullmarky]

Try using the "as" when you have duplicate names:

 

SELECT buscontacts.fTitle as bus_title,buscontacts.fForenames as bus_forenames,buscontacts.fSurname as bus_surname, candidates.fTitle, candidates.fForenames, candidates.fSurname FROM mydatabase WHERE criteria matched

216848[/snapback]

 

cheers bilbot, i managed to get this problem sorted on another thread, so for anyone else having the same problem : - SOLUTION

[Dillinger]

SELECT buscontacts.fTitle,buscontacts.fForenames,buscontacts.fSurname, candidates.fTitle, candidates.fForenames, candidates.fSurname FROM mydatabase WHERE criteria matched

 

Alias the field names

 

SELECT buscontacts.fTitle ftitlefrombus ,buscontacts.fForenames fForenamesfrombus ,buscontacts.fSurname fSurnamefrombus , candidates.fTitle fTitlefromcan

 

and so on and so forth