• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

ColdFusion and SQL Query

I am trying to output a query within a query that has many fields without having to name each field and I want to create a table.

My first query is

<cfquery name="GetAvailableOptions" datasource="mydata">
select * from mytable
</cfquery>

My second query is

<cfquery name="GetPartAvailableOptions" dbtype="query">
      select * from GetAvailableOptions where part = '#partnumber#'
</cfquery>

Basically the second query gets options available from a specific part number from the first query.

I then have the below, which only gets me columns that don't have value of 0

<cfset curcolHeaderNames = ArrayToList(GetPartAvailableOptions.getColumnList())/>

<cfset PartOptionsArray = ListToArray(curcolHeaderNames, ",")>

<cfloop index="x" from="1" to="#arrayLen(PartOptionsArray)#">
<cfset CurColName = "#trim(PartOptionsArray[x])#">


<cfquery name="partOptions" dbtype="query">
            select   #CurColName# FROM GetPartAvailableOptions where #CurColName# <> 0
</cfquery>

And lastly, I have this to output everything into a table.

<cfif #partOptions.recordcount# GT 1>

<table border = '1'>
<tr><cfloop list="#partOptions.ColumnList#" index="curcol" delimiters=",">
<th><cfoutput>#curcol#</cfoutput></th>
</cfloop></tr>
<cfoutput query="partOptions">
<tr><cfloop list="#partOptions.ColumnList#" index="col">
<td>#partOptions[curcol][CurrentRow]#</td></cfloop></tr>
</cfoutput>
</table>


</cfif>
</cfloop>

The problem here is that it outputs into this format

COLOR (this is the column name and options are below)
5
5
MATERIAL (this is the column name and options are below)
1573
1576

So I end up with the above, but I want the below.

Color      Material
5      1573
5      1573

I know this is probably simple but I just can't figure out the actual HTML to make this output the way I want.






0
Kamilek0617
Asked:
Kamilek0617
  • 13
  • 12
  • 2
1 Solution
 
gdemariaCommented:
It's unclear to me which values are coming from which fetch of the query



Can you tell me what is the output of SELECT * from GetPartAvailableOptions  ?

The fields are COLOR, MATERIALS, etc..  right?

So those are the columns going across.

What about going down?   Is the first row going across related in some way?

Color     Material
Red       1111
Blue       2222


For example, in the above sample is the RED and Material 1111 part of the same Item or Product or something?  Or is the Color list going down completely independant from the Material list going down.

Another way of saying it could it just as easily read this way...

Color     Material
Blue      1111
Red       2222

as this way...

Color     Material
Red       1111
Blue       2222


Because there is not association between Red and 1111 and Blue and 2222 or must they be in the same row like this...  because it is the T-shirt that is red, not the jacket..


Product   Color     Material
T-Shirt     Red       1111
Jacket      Blue       2222




 select   #CurColName# FROM GetPartAvailableOptions
0
 
gdemariaCommented:
I really had to ponder that for a while, not sure if I'm right on understanding, but give this a try.

I was thrown off by the looking of the column names in the partOptions query, but there seems to be ONLY ONE column returned, so there is nothing to Loop!   I gave that column an alias name and got rid of the extra loops.

I was also confused by the conversion of column list from array to list and back to array to only pull out each element of the list, so I removed all that and just looping through the column list..


<table>
  <tr>
<cfloop index="CurColName" list="#GetPartAvailableOptions.columnList#">
   <cfquery name="partOptions" dbtype="query">
     select #CurColName# as theOption FROM GetPartAvailableOptions where #CurColName# <> 0
   </cfquery>
   <cfif partOptions.recordcount>
     <td>
        <table border = '1'>
        <tr>
          <th><cfoutput>#CurColName#</cfoutput></th>
        </tr>
        <cfoutput query="partOptions">
          <tr>
           <td>#partOptions.theOption#</td>
          </tr>
        </cfoutput>
        </table>
   </td>
   </cfif>
</cfloop>
  </tr>
</table>

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Kamilek0617Author Commented:
Error Occurred While Processing Request
Element COLUMNLIST is undefined in GETPARTAVAILABLEOPTIONS.
0
 
gdemariaCommented:
is that the name of your query?  GetPartAvailableOptions?

0
 
Kamilek0617Author Commented:
Here is exactly what I now have

<cfquery name="GetOptions" datasource="MyData">

 select  distinct
    *
   
from apps

PartType = '11614'

</cfquery>




<cfquery name="GetPartOptions" dbtype="query">
            select * from GetOptions where part = 'W0133-1715303'
</cfquery>

<cfset curcolHeaderNames = ArrayToList(GetPartOptions.getColumnList())/>

<cfset PartOptionsArray = ListToArray(curcolHeaderNames, ",")>

<cfloop index="x" from="1" to="#arrayLen(PartOptionsArray)#">
<cfset CurColName = "#trim(PartOptionsArray[x])#">



<cfquery name="partOptions" dbtype="query">
            select   #CurColName# FROM GetPartOptions where #CurColName# <> 0
</cfquery>


<cfif #partOptions.recordcount# GTE 1>

<table>
  <tr>
<cfloop index="CurColName" list="#GetPartOptions.columnList#">
   <cfquery name="partOptions" dbtype="query">
     select #CurColName# as theOption FROM GetPartOptions where #CurColName# <> 0
   </cfquery>
   <cfif partOptions.recordcount>
     <td>
        <table border = '1'>
        <tr>
          <th><cfoutput>#CurColName#</cfoutput></th>
        </tr>
        <cfoutput query="partOptions">
          <tr>
           <td>#partOptions.theOption#</td>
          </tr>
        </cfoutput>
        </table>
   </td>
   </cfif>
</cfloop>
  </tr>
</table>

</cfif>
</cfloop>
0
 
gdemariaCommented:
I don't understand why columnList is not available in the query name, is the CF 9 perhaps?

In any case, I switched it to use the array function that you were using.

I removed the redundant code from your example...

This is the full code that matches what you posted..

<cfquery name="GetOptions" datasource="MyData">
 select  distinct * from apps where PartType = '11614'
</cfquery>

<cfquery name="GetPartOptions" dbtype="query">
  select * from GetOptions where part = 'W0133-1715303'
</cfquery>

<cfset curcolHeaderNames = ArrayToList(GetPartOptions.getColumnList())/>


<table>
  <tr>
<cfloop index="CurColName" list="#curcolHeaderNames#">
   <cfquery name="partOptions" dbtype="query">
     select #CurColName# as theOption FROM GetPartOptions where #CurColName# <> 0
   </cfquery>
   <cfif partOptions.recordcount>
     <td>
        <table border = '1'>
        <tr>
          <th><cfoutput>#CurColName#</cfoutput></th>
        </tr>
        <cfoutput query="partOptions">
          <tr>
           <td>#partOptions.theOption#</td>
          </tr>
        </cfoutput>
        </table>
   </td>
   </cfif>
</cfloop>
  </tr>
</table>

Open in new window

0
 
Kamilek0617Author Commented:
Yes, it is CF 9.

Will test in an hour and get back to you.
0
 
_agx_Commented:
Guys - Isn't the limit for questions on EE 500 pts? This same question is posted twice making it 1000pts

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_25649706.html

0
 
Kamilek0617Author Commented:
It somewhat works, but not really.  The table columns, really only 2 are relevant in this example out of the 80+ are very far apart, and I couldn't get them closer together even formating them to be at the same hight.  I think maybe its creating extra spaces for the columns that are blank?
0
 
gdemariaCommented:
Hmmm, not sure how that's happening.  You're saying there is space between going across between the columns?  

First, let's see if the right columns are being considered.  Can you put this before your table

<cfoutput>Using Columns: #curcolHeaderNames#</cfoutput>


Also,  put border="1" on the outer table to see if the table is just stretching.   Perhaps adding a column width would do it if that's the case..


<cfoutput>Using Columns: #curcolHeaderNames#</cfoutput>   <==== see if the right columns are fetched
<table border="1">  <===== add border
  <tr>
<cfloop index="CurColName" list="#curcolHeaderNames#">
   <cfquery name="partOptions" dbtype="query">
.... etc....




This CFIF statement should keep any unnecessary columns from drawing..

   <cfif partOptions.recordcount>


Can you show a screen shot of your output (with border="1" and column display in place)

I need to get a better sense what what's happening
0
 
Kamilek0617Author Commented:
OK, I see what it's doing.  Its creating one large table and within that, smaller tables, which is not what I wanted.  I just need one table with the column names on top and the options below that.  
0
 
gdemariaCommented:
> Its creating one large table and within that, smaller tables, which is not what I wanted
What you wanted is to display the values across, that's how to do it.

If you don't do the nested table, then you need to restructure how you're fetching the data into a single query.   The problem is that you are doing a query every time you loop, then you display the results from that query and move to the next column.  

Without the nested table, you have to fetch the first record from every query to display across the table.   Then, go to the next row and display the second record from every query...   the only way to do that is to make ONE query.

 Color    Material
 C-Rec1     M-Rec1....etc....

then the next row...
 C-Rec2     M-Rec2.... etc....

If you want to redo the queries, that's fine.   If not, we can delve into how to clean up what we have so far.  Just show what it looks like.

0
 
Kamilek0617Author Commented:
I don't know any other way to write the queries.
0
 
gdemariaCommented:
If not, we can delve into how to clean up what we have so far.  Just show what it looks like.
0
 
Kamilek0617Author Commented:
OK so I have this:

<cfset curcolHeaderNames = ArrayToList(GetPartOptions.getColumnList())/>


<table border="1">
<tr>
<cfloop index="CurColName" list="#curcolHeaderNames#">
<cfquery name="partOptions" dbtype="query">
select #CurColName# as theOption FROM GetPartOptions where #CurColName# <> 0
</cfquery>
<cfif partOptions.recordcount>
     <td valign="top" width="1">
        <table border = '0'>
        <tr>
          <th><cfoutput>#CurColName#</cfoutput></th>
        </tr>
        <cfoutput query="partOptions">
          <tr>
           <td valign="top">#partOptions.theOption#</td>
          </tr>
        </cfoutput>
        </table>
   </td>
</cfif>
</cfloop>
</tr>
</table>

And it looks like the attached.  I want it to look like 1 regular table.


table.gif
0
 
gdemariaCommented:
hmmm, something is wrong with the valign="top"

Can you play with that a bit?

That should have all inner tables align at the top..
Perhaps turn on the border of the inner table to see what's happening

Remove width="1" for now, that's too narrow and may be a factor

  <td valign="top">
       <table border = "1">

0
 
Kamilek0617Author Commented:
Its not gonna work.  I need a clear way to output the data into one table.  In this example I have only 2 columns and each has only two options, which is easy, but in another example I have 17 columns each ranging from 1 to 9 options, and than one option applies to only certain other options.  I need to think of something different here.
0
 
gdemariaCommented:
> I have 17 columns each ranging from 1 to 9 options

This is all the more reason to do it this way, the easy way.

See the images below.   With the one table design, you must draw each row completely before moving on to the other row.   One row consists of the first record of Part A, Part B, Part C, etc..

Then you have to draw the second row which is the second record from the part options query.  To do this, you need to requery the part options for every cell in your table and fetch the 2nd record for the entire second row and then when drawing the third row, fetch the third record each time from your part options query, etc..

When you get to the third record (in my example) Col A and B don't have a third record, so you have to draw empty table cells until you get to Col C, then more empty cells until you get to Col G.

This is entirely do-able, but I just want you to be aware of the steps.   First, you have to rewrite the queries so they are either (a) all in one query or (b) a different query for each part or (c) loop over the queries and save the output into a structure before starting to draw the table

This is no fine to do, but your reason for doing it is a little light, you just need to align the data within the cells by having a consistent height for the rows and vertically aligning them.

Unless I misunderstand your objective, do the picture reflect what you want to accomplish?
down-the-across.png
empty-cells.png
0
 
Kamilek0617Author Commented:
Ok, the main reason I am doing this is because if the page is displaying many items that match the criteria selected, on top you see all the available options, so you can click on one and narrow down the results.  So the first query:

<cfquery name="GetAvailableOptions" datasource="mydata">
select * from mytable
</cfquery>

This gets me something as the attached.  You will see that it some columns are empty and therefore irrelevant.  

I can then check each item by running a distinct on the column.  So for example:

<cfquery name="options" dbtype="query">
select distinct option1 from GetAvailableOptions where option1 is not null
</cfquery>

If you look at Example.jpg this allows me to put the info on top.  Like the colors and the style.  Basically what I am doing here is if the option query has more than one result, it means it has options therefore I display the column name and the options next to it as links.  Instead of having the write query separately for each column of the first table, I am looping the column names and the query inside them.  So the top of the page part works perfectly.  No problems with it at all.  All works.

The second part is that under each item I want to, or in this case have to, put a table with all the options for that single part.  So what I do is:

<cfquery name="GetItemPptions" datasource="mydata">
select * from mytable where partnumber = '#partnumber#' and brandID = '#BrandID#'
</cfquery>

So let now, look at my first table output, and let's assume I want to display the options, or features, or whatever you want to call them for part number 444.  I would only have 3 relevant columns.  Option2, Option4 and Option12.  And I need to display the data as attached in the Singlepart.gif.  I can't move all the info into one row, it has to be in separate rows.  Don't ask me why, but it has to be that way.  

If you can suggest how to structure the queries differently, or just how to output the date differently it would be awesome.  Again, I have the entire top section working exactly right.  It's the small, single part table that I can't get working.
QueryExample.gif
example.jpg
SinglePart.gif
0
 
Kamilek0617Author Commented:
I guess what I basically need to do is find out which columns are not all null for a specific part number.  Once I have that info I can run a query to get only those columns from the first query and I have my table.  Just don't know how to do that.
0
 
gdemariaCommented:
Thanks for the extended description, that helped a lot.  As you can see I was thinking of it a bit differently.   I am thinking about the query you need.

Does a part number correspond to one image in your example gif?   So, that means under each pair of pants you will draw a little table shown in "SinglePart" ?
0
 
Kamilek0617Author Commented:
Exactly.
0
 
gdemariaCommented:
Ok, consider this solution - hope you can adjust the table, column names and the where clause

The problem is that you need just a list of columns that have something in it.  But the query will return all columns in the select clause regardless, the where clause limits the records, but not the columns.

So this query will return 1 record total with every column.   Each column will be 0 or 1+
The number will match the number of non-null values.

Then in order to get a list of columns with non-null values, I added a CFLOOP that loops over all columns and makes a simple list of the columns that have a value > 0

so the variable availableOptions should have a list of the column names that have a value



<cfquery name="getAvailableOptions" datasource="xxxx">
  select  sum(case when Color is null then 0 else 1 end) as color
       ,  sum(case when Style is null then 0 else 1 end) as Style 
       ,  sum(case when Type  is null then 0 else 1 end) as Type 
       ,  sum(case when Material  is null then 0 else 1 end) as Material 
  from myTable 
   where category = '#category#' and  partname = '#partname#'
</cfquery>

<cfset availableOptions = "">
<cfloop index="aCol" list="#arrayToList(getAvailableOptions.getColumnList())#">
  <cfif getAvailableOptions[aCol][1] gte 1>
    <cfset availableOptions = listAppend(availableOptions, aCol)>
  </cfif>
</cfloop>

Open in new window

0
 
Kamilek0617Author Commented:
Giving me a syntax error for some reason in Query Analyzer
0
 
gdemariaCommented:
in case there is a simple type-o, you could start with selecting only one colum with the sum() line and see if you can narrow in on the problem
0
 
Kamilek0617Author Commented:
Had to modify it a bit, but your concept is right and solved it.  
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 13
  • 12
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now