For each queries

Reformed

What is the best way to go about making a foreach query? I have an array of user id's and I need to perform a query for each id. I have one query that gets the id's from a database table and I want to take those id's and get a record from another table for each one. Any suggestions?

AstroTeg

Inner join or left join whenever possible. Depends on the data's relationship(s)...

Reformed

Here is what I'm starting with:

//for each user of the system, query them and get their id's
		$dbObj = new CommonQueries();
		$userQuery = "SELECT usr_id FROM burb_users";
		$userList = $dbObj->getAllQuery($userQuery);

	foreach($userList as $key=>$value) {
		$timeQuery = "SELECT ts_id, ts_total_time FROM burb_timesheets WHERE ts_id = '$key'";
		$key = $dbObj->getAllQuery($timeQuery);
		print_r($key);

	}

LordShryku

Should be able to do it in one query with a join. Try this:

$sql = "SELECT bu.usr_id,
               bt.ts_id, 
               bt.ts_total_time
          FROM burb_users bu, burb_timesheets bt
         WHERE bu.usr_id = bt.ts_id";

Reformed

I'm still struggling with this problem. I may not have been very clear in describing what I'm trying to do. I have an array of integers and I simply need to query my database for each integer in my array. Can anyone offer advice as to do this, or has it been covered in the above posts and I'm just missing something?

AstroTeg

Where is the list of IDs coming from? See, if those IDs are coming from another table, it would make sense to do 1 query instead of 2 (its a lot faster for everyone involed).

But if you're taking IDs from say, a web form, then you could do a list and query that way:

SELECT myfield FROM mytable WHERE myID IN ([comma seperated list of IDs)

So its just a matter of getting the IDs into a comma seperated list...

Reformed

That's it. I had no idea that it was possible to use comma seperated values for a query. This is great and just what I needed. The IN clause is the answer to my problems. Thanks a lot.