10 Replies - 6604 Views - Last Post: 15 October 2005 - 02:39 AM Rate Topic: -----

#1 wendy_w  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 02-June 05

Coldfusion Search and Query question

Posted 28 September 2005 - 06:15 AM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: Coldfusion Search and Query question

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Coldfusion Search and Query question

Posted 28 September 2005 - 06:21 AM

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?
Was This Post Helpful? 0
  • +
  • -

#3 wendy_w  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 02-June 05

Re: Coldfusion Search and Query question

Posted 28 September 2005 - 06:36 AM

using mysql...
Was This Post Helpful? 0
  • +
  • -

#4 wiredwizard  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 07-May 05

Re: Coldfusion Search and Query question

Posted 07 October 2005 - 06:45 PM

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

:rolleyes:
Was This Post Helpful? 0
  • +
  • -

#5 wendy_w  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 02-June 05

Re: Coldfusion Search and Query question

Posted 10 October 2005 - 06:53 AM

WIRED WIZARD:


sorry, I'm not joining two tables from the same database so it is a bit different than a regular join....
Was This Post Helpful? 0
  • +
  • -

#6 wiredwizard  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 07-May 05

Re: Coldfusion Search and Query question

Posted 10 October 2005 - 01:21 PM

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

:D
Was This Post Helpful? 0
  • +
  • -

#7 wendy_w  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 02-June 05

Re: Coldfusion Search and Query question

Posted 13 October 2005 - 07:36 AM

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.
Was This Post Helpful? 0
  • +
  • -

#8 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Coldfusion Search and Query question

Posted 13 October 2005 - 07:43 AM

You can do it various ways, do you want to do it via SQL, or in ColdFusion?
Was This Post Helpful? 0
  • +
  • -

#9 wendy_w  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 02-June 05

Re: Coldfusion Search and Query question

Posted 13 October 2005 - 03:19 PM

Amadeus,

whatever is fastest and/or easiest ;)
Was This Post Helpful? 0
  • +
  • -

#10 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Coldfusion Search and Query question

Posted 13 October 2005 - 05:58 PM

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.
Was This Post Helpful? 0
  • +
  • -

#11 wiredwizard  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 07-May 05

Re: Coldfusion Search and Query question

Posted 15 October 2005 - 02:39 AM

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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1