The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Best Practice - mysql database query

Lets say I have a messaging section of my site. In this site, there are various SELECT queries:

- Query #1 SELECTS all the message body details, and whoever wrote the message, based on message_id
i.e. SELECT message.body, message.from_account_id
FROM message, account
WHERE message.message_id = $message_id
AND account.account_id

- Query #2 SELECTS just the message body, based on message_id
i.e. SELECT message.body
FROM message
WHERE message.message_id = $message_id

The queries above are just examples I thought off the top of my head.
Now my question is, is there a way to generalize all these queries, so that I don't have to keep on writing queries over and over, or do I have write these different queries as there are all on a different case by case basis?

The user then clicks on reply, to reply to the respective message. So we call another function to reply to the message. This function
quotes the original message in the reply message body. In order to quote it, we have to get the body of the original message. So we call this
function:

Should I have written out all these distinct queries based on the requirements or is there a better way? In the first function, to display who the message is from, I could have, instead of using a join, just SELECT from the message table, and use getUsernameFromAccountId() but is the better way?

Also, please note that this is just an example and I just put the, into functions for simplicity sake and to try to illustrate what I'm try to convery
as clearly as possible. This could have actually been put into the class methods in a data access layer.

Now lets say a user has a listing of messages in his box. He clicks on one of the messages, and it pops up another window to show the
full message with who its from. So we call this function:

so far so good, sort of

after all, you had to have already run a query to get his list of messages, and that's where you should already have retrieved who each message is from -- in fact, the only thing you should be missing is the body of the message clicked on (whoch you would not retrieve when retrieving a list of messages)

The user then clicks on reply, to reply to the respective message. So we call another function to reply to the message. This function
quotes the original message in the reply message body. In order to quote it, we have to get the body of the original message.

nope, you would've got that in order do display it so that he can reply to it

so i just saved you one complete call

Then after it gets the original message and quotes it, it produces a message form for the user to reply to: ... which uses getUsernameFromAccountId(), since there are other places in my app where I have to derive the username when given the account_id:

fine, if there are other places where you need that, but this isn't one of them, you already know who it's from!!

Also, please note that this is just an example and I just put the, into functions for simplicity sake and to try to illustrate what I'm try to convery
as clearly as possible. This could have actually been put into the class methods in a data access layer.

i appreciate that this is just an example, but i would advise you to look carefully at everything you do, so as not to call the database needlessly

and as far as classes are concerned, good luck with them, 'cause i have no idea what those are

Are you giving advice for offline applications? Because I was reading this thread and not following your advice. Each action here -- listing messages, viewing an individual message, replying to a message, are separate executions of the scripts, whatever language it's in. The results of the query to build the list of messages are not available to the page that shows an individual message clicked on...

Gotcha. So in the example I showed, I should have persisted my data on each database call with session variables instead of unnecessarily calling the database on each new HTTP request? This would have the been the best practice?