flutter Infinite Scroll View with MySQL

Question

Recently i worked on a gallery like project that display a bunch of image from database. I'm using MySQL because I have a dedicated server and using firestore may not a great idea due to pricing. I want it to have an infinite scroll view that load 10 images each time, but currently it loads all the images at the same time and it makes the app felt really slow.

here is my lib/main.dart

import 'dart:async';
import 'dart:convert';

import 'package:flutter/material.dart';
import 'package:flutter_staggered_grid_view/flutter_staggered_grid_view.dart';
import 'package:http/http.dart' as http;
//local import, you can ignore it
import './nav.dart';
import './detail.dart';
import './placeholder.dart';

void main() => runApp(MyApp());

class MyApp extends StatefulWidget {
  @override
  _MyApp createState() => new _MyApp();
}

class _MyApp extends State<MyApp> {
  Future<List> getData() async {
    final resp = await http.get("http://192.168.43.68/API/readImage.php");
    return json.decode(resp.body);
  }

  @override
  Widget build(BuildContext context) {
    // TODO: implement build
    return new MaterialApp(
      theme: new ThemeData.dark(),
      home: Scaffold(
        appBar: new AppBar(
      title: new Text("Title"),
    ),
    drawer: Nav(),
    body: new FutureBuilder(
      future: getData(),
      builder: (context, snapshot) {
        if (snapshot.hasError) {
          print(snapshot.error);
        }

        return snapshot.hasData
            ? new ItemList(
                list: snapshot.data,
              )
            : new Center(child: new CircularProgressIndicator());
      },),),);}}


//Item builder to display each image
class ItemList extends StatelessWidget {
  final List list;
  ItemList({this.list});

  @override
  Widget build(BuildContext context) {
    // TODO: implement build
    return new Padding(
      padding: const EdgeInsets.all(0.0),
      child: new StaggeredGridView.countBuilder(
        crossAxisCount: 4,
        itemCount: list.length,
        itemBuilder: (BuildContext context, int index) => new Container(
        child: new GestureDetector(
            onTap: () => Navigator.of(context).push(new MaterialPageRoute(
                builder: (BuildContext context) => new Detail(
                      list: list,
                      index: index,
                    ))),
            child: Card(
              child: new Column(
                children: <Widget>[
                  new Stack(
                    children: <Widget>[
                      FadeInImage.memoryNetwork(
                        placeholder: kTransparentImage,
                        image: list[index]['url'],
                      ),],),

                  new Padding(
                    padding: EdgeInsets.all(4.0),
                    child: new Column(
                      children: <Widget>[
                        new Text(list[index]['name'],
                            style: TextStyle(
                              fontWeight: FontWeight.bold,
                              color: Colors.white70,
                            ))],),)],),))),
    staggeredTileBuilder: (index) => new StaggeredTile.fit(2),
      ),);}}

and here is my readImage.php (i still got no idea what to do)

<?php
include 'dbcon.php';
$sth = "SELECT * FROM pict INNER JOIN user ON pict.uid=user.uid LIMIT 10";

$result = $conn->query($sth);
$outp = array();
$outp = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode($outp);

enter code here

so that's all, any response will be appreciated.

Solution

I believe the main issue you're having is that you're using StaggeredTile.fit().

A bit of background information: in flutter, the image size isn't something that gets determined right away but rather once the image has actually loaded. This is fine for small number of images, but when you have a large (potentially infinite) number it causes problems.

The reason for this is that when the list is first constructed, it lays out enough children to fit the screen ( + the cacheExtent which might not apply to StaggeredGridView but does to most of the flutter lists). Think about how each item in the list will lay itself out - the GestureDetector, Card, Column, and Stack all delegate sizing to their children. So the size becomes dependent on the image. The placeholder doesn't really determine the height, so you can think of the image of having a height of zero or one.

That means that in your page, it keeps trying to load more and more images as more and more items are added to the screen - either the number of pixels vertically or essentially infinite depending on how exactly the height of your placeholder is treated.

There is a simple solution to this, but it might not appeal to you - instead of using StaggeredTile.fit(), use a fixed height and then have your images fit themselves within that height.

Unfortunately, it gets a lot more complicated if you want each of the images to have a different height. One of the ways of dealing with that (since you have control over the database) would be to store an aspect ratio along with each image's path in your database. That way you could wrap your FaceInImage with an AspectRatio widget and therefore the height would be determined immediately.

This would only work if your list of items isn't truly infinite of course, but since it's in a database I think that'd be fairly safe to assume =D. You could use the getimagesize function in PHP to figure out the aspect ratio of each image, run through the entire database once doing that, and then from now on whenever you insert a new image you'd have to calculate the aspect ratio.

Another solution would be to start with a fixed height you specify yourself, then change the height once the image is loaded. However, I don't know how well StaggeredGridView would deal with that - most of the list/grid classes I've seen expect their children to stay the same size. In that case you would potentially have to look into a CustomScrollView and writing your own sliver, which is way outside the scope of this question.

---

EDIT: OP clarified that apparently the MySQL part is what they're asking about rather than why it's slow. I still think that the images is why it's slow rather than the request for the URLs, but I could be wrong. It hasn't been specified whether 10, 50, 100, or 1000's of images are being loaded.

Without more knowledge about how the database is set up, it's hard to give a clear answer for whether that is the issue or not. But I'll write a quick overview of a possible way to set up the server.

I won't write the actual code for your server as that's way outside the scope of a SO question about flutter. If you can't figure that out, I'd advise finding a course or tutorial on getting a server set up, following that, then afterwards asking a question not tagged with flutter but rather with PHP/MySQL etc as that would actually be what you're asking about, about the specific problem you're having rather than a general "I can't do it help me" as that won't get an answer on SO.

---

Assuming the following:

• you have a database set up which you can query for a list of URL, description, etc
• this database is expected to return a large number of values (e.g. more than 200 or something)

On the server side, you're going to have to set up an endpoint that responds to requests and returns sections of the data. I'd expect the endpoint with no parameters to return something like the following if you want to use JSON:

{
   num: 30,
   images: [
    { title: ..., url: ..., .... },
    ... 
   ],
   more: true
}

And that list would have to be sorted by some parameter - title, date, id, etc. You could alternatively have a totalItems that your return as part of the result that tells the client how many images there are. And you could optionally accept the number of items to return as well.

For subsequent request, you could accept parameters num, and offset. The offset should be a way of describing the last result returned so that the query knows where to start from - if you're sorting by an ID it could be that, or by date it could be the date. And num would be the maximum number of results to return.

If you don't return totalItems as part of the response, you could optionally have a different request that just gives you the maximum number of items as that might make your life a bit easier. If you don't want to do either of those, then take a look at the link someone posted in a comment that describes how to write an infinitely loading list.

Note that this would only ever be returning the URLs for the images rather than the images themselves.

Next, you would need to make some sort of system for caching in your flutter code. This could get a little bit complicated, as you not only have to fetch the images but also the description of the images as the page scrolls.

How I would do it is as follows:

• Make a paging manager that handles loading up the descriptions of items
• Paging manager can have a request for items i.e. starting at 0, num 30
• The paging manager starts this request and keeps track of it
• Each individual list item requests its data from the paging manager
• each list item has a FutureBuilder that shows something if the data is loading, something else if the data is loaded.
• The manager knows whether a request has been started for that item or not, if not it makes a request for that item + and the ones around it (so that you don't have a bunch of individual requests being sent at once)
• The list item gets back a future that will return its data only, which finishes instantly if the data is already loaded or waits until the data is returned from the server
• At some point you'll probably want to deinitialize the data in the caching manager if you have a LOT of images - you could do that by keeping track of which images are currently being requested and removing anything < 100 lower or > 100 higher or something like that.

---

There is one other way you could do this, but might not be particularly optimal for your database (depending on how it's set up). Instead of doing the caching on your client side, you could simply do requests to your server for each image (i.e. GET user/images/1, user/images/2, etc). Then your server would internally figure out where to load that image from and would return it directly, with title etc as headers.

The problem with this is that the naive approach would be to do a database request for each and every single request that comes in; this would work but would result in a high number of requests to your database. Depending on your database setup that could be fine, but most likely would cause problems eventually. Instead, you could do batching of requests on your server (i.e. if request 0, also get and cache 0-30), and simply do the processing on the cached data. I'll leave that to you to implement though as it's way outside the scope of this question.

Comments

[diegoveloper]

Take a look at this post : marcinszalek.pl/flutter/infinite-dynamic-listview

[Anang M]

thanks, it really helps, and then the thing is is it okay to just load all the data from the database at once? you know maybe like a pagination and insert the data from next page to the list

[diegoveloper]

no, it's a bad idea to load all the data in one time, you need a pagination

[Anang M]

yups, so it's just like a pagination and lets says that load 10 data each time, and every update add the data to existing list and display it? so i need to make a void to fetch the data

[diegoveloper]

yes, read the post

[Anang M]

it help, but it doesn't seem to answer the main question that i need to know how to make an infinite scroll with mysql

[rmtmckenzie]

I was dealing with the feels really slow part, as that seemed the primary problem. And realistically, you should be able to load many more than 10 items at once without making the app slow - the reason it's getting slow is what I outlined above as when done properly flutter will only load the images that are currently being shown on the screen. If you actually have an infinite number of items, that's going to be an issue. But if it's simply a fairly large number, I doubt the json you're sending back is nearly as large as even a single image.

[rmtmckenzie]

See my edited answer for a general description. But as I said in the edited part, you should really open a question tagged with mysql and php as that's what you're using for the server side. Even then, your question is pretty general and will probably be closed as such, so I'd advise trying to figure it out using tutorials etc and then coming back to ask questions about a specific problem.

[Anang M]

thanks, appreciate it. I was done with my server side