current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I was wondering how I could make a general function for SELECT mysql queries from my SelectQuery object in PHP. SelectQuery extends Query, which means it inherits the database connection, a realescape method (which is mysqli_real_escape_string()), and a query method which executes the query. Besides that, it also gets a protected variable called _sql, which is the SQL the query() method passes to the database. And it also gets a protected variable called _table, which contains the (escaped) name of the table it's working on.

My code: 

public function select($columns = array('*'), $known = null, $limit = null, $offset = null, $orderby = null, $asc = true) {
    if (!is_array($columns)) {
        new Error('Parameter is not an array.');
        return;
    }

    $select = '';
    foreach($columns as $column) {
        $select .= (($select != '')?', ':'') . '`' . $this->realescape($column) . '`';
    }
    $conditions = '';
    if (is_array($known)) {
        foreach($known as $column => $value) {
            $conditions .= (($conditions != '')?' AND ':'WHERE ') . '`' . $this->realescape($column) . '` = ' . ((is_string($value))?'\'':'') . $this->realescape($value) . ((is_string($value))?'\'':'');
        }
    }
    $domain = '';
    if ($limit !== null) {
        $domain = 'LIMIT ' . $this->realescape($limit);
        if ($offset !== null) {
            $domain .= ' OFFSET = ' . $this->realescape($offset);
        }
    }
    $order = '';
    if ($orderby !== null) {
        $order = 'ORDER BY `' . $this->realescape($orderby) . '` ' . (($asc)?'ASC':'DESC');
    }

    $this->_sql = 'SELECT ' . $select . ' FROM `' . $this->_table . '`';
    if ($conditions != '') {
        $this->_sql .= ' ' . $conditions;
    }
    if ($domain != '') {
        $this->_sql .= ' ' . $domain;
    }
    if ($order != '') {
        $this->_sql .= ' ' . $order;
    }
    return $this->query();
}

The $known variable might be set, if set it should be an array which contains all the 'known' elements of the rows we are selecting.

My question: How can I make this so that conditions such as age < 18, or date > 5120740154 are easily made?

Also, if you think the way I'm making this work is wrong, please say so.

Thanks in advance

share|improve this question
    
I don't have a method for selects in my custom DB class because, as your code shows, you usually end up writing just as much in a function call as you would writing out the whole query. I also don't need to worry about more complex things like the conditions you mention. –  G-Nugget Nov 10 '12 at 1:37
    
@G-Nugget: Hmm, thank you for sharing that, I might change it to a more simple way of executing queries.. The only problem I see is escaping, since MySQLi needs a database connection identifier to escape (I'm not sure why). –  Jochem Kuijpers Nov 10 '12 at 1:43

1 Answer 1

up vote 1 down vote accepted

Dynamically build WHERE conditions that can use any operators

Create a $where array that holds your where conditions.

$where = array (
    'age <' => 18,
    'date >' => 5120740154
);

You can build that into your SQL query using a function like this:

private function buildWhereConditions($where) {
    $conditions = 'WHERE 1=1';
    /*1=1 is just so you can easily append ANDs */
    foreach ($where as $key => $value) {
        $conditions .= " AND $key $value ";
    } 
    return $conditions;
}

This will return the where conditions to be appended to your query:

WHERE 1=1 AND age < 18 AND date > 5120740154
share|improve this answer
    
Thank you for your solution. I'm not a big fan of the WHERE 1=1 thing, but the other part is fine.. The only thing bothering me about it, is that I have to include the operator(?) in the column-name. How can I do this differently? Maybe create condition-objects so it can contain 3 values instead of 2 (key=>value)? –  Jochem Kuijpers Nov 10 '12 at 1:40
    
You can get away from 1=1 using your ternary operator: ($conditions != '')?' AND ':'WHERE ' Why is it a problem to include the operator in the column name? Apart from it being a little ugly, it works. You COULD create an object that contains 3 values, but its a hassle. If it ain't broke, don't fix it. –  Jonathan Spiller Nov 10 '12 at 1:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.