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.

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

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.

I see what you're trying to do (++ for effort!), but that's not quite it. See my previous response (to your previous response :o) for more details.

LAI
:eof

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.

Okay, a brief lesson on references: you can do anything to the array pointed at by a reference that you could do to the array itself. So, the following two snippets do exactly the same thing (one to an array, the other to a referenced array):

# create and fool around with an array
my @rray = ();
$rray[0] = "foo";
push @rray, "bar";
print $rray[1], "\n";
# prints "bar"

# create and fool around with an arrayref
my $arrayref = [];
$arrayref->[0] = "foo";
push @$arrayref, "bar";
print $arrayref->[1], "\n";
#prints "bar"
[download]

So as you can see, using an arrayref is just an indirect way of using an array, with the added benefit that the reference can be passed around like a scalar, and put into arrays and hashes, and all sorts of fun stuff like that.

So, back to your problem. You wind up with $ref (which happens to be a reference to a hash). You're already pulling out values like $ref->{item_id} and $ref->{description}, so now you need to make $ref->{color} an arrayref and iterate over it. Here's the part of your code that takes the record set and turns it into a table. The HTML looks kinda b0rked... Please forgive me if I screw it up more.

while (my $ref = $sth->fetchrow_hashref ()) {

    ### NEW CODE ###
    # Start up a new query
    $clref = $dbh->prepare ("SELECT color FROM colors WHERE item_id = 
+".$ref->{item_id});
    $clref->execute;
    # Push each new colour onto the array referenced by
    # $ref->{color}
    while (my $color = $clref->fetchrow_hashref()) {
        push @{$ref->{color}}, $color->{color};
    }
    # Now $ref->{color} is an arrayref full of colours.
    ### ### ### ###

    my $serve_url = sprintf ("serve_image.cgi?item_id=%s;picture", esc
+ape ($ref->{item_id}));  

    # generate a form allowing a quantity of the item to be added
    # to the cart
    push (@row, 
        start_form(-method=>'POST', -action=>url())),
        hidden( -name => "choice", -override => 1, -default => "add" )
+,
        hidden( -name => "item_id", -override => 1, -default => escape
+HTML($ref->{item_id})),
        Tr ({-style=>"font-family: verdana; font-size: 10pt;"},
            td (
                img ({-src => $serve_url, -alt => escapeHTML($ref->{it
+em_id})})
            ), 
            td ({-width=>"80", -valign=>"top", -align => "center"}, 
                escapeHTML( $ref->{item_id} )
            ),
            td ({-width=>"190", -valign=>"top"}, 
                escapeHTML( $ref->{description} )
            )
        ),
        Tr ({-style=>"font-family: verdana; font-size: 10pt;"}, 
            td ({-colspan => "3", -align => "right", -style=>"font-wei
+ght: 700; color:blue;"}, 
                "Price:  \$", 
                escapeHTML( 
                    sprintf( "%.2f", $ref->{price})
                )
            )
        ),
        Tr ({-style=>"font-family: verdana; font-size: 10pt;"}, 
            # Here is where you need to RTFM. At this point,
            # $ref->{color} is an arrayref. I suspect that
            # the popup_menu procedure will accept an array
            # reference as an argument, but I'm not sure.
            # Also, I'm too lazy to RTFM myself :o) In any
            # case, if you need to give it an array instead
            # you can dereference the arrayref. Check the 
            # links below for how to do that.
            td ({-colspan => "3", -align => "right"},
                "Colors:", 
                popup_menu( -name => "color", -values => $ref->{color}
+)
            )
        ),
        Tr ({-style=>"font-family: verdana; font-size: 10pt;"}, 
            td ({-colspan => "3", -align => "right"},
                "Quantity:", 
                popup_menu( -name => "quantity", -values => ["1", "2",
+ "3", "4", "5", "6", "7", "8", "9"])
            ),
            td (
                image_button( -name => "Add Item", -src=>"../images/ad
+d.jpg", -border=>"0" )
            )
        ),
        Tr ({-valign=>"top"},
            td ({-colspan=>"7", -width=>"100%"}, 
                hr
            )
        ),
        end_form()
    );
}
[download]

For more information on references and how to use them (from people who know a whole lot more than me) See:
The 'references' tutorial by busunsl
References quick reference by tye


LAI
:eof

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