most efficient way of making html options list from mysql?

Question

I can retrieve records from my tables above 5000 record minimal, and I have four ways to do it, and I don't know which and why is the suitable for my requested work and which is best if it was lesser records: (Queries)

#---- Rows
SELECT
  a.`id`,
  a.`content`
FROM
  `docs` a;

I use while() loop for each row:

while ($row = $rows->fetch()) {
  echo "<option value=\"{$row['id']}\">{$row['content']}</option>";
}

#---- Group
SELECT
  GROUP_CONCAT(
    a.`id`,
    ".",
    a.`content`
  ) AS `options`
FROM
  `docs` a;

I use explode() + foreach() loop (content isn't allowed naturally to have "." in its text):

foreach (explode(",", $options) as $option) {
  $option = explode(".", $option); //[0]: id, [1]: content
  echo "<option value=\"{$option[0]}\">{$option[1]}</option>";
}

#---- HTML Rows
SELECT
  CONCAT("<option value=\"",a.`id`,"\">",a.`content`,"</option>") AS `option`
FROM
  `docs` a;

I apply html in query and then use while() loop:

while ($row = $rows->fetch()) {
  echo $row;
}

#---- HTML Group
SELECT
  GROUP_CONCAT("<option value=\"",a.`id`,"\">",a.`content`,"</option>" SEPARATOR "")
FROM
  `docs` a;

here everything is already made in query to just print:

echo $rows->fetch();

5000 isn't a lot of records for a query, but it's far too many to put into a ` — ADyson, Aug 26 '21 at 12:52
@ADyson I did try to convince my client that it can be done a lot better with `client-side` and `search functionality`, but it was insisted to make it into a ` — ijfierjf jeifjerij, Aug 26 '21 at 12:58
@ADyson tried to explain and prove a lot, but it was in the end "just need this back-end work, the front-end will be managed with other specialized developer in it" response (I believe his work won't have more than 200 ~ 500 records and he exaggerated in the data sample he gave me to work on). — ijfierjf jeifjerij, Aug 26 '21 at 13:08
But right now your code is generating the front-end UI (i.e. the HTML code). If they want another developer to build the UI then maybe your code should be returning machine-readable format like JSON, which front-end developer can the turn into whatever user experience they wish. — ADyson, Aug 26 '21 at 13:30
@ADyson thought and discussed the same, but in the end I had to ask this question, since what is request doesn't seem to be changeable, the 5000 records can be omitted in the end and seen as 20 ~ 50 records. — ijfierjf jeifjerij, Aug 26 '21 at 13:35
So what exactly is your question then really? Are you asking which of these methods is fastest? To find that out you just have to test it with a large sample. But the simplest query is likely to be faster I'd say. The GROUP_CONCAT stuff doesn't seem to add anything useful and probabably just adds overhead and complication. (Having said all that, again 5000 is not a lot of records for a database or PHP to process, it's barely worth worrying about. The user experience is the tricky part.) — ADyson, Aug 26 '21 at 13:49

score 3 · Accepted Answer · answered Aug 26 '21 at 13:48

I'll take your customer's requirement at face value, even though a 5000-element dropdown menu would drive me bonkers if I had to use it.

You might consider using the HTML5 datalist element to improve this UX a bit.

From php's point of view, the way you generate this code probably doesn't matter enough to worry about. From MySQL's point of view, however, the simpler query with the while loop -- your first choice -- is better. Why?

GROUP_CONCAT() needs a setting if you generate long strings with it. Read this. MySQL and GROUP_CONCAT() maximum length A wise developer, especially when faced with a strange requirement, keeps it simple.
Processing a medium-sized result set row by row can reduce RAM requirements in your php program, and in your MySQL server.
It's easier to read for the inevitable time when you, or somebody, needs to do a better job of this user experience.

score 1 · Answer 2 · answered Aug 26 '21 at 13:44

You can use JQuery select2 and apply search method on it, which will fetch data via AJAX call. I use JSON to populate the select. I am adding sample code here, modify as per your requirement.

Sample HTML

                <select name="ItemID" id="ItemID">
                    <option value="0">--</option>
                </select>

Related javascript

        $("#ItemID").select2({
            minimumInputLength:3,
            ajax: {
                url: 'json/getItemList.php',
                dataType: 'json',
                data: function (params) {
                    return { 
                        Src: params.term
                    };
                },
             processResults: function (data) {
                    var items = [];
                    for (var i = 0; i < data.length; ++i) {
                        items.push({id: data[i][0], text: data[i][1]});
                    }
                    return {
                        results: items,
                    }
             },                  
            },
        });

Related PHP code

<?php

$Src            = "";
if(isset($_REQUEST['Src']))
    $Src        = $_REQUEST['Src'];

// Write your own code to fetch data, and replace the below line 
//$oList        = $mdb->getItemListJSON3($tcmp->getCompanyID(), $Src, 2);

$jsonStr    =   json_encode($oList);    

print($jsonStr);

?>

most efficient way of making html options list from mysql?

2 Answers2