dr3ft

Help, something isn't going well for now. It looks pretty great, but now, when we went live and people start voting. Every refresh of the page gives another result.. I think we are ordering on location, But we need to order the results by choosen songs. Can someone have a look at the code again? Thank you Jeroen

Topic can be closed!

Hello, Thank you for your help, I already got it! <?php $gemeente = $_GET['gemeente']; global $wpdb; $results = $wpdb->get_results("SELECT location , value , COUNT(*) as total FROM wp_rg_lead_detail INNER JOIN ( SELECT lead_id -- subquery to find location for the song choices , value as location FROM wp_rg_lead_detail WHERE field_number = 11 ) loc USING (lead_id) WHERE field_number IN (8,16,20) AND value <> 'Selecteer uw lied!' AND location = '$gemeente ' -- remove this line for all locations GROUP BY location, value -- and remove location from ORDER and GROUP ORDER BY location, total DESC LIMIT 10"); if(!empty($results)) { foreach($results as $r) { echo "<p>".$r->value."</p>"; } } else { echo "<p>Boo, we couldn't find anything that is in all these groups. Try removing a category!</p>"; } ?> Now i can create a view for every Location based on the url i give in! Thank you verry much! Jeroen

Oh wow what a coding. I glad you help me out here. It looks great now. If you are writing "Selecteer uw lied!" to the database then your validation is poor. It should be possibel to just submit 1 song, so the other 2 choices stay 'selecteer uw lied!'. But now it doesn't matter. The last question, could i set 'zoetermeer' through a url parameter? /top10/?gemeente=zoetermeer So i set someting like this: $gemeente = $_GET['gemeente']; And in the query WHERE field_number IN (8,16,20) AND value <> 'Selecteer uw lied!' AND location = '$gemeente' -- in stead of the location GROUP BY location, value When i create a url, the query wil fill with the parameter behind gemeente? Thank you. Jeroen

Hello Sen, That is great! It works, there was a little mistake in the 3rd row. you entered FROM twice. After that the solution was there. GREAT work Thank you verry much. Now i have two more questions. A. People could select: "Selecteer uw lied!". (This isn't a song, it is the first option so people are going to select their song) Is it possible to exclude this option from the query, so in my top 10 you won't see it? You can see the name here at the bottom where it is selected twice: http://screencast.com/t/v24zqvDCw B. This query generates a TOP10 from all places in Holland. Now i would like to get a TOP10 by places. In the screencast you can't see it, but Field_number 11 has places in the column values (Harderwijk, Zoetermeer etc). Can i create a list based on the query above only for Zoetermeer? And if that is possible would i be possible to fill the value Zoetermeer, bases on an URL parameter? /top10/?gemeente=zoetermeer Thank you verry much Jeroen

Hello, I would like to create a TOP10 from choosen songs. Visitors are coming to our site and can vote for their song. They could select a Top 3 from a drop down. See here: http://screencast.com/t/CegeqOqBWOEG Now we would like to show the TOP10 of most choosen songs. This is how the database looks: http://screencast.com/t/v24zqvDCw The id's 8, 16 and 20 could have the same value. So i grouped them. Now i need to count how many value's are the same and order them. With the most counted at top of them. This is the code I use now: <?php global $wpdb; $results = $wpdb->get_results("SELECT * FROM wp_rg_lead_detail where field_number=8 or field_number=16 or field_number=20 GROUP BY value LIMIT 10;"); if(!empty($results)) { foreach($results as $r) { echo "<p>".$r->value."</p>"; } } else { echo "<p>Boo, we couldn't find anything that is in all these groups. Try removing a category!</p>"; } Can someone please help me out and tell me how I could create the code so I get a page with a TOP10?