Looping through DB Fields

#1
[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

#2
[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.

#3
[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.

#4
[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?

#5
[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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.