Re: [mv] Trying to limit SQL queries

[Mike Heins <mike@minivend.com>]

******    message to minivend-users from Mike Heins <mike@minivend.com>     ******

Quoting Christopher P. Lindsey (lindsey@mallorn.com):
 
> But realizing that in the above case each row returned tab-delimited fields,
> I thought that I could use this to my advantage and embed the search function
> in perl:
> 
>    <!-- Aha!  Only 1 query / page, but it's still a hack. -->
>    [perl interpolate=1]
>    $a = "<font size=-1>\n";
>    foreach $i ([loop search="st=sql/ml=999/sq=select distinct p.code, p.invasive, p.sci_name, h.hardiness_name, c.category_name, p.price from plant as p left join size as s on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where c.category_name ='[data session arg]' order by p.sci_name"] q([loop-code]), [/loop]) {
>       @tmp = split '    ', $i;
>       $a .= "<a href=\"/cgi-bin/catalog/$tmp[0]\"><b>$tmp[2]</b></a>\n";
>       $a .= " $tmp[3] $tmp[5] $tmp[0] \$$tmp[6]<br>\n";
>    }
>    $a .= "</font>";
>    return $a;
>    [/perl]
> 
> This is definitely more efficient, costing only one SQL query.  Displaying
> categories that contain 1500 entries now only takes a few seconds instead
> of 15 minutes like the first one.
> 
> But I'd really rather do this in native minivend.  Comments?

That is native MiniVend; it is designed to use embedded Perl. How else
are you going to get all of the data in one query? There is one other
way, to use [sql type=hash ] or [sql type=array], but still you have to
use embedded Perl to access it. Otherwise, no. MiniVend 3 isn't quite
intelligent enough to do this. It must do a query for every row, but should
limit it to one per row displayed.

If you have more than one products file, then [sql-field ...] is
biting you. I am positive I said to use [sql-data products ...] for
efficiency.

In MiniVend 4, as I stated before, you can do [if-sql-param ...] for only
one query. Even MV3 can do [if-sql-param ...] in its latest versions.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
When the only tool you have is a    131 Willow Lane, Floor 2  | ||  _ \
hammer, all your problems tend to   Oxford, OH  45056         | || |_) |
look like nails.                    <mikeh@minivend.com>     |___|  _ <
 -- Abraham Maslow                  513.523.7621 FAX 7501        |_| \_\
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list