How Can I Display Mysql Rows One By One

[sagnik]

How can I show the contents of MySQL table as it fetches data? For example, I'm retrieving data using jQuery-ajax, before sending request to the PHP show an ajax loader and in PHP, if in a table has 4 rows and I'm retrieving the data in a while() block, as MySQL fetch first row show the data in the page using jQuery and continue to show the loader and after retrieving all 4 rows hide the loader. How can I do this?

[Krydos]

Just have the javascript send the row number as a $_GET variable. Like if your php script is called data.php then the javascript just calls it line by line like http://example.com/data.php?line=1 and then edits the html, and then calls the next line and loops through until it edits the html to remove the loader.

[sagnik]

I couldn't understand. If I send row number to PHP, then it will return only one row which matches the row number. Can you please give me an example of jQuery and PHP?

[Krydos]

index.html

<button onclick="showdata()">Load Data</button>
<div id="datagoeshere"></div>
<script>

// when the button is clicked this function executes
function showdata() {

  // load up the div element
  var div=document.getElementById("datagoeshere");
  
  // loop over the 4 rows of data
  for (i = 0; i < 4; i++) {

    // create new http request
    xml=new XMLHttpRequest();

    // watch for http response
    xmlhttp.onreadystatechange = function() {

      // if http response comes back with no errors
      if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {

        // append new data to existing data in div
        div.innerHTML += xmlhttp.responseText;
      }
    }

  // the url to request data from
  xmlhttp.open("GET", "data.php?line="+i);

  // send the http request
  xmlhttp.send();
  }
}
</script>

data.php

// load database user/password/etc
require_once('config.php');

// load up the line we're going to display
$line = $_GET['line'];

// make connection
$con = new mysqli($db_host, $db_user, $db_pass, $db_data);

// query for the data on the line
$sql = "select data from stuff where id = '$line';

// execute the query
$result = $con->query($sql);

// get the row from the results
$row = $result->fetch_assoc();

// get the value from the row
$data = $row['data'];

// print out the data so the javascript can receive it
echo $data;

Anyways, this is all just psuedocode. I haven't tested any of it, and there is no error checking involved at all so it's not robust at all. Also I'm not sure how that javascript would actually work since js is asynchronous unlike php where you can always know what order the command are going to run in.

[sagnik]

Ok, thanks. I'll try it... If it doesn't work I'll let you know.

[Krydos]

It's just an example that I spent 5 minutes writing without actually testing any of the code. I wouldn't be the least bit surprised if it didn't work. The point was to give you an idea.

[sagnik]

Ok, thanks...

 

Can I use fetch_array() instead of fetch_assoc()?

And I want to use jQuery not JavaScript, because I'm not well familiar with JavaScript.

[Krydos]

Can I use fetch_array() instead of fetch_assoc()?

Sure.

 

And I want to use jQuery not JavaScript, because I'm not well familiar with JavaScript.

jQuery is Javascript. It's the same thing, jQuery is just written in Javascript and makes shortcuts for you. You can use either or both.

[sagnik]

Ok, thanks... But I've wanted to show rows as MySQL fetches rows in a while loop as MySQL doesn't fetches all rows at once so I've wanted to display the row as soon as it ready instead of waiting for MySQL to fetch all rows. You take Facebook as an example, Facebook displays the posts as soon as it ready and at the bottom of page it shows a loading image.

[Krydos]

Facebook uses javascript to detect when you scroll to the bottom of the page, and then makes an ajax query like I described above to load the next batch of posts. Instead of using the for loop just use something like this:

// this function fires when the user scrolls the page
window.onscroll = function(ev) {

  // if the scroll is near enough to the bottom
  if ((window.innerHeight + window.scrollY) >= document.body.offsetHeight) {

    // show the loading twiddler
    showloading();

    // load the next data
    showdata(next);    
  }
};

// display the next batch of data
function showdata() {

  // load up the div element
  var div=document.getElementById("datagoeshere");
  
  // create new http request
  xml=new XMLHttpRequest();

  // watch for http response
  xmlhttp.onreadystatechange = function() {

    // if http response comes back with no errors
    if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {

      // remove the loading twiddler
      removeloading()

      // append new data to existing data in div
      div.innerHTML += xmlhttp.responseText;
    }

  // the url to request data from
  xmlhttp.open("GET", "data.php?line="+i);

  // send the http request
  xmlhttp.send();
};

[sagnik]

Thanks for your help, sir. It helped me a lot, but not like that I wanted to. I know, I couldn't describe the thing clearly. I'm trying last time to describe the problem:

$q=mysqli_query($con, "SELECT * FROM posts");
while($r=mysqli_fetch_array($q)){
//When first row is fetched echo the first row
//When second row is fetched echo the second row
}

And for your previous suggestion, do you mean to do that, I've to use paging? Like paging does, for example, shows 5 contents per page, I've to show 1 row per page and use jQuery for auto-paging?

 

In short, I don't want my users to wait until all rows are displayed.

[Krydos]

In short, I don't want my users to wait until all rows are displayed.

If you use only php then that's exactly what your users will have to do. Php is pretty fast though so you'll only notice the page loading and changing if you have thousands of rows or something.

 

And for your previous suggestion, do you mean to do that, I've to use paging? Like paging does, for example, shows 5 contents per page, I've to show 1 row per page and use jQuery for auto-paging?

Yep, php has no way to know when the user scrolls to the bottom of the page. The only scripting language that can do that is javascript because it runs in the browser not on the server.

[sagnik]

Thanks for your help, sir...

[Krydos]

No problem.