Speed up table creation

[berserk]

hello everyone, i am in need of some advice. You see i created an application to manage a large user base, this user base has additional hidden data per user that is in the form of a drop down. My problem is that 1k to 5k of users is fine, the app handles well within reason and is completely usable BUT when i hit number like 20k 50k and 200k thats when my speed truly suffers. Is there a way to speed this up, this is currently the method im using to populate and create the data in the database on a table

<table>
    <tbody>

$query=mysqli_query($con, "SELECT * FROM userprofile")or die(mysqli_error($con));

while($row=mysqli_fetch_array($query)){
$id=$row['id'];

<script>
                        function changeText(idElement) {
                            var element = document.getElementById('back' + idElement);
                            // this script is for processing each HTML element of a specific string and changing it on user click
                            // I used echo row-number as a way to go through each row to find the proper label, in my case should be the first header
                          for (var i = 0; i <= <?php echo $rownumber;?>; i++) {
                            i = i++;
                            if (idElement === i) {
                                if (element.innerHTML === '[+]') element.innerHTML = '[_]';
                                else {
                                    element.innerHTML = '[+]';
                                }
                            }
                          }
                        }
                        </script>
<tr>
                          <td width="5%"><center>
                            <div title="Show more Details for <?PHP echo @$row['username'];?>">
                            <!-- I used php echoes to get the data stored in my php script and use it in my html/javascript -->
                            <!-- this individual column is important because it has all the functionality for expanding and collapsing the nested table -->
                           <a id="back<?php echo $rownumber;?>" onClick="javascript:changeText(<?php echo $rownumber;?>)" href="#row<?php echo $rownumber;?>" data-toggle="collapse" data-target="#row<?php echo $rownumber;?>" class="btn accordion-toggle">[+]</a>
                          </div>
                          </center></td>
                          <!-- each one of these access values that are stored under a specified header in the database -->
                         <td><?php echo @$row['subsciber_account_number'] ?></td>
                         <td><?php echo @$row['company'] ?></td>
                         <td><?php echo @$row['username'] ?></td>
                         <td><?php echo @$row['contact'] ?></td>
                         <td><?php echo @$row['email'] ?></td>
                         <td><?php echo @$row['phone'] ?></td>
                         <td><?php echo @$row['city_state'] ?></td>
                         <td><?php echo @$row['log_history'] ?></td>
                         <td><?php echo @$row['permission'] ?></td>
                      </tr>
                      <?php } ?>
                      </tbody>
      </table>

im sure this is not the most elegant way but now i need to figure out a way to make the code MUCH faster so the user base will have more freedom to expand. In all honesty im projecting upt o 2m+ but for now id like to get 200k working at a reasonable speed

my database type is InnoDB
im running the latest version of php through xampp
im still somewhat of a beginner as almost everything i know is self taught
thank you for any and all guidance and assistance, always love learning about new things especially when they are code related

[diafol]

You will not display 200K rows in a page will you? Nor will you extract this info and place it into JSON or any other such nonsense.

You seem to have a number of rows of people which can be expanded to show their additional data.

You have two main options it seems to me - load a page of users (30?) into JSON and keep it in the background, OR, perhaps more cleanly, get info on the fly via Ajax.

Pagination is called for here - and ensure that you use the LIMIT clause on your main query (list of users).

You can dynamically add an expand panel with content when the site user clicks a user row. Each row does not have to have it's own hidden expand panel.

If you have 2M users, then I would imagine that you will have a number of concurrent users, which could be a susbtantial strain on your resources. Retrieving small datasets instead of a large one and hiding the ones you don't want, is the way to go.

I'm sure that I've got this wrong. You aren't loading all your users into the page are you?

[berserk]

not all of them, i was just doing testing to see how much of a load my app could handle, i was also thinking of setting up the expand option to pull data when clicked instead of pulling it before but hiding it and then showing it when clicked, a few more things i thought of doing like what you explained but how would i go about setting up a chow more users button? and also if i did a search (theres a search function at the top of my page on the right) would i still search all the users from the database or just the ones displayed on the table?

and no not all users will be displayed, in fact to be honest 200k users is really pushing it, i doubt ill exceed 5000 honestly but i just like to be prepared (luck favors the prepared) and also i was interested in learning how to handle such numbers in case i ever did need to develope something that will display such an enormous amount of data

i just really like and enjoy the way php works and would love to learn every little method i could when i can.

ALSO
JUST for the sake of cleaning my code up, is there a way to pass info to a javascript function thats outside of a loop instead of needing to use it inside the loop, i feel this is an incredibly slopy way to do this, i know it has something to do with referencing "this" into it so the data is passed differently each time but i was just curious if anyone knew anything about this

thanks again

[diafol]

The amount of data you return is down to the purpose. I usually find that a blind list (even paginated) is pointless - especially if you have 5000+ records.

This is where your search or filter fields come in - I'd just return the first page of a specified search, e.g. username or whatever you're using to identify people to the community.

There are hundreds of pagination scripts out there, hell, I've probably written and rewritten a hundred myself!

If you say filter on usernames starting with 'dia*', then you'd get a list of maybe 50. BUt that's too many for your page, so you decide on 20 and then give the peruser the option of going to the second and third page by page links (previous and next too if you're into them).

[berserk]

so i managed to speed up the program by quite a bit, i did this by taking the scripts out of the loops first off, thats bad programming practice, i know that now. It also made things essentially uglier and harder to read. The second thing i want to do to dramatically increase the speed is simple but is not something i seem to be able to figure out on my own. I have done many searches and read and tried many different methods but none of them seem to work. I needed a way to load the details page on click without loading it in php when the page is loaded (with 5000 people that a LOT of additional php to load that essentially might not get used) so i thought i could maybe use javascript to request the page on click, since i know that php can only load on the server side.

my first method was to use pure jquery/ajax.

i did something like

function show_details(id)
{
    $.ajax({
    url: 'details.php',
    type: 'POST',
    success: function(data) {
      $('#row'+id).load(data);
      alert('Load was performed.');
    }
  });
}

but nothing, then i started looking more into onclick functions but i already have the onclick in the html, so what method will work? or am i just reading and writing the code wrong entirely?
my biggest question really is is this even possible?

any help will be (like always) both appreciated and good learning experience.

thank you