Skip to main content
D
Dax_Trajero
Inspiring
May 26, 2011
Question

transfer data from one type of table to another

  • May 26, 2011
  • 3 replies
  • 1428 views

Can some kind soul put me out of my misery and explain how I can get the data from Table1 to Table2

I'm really struggling!

Table1

MerchIDMatchesSKU
1BR34
1BD16
2DE11
3HB12
3DX15
3DR19

Table2

MerchIDMatchesSKU1MatchesSKU2MatchesSKU3
1BR34BD16
2DE11
3HB12DX15DR19
    This topic has been closed for replies.

    3 replies

    A
    Anonymous
    May 27, 2011

    @Dax,

    You may also help your table performance by using optimize table:

    OPTIMIZE TABLE Syntax

    OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...


    <cfwild />
    A
    Anonymous
    May 27, 2011

    @Dax,

    Direct SQL approach:

    SELECT table1.MatchesSKU

    FROM table1

    WHERE table1.MerchID = '#URL.ProductID#'

    Other thoughts:

    Use a cfqueryparam tag with your URL.ProductID. No SQL injection please.

    Make sure your db table has an index on MerchID.

    I agree with Dan on his TOP N comments; however, if you're only holding 3 values, why include the overhead of MAXROWS, eliminate this from the tag.

    Good Luck!

    <cfwild />

    D
    Dax_TrajeroAuthor
    Inspiring
    May 27, 2011

    Thanks guys

    As for how long the query takes - there's a noticeable pause of 2 or more secs while the page loads. I started a process of elimination and pin-pointed the 'matches with' SQL as the culprit.

    Think I'll stick with Table1 and try and optimise it more, using Dan's suggestion of TOP N. I'm running MySQL so that's fine.

    CFWIld

    Think I'm messing up with my code here:

    SELECT table1.MatchesSKU

    FROM table1

    WHERE table1.MerchID = '#URL.ProductID#'

    If I'm only looking for one unique row, should the WHERE line read...

    WHERE table1.<primary key field> = '#URL.whatever#'

    (MerchID currently isn't the primary key, or indexed)

    D
    Dan_Bracuk
    Inspiring
    May 26, 2011

    Table 2 looks like a really bad idea.  What are you trying to accomplish that you can't do with Table 1?

    D
    Dax_TrajeroAuthor
    Inspiring
    May 26, 2011

    I've got a page which shows a piece of merchandise in detail. Below that is a section called "you may also like..." and it shows 3 matching items to the main merchandise.

    I currently hold the matching data in a table like Table1 but I've been getting some performance issues and have narrowed it down to the query that gets the 3 matches.

    I was thinking if I place the 3 matching merchID's into the corresponding row of the main merchandise being shown, it saves hitting the database for the 3 matching items?

    Here's the SQL used to find the matching items

              SELECT *

              FROM merchandise

              INNER JOIN matches_with

              ON merchandise.supplier_code = matches_with.matches_with_supplier_code

              WHERE matches_with.merchandise_ID = '#URL.ProductID#'

    I limit the CFQUERY to MAXROWS=3

    D
    Dan_Bracuk
    Inspiring
    May 26, 2011

    How long is that query taking to run?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices