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();
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.
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);
?>