Complex where statements

[Josh Smith]

Use case: I need to select a row from a table where some value $a = 15 is either equal to column foo or greater than foo AND less than or equal to bar.

Query:

SELECT `column`
FROM `table`
WHERE `foo` = 15
OR (`foo` < 15 AND `bar` >= 15)

The class does not currently provide an easy way to do this.

[Vivek N]

$where = "`foo` = 15 OR (`foo` < 15 AND `bar` >= 15)";
$db->select('column')->from('table')->where($where);

[Vivek N]

How do you call this where clause using CodeIgniter ?

[Josh Smith]

I don't use CodeIgniter. I just use the vanilla class and call $db->select(COLUMN)->from(TABLE)->where(WHERE)->fetch_first()[COLUMN];

[Vivek N]

This class is based on CodeIgniter mysql class. It is extracted from CI so that the users don't have to use the whole codeigniter framework just for the mysql class. Whatever method CI supports, this class supports that as well. I've also added few more methods to this class in addition to basic methods CI provides. If CI doesn't have an easy method for complex WHERE statements, this class doesn't have that too.

As always, you can use $db->where() to add an extra/complex where clauses. Thanks for understanding.

[Vivek N]

An update to the php class is now available with this feature added. Please use the latest version 1.5 to test it.

https://bitbucket.org/getvivekv/php-mysqli-class/wiki/Home#markdown-header-parenthesis-between-where

To open a parenthesis, use open_where() and to close use close_where()

<?php
$db->select('column')->from('table');
$db->where('foo', 15);
$db->open_where();
$db->or_where('foo <', 15);
$db->where('bar >=', 15);
$db->close_where();

// Produces  SELECT `column` FROM `table` WHERE `foo` = 15 OR (`foo` < 15 AND `bar` >= 15) 

[Vivek N]

New version available.