Getting Bad Count from Query

krotkruton

I've got this query that isn't giving me the corrent count on instances. Basically it is supposed to count the number of times a specific ID number appears in the table. Here's the query:

$query_popular = "select b.*, (count(t1.first_bar) + count(t2.second_bar) + count(t3.third_bar)) as 'bar_count'
	from barlist b
	left join userdestinations t1 on b.id = t1.first_bar
	left join userdestinations t2 on b.id = t2.second_bar
	left join userdestinations t3 on b.id = t3.third_bar
	where b.city = '$city'
	group by b.id order by bar_count desc";
$popular = mysql_query($query_popular, $connBarHopper) or die(mysql_error());
$row_popular = mysql_fetch_assoc($popular);

Where userdestinations has 5 fields: email, first_bar, second_bar, third_bar, and date. Each bar field is the ID number of a bar from the barlist table. Date is in the form date("Ymd"), although I don't think that matters.

I have 4 entries in the userdestinations table:
address1 5 4 1 20051118
address1 1 5 2 20050626
address1 1 4 5 20051122
address2 5 20051122
Notice that the last entry has a blank field for both second_bar and third_bar.

When I display b.id and bar_count in a table using a do while loop with mysql_fetch_assoc in the while part, I get the following results:
id bar_count
5 6
1 4
4 2
2 1
As you can see from the actual values in the table, the count I want should be
5 4
1 3
4 2
2 1

I admit I don't completely understand what the above code does entirely because I got it from someone who responded to another question I had, and then I modified it a bit. I don't completely understand how the joins work which is probably my problem. I'm also not completely sure if I can add up those counts...
Also, I'm more than a little confused with the fact that this similar query works fine:

$query_bars_num = "select b.*, 
		(count(t1.first_bar) + count(t2.second_bar) + count(t3.third_bar)) as 'bar_count' 
	from barlist b 
	left join userdestinations t1 
	on b.id = t1.first_bar and t1.date = '$date' 
	left join userdestinations t2 
	on b.id = t2.second_bar and t2.date = '$date' 
	left join userdestinations t3 
	on b.id = t3.third_bar and t3.date = '$date' 
	where b.city = '$hometown'     

	group by b.id
	order by bar_count desc";
$bars_num = mysql_query($query_bars_num, $connBarHopper) or die(mysql_error());
$row_bars_num = mysql_fetch_assoc($bars_num);

Any help is appreciated

Roger_Ramjet

I'm sure I'm not the only person who has looked at this and has not the foggiest idea what you are on about. Stop prevaricating like your app is a state sercret. Post the real table structure, a definitipon of the data in each field, and then an english explanation of what results you want and why. Maybe then someone will be able to help you.

krotkruton

Wow. Well I wasn't try to be secretive about it, the only thing I really left out was the email addresses. Those two tables I tried to describe are the exact data I have in my tables. Since, at the moment, I only have a few test bars in my database, the IDs only range from 1 to 5.
Userdestinations has 5 fields as I said above. The email field is varchar(30), each bar is int(11) which is the same as my ID field in my barlist table, and the date is stored as an int as well.
I thought it was clear when I said that I want it to count the number of times a bar's ID appears in the userdestinations table. The userdestinations table is a table filled with email addresses, dates, and bar id's indicating that the person with the email address plans to go to the three bars with the bar id's from the row on the date in the row. I used address1 and address2 just to avoid putting my other email addresses on there. The rest of the data is exactly what I have in my table.

I tried to give an example of what the query displayed under the line "id bar_count". To explain that, the query responds that for id=5, bar_count=6. Since id=5 appears in the table of values 4 times, I want id=5 with bar_count=4.

After that, I displayed another piece of code that works perfectly for me and only counts the number of times a barid appears in the table for a given date, which is exactly what I want it to do.

So, is that how you want the table structure displayed? Is there some better way to display it with tags or something or using a query to show it or phpadmin? I can see that I wasn't completely clear on what I wanted, but after getting some code that worked really well for half of my problem (counting for a specific date), I thought it would be better to try to ask for help understanding it than to just ask again with a slightly different question(counting all instances regardless of date).

With that said, maybe I misinterpreted your post, but I don't understand why you feel the need to be rude to me when I've done nothing to you. If I'm asking for help in an inappropriate manner, I'd like to know why so I can avoid doing it in the future. It was never my intention to waste anyone's time or anger someone, I'm just going about this the best I know how.

Roger_Ramjet

OK. I did not mean to be rude, sorry.

Even with your explanation, this still does not make a lot of sense, so I'm going to fill in the missing parts.

You have a table that contains person data, or should have.
You have a table that contains bar data.
You have a table that links person data to bar data, or should do.
It contains entries that show a specific person will visit 3 specific bars on a given date.
The order of the bars they will visit is not important.
You need to calculate how many people will visit each bar in total.

This is one of those problems that looks much simpler than it is, and is easy to get the wrong answer without realising it. Good job you are testing it with a simple dataset so that you know what the results should be.

A simple query with multiple left joins looks like it will work, as you tried, but it will not, as you found out. This is because the joins are implemented before the grouping so you will start with multiple rows for some bars after the first left join which will then each be joined in the secong left join, etc. The grouping will then come into play and count the extra rows.

Me. I'd just run three simple queries with 1 join each and then total the results in code. Foolproof in it's way.

I'm sure you could also run it with subqueries, or one of the non-standard and less used join types. Maybe even with some sort of union query as a subquery. But I'm not tempted to try, to easy to get the wrong results.

krotkruton

You definitely have the right idea about what I'm trying to do, and I completely aggree that it seems a lot easier than it is. I keep thinking that there will be some easy solution, but it doesn't seem like there is.

I guess it would have helped to point this out also, but this is supposed to be a count of the most popular bars, so I want to be able to order them by the number of people going to each bar. I can't think of how to do that with 3 separate queries because the ordering would be really hard.

Maybe it would be possible to create arrays with the data after the 3 queries are called... then I could set up two variables, one to carry the index of the array with the highest count and the other to carry the count, then I could find the next highest and its index until I go through the whole display... then display the information in each array according to which is highest...
I don't know if that would even work well, but I feel like my best luck would be to refine this query

Roger_Ramjet

The only way I can see to do this, and you got me hooked so I've tried loads of ways, is to use 2 queries:
1 to make a work table and then another to sum the results


// make table
"create table test
select b.id as id, count(u.bar1) as c 
from barlist as b left join userdestinations as u on b.id=u.bar1 group by b.id
union all
select b.id as id, count(u.bar2) as c 
from barlist as b left join userdestinations as u on b.id=u.bar2 group by b.id
union all
select b.id as id, count(u.bar3) as c 
from barlist as b left join userdestinations as u on b.id=u.bar3 group by b.id"

// this will result in 3 records for each bar in barlist

// then sum the counts

"select id, sum(c) as count from test group by id order count desc limit 10"

may need tweaking cos I used existing data from my own db to test it and of course the table and column names are different

krotkruton

Don't ya just hate it when something keeps running through your head that should be easy but you just can't figure it out. I'm having the same issue with the php mail() function and I can barely get any other work done because its all I can think about.

Anyway, I didn't test your last post yet because I didn't see it until now, but I found something that actually works... or at least it works for only a few values. I'll have to test it more before I'm convinced that it really works.

After a few tries, one of which actually added the three counts and then divided them by 3 which gave me the right results but in decimal form instead of integer, I came up with this:

$query_popular = "select b.*, (count(t1.first_bar)) as 'bar_count'
	from barlist b
	left join userdestinations t1 on (b.id = t1.first_bar OR b.id = t1.second_bar OR b.id = t1.third_bar)
	where b.city = '$city'
	group by b.id order by bar_count desc";

I guess since the ON part of the join is dependent (I don't know what better word to use there) on the bar ID being equal to either of the three bars, then the count(t1.first_bar) will count 3 times as often... or something like that. When I did

(count(t1.first_bar) + count(t1.second_bar) + count(t1.third_bar)) as 'bar_count'

I got results that were 3 times as large as they should have been.

You probably understand how/why this works better than I do, but I'm just glad it works (even though I'm still going to test more). Either way, I hurt myself last night and the pain killers are making it difficult to concentrate and actually think things through, so I'm going to have to put off any more analysis until tomorrow.

Roger_Ramjet

And of course it's obvious when you look at it. Should have spotted that myself. My ego tells me I would have if I'd started from scratch instead of seeing your queries, and had not been here all night cos I'm running housekeeping and maint routines that can't be done during the day. But that's only my ego talking.

It works cos you get a join whichever column the bar id is in. All you have to do is to count the joined rows, and you can do that with any column from the joined rows.