i have 4 related selects in Coldfusion AJAX

I think i have asked this question i do not know many times. i think this question is not difficult as it looks. nobosy answered me as i desperately need experts help. i am not that much experienced as you are guys, so please if you pick up little bit of time to sort my issue, it will help me a lot in savng a lot of time.

So pleaser help me:

i have the following function when it loads it shows error:

<select name="category" class="textfield_select" size="5" id="category">
            <cfoutput query="Stuff">
              <option value="#catID#" selected="selected">#catName#</option>
        </cfoutput>
      </select>

<cfselect name="state" size="5" class="textfield_select" bindOnLoad="yes" id="state" bind="cfc:#request.cfcPath#.statecity.getStateHere({category})" display="stateName" value="state"></cfselect>

<tr>
      <td><cfselect name="city" size="5" class="textfield_select" bindOnLoad="yes" id="city" bind="cfc:#request.cfcPath#.statecity.CitySelect({state@click})" display="cityName" value="cityID"></cfselect>
    </td>
</tr>
<tr>
      <td><cfselect name="subcat" size="5" class="textfield_select" bindOnLoad="no" id="subcat" bind="cfc:#request.cfcPath#.statecity.getsubcat({category},{city})" display="catName" value="catID"></cfselect>
    </td>
</tr>    


the query is:
 
Not unique table/alias: 'categories'
 
<cffunction name="getsubcat" access="remote" returntype="query">
  <cfargument name="city" required="true" default="1">
  <cfargument name="category" required="true" default="12">
  <cfset var myset = "">
  <cfquery name="myset" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
	SELECT dpages.catID, dpages.cityID, categories.*, cities.* 
    FROM dpages  
    inner JOIN cities ON dpages.cityID = cities.cityID 
    left Join categories on dpages.catID = categories.catID 
    left Join categories on dpages.parentID = categories.parentID
    WHERE 
    dpages.cityID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.city#">
    AND 
    (dpages.catID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.category#"> 
    or 
    dpages.parentID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.category#">) 
  </cfquery>
  <cfreturn myset>
</cffunction>

Open in new window

LVL 16
Gurpreet Singh RandhawaCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gurpreet Singh RandhawaCEOAuthor Commented:
<tr>
    <td><cfselect name="subcat" size="5" class="textfield_select" bindOnLoad="no" id="subcat" bind="cfc:#request.cfcPath#.statecity.getsubcat({category},{city})" display="catName" value="catID"></cfselect>
    </td>
</tr>  

The above select is the last select but it gets executed every time i select upper select boxes to load contents and show invalid '' value error.

the above select is required to be executed after i select the exact city but it executes evn after i select main category or even after main category i select state.

this shows the same error.

at last when i select the city to load contents then it shows the error:



Not unique table/alias: 'categories'

Open in new window

0
azadisaryevCommented:
1) the order of the bind variables must match the order corresponding arguments are defined in your function.
you function defines first city, then category arguments, but you are passing them in wrong order to the function: bind="cfc:#request.cfcPath#.statecity.getsubcat({category},{city})" should instead be bind="cfc:#request.cfcPath#.statecity.getsubcat({city},{category})"

2) if you want your binding to pass the binded control's value, but not to react to changes in that element, use @none binding event: bind="cfc:#request.cfcPath#.statecity.getsubcat({city},{category@none})"

3) since your SUBCAT control is binded to the CITY select, which in turn is binded to STATE select, that's what is causing the chain reaction... try adding binding to @click event on CITY control maybe... bind="cfc:#request.cfcPath#.statecity.getsubcat({city@click},{category@none})"

Azadi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
azadisaryevCommented:
the "Not unique table/alias: 'categories'" error is caused by these lines in your sql:

left Join categories on dpages.catID = categories.catID
left Join categories on dpages.parentID = categories.parentID

if you need to join to categories table twice, give it separate aliases:

left Join categories c1 on dpages.catID = c1.catID
left Join categories c2 on dpages.parentID = c2.parentID

Azadi
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gurpreet Singh RandhawaCEOAuthor Commented:
Thanks predence order worked Great.

i get this error when i select the second last City select to load actually the subcategories based on cityID and main category

the query which deals with it is:

2. if i select the category which has no related state, how the select can be emptied then, if i choose another category if it has no record, it still displayes the old state of previously selected category whose record exist.


<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
 <tr>
        <td id="tablePropsWidth" width="400" colspan="2">
            <font style="COLOR: black; FONT: 8pt/11pt verdana">
            Not unique table/alias: 'categories'
            </font>
        </td>
    </tr>
    <tr>
        <td height>&nbsp;</td>
    </tr>
 
<cffunction name="getsubcat" access="remote" returntype="query">
  <cfargument name="city" required="true" default="1">
  <cfargument name="category" required="true" default="12">
  <cfset var myset = "">
  <cfquery name="myset" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
        SELECT dpages.catID, dpages.cityID, categories.*, cities.* 
    FROM dpages  
    inner JOIN cities ON dpages.cityID = cities.cityID 
    left Join categories on dpages.catID = categories.catID 
    left Join categories on dpages.parentID = categories.parentID
    WHERE 
    dpages.cityID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.city#">
    AND 
    (dpages.catID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.category#"> 
    or 
    dpages.parentID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.category#">) 
  </cfquery>
  <cfreturn myset>
</cffunction>

Open in new window

0
Gurpreet Singh RandhawaCEOAuthor Commented:
i solved that unique precence before your answer but many thanks for this. one last issue?




<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
if i select the category which has no related state, how the select can be emptied then, if i choose another category if it has no record, it still displayes the old state of previously selected category whose record exist.

Open in new window

0
azadisaryevCommented:
you can add this to your getStateHere cfc function after the query:

(i do not know the name of the query you use in that function, so i just used 'getState' - change as required)

<cfif NOT getState.recordcount>
  <cfset getState= querynew('state, statename')>
  <cfset queryaddrow(getState)>
  <cfset querysetcell(getState, 'state', 0)>
  <cfset querysetcell(getState, 'statename', 'no data...')>
</cfif>

Azadi
0
Gurpreet Singh RandhawaCEOAuthor Commented:
Thanks Azadi But recently i noticed some wired errors.

I tried to click the category and checked in firebug response 200OK, but it did not show any  State and also when i checked it loaded the whole page contents.

i mean the full page HTML

do you know [interpret] what had happened
0
azadisaryevCommented:
looks like your cfc has an error in it... if you scroll way down in the return displayed in firebug, you will probably see the familiar cf exception error text there...

Azadi
0
Gurpreet Singh RandhawaCEOAuthor Commented:
i solved that i just moved the statecity.cfc outside the root and it just worked. well do not why it behave very ridiculous sometimes

Cheers
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.