Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What I am trying to do is set up a selection filter for a cascading list on my site. When I say selection filter, I mean I am trying to limit, and sort the results based on what the user selects.

I have set up the ability for my users to click a button and mark an item as a favorite. That information is stored in "billdata.favoritebills".

Also, for efficiency I have saved vote totals for each bill in billdata.billvotesmeta. billdata.billvotesmeta contains the column names: billid,totalvotes,yesvotes,presentvotes, and novotes. The primary key is billid.

My database is set up as follows:

billdata.bills

billdata.favoritebills

billdata.billvotesmeta

In this particular filter I am trying to set up, I want to limit the results to only being ones that the user has marked as a favorite and at the same time I want to order them from least to greatest total number of votes.

billdata.favoritebills contains the column names: userid and billid and a combination either exists in marked as a favorite or is deleted if it is unmarked as a favorite.

$testquery = "SELECT * FROM bills INNER JOIN bills ON billdata.billvotesmeta.billid = billdata.bills.id WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) ORDER BY DESC billdata.favoritebills.totalvotes";

$result = mysqli_query(dbcxn('bill'),$testquery)
     or mysqli_debug();

$fetched = mysqli_fetch_assoc($result);

The resulting output from PHP is:

Warning: mysqli_debug() expects exactly 1 parameter, 0 given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 38

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 39
1NULL

What should happen is that it limits the billdata.bills.* selection to only being favorites and then orders them by the joined table's total vote count. The data from billdata.bills is the information used to build the page.

I think the problem is in the " WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) maybe this is not compatible with a query that has a join as I have not found anywhere else detailing a WHERE IN clause along with a join. "

share|improve this question

closed as off-topic by Mark Storey-Smith, Paul White, RolandoMySQLDBA, Mat, StanleyJohns Aug 15 '13 at 0:59

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, Paul White, Mat, StanleyJohns
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

not familiar with mySQL syntax, but I believe there are multiple errors with your query. you've got an extra ")" or you tried to use "order by" in a subquery, the join clause looks weird, it's returning everything and there is no group by with sum(votes), etc.

For problems like these, it is best to start with a working query and expand it one step at a time. For example, first join 2 tables, then change the * to the list of fields you want, then restrict it with the third, then sum up votes, then sort them, ...

try:

SELECT b.id,sum(bm.totalvotes)
FROM 
bills b 
inner join favoritebills fb on b.id = fb.billid
inner join billvotesmeta bm on b.id = bm.billid
WHERE 
userid='$_SESSION[userid]'
group by b.id
order by sum(bm.totalvotes) desc;

You may have to rename some fields. the names aren't clear to me.
if you want to add fields to your resultset, be sure to add them to the group by clause at the bottom too.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.