/    Sign up×
Community /Pin to ProfileBookmark

Best practice: Processing via PHP or DB?

Copy linkTweet thisAlerts:
Oct 10.2011

I [I]think [/I]this is more a PHP question than SQL.

I know it depends on the usage, but i wanted other people’s opinions on when to use PHP to process/filter/sort data and when to use the database.

Example: I have say 30-50 ‘objects’ each of the same type. They are each stored in the database and most of their properties are assigned to specific fields. As there are so few objects, my first reaction is to just pull them all out the database and do any searching/filtering in PHP to get the ones you want. That way, if elsewhere in the code you want the same lot of objects but perhaps ordered differently or only want ones on specific criteria, then there’s no need for another trip to the database because they are all already there.

For such a small sample of data i’m guessing storing them all in PHP is the best way. But at what point do you draw the line? What if out of all those objects you end up only using one of them – isn’t that a waste of memory? Even if you do use more than one, but then later want to re-order them and retrieve specific ones, then is it really that bad to do another trip to the database? Sorting and filtering data is after all what databases are designed for…

to post a comment
PHP

5 Comments(s) ↴

Copy linkTweet thisAlerts:
@mavigozlerOct 10.2011 — Not sure what you are concerned about.

Seems to me you are asking a lot to code PHP to do all of what you want, when it seems that the database functions (particularly in MySQL, assuming that is what you are using) is well suited to do things like filtering and sorting.

If you are so worried about database retrieval and page updating operations between client and server, then what you want to do is a lot of Javascript to do sorting and filtering...all done at the client. PHP is really only an interface between HTTP and (My)SQL server anyway, and while it might do a lot of those functions, it seems better for you to use it to construct proper SQL statements for what you want.

If you want to keep a lot of operations on the client side, you can set up tables of your database retrievals using Javascript, with sorting and/or filtering. Then if you realize the need do more server-side interaction, use Ajax to call PHP scripts for more action.
Copy linkTweet thisAlerts:
@svidgenOct 10.2011 — There are two best-practices:

Minimize trips to the database.

Don't return more rows than you need (filter database-side).

When these two are in opposition, you just need to find the right balance. If there's some uncertainty, that often mean you just need to build out both solutions and benchmark. (If the difference is expected to be significant.)
Copy linkTweet thisAlerts:
@chris0Oct 11.2011 — in the above case, using php would be weird , mysql is designed to do searching and sorting, as fast as it can get, while php is not designed for searching and storing big arrays of data, and it results in wasting allot of memory would could be significant on big sites or big data size.
Copy linkTweet thisAlerts:
@pavsidauthorOct 16.2011 — There are two best-practices:

Minimize trips to the database.

Don't return more rows than you need (filter database-side).

When these two are in opposition, you just need to find the right balance. If there's some uncertainty, that often mean you just need to build out both solutions and benchmark. (If the difference is expected to be significant.)[/QUOTE]

I think svdigen understands what i meant - sorry if i confused matters when talking about searching/sorting etc -

To explain it better, basically what i meant was that say i have 20 different fruits stored in the database. In a single script, i may want to grab any number of fruits - one, two or maybe all 20. The script is dynamic and so I can't be sure how many will be required.

So, with so few fruits to worry about, i'm guessing the most efficient way to handle this is to pull all 20 fruits from the database, store them in memory and refer to them as necessary, thus minimising the number of trips to the database (it would be bad to do 20 queries, even if this only were to happen once in every 100 page requests).

When i do something like this i look at the max amount of memory used by php using memory_get_peak_usage() and providing it is not more than a few MB to store the data then i'll do it.

My original question was at what point do you decide that multiple trips to the database are acceptable - how may is too many?

However, i guess svidgen has answered that one - you need to find the right balance, and benchmark if necessary ?
Copy linkTweet thisAlerts:
@touch_the_sky1Oct 16.2011 — i'm guessing the most efficient way to handle this is to pull all 20 fruits from the database, store them in memory and refer to them as necessary[/quote]

No.

Svidgen was right, but you seem to have misinterpreted the 'trips to database' bit?

Do it the way it's meant to be done. Provide results from database when requested (look up how to build your queries efficiently, use caching when possible, etc). Bad practice would be to get all rows from the table, while you needed only a particular one, etc.
×

Success!

Help @pavsid spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.20,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...