Looping through DB Fields - Printable Version

Looping through DB Fields - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Looping through DB Fields (/showthread.php?tid=21536)

Looping through DB Fields - El Forum - 08-12-2009

[eluser]Richard Testani[/eluser]
I'm building an SQL query with the or_where and coming up with some oddities, maybe someone can help me out with.

The following function accepts an array for each field to search through with another value set elsewhere as the string.

Code:
public function set_fields($fields) {

        $f    =    "";

        $i = 0;

        foreach($fields as $val) {

            if($i == 0) {

                $f .= "`$val` LIKE '%$this->text_value%' ";

            } else {

                $f .= " OR `$val` LIKE '%$this->text_value%' ";

            }

            $i++;

        }

        $this->db->where($f);

    }

Code:
SELECT `first`, `last`, `email`, `address` 

FROM (`contacts`) WHERE `first` LIKE '%search contacts%' 

OR ` last` LIKE '%search contacts%' 

OR ` email` LIKE '%search contacts%' 

OR `is_volunteer` = '1' 

OR `is_donor` = '0' 

OR `is_sponsor` = '0' LIMIT 100, 100

The error returned is:

Code:
Unknown column ' last' in 'where clause'

Notice the space before the column name which it seems to add.
On another occasion using a loop and where(name, 'value') would drop the '=' on all but the first loop.

Any ideas on this?
Thanks
Rich

Looping through DB Fields - El Forum - 08-12-2009

[eluser]thody[/eluser]
The obvious question would be does the `last` column exist in the database. Could also be a reserved word perhaps? Assuming the column exists, might want to try renaming it to test if it's a reserved word issue.

Looping through DB Fields - El Forum - 08-12-2009

[eluser]Richard Testani[/eluser]
The problem however is not whether it exists but why is there a space before the name?
both last and email columns both have spaces in front of their column names.

Cant tell if its in my code or something with how codeigniter is building the query.

Looping through DB Fields - El Forum - 08-12-2009

[eluser]jedd[/eluser]
Just as a wild stab in the dark, try this instead:

Code:
foreach($fields as $val)  {

    if ($i++)

        $f .=" OR ";

    $f .= "\"". $val ."\" LIKE \"%". $this->text_value ."%\"";

    }

If that doesn't make any difference, try this line:

Code:
$f .= "\"". trim($val) ."\" LIKE \"%". $this->text_value ."%\"";

You might also want to play with print_r or var_dump to investigate the actual content of your $fields array. Btw, shouldn't each element within $fields be called $field?

Looping through DB Fields - El Forum - 08-12-2009

[eluser]Richard Testani[/eluser]
Found it. Totally my bad.

In another function I exploded the string like:

Code:
$fields        =    explode(",", $_POST['fields']);

The string had a ', ' separating them.
Changing to:

Code:
$fields        =    explode(", ", $_POST['fields']);

fixed it.

Thanks
everyone.