?
Solved

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

Posted on 2006-03-29
26
Medium Priority
?
382 Views
Last Modified: 2008-03-03
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
0
Comment
Question by:Navgod
  • 12
  • 12
  • 2
26 Comments
 
LVL 11

Expert Comment

by:Isisagate
ID: 16321793
you can add a  "group by pck.productCode" to the end of the statement if all the records are the same...
0
 
LVL 11

Expert Comment

by:Isisagate
ID: 16321800
Select * FROM tblProducts pdt
left join tblProduct_Pack pck
ON pdt.ProductCode = pck.productCode
group by pck.productCode
0
 

Author Comment

by:Navgod
ID: 16321977
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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
LVL 6

Expert Comment

by:campbelc
ID: 16322206
Maybe this would be better addressed in your application? What language are you using for this application?
0
 

Author Comment

by:Navgod
ID: 16322217
I an using Coldfusion and i have been tryin this for a while now with no avail.
0
 
LVL 6

Expert Comment

by:campbelc
ID: 16322258
Bah, can do this in two seconds for you in Coldfusion.. =) One sec. Let me start up my Coldfusion server to test code..
0
 
LVL 6

Expert Comment

by:campbelc
ID: 16322268
Sorry missed that reference up above. =(
0
 

Author Comment

by:Navgod
ID: 16322274
no probs
0
 
LVL 6

Expert Comment

by:campbelc
ID: 16322294
Are you performing a search for the Product then displaying the packing information or showing all records at once?
0
 

Author Comment

by:Navgod
ID: 16322307
showing all records at one on the same page with a next and previous button to move between the products.
0
 
LVL 6

Expert Comment

by:campbelc
ID: 16323054
Sorry, my mysql server is having fits. Be just a second..
0
 
LVL 6

Expert Comment

by:campbelc
ID: 16324154
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
 

Author Comment

by:Navgod
ID: 16324540
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
 
LVL 6

Expert Comment

by:campbelc
ID: 16324582
Sure just want navigation buttons added then?
0
 

Author Comment

by:Navgod
ID: 16324640
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
 
LVL 6

Expert Comment

by:campbelc
ID: 16324697
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
 

Author Comment

by:Navgod
ID: 16324752
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
 
LVL 6

Expert Comment

by:campbelc
ID: 16324768
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
 

Author Comment

by:Navgod
ID: 16324846
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
 
LVL 6

Expert Comment

by:campbelc
ID: 16325492
I'm in EST time zone. -5 GMT. Available 9 am - 5pm. Had a meeting, finishing up the prev/next buttons.
0
 
LVL 6

Accepted Solution

by:
campbelc earned 2000 total points
ID: 16325981
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
 

Author Comment

by:Navgod
ID: 16327088
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
 

Author Comment

by:Navgod
ID: 16328070
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
 

Author Comment

by:Navgod
ID: 16328319
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
 
LVL 6

Expert Comment

by:campbelc
ID: 16328449
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
 

Author Comment

by:Navgod
ID: 16328932
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question