1

I need to make a "SELECT * FROM" Sql from PHP, that now returns about 10.000 records, that I insert in an Array and re-utilize in a page to show results in a table.

I need to have all that elements also in a hidden input fields, to connect via API to a SMS service to send SMS

<input type="hidden" id="destinatariSMS" name="destinatariSMS" value="<?php echo htmlentities(serialize($destinatariSMS)); ?>" />

Obviously, with 10k elements loading of page is about 10-15 seconds (in a VPS with 2GB ram).

I'm worrying because in about two months element will be 12-13k, about 1k elements at the month.

I cannot paginate elements because I need all together in that input...

In your opinion, what's best practice to no "destroy" VPS or browser?

Thank you very much

closed as unclear what you're asking by Arseni Mourzenko, user40980, m3th0dman, gnat, user53019 Sep 18 '13 at 3:28

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    A lot would depend on how often you need this data, what you do with it and how often it changes. We have similar amounts of data to process in some cases and cache the html once rendered (and sweep cache when data changes and recreate immediately outside normal page request cycles). btw: You can still use pagination and just make several calls within a loop (in case you run into memory issues or whatever) – thorsten müller Sep 16 '13 at 9:59
  • 1
    btw: From a usability view I really don't think it makes sense to display 10k records on a single page. Nobody gone read that so what for? – thorsten müller Sep 16 '13 at 10:11
  • 1
    btw, do you need an intermediate Array? why not output the table directly? – coredump Sep 16 '13 at 12:00
3

So, you want to build a form containing 10 000 mobile numbers, and submit it to an SMS service. The first questions you may ask yourself:

  • Would the SMS service accept so many numbers?
  • Isn't there a way to do the same task in steps, using only a subset of mobile numbers at each step?
  • Does it really make sense to generate the form, display it to the person behind a browser, and let this person submit it, given all the constraints?

If you consider just the last question, the answer would probably be negative. Displaying such form:

  • Is a disaster from the point of view of the privacy of your customers (unless the mobile numbers are not the numbers your customers gave you, and you're just a spammer). Why would you ever send to anyone an HTML code containing every number of every person who trusted you, ignoring how do you care protecting sensitive data?

  • It simply doesn't make sense to flush all this data to the client and let the client flush it somewhere else. Why not sending it directly to the final destination? Don't waste everyone's' bandwidth without need.

  • Browsers will uselessly suffer from such large amount of data on some low-end machines. You're wasting your servers' processor power, you're wasting clients' processor power, and you increase the risk of something going wrong. Like the client who submits the form once, waiting for a while, and, thinking that it wasn't submitted, resubmits it.

  • The SMS service probably uses an API key. Does that mean that you're publicly showing the key as well? Why?

  • What about input validation? Since you don't do any, it means that anyone may send anything to all of your customers' mobile phones. What a lucky day for a hacker. And it becomes even better since you let everyone see the list of mobile phones and the API key.

What you can do instead is to show to the user a simple, basic form with the text area itself to type the message (or any other form from which the message will be generated). When submitted, you validate the input, and, if correct, make one or several HTTP requests to the SMS service yourself, from the server. If SMS service is able to handle lots of mobile numbers at once, you'll still have your select MobileNumber from Customer. If not, MySQL limit clause can be used to paginate data.

See, no disclosure of sensitive data, no security issues, no wasted network bandwidth, and reduced impact on the CPU.

  • obviously this data are all in a protected area. You are pointing (correctly) to the security and privacy, but all data are behind a SSL site, no problem for that. – sineverba Sep 16 '13 at 12:59
  • Never ever send to anyone sensitive data without need. It doesn't matter if you use SSL, if the person is your best friend or if the person is under video surveillance and body searched before and after using your web application. – Arseni Mourzenko Sep 16 '13 at 13:05
1

This is where UX is needed, I believe.

Understand your data and their use cases. You may not have to show all 10000 at once. There may be some grouping that can chunkify this data to speedup page loads. There can be other information you can get from the requester to understand which category he requests. Loading all 10000 at once should not be an option. Look to redesigning the user experience.

0

The best practice in SQL is NOT to use "SELECT *".

Is there no way that you can bring logic from your PHP program to your query so you don't get 10.000 results?

I always cringe when I see programs filtering or making trivial calculations on data which the database server could easily do.

  • 4
    I won't -1, since the question is not very clear, but still, you doesn't answer it. The OP explains that he expects those 10 000 results, so no, it's not about filtering at SQL level. – Arseni Mourzenko Sep 16 '13 at 11:48
  • 1
    agree with @MainMa The point about whether stuff could be done in query is really a comment to the original question not an answer. (Not using SELECT * in production is a valid point though.) – Murph Sep 16 '13 at 11:51
  • @Mainma you can chose which question you want. The question in the subject "what's the best practice for using select *" is very different from the question in the body: "what's best practice to no "destroy" VPS or browser" .......The first I answered, the second I can't answer because I don't know what poster means. – Pieter B Sep 16 '13 at 12:32
  • 1
    @PieterB: the questions in the title are taken in a context of the body and the tags. There is no need to answer a question from the title if you don't know what poster means. – Arseni Mourzenko Sep 16 '13 at 12:40
  • @PieterB: no, i cannot bring logic. My customer want see exactly all 10.000 record. And i cannot paginate because i need to pass in an input... – sineverba Sep 16 '13 at 13:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.