Mysql two tables, one unique field but overlapping data on query.. please help!

Hello there,

I have two normalised tables, The parent table contains Product Details with Product Code as the Primary field. The child table contains data for the packaging information of the products in table 1 and the records are made unique once again by the Product Code.

The Problem

I am Using Coldfusion and this MYsql setup to query data for updating/editing etc.. The problem i am having is that due to the multiple records of each product in table 1 the query repeats this data on the screen. I was wondering if there is a method or query which i can use to join the tables in a way that only one record from table 1 is shown with its respective multiple records from table 2.

Query i am using at the moment:

Select * FROM tblProducts pdt
join tblProduct_Pack pck
ON pdt.ProductCode = pck.productCode



Previously using MS access this is very easy by joining the tables on a one-to-many relationship but using mysql i am having a difficult time organising the data.

All help will be much appreciated.

Thank you,
Navgod
NavgodAsked:
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.

IsisagateCommented:
you can add a  "group by pck.productCode" to the end of the statement if all the records are the same...
0
IsisagateCommented:
Select * FROM tblProducts pdt
left join tblProduct_Pack pck
ON pdt.ProductCode = pck.productCode
group by pck.productCode
0
NavgodAuthor Commented:
Hi Isisagate,

This is the table structure with some data:

Table 1

Productcode | Description |Type | Source | Destination|
700038        |EXPERT      |GT    | LOL  | HY            |  

Table 2

Productcode |Date_From| Date_To|Size|
70038          |2003/01    |2003/12 |52   |
70038          |2004/01    |2004/12 |45   |


Now what i want to display after quering is this:

Product Code: 70038    Description: EXPERT      Type: GT   Source: LOL    Destination: HY


Date_From                Date_To                  Size
2003/01                    2003/12                  52
2004/01                    2004/12                  45

BUT what i get with the query is that the table 1 data is repeated for every record it corresponds to on table 2 as follows

Product Code: 70038    Description: EXPERT      Type: GT   Source: LOL    Destination: HY
Product Code: 70038    Description: EXPERT      Type: GT   Source: LOL    Destination: HY

Date_From                Date_To                  Size
2003/01                    2003/12                  52
2004/01                    2004/12                  45

Is there a way to modify the query that it only shows table 1's data once while still showing all data from table 2?

I hope i have cleared stuff up.

Thanks,
Navgod
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

campbelcCommented:
Maybe this would be better addressed in your application? What language are you using for this application?
0
NavgodAuthor Commented:
I an using Coldfusion and i have been tryin this for a while now with no avail.
0
campbelcCommented:
Bah, can do this in two seconds for you in Coldfusion.. =) One sec. Let me start up my Coldfusion server to test code..
0
campbelcCommented:
Sorry missed that reference up above. =(
0
NavgodAuthor Commented:
no probs
0
campbelcCommented:
Are you performing a search for the Product then displaying the packing information or showing all records at once?
0
NavgodAuthor Commented:
showing all records at one on the same page with a next and previous button to move between the products.
0
campbelcCommented:
Sorry, my mysql server is having fits. Be just a second..
0
campbelcCommented:
Very quick mock-up, but does this work for you?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<cfquery name="get_products" datasource="local_mysql">
  select * from table1
</cfquery>

<cfoutput query="get_products">

  <cfset currentproduct = #productcode#>

  <cfquery  name="get_shipping" datasource="local_mysql">
    select * from table2 where productcode = '#productcode#'
  </cfquery>
  <cfparam name="lastproduct" default="">
  <cfif currentproduct is not lastproduct>
  <table width="80%" cellspacing="1" cellpadding="3" bgcolor="##cccccc">
    <tr bgcolor="##19569B" style="color:##FFFFFF;font-weight:bold">
      <td>Product Code:</td>
      <td>#ProductCode#</td>
      <td>Description:</td>
      <td>#Description#</td>
      <td>Type:</td>
      <td>#Type#</td>
      <td>Source:</td>
      <td>#Source#</td>
      <td>Destination</td>
      <td>#Destination#</td>
    </tr>
  </table>  
  </cfif>
 

  <table width="80%" cellspacing="1" cellpadding="3" bgcolor="cccccc">
  <cfloop query="get_shipping">
    <tr bgcolor="<cfif currentrow mod 2>##FFFFFF<cfelse>##E6E6E6</cfif>">
      <td>DateForm:</td>
      <td>#Date_From#</td>
      <td>DateTo:</td>
      <td>#Date_To#</td>
      <td>Size:</td>
      <td>#Size#</td>
    </tr>  
   </cfloop>
   </table>

<cfset lastproduct = #productcode#>
<br /><br />
</cfoutput>
</body>
</html>
0
NavgodAuthor Commented:
Hey campbelc,

Thats very good, but the code shows all the records from the table on the same page. I am trying to make it dynamic by restrcting the max rows to = 1 and making the data within the table dynamic by having a next and previous button to move between records but cant seem to get it to work. I am also using COUNT so that it shows as a url to confirm the number of record.

Can you suggest how i can change the code to implement this?

Thanks soo much.

Nav
0
campbelcCommented:
Sure just want navigation buttons added then?
0
NavgodAuthor Commented:
Yes for now atleast before i move on to more harder things i need to implement in CF.

The table that is showing the data is fine, the botton row being dynamic is purfect as there are many records to display for each of the products on the top row.

But i only need one product to be displayed at a certain time and its details therefore navigation is mandatory.

I will be adding various functionalities once this is done i.e Add new Product, update Product Details, Add new Product Details etc.. but all that will be done tmr.

Thanks for all your help.

Nav
0
campbelcCommented:
Ok, no problem. How do you find the first record to display, via a search or you want the display to pick the lowest number ID first then navigate through from there?
0
NavgodAuthor Commented:
Oh sorry forgot to mention it.

I did the following change in your code and it does what i want.

<cfquery name="get_products" datasource="local_mysql">
  select * from table1
  order by description asc
</cfquery>

That sorts the data just the way i want it, with the first record being the first record from table 1 sorted aphabetically via the description field.

Can i ask you any future questions regarding CF when i am stuck?

Thanks,

Nav
0
campbelcCommented:
Absolutely! I recently switched jobs where now I don't get to use Coldfusion daily, now doing more networking and firewall stuff again. REALLY miss Coldfusion. Would love to help!

Working on a solution now for you. Give me 10 mins..
0
NavgodAuthor Commented:
Great! I am working on this project and need to finish it by monday next week. I just had to join EE to solve this nagging problem that i have been having for days now. Thanks for helping me out.

I'm sure i will need your assistance at the next hurdle. During what hours can you get back to me? I am in London GMT time. This is the first time i am programming in CF, pretty useful but find it hard.. I guess i need more pracitise.

0
campbelcCommented:
I'm in EST time zone. -5 GMT. Available 9 am - 5pm. Had a meeting, finishing up the prev/next buttons.
0
campbelcCommented:
Kind of messy, but works. Try this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<cfquery name="get_products" datasource="local_mysql">
    select * from table1 order by description asc
</cfquery>

<!--- Set the default startrow to 1 if a value was not passed. --->
<!--- Determine whether or not to show the previous or next links. --->
<CFPARAM NAME = "StartRow" DEFAULT = "1">
<!--- Set the value of endrow to the maxrows + startrow - 1 --->
<CFSET EndRow = StartRow + 1 - 1>
<!--- If the end row is greater than the recordcount, determine how many records are left. --->
<CFIF EndRow GTE get_products.RecordCount>
      <CFSET EndRow = get_products.RecordCount>
      <CFSET Next = false>
<!--- Otherwise, set Next to true and determine the next set of records. --->
<CFELSE>
      <CFSET Next = true>
      <CFIF EndRow + 1 GT get_products.RecordCount>
            <CFSET NextNum = get_products.RecordCount - EndRow>
      <CFELSE>
            <CFSET NextNum =  1>
      </CFIF>
      <CFSET NextStart = EndRow + 1>
</CFIF>

<!--- If StartRow is 1, set Previous to false. --->
<CFIF StartRow IS 1>
      <CFSET Previous = false>
<!--- Othewise, determine the previous set of records. --->
<CFELSE>
      <CFSET Previous = true>
      <CFSET PreviousStart = StartRow - 1>
</CFIF>

<!--- Determine how many pages will be displayed. --->
<CFSET NumPages = Ceiling(get_products.RecordCount / 1)>
<CFPARAM NAME = "PageNum" DEFAULT = "1">

<cfoutput query="get_products" maxrows="1" startrow="#STARTROW#">

  <cfquery  name="get_shipping" datasource="local_mysql">
    select * from table2 where productcode = '#productcode#'
  </cfquery>
 
  <table align="center" width="80%" cellspacing="1" cellpadding="3" bgcolor="##cccccc">
    <tr bgcolor="##19569B" style="color:##FFFFFF;font-weight:bold">
      <td>Product Code:</td>
      <td>#ProductCode#</td>
      <td>Description:</td>
      <td>#Description#</td>
      <td>Type:</td>
      <td>#Type#</td>
      <td>Source:</td>
      <td>#Source#</td>
      <td>Destination</td>
      <td>#Destination#</td>
    </tr>
  </table>  
 
  <table align="center" width="80%" cellspacing="1" cellpadding="3" bgcolor="cccccc">
  <cfloop query="get_shipping">
    <tr bgcolor="<cfif currentrow mod 2>##FFFFFF<cfelse>##E6E6E6</cfif>">
      <td>DateForm:</td>
      <td>#Date_From#</td>
      <td>DateTo:</td>
      <td>#Date_To#</td>
      <td>Size:</td>
      <td>#Size#</td>
    </tr>  
   </cfloop>
   </table>
</cfoutput>

<TABLE align="center" width="600" BORDER = "0" cellspacing="1" cellpadding="3" bgcolor="cccccc">
      <TR bgcolor="##19569B" style="color:##FFFFFF;font-weight:bold">
            <TD VALIGN = "top" width="150">
                  <!--- If Next is true, display the previous link. --->
                        <CFOUTPUT>
                              <A HREF = "testing.cfm?StartRow=1" style="color:##FFFFFF">&lt;&lt; First Record &nbsp;</A>
                        </CFOUTPUT>
            </TD>

            <TD VALIGN = "top" width="150" style="color:##FFFFFF">
                  <!--- If Previous is true, display the previous link. --->
                  <CFIF Previous>
                        <CFOUTPUT>
                              <A HREF = "testing.cfm?StartRow=#PreviousStart#&PageNum=#DecrementValue(PageNum)#" style="color:##FFFFFF">&lt;&lt;&nbsp;Previous</A>
                        </CFOUTPUT>
                  <CFELSE>
                        <A HREF = "##" style="color:##FFFFFF" onclick="alert('This is the first record')">&lt;&lt;&nbsp;Previous</A>
                  </CFIF>
            </TD>
            <TD VALIGN = "top" width="150">
                  <!--- If Next is true, display the previous link. --->
                  <CFIF Next>
                        <CFOUTPUT>
                              <A HREF = "testing.cfm?StartRow=#NextStart#&PageNum=#IncrementValue(PageNum)#" style="color:##FFFFFF">Next&nbsp;&gt;&gt;</A>
                        </CFOUTPUT>
                  <CFELSE>
                        <A HREF = "##" style="color:##FFFFFF" onclick="alert('This is the last record')">Next Record</A>
                  </CFIF>
            </TD>
            <TD VALIGN = "top" width="150">
                        <CFOUTPUT>
                              <A HREF = "testing.cfm?StartRow=#get_products.recordcount#" style="color:##FFFFFF">Last Record&nbsp;&gt;&gt;</A>
                        </CFOUTPUT>
            </TD>

      </TR>
</TABLE>

</body>
</html>
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
NavgodAuthor Commented:
Hey!,

I am trying to use your code above but somehow it does not work, nothing appears on the page! not even an error message so i have no idea what is the problem... tried everything i could.. may be it is to do with what record to show first and that it is getting confused. the following is the code with the bits i have modified. There seems to be some crucial flaw in the CF code that it does not even generate an error message from CF.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<cfquery name="get_products" datasource="dt446">
    select * from horsham_exports order by description asc
</cfquery>

<!--- Set the default startrow to 1 if a value was not passed. --->
<!--- Determine whether or not to show the previous or next links. --->
<CFPARAM NAME = "StartRow" DEFAULT = "1">
<!--- Set the value of endrow to the maxrows + startrow - 1 --->
<CFSET EndRow = StartRow + 1 - 1>
<!--- If the end row is greater than the recordcount, determine how many records are left. --->
<CFIF EndRow GTE get_products.RecordCount>
     <CFSET EndRow = get_products.RecordCount>
     <CFSET Next = false>
<!--- Otherwise, set Next to true and determine the next set of records. --->
<CFELSE>
     <CFSET Next = true>
     <CFIF EndRow + 1 GT get_products.RecordCount>
          <CFSET NextNum = get_products.RecordCount - EndRow>
     <CFELSE>
          <CFSET NextNum =  1>
     </CFIF>
     <CFSET NextStart = EndRow + 1>
</CFIF>

<!--- If StartRow is 1, set Previous to false. --->
<CFIF StartRow IS 1>
     <CFSET Previous = false>
<!--- Othewise, determine the previous set of records. --->
<CFELSE>
     <CFSET Previous = true>
     <CFSET PreviousStart = StartRow - 1>
</CFIF>

<!--- Determine how many pages will be displayed. --->
<CFSET NumPages = Ceiling(get_products.RecordCount / 1)>
<CFPARAM NAME = "PageNum" DEFAULT = "1">

<cfoutput query="get_products" maxrows="1" startrow="#STARTROW#">

  <cfquery  name="get_shipping" datasource="dt446">
    select * from tblhorsham_exports_pack_details where productcode = '#productcode#'
  </cfquery>
 
  <table align="center" width="80%" cellspacing="1" cellpadding="3" bgcolor="##cccccc">
    <tr bgcolor="##19569B" style="color:##FFFFFF;font-weight:bold">
      <td>Product Code:</td>
      <td>#ProductCode#</td>
      <td>Description:</td>
      <td>#Description#</td>
      <td>Type:</td>
      <td>#Type#</td>
      <td>Source:</td>
      <td>#Source#</td>
      <td>Destination</td>
      <td>#Destination#</td>
    </tr>
  </table>  
 
  <table align="center" width="80%" cellspacing="1" cellpadding="3" bgcolor="cccccc">
  <cfloop query="get_shipping">
    <tr bgcolor="<cfif currentrow mod 2>##FFFFFF<cfelse>##E6E6E6</cfif>">
      <td>DateForm:</td>
      <td>#Date_From#</td>
      <td>DateTo:</td>
      <td>#Date_To#</td>
     
    </tr>  
   </cfloop>
   </table>
</cfoutput>

<TABLE align="center" width="600" BORDER = "0" cellspacing="1" cellpadding="3" bgcolor="cccccc">
     <TR bgcolor="##19569B" style="color:##FFFFFF;font-weight:bold">
          <TD VALIGN = "top" width="150">
               <!--- If Next is true, display the previous link. --->
                    <CFOUTPUT>
                         <A HREF = "EE2.cfm?StartRow=1" style="color:##FFFFFF">&lt;&lt; First Record &nbsp;</A>
                    </CFOUTPUT>
          </TD>

          <TD VALIGN = "top" width="150" style="color:##FFFFFF">
               <!--- If Previous is true, display the previous link. --->
               <CFIF Previous>
                    <CFOUTPUT>
                         <A HREF = "EE2.cfm?StartRow=#PreviousStart#&PageNum=#DecrementValue(PageNum)#" style="color:##FFFFFF">&lt;&lt;&nbsp;Previous</A>
                    </CFOUTPUT>
               <CFELSE>
                    <A HREF = "##" style="color:##FFFFFF" onclick="alert('This is the first record')">&lt;&lt;&nbsp;Previous</A>
               </CFIF>
          </TD>
          <TD VALIGN = "top" width="150">
               <!--- If Next is true, display the previous link. --->
               <CFIF Next>
                    <CFOUTPUT>
                         <A HREF = "EE2.cfm?StartRow=#NextStart#&PageNum=#IncrementValue(PageNum)#" style="color:##FFFFFF">Next&nbsp;&gt;&gt;</A>
                    </CFOUTPUT>
               <CFELSE>
                    <A HREF = "##" style="color:##FFFFFF" onclick="alert('This is the last record')">Next Record</A>
               </CFIF>
          </TD>
          <TD VALIGN = "top" width="150">
                    <CFOUTPUT>
                         <A HREF = "EE2.cfm?StartRow=#get_products.recordcount#" style="color:##FFFFFF">Last Record&nbsp;&gt;&gt;</A>
                    </CFOUTPUT>
          </TD>

     </TR>
</TABLE>

</body>
</html>
0
NavgodAuthor Commented:
Hey ignore the above message, somehow i got it work and it does work! I am jus trying to incorporate all other fields that i have and trying to make it look like my template. It is going to take long so will do it tmr. Thanks for your help campbelc.. I really appreciate it! Is there a way i can contact you before i accept your answer? You def deserve the 500 points!!

Let me know how i can get in touch with you for future CF probs.

Nav
0
NavgodAuthor Commented:
Hey!

Ok while implementing my other fields i have run into various problems..

1. The second table has 36 different fields of data that i need to display. Therefore will need a scroll on the screen within a specific area.
2. When the data is being displayed the table headings are being repeated with every new data, which adds an extra line i need to get rid off.

Is there a way i can show u a screenshot of all this working perfectly well in Ms access and thus u wud knw what i am trying to redo with CF. The necessesity for the whole page to look identical as the MS access counterpart is a must. Please inform me how i can send you this screenshot.

I will not be available today between the hours 12:00 Pm and 15:00 pm.

Thanks,

Nav
0
campbelcCommented:
Well, my only question is if my account can be suspended if I mention my email address to contact me at outside of EE.

Please post your email address and we can go from there. Best to probably create a new one with something like hotmail so you dont get your main email address spammed to death.

If you want to post a screenshot on a webserver and post the link here, along with the code you are using thus far, I can replicate it quite easily for you.

In mysql, if you have console access, type "explain your_table_name" and post the results of each table, that way my code will match up and you won't have to keep editting as you've had to do so far.

Also don't mind teaching you all about the add/edit/update/delete routines in Coldfusion as well. I'm a huge advocate of Coldfusion, so many people don't realize the full potential of this very easy web programming language. I once wrote a complete Physician Order Entry system for our medical laboratory in just 3 months. Saved our company over $100k not to mention the complete imaging system to image and index all of our test requsitions real time, a savings of round $80k. That project took just over a week.

Very powerful, but limited in a few areas as well.
0
NavgodAuthor Commented:
Hey campbelc,

Use patelnaval@gmail.com

I am off to bed now but will be online later today.. Hope to receive your email soon.

Thanks!!

Nav
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
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.