CODEWITHSUNDEEP
phpjavascriptjquery
Richard Hedges
Richard Hedges

Reputation: 1188

JavaScript/jQuery to load more from MySQL?

I searched Google on how to do this, and I got the following link: http://www.9lessons.info/2009/12/twitter-style-load-more-results-with.html

That is exactly what I want to do, however it's rather confusing to me so I can't work out how to implement it properly to my current file.

My current query is as follows.

if (!$query = @mysql_query("SELECT * FROM confessions ORDER BY date DESC LIMIT 10")) {
    echo '<strong>Error:</strong> '.mysql_error().'';
} else {
    echo '<div id="posts">';
    while ($q = mysql_fetch_array($query)) {
        $id = $q['id'];
        $name = $q['confession'];
        $date = date("j M Y", strtotime($q['date']));
        echo '<div class="confession" ';
        echo '>';
            echo '<table>';
                echo '<tr style="width:700px;">';
                    echo '<td style="width:100px;font-weight:lighter;font-style:italic;font-size:95%;">'.$date.'</td>';
                    echo '<td style="width:600px;">'.$name.'</td>';
                echo '</tr>';
            echo '</table>';
        echo '</div>';
    }
    echo '</div>';
}

Obviously that just grabs the data of the last 10 rows in that table, and as far as I know I just need some JavaScript to remember the limit, and how many it grabbed, so it can grab the next lot.

Is it possible somoene can give me a link to help me explain it more, or write up some code (from the link I gave) that'll help?

Upvotes: 1

Views: 1639

Answers (2)

vol7ron
vol7ron

Reputation: 42099

Addressing Questions

Obviously that just grabs the data of the last 10 rows in that table, and as far as I know I just need some JavaScript to remember the limit, and how many it grabbed, so it can grab the next lot.

Right, your demo page does this with the Javascript variable ID, which they send to the ajax_more.php

Is it possible somoene can give me a link to help me explain it more, or write up some code (from the link I gave) that'll help?

That is not necessary.

Clarifying Demo

The demo's main page is loadmore.php, which creates the html page and coincidentally also lists the first batch from the query. It uses the button (the anchor link, not the link's box) to store the most recent ID, in your case the button's ID will be your date (Note: your date better be a unique field, or you may not return all results).

When a user clicks the "more" link, the JavaScript gets its id and then passes that on to the ajax_more.php, which it uses in the query to get the next batch of results since the last query, based on the ID you sent it (the SQL order by is important).

More

  1. As @hakre suggested, what you are referring to is called "paging" and most databases have their on implementation of it, such that you don't need to re-query the database if you keep the connection open.
    Also in mySQL you can supply the length and the offset, so instead of storing the ID and using less than, you can store the amount of records already retrieved (referred to as an offset). Example:

    SELECT * FROM <your_table> LIMIT <offset>, <length>

-- In Use:
SELECT * FROM confessions LIMIT 40, 10
-- 40 is the value that you passed to ajax_more.php via AJAX

  2. Warning: what the example doesn't tell you is the security implications of how they're querying the database. You're exposing user input (through JavaScript) directly to the database, without scrubbing the data or using any special use of parameter passing. This is a big security hole and opens you put to SQL Injection attacks, which are basically holes that allow users to put their own SQL commands, that can gain access to your database, or even entire system.

Upvotes: 1

alabama
alabama

Reputation: 422

Your query: SELECT * FROM confessions ORDER BY date DESC LIMIT 10 has the LIMIT statement 10 without a comma. that means you just get the first 10 results.

Just watch the example on http://php.about.com/od/mysqlcommands/g/Limit_sql.htm and you will get it.

Upvotes: 0

Related Questions