How to increasingly show database results?

Question

I have a tool that checks available names in a game using cURL, and it should show available ones to the user running the check, and just dismiss the taken ones. Basically when you run a test on a list of names, it checks them all and inserts them into the database with their availability, either 0 for taken or 1 for available.

Right now, the page doesn't load until the check is done, then they're all inserted as a whole in the database. I'd like it to dynamically show the status of the check (i.e. "Checked name 405/4938") and show a loading image while the names are being checked, while also displaying available names as they're checked. Then at the end, remove the loading image, and just have the now complete list of available names.

How should I go about doing this? I don't know much about jQuery/AJAX. It's not ideal right now that they're all displayed as a mass at the end.

This is the code on the name check thread.

<?php
$query = mysql_query("SELECT * FROM `lists` WHERE id = '$id'");
while($row = mysql_fetch_assoc($query)) {

    $id = $row['id'];
    $list = $row['list'];

    foreach(explode("\n", $list) as $listItem) {
        checkname_RS(trim($listItem));
    }
}

This is the function for the check.

<?php   
function checkname_RS($name) {

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "http://services.runescape.com/m=hiscore/compare.ws?user1=" . $name);
    curl_setopt($ch, CURLOPT_HTTPGET, TRUE); 
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
    $output = curl_exec($ch);


    if (stristr($output,"Skill Stats")) {
        mysql_query("INSERT INTO names (name, plat, status) VALUES ('$name', 'Runescape', '0')") or die(mysql_error());
    }
    if (stristr($output,"Member Rankings")) {
        mysql_query("INSERT INTO names (name, plat, status) VALUES ('$name', 'Runescape', '1')") or die(mysql_error());
    }

    curl_close($ch);

}

Answer 1

You don't really need to do anything special in order to make this happen. Since you're already just keeping the connection open and dumping data at the bottom of the document, you should just be able to dump more javascript to the client after each item or each n items.

So you could dump some html like the following at some point in the document:

<div id="progressMessage"></div> 
<ul id="availableNames"></ul>

Then your thread would just need to output js to update the view as it goes along, for instance (jQuery used for simplicity):

$("#progressMessage").text("1 of 5999");
$("#availableNames").append("<li>matchedName</li>");

The same goes for loading/hiding the progress bar, the js doesn't get run until the client receives it so the net effect is like a progress bar.

See also: https://stackoverflow.com/a/2920207/486620

Answer 2

A way of doing this is with a Job Queue Server, with workers (tasks that gets the job done) and clients, that ask this workers about the completion of the process. One of the many is Gearman.

Another way of doing it is to run a task yourself using cURL, but do not wait for completion and with set_time_limit(0), and querying the status yourself, for example, by saving the status of the job in a mysql table.

Here some examples on multithreading:
https://github.com/petewarden/ParallelCurl
http://semlabs.co.uk/journal/object-oriented-curl-class-with-multi-threading

The implicit flush not always works.

Hope, it works for you!

Answer 3

A naive solution could be maintaining a numeric processed column in your list table, that you would update at each iteration:

<?php
    // user clicks on "run" (Ajax POST'ing of the input form)

    // load whatever you need from $_SESSION here and then
    session_write_close();
    // so as to allow access to the session to the later Ajax polling

    // reset counter
    mysql_query("UPDATE lists SET processed = 0 WHERE id = $id");
    // then the rest of your current script


    function checkname_RS($name) {
        // your current code here
        // then increment the counter
        mysql_query("UPDATE lists SET processed = processed + 1 WHERE id = $id");
    }

Then have an Ajax call poll the database every now and then, in order to check the status of your current job. The Ajax call would read the status from a script like this:

<?php
    // script handling the Ajax call

    // get the number of names already checked
    mysql_query("SELECT processed FROM lists WHERE id = $id");

    // proposed trick to count the number of names in this list
    mysql_query("SELECT CHAR_LENGTH(names) - CHAR_LENGTH(REPLACE(names, '\n', '')) +1 AS cnt FROM lists WHERE id = $id");
    // better computing this number once and for all at the beginning
    // of the process and (eg.) store it in session

    // generate the response to the Ajax call here

The result can be displayed in a div, or be used to update a JQuery UI Progressbar, or in any way you find suitable.

---

Just one piece of advice regarding your structure. Always avoid storing non-scalar data in a column (such as your "\n"-separated list of names). Instead store one row per value (one row per name). Everything becomes so much simpler (and usually more efficient) when you normalise your data, even for small projects.