[ic] $db-->query inner join

[Sam Batschelet]

On 3/10/09 1:05 PM, "Davor Ocelic" <docelic at spinlocksolutions.com> wrote:

> On Tue, 10 Mar 2009 13:53:42 -0500
> Sam Batschelet <sam at westbranchresort.com> wrote:
> 
>> 
>> 
>> 
>> On 3/10/09 12:18 PM, "Gert van der Spoel" <gert at 3edge.com> wrote:
>> 
>>>> On 3/10/09 10:35 AM, "Stefan Hornburg" <racke at linuxia.de> wrote:
>>>> 
>>>>> Sam Batschelet wrote:
>>>>>> 
>>>>>> 
>>>>>> On 3/10/09 10:14 AM, "jimbo" <jimbo at soundimages.co.uk> wrote:
>>>>>> 
>>>>>>>> my $q = $db->query({
>>>>>>>> 
>>>>>>>> sql => 'SELECT sum(quantity) FROM inventory INNER JOIN variants
>>>> WHERE
>>>>>>>> inventory.sku = variants.code and variants.sku=$sku',
>>>>>>>> 
>>>>>>>> });
>>>>>>> Did you make sure to open the tables in the [perl] tag?
>>>>>>> 
>>>>>>> [perl tables="inventory variants"][/perl]
>>>>>>> 
>>>>>>> jimbo
>>>>>>> ;-)>
>>>>>>> 
>>>>>> I did it this way is this wrong?  I am trying to not use ITL in
>>>>>> this
>>>> script
>>>>>> if possible.
>>>>>> 
>>>>>> my $db = $db{inventory, variants};
>>>>> 
>>>>> $Tag->perl({tables => 'inventory'});
>>>>> 
>>>>> $db = $Db{inventory} # note capital D
>>>>> 
>>>>> would work if both tables share the same DBI connection.
>>>>> 
>>>>> Regards
>>>>> Racke
>>>> Thanks Racke seems to be running the query just curious why I am
>>>> getting
>>>> back an Array is this by design?
>>> 
>>> 
>>> An array reference, by design yes:
>>> http://www.interchange.rtfm.info/icdocs/tags/query.html
>>> 
>>> 
>> But according to the docs this is not a default setting an array
>> needs to be set with wantarray => 1, all I want is to return the
>> final value because its only 1 record/value.  Does it matter that I
>> am using $db->query() vs $Tag->query() that this is default?
> 
> Yeah, but in any case you can easily extract the final value.
> 
> I do it this way (elegant Perl syntax, but place parentheses exactly
> as shown):
> 
> my $val= ( $Db->query(....))[0][0];
> 
Looks good but now results_grid is giving me this error "subroutine
show_product missing for PREFIX-exec".  But I have not changed anything else
on the page ideas?

$Tag->perl({tables => 'inventory'});

my $db = $Db{inventory};

my $sql = "SELECT sum(quantity) FROM inventory INNER JOIN variants WHERE
inventory.sku = variants.code and variants.sku='$sku'";


my $q= ( $db->query($sql))[0][0];

    if ($q > 0) {
        $record->{stock} = <<EOF;
<span style="color: #008000;
font-size:10px; font-family: san-serif; font-weight:
bold;">[L]Yes[/L]</span>
EOF
    }
    else {
        my $url =
$Tag->area('function/stock_alert', $sku);
        $record->{stock} =
<<EOF;
<a href="$url" title="[L]Click here to pre-order this item or get
notified as soon as this item is available again.[/L]" style="color:
#FF0000; font-size:10px; font-family: san-serif; font-weight:
bold;">[L]No[/L]</a>
EOF
    }