Am having some search query issue

PHP
1

i have this code below

$params = array();
if (isset($Q))
{
    $query = "SELECT a.*, u.nick, u.id AS nick_id, COUNT(a.id) as COUNT, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2 FROM " . $DBPrefix . "auctions a
    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 . "users u ON (u.id = a.user)
" . $Q;
}
elseif (isset($_POST['keyword']))
{
    $keyword = $system->cleanvars($_POST['keyword']);
    $query = "SELECT a.*, u.nick, u.id AS nick_id, COUNT(a.id) as COUNT, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2 FROM " . $DBPrefix . "auctions a
    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 . "users u ON (u.id = a.user)
            WHERE s.teams LIKE :team1 OR ss.teams LIKE :team2 OR u.nick LIKE :nick OR a.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.*, u.nick, u.id AS nick_id, COUNT(a.id) as COUNT, s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2 FROM " . $DBPrefix . "auctions a
    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 . "users u ON (u.id = a.user)
    WHERE a.closed = 1 AND a.num_bids = 0 ORDER BY a.id DESC ";
    
}
$db->query($query, $params);
$TOTALUSERS = $db->result('COUNT');
// get page limits
if (isset($_GET['PAGE']) && is_numeric($_GET['PAGE']))
{
    $PAGE = intval($_GET['PAGE']);
    $OFFSET = ($PAGE - 1) * $system->SETTINGS['perpage'];
}
elseif (isset($_SESSION['RETURN_LIST_OFFSET']) && $_SESSION['RETURN_LIST'] == 'listauctions_nobet.php')
{
    $PAGE = intval($_SESSION['RETURN_LIST_OFFSET']);
    $OFFSET = ($PAGE - 1) * $system->SETTINGS['perpage'];
}
else
{
    $OFFSET = 0;
    $PAGE = 1;
}

$_SESSION['RETURN_LIST'] = 'listauctions_nobet.php';
$_SESSION['RETURN_LIST_OFFSET'] = $PAGE;
$PAGES = ($TOTALUSERS == 0) ? 1 : ceil($TOTALUSERS / $system->SETTINGS['perpage']);

$params = array();
if (isset($Q))
{
    $query = "SELECT a.*, 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)" . $Q;
}
elseif (isset($_POST['keyword']))
{
    $query = "SELECT a.*, 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)
        
        WHERE s.teams LIKE :team1 OR ss.teams LIKE :team2 OR u.nick LIKE :nick OR a.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.*, 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)        
        WHERE a.closed = 1  AND a.num_bids = 0 ORDER BY a.id DESC LIMIT :offset, :perpage";

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


$db->query($query, $params);

am trying to make it not to display values that have “1” in “num_bids” column but when i search it pours all out

2

provide example data via http://sqlfiddle.com/

3

the issue is some columns have 1 in closed and 1 in num_bids and 0 in num_bids
i just need it to display only those with with value 0 in num_bids and 1 in closed

4

I don’t see num_bids in your selection, but you’ll just need to add it to the where clause.

NOTE: You’ll need to wrap the entire existing where clause in parenthesis.

5

u mind lending a hand on what to actaully do here been battling it since morning

6

Oh, I just realized there’s a third query in your IF statement. Are you sure you know which SQL statement you’re actually running? Echo $query and make sure you’re running the query you think you’re running.

7

yh its get confusing sometimes also

this is the one for the search

$query = "SELECT a.*, 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)
        
        WHERE s.teams LIKE :team1 OR ss.teams LIKE :team2 OR u.nick LIKE :nick OR a.id LIKE :id";
    $params[] = array(':team1', '%' . $keyword . '%', 'int');
    $params[] = array(':team2', '%' . $keyword . '%', 'int');
    $params[] = array(':id', '%' . $keyword . '%', 'int');
    $params[] = array(':nick', '%' . $keyword . '%', 'str');
8

OK, so my original instructions apply. You need to change that where clause to

WHERE a.closed = 1
  AND a.num_bids = 0 
  AND (s.teams LIKE :team1 OR 
       ss.teams LIKE :team2 OR 
       u.nick LIKE :nick OR 
       a.id LIKE :id)
9

ok ok let me check it out

10

thanks its working, now i got to fix d rest damn gotta b a long nite, thanks

11

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