MySQL data source

[Stephan123]

Hello!

Is there an example of the use of a MySQl database?

With best regards

Stephan

[jiren]

Hi,

In examples right now there are only js and html. For MySql we need full application with backend code. In which technologies right now are you using? i.e Rails, PHP etc.

[Stephan123]

Hello!

Thanks for the quick reply. I'm working with PHP and MySql. Sorry for my bad english.

Where are you from?

With best regards

Stephan Krauss

Jiren Patel notifications@github.com hat am 17. Dezember 2014 um 09:52 geschrieben:

Hi,

In examples right now there are only js and html. For MySql we need full application with backend code. In which technologies right now are you using? i.e Rails, PHP etc.

— Reply to this email directly or view it on GitHub https://github.com/jiren/StreamTable.js/issues/14#issuecomment-67293155 .

[jiren]

Hi,

Sorry I have only rails code for example.

I am from Pune, India

[Stephan123]

Hello !

Can you give me information . I want to get the data from a server. In addition I have the function 'random movies ' changed.

---

function random movies () { var limit = 1 ; blub var = null; // Var i = Math.floor ( parseInt ( Math.random () * 100) ) % Movies.length ; // Var = blub Movies [ i];

$ .ajax ({
           url: ' server.php ',
          data: {
                      limit : limit
          } ,
       success : function (result){
               blub = result.record ;
        }
});

return blub ;

}

---

How can I return the result of the server?

With best regards Stephan

Jiren Patel notifications@github.com hat am 17. Dezember 2014 um 11:27 geschrieben:

Hi,

Sorry I have only rails code for example.

I am from Pune, India

— Reply to this email directly or view it on GitHub https://github.com/jiren/StreamTable.js/issues/14#issuecomment-67303566 .

[Stephan123]

Hello !

Here is my 2. test.

---

function randomMovies() { // var i = Math.floor(parseInt(Math.random()*100)) % Movies.length; // var blub = Movies[i];

var response = $.ajax({ url: 'server.php', async: false });

return response;

}

Here is the server response.

[ {"name":"Bla 1","director":"Bla 2","actor":"Bla 3","rating":"0.5","year":"1900"}, {"name":"Blub 1","director":"Blub 2","actor":"Blub 3","rating":"0.7","year":"2000"} ]

Your sincerly Stephan

[MB34]

Here is an example of loading using PHP:

stream.js

var st; //For debuggin only
$(document).ready(function() {
    // set async to false to get the value back before continuing
    $.ajax({async: false,
        type:"GET",
        url: "assets/php/getMovies.php?recordcount=true",
        dataType: "json",
        success: function(data) {
            window.rec_count = parseInt(data.count);
        }
    });
    // Now get the first set of data...
    var data = $.getJSON("assets/php/getMovies.php?limit=100&offset=1");
    html = $.trim($("#template").html())
    template = Mustache.compile(html);
    var view = function(record, index){
        return template({record: record, index: index});
    };
    var $summary = $('#summary');
    var $found = $('#found');
    var $record_count = $('#record_count');

  $('#found').hide();

  var callbacks = {
    pagination: function(summary){
      if ($.trim($('#st_search').val()).length > 0){
        $found.text('Found : '+ summary.total).show();
      }else{
        $found.hide();
      }
      $summary.text( summary.from + ' to '+ summary.to +' of '+ summary.total +' entries');
    },
    after_add: function(){
        // This code rounds off the percent
        var pct = this.data.length*100/window.rec_count;
        var percent = Math[pct < 0 ? 'ceil' : 'floor'](pct);
        $record_count.text(percent + '%').attr('style', 'width:' + percent + '%;');

        //Only for example: Stop ajax streaming beacause from localfile data size never going to empty.
        if (this.data.length == window.rec_count){
            this.stopStreaming();
            $('.example .progress').removeClass('active').hide();
        }

    }
  }

  st = StreamTable('#stream_table',
    { view: view, 
      per_page: 100, 
      data_url: 'assets/php/getMovies.php', // limit and offset will automatically be sent with this call.
      stream_after: 0.5,
      fetch_data_limit: 100,
      callbacks: callbacks,
      pagination: {span: 10, next_text: 'Next &rarr;', prev_text: '&larr; Previous'}
    },
   data);

});

getMovies.php

<?php
    //***********************************
    // put in your DB connection info
    // I'm using older MySQL code, you may 
    // want to update to use PDO
    //***********************************
    // Hostname
    $hostname = "localhost";
    // Database name
    $database = "db";
    // Database Username
    $username = "username";
    // Database Password
    $password = "password";

    $connection = mysql_connect($hostname, $username, $password);

    if(!mysql_select_db($database, $connection)) {
        die("We could not select the database provided.");    
    };

    //***********************************
    // These are the parameters being sent...
    // limit
    // offset
    // recordcount
    //***********************************

    //***********************************
    // Recordcount was sent, let's fetch the 
    // number of records from the table and
    // return it, then exit.
    //***********************************
    if(isset($_GET['recordcount'])) {
        $sql    = "SELECT count(*) as cnt from `movies`";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result, MYSQL_ASSOC);
        $recordcount = $row['cnt'];
        echo json_encode(array("count"=>$recordcount));
        exit;
    }

    //***********************************
    // a Limit and offset were sent,
    // let's get the dat aand format
    // and send back.
    //
    // Since this data contains UTF-8 data,
    // we must set names and then use iconv
    // to convert the data so that JSON_ENCODE
    // can handle it.
    //***********************************
    if(isset($_GET['limit'])) {
        $limit  = $_GET['limit'];
        $offset = $_GET['offset'];
    }

    $sql    = "SET names='UTF8';";
    $result = mysql_query($sql);
    $sql    = "SELECT * from `movies` LIMIT ".$limit." OFFSET ".$offset;
    $result = mysql_query($sql);
    $return_array = array();
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
        $return_array[]=$row;
    }

    //***********************************
    // This converts all the UTF-8 text
    //***********************************
    array_walk_recursive($return_array, function(&$item, $key) {
        if(is_string($item)) {
            // $item = iconv("UTF-8", "ISO-8859-1//IGNORE", $item);
            $item = iconv("ISO-8859-1", "UTF-8", $item);
        }
    });
    $r = json_encode($return_array);

    if($r) {
        echo $r;
    } else {
        switch (json_last_error()) {
            case JSON_ERROR_NONE:
                echo ' - No errors';
                break;
            case JSON_ERROR_DEPTH:
                echo ' - Maximum stack depth exceeded';
                break;
            case JSON_ERROR_STATE_MISMATCH:
                echo ' - Underflow or the modes mismatch';
                break;
            case JSON_ERROR_CTRL_CHAR:
                echo ' - Unexpected control character found';
                break;
            case JSON_ERROR_SYNTAX:
                echo ' - Syntax error, malformed JSON';
                break;
            case JSON_ERROR_UTF8:
                echo ' - Malformed UTF-8 characters, possibly incorrectly encoded';
                break;
            default:
                echo ' - Unknown error';
                break;
        }
    }

?>

And to load the DB, I converted the movie_data.js to a SQL script:

CREATE TABLE IF NOT EXISTS `movies` (
  `name` varchar(50) DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `rating` varchar(5) DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `director` varchar(50) DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `year` varchar(4) DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `actor` varchar(50) DEFAULT NULL COLLATE 'utf8_unicode_ci'
) COLLATE='utf8_unicode_ci' ENGINE=InnoDB;

INSERT INTO `movies` (`name`, `rating`, `director`, `year`, `actor`) VALUES ('Once Upon a Time in the West','8.7','Sergio Leone','1968','Henry Fonda');
INSERT INTO `movies` (`name`, `rating`, `director`, `year`, `actor`) VALUES ('Terminator 2: Judgment Day', '8.6','James Cameron','1991','Arnold Schwarzenegger');
INSERT INTO `movies` (`name`, `rating`, `director`, `year`, `actor`) VALUES ('Braveheart', '8.4','Mel Gibson','1995','Mel Gibson');
...

I loaded 3276 total records.

Hope this helps you out, I had fun doing it.

[MB34]

@jiren you can include the code above with your distribution. I will zip up the movies.sql file and send to you to include.