[ic] (no subject)

[Kevin Walsh]

> 
> I have a problem with my slow queries. I have a group-table that contains
> skus to a users-table. I would need to pull out all existing info
> (phone-numbers, addresses etc. if any) from the members-table on each match
> from the group table.
> 
> I found the Embedded-Perl example in the icfaq
> (http://icdevgroup.org/cgi-bin/ic/docfly.html?mv_arg=icfaq14%2e02), and
> testing that method on the user-table speeded up the query dramatically. My
> problem is now that how should I combine these tables via embedded Perl. I
> think that I should first split the members skus, and after that loop each
> and compare it with each sku from the users table. I jut don't know how to
> continue.. :(
> 
> 
> This is my piece of code I have for the moment from the icfaq:
> 
>   [query arrayref=myref sql="select * from users"]
>   <!-- make query, this container text is not used. -->
>   [/query]
> [perl]
>   # Get the query results, has multiple fields
>   my $ary = $Tmp->{myref};
>   my $out = '';
>     foreach $line (@$ary)
>       {
>         my ($sku, $name, $address) = @$line;
>         if($sku eq $Scratch->{waiting_for})
>           {
>             $out .= "sku: $sku name: $name address: $address<br>\n";
>           }
>         }
>     return $out;
> [/perl]
> 
> And these are my tables..
> 
> Members table:
> ---------------------------
> | sku   | member          |
> ---------------------------
> | vip   | 1,4,6,14,25,46  |
> ---------------------------
> 
> Users table
> ------------------------
> |sku | name  | phone   |
> ------------------------
> | 1  | name1 | phone1  |
> ------------------------
> | 2  | name2 | phone2  |
> ------------------------
> | 3  | name3 | phone3  |
> ------------------------
> 
I'd advise you to normalise your "members" table so you're able
to perform a proper join between "members" and "users" and then
select all the records you need in a single hit.

Something more like this would be a lot better:

    sku     member
    ------- --------
    vip     1
    vip     4
    vip     6
    vip     14
    vip     25
    vip     46

You've indicated that your existing members.member column contains
users.sku references.  If you were to adjust the table then you
could do this:

    SELECT  m.sku, u.name, u.phone
    FROM    members m, users u
    WHERE   m.member = u.sku
    AND     m.sku = 'vip'

Given your 'vip' membership values, a single row would be returned:

    sku     name    phone
    ------- ------- --------
    vip     name1   phone1

If users 4, 6, 14, 25 and 46 existed in your users table then that
same query would return those rows as well.

Why do you have an SKU column in your users and members table?
You seem to be using users.sku as some form of user ID.  Perhaps
renaming the column to something like 'code', 'userid' or whatever
would make the intended use of the column a little clearer.  If your
users are considered to be stock units then carry on.  The same goes
for the members table.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/