Reducing queries to the DB

[dodgechargerfan]

I am relatively new to this stuff and I'm trying to wrap my head around a concept before I try a little bit of coding on my board.

SELECT field1 from prefix_table where field5 = 1;
SELECT field2 from prefix_table where field5 = 1;

Since I am trying to match on the same field, I figure there must be a way to get both pieces of data with one query.

I think I need to use an array, but can I just grab the fields I'm intersted in, or is it easier to move all of the data into an array and then just reference the fields with array[field1], array[field2].... etc...?

[Brad]

If you're working with the same table you can get them like this:

SELECT field1, field2 FROM table

You can also use a * if you want to grab everything from the table at once:

SELECT * FROM table

If you're working with two or more tables you can use a join to get fields from multiple tables in one query. For example:

SELECT user.username AS username, role.roleid AS roleid FROM user LEFT JOIN role AS role (user.usergroupid = role.usergroupid) WHERE user.posts > 5 GROUP BY user.usergroupid ORDER BY username ASC

The first thing you'll notice is the field names have changed. When working with multiple tables it's important to define which table you are trying to get information from, thus you should always prefix the field name by adding the table name infront of it: tablename.fieldname. To make this a little easier you can use the AS command to map a new name to a field, in the above example user.username would simply become username.

The next important thing is our join, in this example I've used a LEFT JOIN.

LEFT JOIN role AS role (user.usergroupid = role.usergroupid)

role is the table name we will be joining, the bit of text inside the ( - ) tells mySQL to look for matches based on common data in both tables. In this example the common data is the usergroupid but it can be anything. You can also do multiple joins in one query if you need to work with more than two tables at the same time.

[dodgechargerfan]

Thanks. That helps a lot.

From what I see, then, if I want to process all of the results from the query, I'd be best off to build a product plug-in with a function that will roll through all of the returned records using a while() function. I think I can piece that together from other coders' examples.