Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Two Versions of Query?

    My website uses Prepared Statements exclusively.

    Originally, Article-Summaries were just returned in a "paginated" format. (see query below)

    But now I am adding a "Display All" feature, so I need a way to return everything.

    So, do I create two separate queries, and call the appropriate one as needed, OR should I somehow code things in my Prepared Statement so *one* query can serve two purposes?

    Here is a snippet of my original query...
    PHP Code:
        $q2 "SELECT fields
                FROM article
                WHERE section = ?
                AND subsection = ?
                ORDER BY $sortField $sortDirection
                LIMIT " 
    ARTICLES_PER_PAGE " OFFSET $offset"
    Sincerely,


    Debbie

  • #2
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,342
    Thanks
    13
    Thanked 349 Times in 345 Posts
    SELECT fields FROM article WHERE section = ? AND subsection = ? ORDER BY $sortField $sortDirection LIMIT ? OFFSET ?
    this way it solely depends on the parameters passed to the statement.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    SELECT fields FROM article WHERE section = ? AND subsection = ? ORDER BY $sortField $sortDirection LIMIT ? OFFSET ?
    this way it solely depends on the parameters passed to the statement.
    I don't see how that would work, because if the user chose "View All", there wouldn't be this last line...
    PHP Code:
        LIMIT " . ARTICLE_SUMMARIES_PER_PAGE . " OFFSET $offset"; 

    Debbie

  • #4
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Why not do this...

    PHP Code:
    <?php
        
    if ($page == 'all'){
            
    // Show All Records.
            
    $pagination '';
            
        }else{
            
    // Paginate Result-set.
            
    $pagination " LIMIT " ARTICLE_SUMMARIES_PER_PAGE " OFFSET $offset";
        }

        
    // Build query.
        
    $q2 "SELECT DISTINCT a.slug, a.heading, a.image, a.published_on, a.summary_long
                        FROM article AS a
                        INNER JOIN article_placement AS ap
                        ON a.slug = ap.article_slug
                        WHERE ap.sd_section_slug = ?
                        AND ap.ds_subsection_slug = ?
                        ORDER BY $sortField $sortDirection
                        $pagination"
    ;

    ?>
    Sincerely,


    Debbie


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •