How to generate a TOP10 from a Wordpress database

[dr3ft]

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?

Edited September 28, 2015 by dr3ft

[Barand]

you could try

SELECT value
    , COUNT(*) as total
FROM FROM wp_rg_lead_detail 
WHERE field_number=8 or field_number=16 or field_number=20 
GROUP BY value 
ORDER BY total DESC
LIMIT 10

[dr3ft]

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

[Barand]

Hi Newbie,

 

If you are writing "Selecteer uw lied!" to the database then your validation is poor.

 

For the location you need a subquery to pull the "11" records. I am assuming the field "Lead_id" is the one that groups the 8,11,16,20 records together.

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 = 'Zoetermeer '               -- remove this line for all locations
GROUP BY location, value                       -- and remove location from ORDER and GROUP
ORDER BY location, total DESC
LIMIT 10

Edited September 29, 2015 by Barand

[dr3ft]

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

[dr3ft]

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

[dr3ft]

Topic can be closed!

[Barand]

To close it, click the "Best Answer" button in the most helpful reply

[Barand]

Your solution shows another flaw, apart from the lack of validation, in your site. You should never put user input directly into a query. Always sanitize it against SQL injection first, or (better) use a prepared query. Although changing to prepared queries id probably a problem given your wp library.

 

I had prepared a small working script for you so I may as well post it to demonstrate this

<?php
$mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$gemeente = isset($_GET['gemeente']) ? $_GET['gemeente'] : '';
$top10 = '';

/******************************************************************************
*** 
*** GET LOCATION SELECT OPTIONS FOR MENU
*** 
*******************************************************************************/
$sql = "SELECT DISTINCT 
          value 
        FROM 
          wp_rg_lead_detail
        WHERE 
          field_number = 11
        ORDER BY 
          value ";
$loc_options = '';
$res = $mysqli->query($sql);
while (list($locname) = $res->fetch_row()) {
    $sel = ($locname==$gemeente) ? 'selected="selected"' : ''; // select current gemeente
    $loc_options .= "<option $sel>$locname</option>\n";
}

/******************************************************************************
*** 
*** CREATE TOP10 LIST FOR CHOSEN LOCATION
*** 
*******************************************************************************/
if ($gemeente) {
    $gemeente = $mysqli->real_escape_string($gemeente);  // sanitize user input

    $sql = "SELECT location
                , value
                , COUNT(*) as total
            FROM wp_rg_lead_detail
                INNER JOIN
                (
                    SELECT lead_id 
                    , 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'
            GROUP BY location, value
            ORDER BY location, total DESC
            LIMIT 10";
    $top10 = "<ol>\n";
    $res = $mysqli->query($sql);
    while (list($loc, $song, $total) = $res->fetch_row()) {
        $top10 .= "<li>$song ($total)</li>\n";
    }
    $top10 .= "</ol>\n";
}
?>
<html>
<head>
<meta name="generator" content="PhpED 14.0 (Build 14039, 64bit)">
<title>Example</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="09/29/2015">
</head>
<body>
<h1>Top 10</h1>
<form method='get' action=''>
Select location <select name="gemeente">
    <option value=''> - location -</option>
    <?=$loc_options?>
</select>
<input type="submit" name="btnSubmit" value="Submit">
</form>
<hr>
    <?=$top10?>
</body>
</html>

[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

[maxxd]

Your solution shows another flaw, apart from the lack of validation, in your site. You should never put user input directly into a query. Always sanitize it against SQL injection first, or (better) use a prepared query. Although changing to prepared queries id probably a problem given your wp library.

 

Unfortunately, the closest you can come to a prepared statement in WordPress is their prepared statement. So, you could do

$gemeente = isset($_GET['gemeente']) ? sanitize_text_field($_GET['gemeente']) : '';
global $wpdb;

$sql = "
SELECT location
	, value
	, COUNT(*) as total
FROM {$wpdb->prefix}rg_lead_detail
	INNER JOIN
	(
		SELECT	 lead_id 
				,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 = '%s'
GROUP BY location, value
ORDER BY total, location DESC
LIMIT 10";

$posts = $wpdb->get_results( $wpdb->prepare( $qry, $gemeente ) );

Which is at least a bit safer - you've run the user input through the sanitize filter, and you've kinda used a prepared statement. If I'm not mistaken, the prepare() method does use mysqli_real_escape_string() at least before plopping the value into the query string. Still not the safest, but definitely better than what's currently happening.

 

Note that I switched the total and location values in Barand's ORDER BY clause. This should take care of your order question, though it's untested and I've only had one cup of coffee, so no guarantees...

 

 

According to the WP codex entry:

 

All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. The prepare method performs this functionality for WordPress, which supports both a sprintf()-like and vsprintf()-like syntax.

Edited October 24, 2015 by maxxd

[QuickOldCar]

Can also use esc_sql() on a quoted string