how to approach this problem

Report · Apr 19, 2009

I am displaying a dynamic table which consits of four columns named as Ingredient,Lot,d_amount,d_unit......out of which Ingredient,d_amount,d_unit are taken from one

query named as "qrecipes" i.e from one table and Lot Cloumn is taken from another table.Now the problem is the data that is to be displayed in lot column is dependent on the

data present in ingredient.i.e If the first row ingredient column has "XYZ" then i need to pick the id of "XYZ" from second table and display in lot column.

Ingredient Lot d_amount d_unit

        1   xyx       345 is id
                           for XYZ which
                          should be
            displayed from
            Second table

Below is my code ....please say what is wrong .....please suggest me if there are any other methods or any kind of solution to solve this problem

Queirs

<cfquery name="qrecipes" datasource="testmsexcell">
SELECT *
FROM Recipes WHERE product='#form.product#'

</cfquery>

<cfquery name="qlot" datasource="testmsexcell">
SELECT Lots.id
FROM Lots WHERE ingredient='#qrecipes.ingredient#'
</cfquery>

Table

                               table border="2px" id="maketable">
                                    <tr>

                                      <td>Ingredient</td>
                                      <td>Lot</td>
                                      <td>d_amount</td>
                                      <td>d_unit</td>
                                    </tr>
                                    <cfoutput query="qrecipes">
                                      <tr>

                                        <td>#qrecipes.ingredient#</td>
                                        <td><cfselect name="lotid" id="lotid" query="qlot" value="id" display="id"></cfselect></td>
                                        <td>#qrecipes.d_amount#</td>
                                        <td>#qrecipes.d_unit#</td>
                                      </tr>
                                    </cfoutput>
                                  </table>

Report · Apr 20, 2009

Combine the two queries into one by joining the tables. If you don't know how, I've heard good things about the book "Teach Yourself SQL in 10 Minutes" by Ben Forta.

Report · Apr 20, 2009

Thanks. for the reply.....but i tried a lot and i was unsucesful.below are my table.....can you please say the sql query

i have kept those dots to have a table appreance...neglect those dots

First Table is Recipes

Product...........ingredient.........d_amount.......... d_unit id
abc................xyz...............78................... lb.....
....................uvw...........................................

efg..................hig
......................lkm

In the first table the id is the primarykey which is autoincremented.

Second Table is Lot:The id is primarykey which is not related to first table which is recipes.

id.............ingredient

5t6y.............xyz
7yh8.............xyz
9jnu.............uvw

Now i am trying to diaplay to user something like this by taking product as input from the user.if the user selects product as "abc"

ingredient........lotids............d_amount.......d_unit

xyz...............5t6y
...................7yh8

uvw.................9jnu

I am able to display the ingredient,d_amount,d_unit correctly but i am unable to display the correct lotids for the repective ingredient.I am using CFSELECT tag to display the lotids

Report · Apr 20, 2009

Report · Apr 20, 2009

SQL 101.  You'll really want to learn the concepts shown here.  They are 
fundamental to relational database usage.



<cfquery name="myTwoTables" datasource="myDSN">

   SELECT

      rec.product,

      rec.ingredient,

      rec.d_amount,

      rec.d_unit_id,

      lot.id

  
   FROM

      recipes rec INNER JOIN

      lot ON (rec.ingredient = lot.ingredient)



   WHERE

      rec.product = <cfqueryparam value="#form.ProductSelect#" 
cfsqltype="cf_sql_varchar">

</cfquery>



<cfdump var="#myTwoTables#">



Be sure to look up and understand anything new to you in this code.  It 
demonstrates several fundamentals of ANSI SQL syntax and CF Query best 
practices.

Report · Apr 20, 2009

Thanks the given sql is working ...it gave me correct data ..but now the problem is that it is displaying same data multipile times.i.e

ingredient........lotids............d_amount.......d_unit

xyz...............5t6y
xyz................7yh8
xyz................98uj

uvw.................9jnu

instead of that i need to display it as

xyz...............5t6y
..................7yh8
...................98uj

uvw.................9jnu

Report · Apr 20, 2009

Thanks to everyone.........Hay I got the solution.....i tried in this way and it gave me correct answer.....

Query for first table

<cfquery name="qrecipes" datasource="testmsexcell">
SELECT *
FROM Recipes WHERE product='cream'

</cfquery>

dispalying table and query for second table

<table>
<tr>
    <td>product</td>
    <td>Lot</td>
    <td>d_amount</td>
    <td>d_unit</td>

</tr>
<cfoutput query="qrecipes">
    <tr>
      <td>#qrecipes.ingredient#</td>
      <td><cfquery name="qlot" datasource="testmsexcell">
SELECT Lots.id From Lots WHERE Lots.active=true AND Lots.ingredient='#qrecipes.ingredient#'
</cfquery><cfselect name="testing" id="testing" query="qlot" value="id" display="id"></cfselect></td>
      <td>#qrecipes.d_amount#</td>
      <td>#qrecipes.d_unit#</td>

    </tr>
</cfoutput>
</table>

Report · Apr 21, 2009

nagkar wrote:
Thanks to everyone.........Hay I got the solution.....i tried in this way and it gave me correct answer.....

That does work but is generally considered a poor way to do so. It involves many unnecessary database calls per request which can add up and cause performance problems in a high use web site. Granted 90% of web application do not need to worry about preformance issues like this, but it is good to know when it does matter.

One problem is that the better soluting does not lend itself to using the <cfselect...> shortcut and requires you to code your own select control, but it is really not that difficult.

<cfquery name="myTwoTables" datasource="myDSN">
   SELECT
      rec.product,
      rec.ingredient,
      rec.d_amount,
      rec.d_unit_id,
      lot.id
  
   FROM
      recipes rec INNER JOIN
      lot ON (rec.ingredient = lot.ingredient)

   WHERE
      rec.product = <cfqueryparam value="#form.ProductSelect#" cfsqltype="cf_sql_varchar">

   ORDER BY
     rec.product
</cfquery>

<table>
<cfoutput query="myTwoTables" group="product">
   <tr>
     <td>#ingredient#</td>
     <td>
       <select name="testing">
          <cfoutput>
            <option valut="#id#">#id#</option>
          </cfoutput>
       </select>
     </td>
     <td>#d_amount#</td>
     <td>#d_unit#</td>
   </tr>
</cfoutput>
</table

This version will produce the exact same results as yours with a single database query rather then a database query*number of ingredients plus one. Which can be a signicantly faster and uses much less resources.