[RESOLVED] using a query's results in a query - better way?

krotkruton

I have a query that returns some information I need in another query. I can parse the information and send it to a new query, but off the top of my head, that seems to be innefficient. Also, I'd have to order the data properly so I could line everything up when I display it. I was wondering if there's a better way to do it.

Here's my original query:

"SELECT b.id, b.bar_name
	FROM barlist b, userdestinations ud
	WHERE (b.id = ud.first_bar OR b.id = ud.second_bar OR b.id = ud.third_bar)
		AND ud.id = '$userid' AND ud.date = '$current_date' AND b.id != 0";

I have a table, barlist, with fields 'id' and 'bar_name' along with some other stuff that doesn't really matter. The other table is userdestinations, with fields 'id', 'first_bar', 'second_bar', 'third_bar', and 'date'. The bars in userdestinations are 'id' fields from the barlist table, and the 'date' is represented as php's date("Ymd") or 20080130, for example. This query returns the 'id' and 'bar_name' of each bar a user plans on visiting on the specified date (b.id != 0 is used to throw out my fake bar in case the user only picked a 'first_bar').

So basically, after running that query, I have the 'id' field for up to 3 bars. My goal (and the reason for this post), is to use those id's to get the count of every user who might be going to each of those bars on the given night. If we knew those 3 bars had the 'id's 4, 7, and 12, then it's a quick fix to change the last query and get the desired results (I think), but I'm looking for a better way than running two queries. Here's the query that will get my results with those numbers:

"SELECT b.id, b.bar_name, count(b.id)
	FROM barlist b, userdestinations ud
	WHERE (b.id = ud.first_bar OR b.id = ud.second_bar OR b.id = ud.third_bar)
		AND ud.date = '$current_date' AND b.id IN (4,7,12)";

All I did was remove where it specified the $userid and added the "b.id IN (4,7,12)" part. I can use implode to get (4,7,12) if I need to, but I feel like I'm missing something that could make this easier and combine them into one query. I really don't want to run two queries if I don't have to. I think it's better to do it in one query for performance reasons (although I admit I don't know too much about the performance side of queries), but I also don't want to have to add the extra code to sync the two queries up when I display them. Does anyone know how to combine them?

Sorry for the long post, but if anyone has any suggestions, they'd be greatly appreciated. If I left out any information you might need, let me know.

rulian

You can use JOINS to return rows in barlist that match userdestinations and run the match against the date on the ud table. that way it will return the matching id's and fetch those results from the barlist table.

I cant say how the query should be until I see the table structures, but I see that you have 3 fields on the ud table for 3 different bars.

just thinking aloud, but it looksl iek you can normalize this table to have user id's, bar id's and table, that should make your joins a bit easier as well

krotkruton

Yeah, I probably should normalize these tables, but it isn't really a viable option at this point because there's a whole lot of code that was written for this structure. You're definitely right though that should be a better way to do it.

I don't completely follow what you mean for using JOINS. I'm still foggy on how JOINS work, especially the difference between left, right, and outer, so I don't see where I could go from here.

Here's the relevant table structures (I could give you the whole table if you want, but I think that would just unnecessarily take up a lot of space)

barlist:
id int (11)
bar_name varchar(50)

userdestinations:
id int (11)
first_bar int (11) (barlist id)
second_bar int (11) (barlist id)
third_bar int (11) (barlist id)
date int (8) (php's date("Ymd"))

Is there more information that you need about the tables? Sorry if I'm not providing enough, I don't know exactly what to put here. Thanks for the help.

rulian

JOINS are exactly that, the JOIN different tables based on clauses, much like a where clause limits what a select returns, except joins will return rows on table B for each row on table A, or vice versa, so let say if your table B is dependent on your table A, each table A row can have multiple rows on table B returned.

which si what you are looking for
so try someting like this

SELECT `A`.`date`, `B`.* FROM `userdestinations` AS `A` 
LEFT JOIN `barlist` AS `B` on `B`.`id` IN (`A`.`first_bar`, `A`.`second_bar`, `A`.`third_bar`) 
WHERE `A`.`date` = '$current_date'

or something to that effect

krotkruton

Thanks, that helped a lot. I modified that query to get me what I want, but I'm still having one minor problem. Here's my code:

query_tonight2 = "SELECT `ud`.`date`, `b`.*, count(`b`.`id`) as `user_count` 
	FROM `userdestinations` AS `ud`
	LEFT JOIN `barlist` AS `b` on `b`.`id` 
		IN (`ud`.`first_bar`, `ud`.`second_bar`, `ud`.`third_bar`) 
		AND `b`.`id` != 0 AND `ud`.`date` = '$current_date'
	GROUP BY b.id";
$tonight2 = mysql_query($query_tonight2, $conn) or die("Failed query2: " .mysql_error());
while ($tonight_row2 = mysql_fetch_assoc($tonight2)) {
	echo "<a href=/".../" 
		. $tonight_row2['id'] . "\">" . $tonight_row2['bar_name'] . "</a> "
		. $tonight_row2['user_count'] . "a" . $tonight_row2['id'] . "<br>";
}

And the output is:

0a
test_bar_1 1a1
test_bar_2 2a2

Obviously there are some formatting issues there, but I put that in so I could see what's going on. For some reason, my first row has a count of zero but doesn't have a barlist.id associated with it, so I don't know where that's coming from. There are 3 entries in my table, one of them has barid = 0, but I would think that id would show up if it's counting it. Regardless, it's in the talbe at least a few times for that date so it shouldn't have a count of zero anyway.

Do you have any idea why I'm getting that first result? Other than that, the query is working perfectly. Thanks again for the help.

rulian

it looks like somebody went to a bar that doesnt exist, and its not matching any bar names.
you can do an expection so that if $tonight_row2['user_count'] == 0 you dont echo it

krotkruton

Yeah, I thought about doing that, but I was worried that the query might not be returning the correct results. As long as the results are correct, I'll just add that exception and call it a day. Thanks again for the help.