How do I loop through this mysql array without freezing the website

Question

For my current project I fill an array by using the sql query

"SELECT names FROM students";

and throwing every response into an array named $names_array.

Then I use

foreach($names_array as $value) {
    echo "<option>".$value."</option>";
}

to fill up a datalist with options so you can find a name using the list autocomplete or enter a name that is not yet found in the array.

Now here is the issue, if I click on an existent name I need to take a couple of other pieces of data from the table and fill in other input fields automatically.

So lets say the database table per student also has their age, birth, guardians number & guardians email.

How do I check if the typed in student already exists and if they do, get their additional data from the table?

If I can somehow get the entered name in PHP I could just look through the table which would be a lot faster but I've tried doing this and I can't seem to get it done.

I was using a very inefficient method where I json_encode an array gathered from the sql query

"SELECT * FROM students";

and then use

echo "<script>var names = ".$names_json."</script>";

to be able to fetch it in js. Now after parsing it and looping through it I can find my neccesary data but considering the database table already has 6000 options and is still increasing it's starting to take a while to loop through it, especially if the name I'm searching for is near the end of the array. Now this can take anywhere from 1 to 15 seconds where the website is completely frozen and it looks like it crashed until it's done and does what I need to do with the data.

I've tried using the solution offered here but that doesn't seem to change anything.

Please, does anyone know of a better way to do what I'm essentially already doing without temporarily freezing the website? Or maybe a completely different way of getting the other pieces of data? Thanks in advance.

There should be an ID field in your database, if you can retrieve that with your names and add it to the option e.g. `"` then you can make a second much fast SQL query of "SELECT * FROM students WHERE id='YOUR_ID_VALUE'" — Michael Rennison, Mar 10 '20 at 15:37
This seems a problem that can be solved with [AJAX](https://code.tutsplus.com/tutorials/how-to-use-ajax-in-php-and-jquery--cms-32494). — KIKO Software, Mar 10 '20 at 15:39
I wouldn't use a dropdown with 6k+ elements. However, some input with AJAX calls for autocomplete can fits your needs — Cid, Mar 10 '20 at 15:40

score 1 · Answer 1 · answered Mar 10 '20 at 15:51

for prevent the script loading to freeze the website load, you can add defer attribute, like so:

echo "<script defer>...some long logic....</script>";

For search easily through the array, you can sort it by the searched value, then use binary search

Also, you can store it in literal object, where the key is the name, and the value is object of all the student data. it will require some memory space, but make the search super fast

Serge Gorenstein · Answer 2 · 2020-03-10T16:07:34.750

1

At first on server side - pagination/limit, do not "select all"

SELECT names FROM students WHERE names LIKE ? ORDER BY names LIMIT 20;

Second on client side - lazy loading via ajax, but first after, for example, user typed 3 chars of name.

edited Mar 10 '20 at 16:07

answered Mar 10 '20 at 15:56

Serge Gorenstein

63
8

score 0 · Accepted Answer · answered Mar 23 '20 at 01:41

I guess I should answer this question if anyone else ends up stumbling onto the same issue.

I change the foreach loop slightly by adding the ID as a data-id to the options

foreach($names_array as $value) {
    echo "<option data-id='".$value['names_id']"'>".$value['names_name']."</option>";
}

Through js (and jquery) you can obtain the id of the chosen student like this:

currentVal = $("#inputID").val();
currentID = $("#listID option[value='" + currentVal + "']".attr('data-id');

now you can find the index of the chosen student in the namesArray doing this:

if (currentID != undefined || currentVal != "" || currentVal != " ") {
  arrayIndex = namesArray.findIndex(x => x.names_id == currentID);
  currentArray = namesArray[arrayIndex];
}

where namesArray is the var 'names' json parsed which I echo in the script seen in the question and the if block prevents it from even checking the array if the id is undefined or the input is empty.

How do I loop through this mysql array without freezing the website

3 Answers3