supertrucker Posted September 7, 2008 Share Posted September 7, 2008 I have a need for a single query, albeit (to me) a very complicated one...Here's what I need to do:1. Lookup each user that is in room $ChatRoomId (the LastChatRoom field in the user table tells us this).2. If the LastChatRoom flag is greater than 0, assume they are in the room.3. If the user in this row has been inactive (I have an activity time field in the user table) then change their LastRoomFlag to 0 (does sql have "if" statements?).4. Return the number of users in this room that are active.It's complicated, and involves nesting queries in PHP. Can I do all of this in a single SQL statement somehow? The w3schools tutorials don't really get into this kind of stuff...Thanks for any insight or assistance!ST Link to comment Share on other sites More sharing options...
Skemcin Posted September 8, 2008 Share Posted September 8, 2008 You basically have two options here,a.) combine inline queries with some PHP logic to perform the task as you outlined it - pretty much line for lineb.) create a stored procedure to do all the work and make a single request with php to yield the end result.This is a good opportunity to learn how to leverage stored procedures. Link to comment Share on other sites More sharing options...
supertrucker Posted September 9, 2008 Author Share Posted September 9, 2008 1. What do you mean by inline queries... subsequent sql queries in a single PHP statement?2. What do you mean by stored procedure? Thanks!ST Link to comment Share on other sites More sharing options...
Skemcin Posted September 9, 2008 Share Posted September 9, 2008 Inline Queries would be SQL statements that you call from your PHP code, one after the other. So lets say you have a database table with just usernames and passwords that produce and ID for a person. Then you have a table with all your people that the ID relates to. Then, say you have more seperate tables with more information that you need - again all maybe based on that ID. Inline queries would simply be a series of queries that you would call with PHP which would then extract what it needs to make the next request.Stored Procedures are a set of SQL statements (like above) compiled into one entity and are stored on the server in the database itself - rather then in the server side language code (php) and separated out. Since ti is already compiled, the server doesn't need to process the SQL statement, just re-execute it. Additionally, they extend the functionality of regular inline queries since they can manipulate the information as it is produced - i.e. build temporary tables.That should be a pretty good overview to get you acclimated. You'll need to do a little research on how to do these and to determine what is best for you given your project time line. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.