in reply to Joining Tables Problem

Oh my that's a lot of code. If I understand your question correctly then you've really out-done yourself. All the popular databases I'm aware of have JOIN statements (that link is the mysql version, but I'm certain the same principles apply for most databases)

The moral: when trying to manipulate database stuff, check your databases docs first! It will save you lots of time (yes I've learned that the hard-way)

As an extra here's a start on what you are trying to do. This *should* (untested) get all of your ID's and their coorisponding colors, one entry per color, so you'll have multple IDs that are the same (hint you can use that to put together lists to build your pop-up with). Anyway here's the query:

SELECT item_id, color FROM catalog_pet
   LEFT JOIN colors USING (item_id);

[download]

Hope that helps
Chris

Lobster Aliens Are attacking the world!

Replies are listed 'Best First'.
Re: Re: Joining Tables Problem
by b310 (Scribe) on Feb 17, 2003 at 20:10 UTC
    Hi, Thanks for your input. I tried using your LEFT JOIN query and I'm still receiving the same results. If the item has two colors, it appears twice, each with a different color.
[reply]

      That's how joins work.

      You can solve this in two different ways. First, select only the items. Run a query for each item to fetch the available colors. The other option is to continue with your single query, making sure the results are returned and sorted on the item id. Push the available colors onto an array for each item id -- you'll end up with a hash of arrays of colors.

      I'd go for the second solution.

[reply]

      Yes, you're outputting a new line for every line of the return from the Database query. What you probably want to do is one of two things:

      • Go over $ref once you have it and copy it into a different hash, but instead of just saving the colour to $ref->{'color'}, make $ref->{'color'} an arrayref, then you can push new colours onto it every time you iterate over a row with the same item_id.
      • Omit colour information from your initial query. Then, for each iteration over $ref, run another query like 'SELECT * FROM colors WHERE colors.item_id = '.$ref->{'item_id'} and push the results onto $ref->{'color'} as an arrayref.

      Whichever of these two you use, you can then pass $ref->{'color'} to CGI like: 

      popup_menu( -name => "color", -values => $ref->{color})

      [download]

      Note that I haven't used CGI's drawing functions much, so double-check the syntax of popup_menu() before testing this out.

      Update: changed stuff to the american (mis)spelling of colour.

      LAI
      :eof

[reply]
[d/l]
[select]
        Hi Lai,

        I'm trying on my own to figure out the array bit. I have no idea if I'm on the right track.

        I modified my query section in the shop_parameters subroutine which I included in my original node.

        Can you look at this code and tell me if I'm on the right track?

        Here's the code...
        $sth = $dbh->prepare ("SELECT * FROM catalog_pet, item $where ORDER BY
+ catalog_pet.product_id $limit");
               
 my @colors =();
                while (my $ref = $sth->fetchrow_hashref())
                {
                my %color = (
                  'item_id' => $ref->{'item_id'},
                  'color' => $ref->{'color'}
                 );
                push @{$colors{$ref->{'item_id'}}}, \%color;
               }

    $sth->execute (@placeholder);
    $page = get_product_table ($sth);

        [download]


        Thanks.
[reply]
[d/l]
        Hi Lai, I'm very new to Perl with the database programming. In theory, I understand completely what your saying.

        I could use your help in putting your ideas into actual code since I haven't had a need to write an array yet.

        Any chance you can help me out? Thanks.
[reply]

      chromatic and LAI already explained what you're doing and what the possibilities are.
      I started this node, and posted it by accident , while experimenting with a temporary table, to find out that this wouldn't lower the data transfer between your CGI and the DB (au contraire ;))

      So this is basically a useless node :(

      regards,
      tomte

[reply]

In Section Seekers of Perl Wisdom