Combining Select Statements

[ironj221]

I have the following two statements:


$sql = ("SELECT * FROM applications");
$query = mysql_query($sql);
while ($app = mysql_fetch_array($query)) {
// do processing here
}

$sql = ("SELECT * FROM plugins ORDER BY location ASC");
$query = mysql_query($sql);
while ($plugin = mysql_fetch_array($query)) {
// do processing here
}

I have about 10 other code blocks like this, and I need this to be as efficient as possible. The purpose is to get settings for the site, so two questions:

1. Is having 10 different SQL statements like this really inefficient? If I could somehow combine everything into 1 statement, would I save a lot? Keep in mind this may run on sites with thousands of active users.

2. Is it possible to somehow combine these statements? The ideal solution would be something like this:

$row['TABLE_NAME']['FIELD']

for example:

$row['plugins']['id']
$row['applications']['id']

Thanks for the help!

[BubikolRamios]

provide tables structure, otherwise we are guessing, and that would not help you much.

I guess you have:
table application(id_application,...),
table plugins(id_application, location,...)

or ?

[ironj221]

provide tables structure, otherwise we are guessing, and that would not help you much.

I guess you have:
table application(id_application,...),
table plugins(id_application, location,...)

or ?

Sorry, I didn't realize that would help.

It is:

applications(id, name, folder, icon, width, height, link)
plugins(id, name, location)

The other tables are things like:

smilies(id, name, code)
config(config_name, config_value, is_dynamic)

None of the tables have anything to do with each other, so I thought things like join and union would not be appropriate. I just don't know. :confused:

[BubikolRamios]

None of the tables have anything to do with each other


You answered yourself. There is no need for join. Although I think that
some applications has one plugins other application other plugins, so applications and plugins should be related, but I might be wrong.

[ironj221]

You answered yourself. There is no need for join. Although I think that
some applications has one plugins other application other plugins, so applications and plugins should be related, but I might be wrong.
Regarding efficiency, is it bad to make 10 calls to the database like this every time a user loads a new page with thousands of active users?

[Old Pedant]

Define "thousands of active users".

You mean "thousands per second", "thousands per minute", "thousands per hour" or just "thousands in my universe of users"?

Unless it is one of the first two of those, don't worry about it. Queries like that don't have enough overhead to worry about.

Now, *if* you really and truly have thousands of users hitting your pages every single minute, then yeah, you should do something different.

It looks to me like those tables are likely to be ones that you update very infrequently, if ever. So what you could do is create a "batch" process that does those queries and creates a PHP file that has all those values as constant PHP arrays (or even as HTML or JavaScript). And you only update that PHP (or JS or HTML) file when and if there is a change in the DB in those table(s).

But to answer the original question: There's no way to do this except via individual queries. Oh, you could probably figure out a messy way to get everything in one big array of records and then use PHP to pull "chunks" out of the array. Ugh. Not worth it.

[ironj221]

Define "thousands of active users".

You mean "thousands per second", "thousands per minute", "thousands per hour" or just "thousands in my universe of users"?

Unless it is one of the first two of those, don't worry about it. Queries like that don't have enough overhead to worry about.

Now, *if* you really and truly have thousands of users hitting your pages every single minute, then yeah, you should do something different.

It looks to me like those tables are likely to be ones that you update very infrequently, if ever. So what you could do is create a "batch" process that does those queries and creates a PHP file that has all those values as constant PHP arrays (or even as HTML or JavaScript). And you only update that PHP (or JS or HTML) file when and if there is a change in the DB in those table(s).

But to answer the original question: There's no way to do this except via individual queries. Oh, you could probably figure out a messy way to get everything in one big array of records and then use PHP to pull "chunks" out of the array. Ugh. Not worth it.

Ah, thank you very much. I'm creating a script for other websites, so that is why I need to be as efficient as possible because some of my customers have very large audiences.

I had thought of your "cache" idea before, and I think that is the route that I'm going to take.

Thanks again.

[Old Pedant]

FWIW: If those tables/queries are used for creating <select> HTML elements, I prefer creating ".js" files and then using JS to build the <select>s *and*, when the user has already chosen one value and you want to reflect that, doing the pre-select.

Now, that only works if you are willing to insist that all users allow JavaScript. But even if you don't opt for that, at least dump them into PHP arrays and then use PHP to convert the array into the <select>, again with the prior user-chosen value pre-selected.