Feature Request

[sirchick]

Hello

On the lot table page it be nice if you could add a column that says if we have downloaded it already.. that way we can filter out the ones we already have downloaded

That be really useful!

Edit: i meant lot table aka this: http://sc4devotion.com/csxlex/lex_lottable.php

[Indiana Joe]

Hello

On the lot list page it be nice if you could add a column that says if we have downloaded it already.. that way we can filter out the ones we already have downloaded

That be really useful!

It already exists; as long as you are logged in it tells you if you have downloaded a file (beneath the picture).

[isii94]

I think, it's about the list that shows up when using power search.
This feature exists there as well: The date when you last downloaded a file is shown on the right (Last DLed).

[Indiana Joe]

I think, it's about the list that shows up when using power search.
This feature exists there as well: The date when you last downloaded a file is shown on the right (Last DLed).

Hmmm, I could have sworn it showed up there too, but after double-checking I see it does not.  Perhaps it used to, but didn't remain through the big update.

[compdude787]

I think, it's about the list that shows up when using power search.
This feature exists there as well: The date when you last downloaded a file is shown on the right (Last DLed).

Hmmm, I could have sworn it showed up there too, but after double-checking I see it does not.  Perhaps it used to, but didn't remain through the big update.

It works for me--it says that I've downloaded the NAM 1 time and the date that I downloaded it. Maybe it got fixed already? 

[sirchick]

I don't see it here is what i see.. when i say lot list i mean lot table. Sorry I thought it was called lot list.

As you can imagine, adding to the lot list so you can filter out the ones you downloaded - would be great.

See image attached.

[CasperVg]

I tried doing that back when I implemented the Lot Table, but sadly it was extremely expensive on server resources and loading the Lot Table would take ~2 minutes, which isn't really acceptable. I may eventually update the download list page to use a filter-table like the Lot Table has, though.

[sirchick]

Sounds like the query method was not good, might not have been set-up correctly..firstly limit the number of rows a user can list from the drop down box to a max of 50 - 100 at a time for pagination... the 1000+ rows is a bit too much in terms of parsing a lot of data to a client at any one time.

Then for your table joins well im not sure how you wrote it but it should not take that long for 100 rows given how fast this website loads.

Were you iterating each row then doing another query to check if the user had it downloaded already? If so that would be why, the entire thing should just be one mySQL query.

Boolean checks are almost no workload on a server

[CasperVg]

That's probably the problem - I'm no database wiz. And we did in fact load all the files in one big chunk, no pagination. I tried using only joins joins at first but that only resulted in files the users had actually downloaded - other files where not included in the results.

I found a reasonable query for now, using COUNT(*) on the downloadtrack table, and updated the Lot Table to shown if you've downloaded a file before or not.

Code Select Expand


    $sql = "SELECT
            l.LOTNAME,
            u.USRNAME,
            c.CATNAME,
            t.TYPENAME,
            l.ACCLVL,
            l.DATEON,
            l.LASTUPDATE,
            l.LOTDOWNLOADS,
            l.LOTID,
            (SELECT COUNT(*) from LEX_DOWNLOADTRACK d WHERE d.USRID = '" . $usr->USRID . "' AND d.LOTID = l.LOTID AND d.ISACTIVE = 'T') AS DLCOUNT
        FROM LEX_LOTS l
            INNER JOIN LEX_USERS u USING(USRID)
            INNER JOIN LEX_CATAGORIES c USING(CATID)
            INNER JOIN LEX_TYPES t USING(TYPEID)
        WHERE l.ISACTIVE = 'T'";


If you have suggestions to improve the query (as the load time is still a bit longer than I would like), feel free

[Yild]

atm there is no LIMIT statement, this results with over 3000 rows (atm) returned to user (saved html file is over 1,2MB) - there is limit selection but it operate only in layout level, combo with rows count only affects table rows visibility so every time one open this page all records are requested - if I want to get all data I should explicitly ask for them

solution two combos (or direct links with numbers) one with rows count that I want to see, second with "page" count, so query would be like

[your current query] LIMIT rows_count, page (LIMIT 100, 1 - first 100 rows, etc.)



pls don't remove option to view all rows (if I choose so)

[sirchick]

atm there is no LIMIT statement, this results with over 3000 rows (atm) returned to user (saved html file is over 1,2MB) - there is limit selection but it operate only in layout level, combo with rows count only affects table rows visibility so every time one open this page all records are requested - if I want to get all data I should explicitly ask for them

solution two combos (or direct links with numbers) one with rows count that I want to see, second with "page" count, so query would be like

[your current query] LIMIT rows_count, page (LIMIT 100, 1 - first 100 rows, etc.)



pls don't remove option to view all rows (if I choose so)

To expand on the above ^

Currently you get all rows even if the user is only looking for one, this is hugely eating server resources, cpu time, bandwidth etc.

Based on how many rows the user is listing at any one time should be included in your query as your LIMIT. These means each change of page or change of number of rows per page, you have to request and do another query, this may seem bad at first but its infact much less work for the server compared to loading your entire database which will as it gets bigger and bigger - eventually cause the server to hang.

The page number then defines the start in your query:

<php
$number_of_rows = 10; //default number listed on each page unless specified otherwise

if(isset($_GET['pagesize'])){ //user specified a custom number of rows they want to see per page
    $number_of_rows = $_GET['pagesize'];
    if($number_of_rows > 1000){ $number_of_rows = 1000; } // this line is optional but more than 1000 is a lot of rows i don't know why any one needs to see that many at any one time, so this can help cap the maximum
}

$pageNum = 1; //default page (you always start on page 1)
if(isset($_GET['pagenumber'])){ //user changed to a new page
   $pageNum = $_GET['pagenumber'];
}


$sql = "SELECT
            l.LOTNAME,
            u.USRNAME,
            c.CATNAME,
            t.TYPENAME,
            l.ACCLVL,
            l.DATEON,
            l.LASTUPDATE,
            l.LOTDOWNLOADS,
            l.LOTID,
            (SELECT COUNT(*) from LEX_DOWNLOADTRACK d WHERE d.USRID = '" . $usr->USRID . "' AND d.LOTID = l.LOTID AND d.ISACTIVE = 'T') AS DLCOUNT
        FROM LEX_LOTS l
            INNER JOIN LEX_USERS u USING(USRID)
            INNER JOIN LEX_CATAGORIES c USING(CATID)
            INNER JOIN LEX_TYPES t USING(TYPEID)
        WHERE l.ISACTIVE = 'T'"
        LIMIT ' .($pageNum - 1) * $number_of_rows .',' .$number_of_rows; //consider this example below for this line to make sense

       //EXAMPLE OF THE LIMIT ABOVE using numbers instead of variables
       //LIMIT (3-1) * 10, 10 becomes ¬
       //LIMIT 2 * 10 , 10 becomes ¬
       //LIMIT 20, 10
       //this means : get rows starting from position 20 and 10 further rows from that, there for you get rows from 20 to 30 returned
       //as there is 10 rows per page - page 2 obviously therefore starts at row 20, and we list only 10 per page so 20 to 30
       //we use minus 1 to fix the page 1. As page 1 starts at 0 not 10. We can't do 1 * number of rows because then page 1 starts on 10 and we missed the first 10 rows.
?>

Where number of rows comes from this element:

See bold for element id so you can get its value in PHP using HTML form

<select class="pagesize input-mini" title="Select page size" id="pagesize">
                    <option selected="selected" value="10">10</option>
                    <option value="25">25</option>
                    <option value="50">50</option>
                    <option value="100">100</option>
                    <option value="500">500</option>
                    <option value="1000">1000</option>
                    <option value="9999">All</option>
                </select>

And page number comes from this element:

See bold for element id so you can get its value in PHP using HTML form

<select class="pagenum input-mini" id="pagenumber" title="Select page number"><option>1</option><option>2</option></select>

To then list the pages you simply need a count query to count all available downloads in the table and divide that total by user's choice of rows per page.. example:

LEX has 10,000 downloads, user is viewing 50 rows per page so:

10000/50 = 200 pages.

You can use a javascript while loop to loop the links to the next and previous page.

Note you will need to edit some of your HTML form elements so it links to your GET or POST method. I'm sure  you know what this all means given the complexity of the features you have already added.

[j-dub]

irrelevant, sorry i know, couldn't help it, but i will say it's great someone with technical knowledge cares enough to share their coding sample of improvement, whether they agree to that coding edit or not is something else, but traffic and server hangs, always bad for anyone, anywhere