asked on

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

Coast Line

ASKER

<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

ASKER CERTIFIED SOLUTION

azadisaryev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

azadisaryev

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

Coast Line

ASKER

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"><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

Coast Line

ASKER

i solved that unique precence before your answer but many thanks for this. one last issue?

<input id="gwProxy" type="hidden"><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

azadisaryev

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

Coast Line

ASKER

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

azadisaryev

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

Coast Line

ASKER

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"><input onclick="jsCall();" id="jsProxy" type="hidden">