db insertion progress meter/bar

[webent]

Hi, I've found tons of different scripts for metering the progress of uploading files and I can see how that would require the loads of scripts that come with it and configurations neccessary...

 

The thing is though, I am inserting thousands of rows into a database from the file that I uploaded, which can take anywhere from 5 to 10 minutes or more... thanks to help from the php forums, I have decided the best solution is AJAX,...

 

I have the total amount of rows in the file, done by a pre-query... That is done before the actual loop of populating the db with the file contents... which is done like so...

$total_count = 0;
$handle = fopen($_POST['file_name'], "r");
while (($fields = fgetcsv($handle, 0, ",")) !== FALSE) {
$total_count++;
}
$total_count -= 1; // Minus 1 for the file header
mysql_query("UPDATE insertion_counter SET total_count = '$total_count' WHERE counter_id = '1'");

 

And in the loop that is actually populating the db with the file contents, I have it also updating the insertion_counter table with the current row that it is on, like so...

$row_counter = 0;
$handle = fopen($_POST['file_name'], "r");
while (($fields = fgetcsv($handle, 0, ",")) !== FALSE) { 
    $row_counter++;
    if ($row_counter == 1) { // First row header removed. 
                                }    else    {
$products_inserted_counter = $row_counter - 1;   
mysql_query("UPDATE insertion_counter SET current_count = '$products_inserted_counter' WHERE counter_id = '1'");

 

So now that I have both values, that I can call like so...

$results = mysql_query("SELECT current_count, total_count FROM insertion_counter WHERE counter_id = '1'");
    while ($row = mysql_fetch_array($results)) {
        $current_count = $row[current_count];
        $total_count = $row[total_count];
    }
if ($total_count != 0) {
$percent_complete = number_format($current_count / $total_count,2);
$percent_complete *= 100;
}

 

Isn't there a simple way to use that with AJAX for a meter or something?

[xtopolis]

If you are saying you have to code to check the progress at any time, and it won't interfere with your large file that is being uploaded/inserted to the database, what's the issue?

 

So now that I have both values, that I can call like so...

$results = mysql_query("SELECT current_count, total_count FROM insertion_counter WHERE counter_id = '1'");
    while ($row = mysql_fetch_array($results)) {
        $current_count = $row[current_count];
        $total_count = $row[total_count];
    }
if ($total_count != 0) {
$percent_complete = number_format($current_count / $total_count,2);
$percent_complete *= 100;
}

 

If you set this bit of code in it's own file or whatever, you could just add the line

print $percent_complete;

 

Using this concept of yours, I made a 'not so simple' CSS graph using styling from http://applestooranges.com/blog/post/css-for-bar-graphs/?id=55.

 

Example is here: http://xtopolis.com/temp/ (wait 3 seconds for it to start)

My math isn't accurate, I know this, but it's 1am, and you get the jist of it.

 

Download files: http://xtopolis.com/temp/ajax_graph.rar

^--I broke it down into each piece so you could see it.

 

-Updater.php: outputs the current time in seconds, with a little manipulation for no leading 0s.

-Updater.js: Calls the same function every 3 seconds with 2 arguments (target IDs to change)

  + Creates the XMLHttpRequestObject as xHRO

  + Calls updater.php

  + Takes the output and updates the <span> and <strong> tags.  It also updates the <strong> tags innerHTML.

  + The graph div is 120px wide, so I multiply the 'seconds' output by 2 to make it accurate, and easier to see.

-index.css: delicious styles

-index.html: i wonder.

[webent]

I am really sorry that I never noticed that anyone responded to this post... I did not intend on being rude...

 

Thank you for your response, I will check out it right away...

[webent]

It looks great, but the only thing I can't figure out is how to integrate my "print $percent_complete;" value into your code... I tried this...

 

/*
  $s = Date('s');
  if(substr($s,0,1) == 0)
  {
    $s = substr($s,1,1);
  }
  print $s;
*/  
include_once "../connection.php";
$results = mysql_query("SELECT current_count, total_count FROM insertion_counter WHERE counter_id = '1'");
    while ($row = mysql_fetch_array($results)) {
        $current_count = $row[current_count];
        $total_count = $row[total_count];
    }
if ($total_count != 0) {
$percent_complete = number_format($current_count / $total_count,2);
$percent_complete *= 100;
} 
print $percent_complete;

 

Where that updates the graph value, it breaks the timer... I don't see where the function calls for both values, the timer value and the percentage value...

[xtopolis]

Example Files

 

You will have to change the database query and connection in progress.php and test.php(if you use it, i just had it insert into my db to test it) to match your own again.  This should be a clearer example using your code of how to view progress and apply it.  Remember it will show 0% until a certain amount of records have been entered (depending on your total).

[webent]

Still not working, was just wondering, where is this mysql.php class file that you are calling in the progress.php file?

[xtopolis]

I copied/modified a mysql database class from a book I bought on PHP/MYSQL (Sitepoint)

 

The class is this:

You instantiate it via:

  $db = new MYSQL('HOST','USERNAME','PASS','DATABASENAME');

<?php
//MYSQL

class MYSQL {

  var $host;
  var $user;
  var $pass;
  var $dbname;
  
  var $dbConn;

  //constructor
  function MYSQL($host,$user,$pass,$dbname)
  {
    //assign vars to local vars
    $this->host = $host;
    $this->user = $user;
    $this->pass = $pass;
    $this->dbname = $dbname;
    //connect to DB
    $this->Connect();
  }
  
  //Connect to DB
  function Connect()
  {
    //connect
    if(!$this->dbConn = @mysql_connect($this->host, $this->user, $this->pass)) {
      trigger_error("Could not connect to Database.");
    }
    //select db
    if (!@mysql_select_db($this->dbname)) {
      trigger_error("Could not select Database");
    }
  }
  
  function &query($sql)
  {
    if (!$queryResource = mysql_query($sql, $this->dbConn)) {
      trigger_error("Query Failed: " . mysql_error($this->dbConn));
    }
      return $queryResource;
  }
  
function checkExistsOne($sql)
{
	if (!$queryResource = mysql_query($sql, $this->dbConn)) {
		trigger_error("Query Failed: " . mysql_error($this->dbConn));
	}
	$howmany = mysql_num_rows($queryResource);

		return $howmany;
}

}//mysql class

?>

 

If it's still not working, tell me WHAT part isn't working, not that it still isn't working...