[wendy_w]

Hi folks,

I am trying to set up a search page for one of our sites using cfmx. I am making a page that allows people to search our plan databases based on several different questions I supply. My trouble is that in order for them to search by the person's name I have to figure a way to connect two databases together to match an ID number with their name. The name and ID number is in one database and then the other database has their ID number.
Is there a way to make a query that looks in two different databases (not tables) at the same time?

Does anyone know of any good search pages that use cf that I can look at?

[Amadeus]

For the most part, you can do a join on tables in different databases, but it depends on what db software you are using. Can you specify your db software?

[wendy_w]

using mysql...

[wiredwizard]

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

http://www.w3schools...ql/sql_join.asp

easy stuff check w3cschools for some goob overviews on the basics

[wendy_w]

WIRED WIZARD:


sorry, I'm not joining two tables from the same database so it is a bit different than a regular join....

[wiredwizard]

ahh I havent tried this but I bet it would work if you do two quries then try a query of quries with the join. Like I said I havent tried it but it might be worth a shot

[wendy_w]

WIRED WIZARD:

I was able to create a cfc that looped over the ids and replaced them with their names from the other db but now I have another question...


question... i have a field that has ID numbers seperated by commas. I'm doing a query over it to change the ID numbers to their names but I dont know how to get rid of the commas.

[Amadeus]

You can do it various ways, do you want to do it via SQL, or in ColdFusion?

[wendy_w]

Amadeus,

whatever is fastest and/or easiest

[Amadeus]

Well, by your description, you have one field that is comprised of several names, separated by commas. You can put the whole field into a string, and use the CF Replace() function, if I'm not mistaken, replacing the commas with nothing.

[wiredwizard]

might want to just grab a quick UDF to take care of this check out CFLib.org lots of free ones. Look for a strip() function it will be something like this.

<cfscript>
function stripString(str) {
var temp = reReplaceNoCase(str,",[[:space:]]","","all");
return
}
</cfscript>

http://www.cflib.org/udf.cfm