Count not working well

PHP
1

This below is my count code


    $query = "SELECT a.*, COUNT(b.bidder) as COUNT FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
        WHERE a.user = :user_id AND a.closed = 1 AND a.num_bids > '0'  ";

the code below shows the table and only display 6 items but the count above displays 11

SELECT a.*, bc.all_clicked AS all_clicked_fts, bc.nottagged_clicked, bc.team2_clicked, bc.team1_clicked, bc.paid_error_clicked AS paid_error_clicked_fts, b.auction AS auction, b.bid, u.nick, u.id AS nick_id, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, c.cat_name FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "users u ON (u.id = a.user)
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
        LEFT JOIN " . $DBPrefix . "categories c ON (c.cat_id = a.category)
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
        LEFT JOIN " . $DBPrefix . "buttonclicked_score bc ON (bc.auction_id = b.auction)
        
        WHERE a.user = :user_id  AND b.quantity = 1 AND a.closed = 1 AND a.num_bids > '0'  GROUP BY a.id

what am i doing wrong here

2

You’ve got an extra condition in the second query, and b.quantity = 1. Is that the difference?

3

nope heres the full code below

this is the count

// Retrieve close games from the database
$params = array();
if (isset($Q))
{
    $query = "SELECT a.*, b.bid, COUNT(b.bid) as COUNT FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
" . $Q;
}
elseif (isset($_POST['keyword']))
{
    $keyword = $system->cleanvars($_POST['keyword']);
    $query = "SELECT a.*, b.bid, COUNT(b.bid) as COUNT FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
            WHERE a.user = '1' AND  a.closed = 1  AND b.quantity = 1 AND (b.id LIKE :id)";
    $params[] = array(':team1', '%' . $keyword . '%', 'int');
    $params[] = array(':team2', '%' . $keyword . '%', 'int');
    $params[] = array(':id', '%' . $keyword . '%', 'int');
    $params[] = array(':nick', '%' . $keyword . '%', 'str');

    
}
else
{
    $query = "SELECT a.*, b.bid, COUNT(b.bid) as COUNT FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
    WHERE a.user = '1' AND a.closed = 1 AND b.quantity = 1 ";
    
}
$db->query($query, $params);
$TOTALUSERS = $db->result('COUNT');

while this displays the table

$params = array();
if (isset($Q))
{
    $query = "SELECT a.*, bc.all_clicked AS all_clicked_fts, bc.nottagged_clicked, bc.team2_clicked, bc.team1_clicked, bc.paid_error_clicked AS paid_error_clicked_fts, b.auction AS auction, b.bid, u.nick, u.id AS nick_id, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, c.cat_name FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "users u ON (u.id = a.user)
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
        LEFT JOIN " . $DBPrefix . "categories c ON (c.cat_id = a.category)
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
        LEFT JOIN " . $DBPrefix . "buttonclicked_score bc ON (bc.auction_id = b.auction)
        " . $Q;
}
elseif (isset($_POST['keyword']))
{
    $query = "SELECT a.*, bc.all_clicked AS all_clicked_fts, bc.nottagged_clicked, bc.team2_clicked, bc.team1_clicked, bc.paid_error_clicked AS paid_error_clicked_fts, b.auction AS auction, b.bid, u.nick, u.id AS nick_id, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, c.cat_name FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "users u ON (u.id = a.user)
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
        LEFT JOIN " . $DBPrefix . "categories c ON (c.cat_id = a.category)
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
        LEFT JOIN " . $DBPrefix . "buttonclicked_score bc ON (bc.auction_id = b.auction)
        
        WHERE a.user = :user_id  AND b.quantity = 1 AND a.closed = 1 AND a.num_bids > '0' AND (s.teams LIKE :team1 OR ss.teams LIKE :team2 OR a.id LIKE :id)  GROUP BY a.id";
    $params[] = array(':team1', '%' . $keyword . '%', 'int');
    $params[] = array(':team2', '%' . $keyword . '%', 'int');
    $params[] = array(':id', '%' . $keyword . '%', 'int');

    
}
else
{
    $query = "SELECT a.*, bc.all_clicked AS all_clicked_fts, bc.nottagged_clicked, bc.team2_clicked, bc.team1_clicked, bc.paid_error_clicked AS paid_error_clicked_fts, b.auction AS auction, b.bid, u.nick, u.id AS nick_id, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, c.cat_name FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "users u ON (u.id = a.user)
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
        LEFT JOIN " . $DBPrefix . "categories c ON (c.cat_id = a.category)
        LEFT JOIN " . $DBPrefix . "bids_score b ON (b.auction = a.id)
        LEFT JOIN " . $DBPrefix . "buttonclicked_score bc ON (bc.auction_id = b.auction)
        WHERE a.user = :user_id AND b.quantity = 1 AND a.closed = 1 AND a.num_bids > '0' GROUP BY a.id ORDER BY a.ends DESC LIMIT :offset, :perpage";

}
$params[] = array(':offset', $OFFSET, 'int');
$params[] = array(':user_id', $user->user_data['id'], 'int');
$params[] = array(':perpage', $system->SETTINGS['perpage'], 'int');
$bg = '';

not sure why this count is given issue

4

update am getting closed now am now having a grouping issue

value with same id number counts as multiple for example

coloumn “auction”
125
125
234
345
345

total should be 3 instead by getting five and when i try to use group by auction i get 2

anytime i use group by auction its give a different number

count code below

$query = "SELECT a.*, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, COUNT(b.auction) as COUNT FROM " . $DBPrefix . "bids_score b
        LEFT JOIN " . $DBPrefix . "auctions a ON (b.auction = a.id)
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
" . $Q;
}
elseif (isset($_POST['keyword']))
{
    $keyword = $system->cleanvars($_POST['keyword']);
    $query = "SELECT a.*, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, COUNT(b.auction) as COUNT FROM " . $DBPrefix . "bids_score b
        LEFT JOIN " . $DBPrefix . "auctions a ON (b.auction = a.id)
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
        WHERE a.user = :user_id  AND  a.closed = 1 AND a.num_bids > '0' AND (s.teams LIKE :team1 OR ss.teams LIKE :team2 OR a.id LIKE :id) ";
    $params[] = array(':team1', '%' . $keyword . '%', 'int');
    $params[] = array(':team2', '%' . $keyword . '%', 'int');
    $params[] = array(':id', '%' . $keyword . '%', 'int');

    
}
else
{
    $query = "SELECT a.*, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, COUNT(b.auction) as COUNT FROM " . $DBPrefix . "bids_score b
        LEFT JOIN " . $DBPrefix . "auctions a ON (b.auction = a.id)
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
        WHERE a.user = :user_id AND a.closed = 1 AND a.num_bids > '0'  LIMIT :offset, :perpage";
    
}

thank you for ur assistance

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.