Group by is not showing all the rows

2 Views

I am working in WordPress and below is my select query. I have used leftjoin and group by. But only one row is returned if I have duplicate entries in my articles.username coloumn. So I want all the rows to be returned with group by and duplicates should be allowed in username field.
PHP Code 

$sqll = "SELECT articles.aid, articles.username, articles.competition, articles.path, articles.category, articles.title, Sum(zvotes.zvotes) AS votessum FROM articles LEFT JOIN zvotes on articles.aid=zvotes.aid GROUP BY articles.competition HAVING articles.category = '$cat' && articles.competition = '$comp' ORDER BY votessum";

    $results = $wpdb->get_results($wpdb->prepare($sqll)) or die(mysql_error());

Snapshot of articles table
This is the articles table

Read More

Snapshot of votes table (currently no data in it)
Votes table

Below is my full code 

echo '<form action="" method="post">';
echo '<select name="category" id="category" style="width:250px; background-color:lightgrey;">';
echo    '<option value="" disabled="disabled" selected="selected" ">Select category</option>';
echo   '<option value="My Testimony">My Testimony</option>';
echo    '<option value="Love & Relationships">Love & Relationships</option>';
echo    '<option value="Miscellaneous">Miscellaneous</option>';
echo '</select>'; 
echo    '<input type="submit" name="a" value="Search"  style="margin-left:15px; margin-bottom:15px;">';
echo '</form>';


//show after drop down value is selected
if(isset($_POST['a'])){
//echo "zeeshanaslamdurrani". "<br>";

echo do_shortcode('[ujicountdown id="Photos Contest" expire="2015/04/30 00:00" hide="true" url="" subscr="sdf" recurring="" rectype="second" repeats=""]');
      global $wpdb;
//get current competition value
$cat =$_POST['category'];
        $comp = $wpdb->get_var("SELECT competition FROM competition ORDER BY cid DESC LIMIT 1");
//echo $comp;
        $sql = "SELECT * FROM articles WHERE category='$cat'";

      $comp = $wpdb->get_var("SELECT competition FROM competition ORDER BY cid DESC LIMIT 1");
            echo "current competition is ". $comp;

//test query
      $sqll = "SELECT articles.aid, articles.username, articles.competition, articles.path, articles.category, articles.title, Sum(zvotes.zvotes) AS votessum FROM articles LEFT JOIN zvotes on articles.aid=zvotes.aid GROUP BY articles.competition HAVING articles.category = '$cat' && articles.competition = '$comp' ORDER BY votessum";

$results = $wpdb->get_results($wpdb->prepare($sqll)) or die(mysql_error());

foreach( $results as $result ) {
echo '<form action="" method="post">';
echo "<input name='category' type='hidden' value='$result->category'>";
echo $result->title.'<br>';
echo "<img src='$result->path' width='150' height='150' >" . '<br><br>';
echo $result->body.'<br>';
echo "<input name='comp' type='hidden' value='$result->competition'>";
echo $result->username.'<br>';

echo $result->votessum.'<br>';
echo "<input style='margin-bottom:30px;' value='vote' name='submit' type='submit'/></form>";    

}//end of foreach




}//end of isset

I have a drop down on the top of the page and a search button as shown below on pressing search the results are shown but if I add duplicate values in username field of articles table I get only 1 row in result.
My pageenter image description here

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. If you want to show each user, then I think you should be aggregating by the user. In fact, you should be aggregating by every column in the SELECT that is not an argument to an aggregation function.

    This may do what you want:

    SELECT a.aid, a.username, a.competition, a.path, a.category, a.title,   
       Sum(z.zvotes) AS votessum
FROM articles a LEFT JOIN
     zvotes z
     on a.aid = z.aid
WHERE a.category = '$cat' AND a.competition = '$comp'
GROUP BY a.aid, a.username, a.competition, a.path, a.category, a.title
ORDER BY votessum";