SQL and Function Definition

I frequently find myself questioning the best way to organize functions with mysql inside. Using functions is generally more organized, but a lot of the time the organization is mitigated by the fact that a better query could be created to run a lot faster.

For example, say one function on your website needs to check if a user’s account is activated. You’ve created a function, checkActive($user), that queries the database to see if the user’s account has been activated. Later on, you find yourself on another page that needs to check if a group of users is activated. In an ideal world, you’d be able to loop through your users and run checkActive() on each one. But that’s too many queries, so you make a second method checkGroupActive($users).

I’ve oversimplified this for the sake of explanation, because in this case you would just always use checkGroupActive() and pass a group size of 1. But does anyone else find that they run into similar issues often? Do you have some method of organizing functions and SQL that you swear by? I’d love to hear about it.

Thanks,
e39m5

Use prepared statements. Executing the same statement only sends new data, the database already pre-compiled the SQL from the first request. Another option is to dynamically generate the query in the function based on the information it is given. For exampling passing an array to “checkActive” would fetch for that whole group, etc. Or write the SQL every time you need it.

You could think about how to share such similar single table requests among many other applications by making it do more and yet be a bit more generic.

You are then really getting into the realm of OOP, there are many reasons to use OOP - and they are going to make more sense to you when you already rely on functions - but the one of the biggest is to promote code reuse.

e.g.


$row = new Users ;
$row->setFields("active") ;
$row->getById( $user );

Which results in the following query:

“select active from users where id = $user”

but you could see how that could be built up to take a variable amount of fields, as was said previously.


$rows = new Users ;
$rows->$setFields(array('name','address','tel'));
$rows->getAll("salary > 10000") ;

Which results in the query:

“select name, address, tel from users where salary > 10000” ;

This is a kind of half-way house idea and will not be the answer to every prayer - it is somewhere between where you are now and a pattern of OOP code known as an ActiveRecord.

Class Users builds upon some kind of simple sql generator, and then contains any special one-off queries you need.

But for much of the basic readonly stuff building on this idea will probably do much of the work for you, and might lead you in the direction of at least reading up on what OOP is about.