New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bug if use where clause #211

Closed
diwms opened this Issue Jul 3, 2013 · 30 comments

Comments

Projects
None yet
@diwms
Copy link

diwms commented Jul 3, 2013

Hi all! I have little question. When in method's used where clause (for example):

$crud->where('manager', 1);

Search don't work. Ajax request return 500 Internal Server Error and information that this column don't exist.

Error Number: 1054 Unknown column 'manager' in 'having clause'

SELECT COUNT(*) AS `numrows`
FROM (`users`)
WHERE `manager` =  1
AND  `first_name`  LIKE '%query%'
OR  `last_name`  LIKE '%query%'
HAVING `manager` =  1

#Filename: /var/www/grocery_crud_model.php Line Number: 191

How to solve this bug? If remove where clause - search work fine. I hope you help me. And sorry for my bad english.

@diwms

This comment has been minimized.

Copy link

diwms commented Jul 3, 2013

I was replace 191 line, instead of:

return $this->db->count_all_results($this->table_name);

I write:

return $this->db->get($this->table_name)->num_rows();

And search work fine. Maybe necessary replace this in library too? ;)

@rosnel

This comment has been minimized.

Copy link

rosnel commented Jul 11, 2013

You're big brother, = D thanks for this solution, I was looking for much in the forum ... God bless you ..

@scoumbourdis

This comment has been minimized.

Copy link
Owner

scoumbourdis commented Oct 6, 2013

Hello @diwms and thank you for your suggestion.

I've changed the code to the master branch. I am closing this ticket.

Thanks for sharing.

@v-volaju

This comment has been minimized.

Copy link

v-volaju commented May 3, 2015

Good day. I am using the version 1.5.0 of GC and got this problem on flexigrid theme. Search works if I specify which field to search but on 'search all' it just returns all rows and no error msg like the one mentioned by diwms.

@diwms

This comment has been minimized.

Copy link

diwms commented May 4, 2015

@v-volaju Hi! This fix already on master branch. Could you please give more information about problem?

@v-volaju

This comment has been minimized.

Copy link

v-volaju commented May 4, 2015

Hi @diwms. I am using CI 3.0 and GC 1.5.0 and this is the controller code from the accompanying GC sample. I just added a field called deleted which can have the value of 0 or 1. Now when I set the where condition, the search all doesn't work properly. It only searches if I specify which field to seach in the select box.

public function customers_management()
    {
            $crud = new grocery_CRUD();
            $crud->set_table('customers');
             $crud->where('deleted',0);
            $crud->columns('customerName','contactLastName','phone','city','country','salesRepEmployeeNumber','creditLimit');
            $crud->required_fields('customerName','contactLastName','phone');
            $crud->display_as('salesRepEmployeeNumber','from Employeer')
                 ->display_as('customerName','Name')
                 ->display_as('contactLastName','Last Name');
            $crud->set_subject('Customer');
            $crud->set_relation('salesRepEmployeeNumber','employees','lastName');

            $output = $crud->render();

            $this->_example_output($output);
    }

I hope you can help me out.

@darkbe

This comment has been minimized.

Copy link

darkbe commented Nov 28, 2015

i have this "search all" problem.
How to solve this bug?

@beodob

This comment has been minimized.

Copy link

beodob commented Nov 28, 2015

You have relations?

@darkbe

This comment has been minimized.

Copy link

darkbe commented Nov 28, 2015

yes. relations with where, as in previous comment.
$crud->set_relation();
$crud->where();

@beodob

This comment has been minimized.

Copy link

beodob commented Nov 28, 2015

In relation write
$crud->set_relation('field', 'table', 'table.field');

@darkbe

This comment has been minimized.

Copy link

darkbe commented Nov 28, 2015

thanks, but it is not good.
throws an error:

Error Number: 1054
Unknown column 'j93bfec8a.table.field' in 'field list'

@beodob

This comment has been minimized.

Copy link

beodob commented Dec 4, 2015

Ps. $crud->where('custumers.deleted',0);

@v-volaju

This comment has been minimized.

Copy link

v-volaju commented Dec 12, 2015

It's still not working with that. Meanwhile I switched to datatables theme but I still want to see how to fix this.

@beodob

This comment has been minimized.

Copy link

beodob commented Dec 12, 2015

Screen send

@darkbe

This comment has been minimized.

Copy link

darkbe commented Feb 10, 2016

i add to employees_management() function in Examples.php:
$crud->where('employees.officeCode', '1');
Theme is flexigrid.

If i search "Patter" string with "Search all" option, then i get all the rows with officeCode=1. If i seach with "LastName", i just get a row.

It uses "or" instead of "and" here:
officeCode = '1' OR lastName LIKE '%patter%'

SELECT employees.*, jd29d42cf.city AS sd29d42cf FROM employees LEFT JOIN offices as jd29d42cf ON jd29d42cf.officeCode = employees.officeCode WHERE employees.officeCode = '1' OR lastName LIKE '%patter%' ESCAPE '!' OR firstName LIKE '%patter%' ESCAPE '!' OR extension LIKE '%patter%' ESCAPE '!' OR email LIKE '%patter%' ESCAPE '!' OR jd29d42cf.city LIKE '%patter%' ESCAPE '!' OR file_url LIKE '%patter%' ESCAPE '!' OR jobTitle LIKE '%patter%' ESCAPE '!' HAVING employees.officeCode = '1' LIMIT 10

@Pimnr47

This comment has been minimized.

Copy link

Pimnr47 commented Jul 1, 2017

Hi,

I have been struggling with a similar problem. First I had a 500 error with Flexigrid with the AJAX_LIST call whenever I had a WHERE clause in my controller. The where clause was always related to a foreign key. I fixed this issue by replacing having with where on line 635.

Next I wanted to resolve the issue that it searches for your search string OR your where clause. I wanted it to be AND. So I also changed the rest of the function after line 635 into this:

if (!empty($this->where))
                    foreach ($this->where as $where)
                        $this->basic_model->where($where[0], $where[1], $where[2]);

                $likes = array();
                
                foreach ($columns as $column) {
                    if (isset($temp_relation[$column->field_name])) {
                        if (is_array($temp_relation[$column->field_name])) {
                            foreach ($temp_relation[$column->field_name] as $search_field) {
                                $likes[$search_field] = $search_text;
                                //$this->or_like($search_field, $search_text);
                            }
                        } else {
                            $likes[$temp_relation[$column->field_name]] = $search_text;
                            //$this->or_like($temp_relation[$column->field_name], $search_text);
                        }
                    } elseif (isset($this->relation_n_n[$column->field_name])) {
                        //@todo have a where for the relation_n_n statement
                    } else {
                        $likes[$column->field_name] = $search_text;
                        //$this->or_like($column->field_name, $search_text);
                    }
                }
                
                $where_clause = "";
                foreach ($likes as $field => $search_text) {
                    $where_clause = $where_clause. " OR ".$field. " LIKE '%".$this->basic_model->escape_like_str($search_text)."%' ESCAPE '!'";
                }
                
                if(sizeof($likes)>0){
                    $where_clause = "(".substr($where_clause, 4).")";
                    $this->basic_model->where($where_clause);
                }
@zar0ku1

This comment has been minimized.

Copy link

zar0ku1 commented Jul 25, 2017

I have this problem
Grosery CRUD 1.5.8

@scoumbourdis scoumbourdis reopened this Aug 7, 2017

@scoumbourdis

This comment has been minimized.

Copy link
Owner

scoumbourdis commented Aug 7, 2017

Re-opening issue as per above comments to investigate more to the problem

@scoumbourdis

This comment has been minimized.

Copy link
Owner

scoumbourdis commented Aug 8, 2017

Hello All,

I can't reproduce this error. This is probably solved at the latest version. Can you please check the latest master version (https://github.com/scoumbourdis/grocery-crud/archive/master.zip)

If the bug still occurs, please add as comments:

  1. The code of your function
  2. The error that you are getting

Thanks
Johnny

@zar0ku1

This comment has been minimized.

Copy link

zar0ku1 commented Aug 8, 2017

if i use WHERE construction in Controller search dont work

if ($this->ion_auth->in_group('Operator'))
{
$crud->unset_edit();
$crud->unset_delete();
$crud->unset_export();
$crud->unset_print();

$crud->fields('LastName','LastNameLatin','FirstName','FirstNameLatin','Patronymic','PatronymicLatin','Date_of_born','Date_of_exam',
'Number', 'Blank', 'Nationality', 'File', 'Institutes');
$crud->field_type('Institutes', 'hidden');
$crud->callback_before_insert(array($this,'add_function'));
$crud->where('Institutes',$Institutes_id);
}

If I comment the line

//$crud->where('Institutes',$Institutes_id);

search works

Before that I completely updated on your link
problem still exists

@scoumbourdis

This comment has been minimized.

Copy link
Owner

scoumbourdis commented Aug 8, 2017

Hello @zar0ku1 ,

Can you please also tell me to which search are you referring to? Are you referring to the "search all" or search by column?

Also are you getting any errors at the response?

Thanks
Johnny

@zar0ku1

This comment has been minimized.

Copy link

zar0ku1 commented Aug 8, 2017

I'm talking about global search, not search by columns
No mistakes, nothing happens
The log file is clean

@zar0ku1

This comment has been minimized.

Copy link

zar0ku1 commented Aug 10, 2017

Could you reproduce the problem?

@alvofr

This comment has been minimized.

Copy link

alvofr commented Dec 7, 2017

Hi - i can reproduce this issue with the latest master:
You use a table and a where clause:
$crud->set_table('kontakte');
$crud->where('deleted',0);

If i use "search all" in this case for "hausr" there is a 500er error coming up:

A Database Error Occurred
Error Number: 1054

Unknown column 'deleted' in 'having clause'
SELECT kontakte.id
FROM kontakte
WHERE deleted =0
OR name LIKE '%hausr%' ESCAPE '!'
OR vorname LIKE '%hausr%' ESCAPE '!'
OR email LIKE '%hausr%' ESCAPE '!'
OR deleted LIKE '%hausr%' ESCAPE '!'
HAVING deleted =0
Filename: E:/UwAmp/UwAmp/www/fm-wp/admin3/system/database/DB_driver.php

Line Number: 691

As you can see the deleted =0 has an issue in the HAVING clause, as the deleted is not part of the select.
Another issue will be the where clause with the ORs and the desired deleted=0.
This won't work as this needs to be an AND
WHERE deleted =0 AND (
name LIKE '%hausr%' ESCAPE '!'
OR vorname LIKE '%hausr%' ESCAPE '!'
OR email LIKE '%hausr%' ESCAPE '!'
OR deleted LIKE '%hausr%' ESCAPE '!'
)
or you will use the HAVING for getting all rows with deleted=0 - in this case deleted must be selected as kontakte.id

So there is an issue with "search all" in case you use a "$crud->where"
I hope this helps fixing this bug.

best regards

scoumbourdis added a commit that referenced this issue Dec 10, 2017

@scoumbourdis

This comment has been minimized.

Copy link
Owner

scoumbourdis commented Dec 10, 2017

Hello All,

I am glad to inform you that this bug is (finally) reproduced and also fixed. You can now have this functionality from the latest master version. I am doing some more testing in order to have a very cool release for version 1.6.0 just before Christmas.

Let me know if you have any issues with the fix.

Regards
Johnny

@scoumbourdis

This comment has been minimized.

Copy link
Owner

scoumbourdis commented Dec 11, 2017

This is now released on version 1.6.0

@andipahlevy

This comment has been minimized.

Copy link

andipahlevy commented Jan 24, 2018

search not working when use $crud->where

i am using GC version 1.6.0

@hunter205

This comment has been minimized.

Copy link

hunter205 commented Jul 26, 2018

This bug still happen. I used v1.6.1. The newest one.

@hunter205

This comment has been minimized.

Copy link

hunter205 commented Jul 26, 2018

This bug still happen. I used v1.6.1. The newest one.

    `$crud->set_table('confirmsheets');
    
    /* query by userName logged in */
    $crud->where('userName', $this->nativesession->get('user_session'));
    
    /* set validate */
    $crud->required_fields('confirmSheetNumber', 'flightNumber', 'date');
    $crud->order_by('date', 'desc');
    $crud->unset_clone();
    $crud->fields('confirmSheetNumber', 'date', 'flightNumber', 'userName', 'file_url', 'lastUpdate', 'createDate');

    /* set event load date when add, edit */
    $crud->callback_before_insert(array($this, '_add_default_date_insert'));

    $crud->change_field_type('lastUpdate', 'invisible');
    $crud->change_field_type('createDate', 'invisible');
    $crud->change_field_type('userName', 'invisible');

    $crud->callback_before_update(array($this, '_add_default_date_update'));

    /* xampp\php\php.ini set upload_max_filesize=10M */
    $crud->set_field_upload('file_url', 'assets/uploads/files');

    $output = $crud->render();

    /* render this to views/quocte.php */
    $this->_example_output($output);`

as you see, i used 'where':

$crud->where('userName', $this->nativesession->get('user_session'));

**and when i "search all":

this error causes:**

`A Database Error Occurred
Error Number: 1054

Unknown column 'userName' in 'having clause'

SELECT confirmsheets.confirmSheetId FROM confirmsheets WHERE userName = 'test@phpflow.com' AND (confirmsheets.confirmSheetNumber LIKE 'w%' OR confirmsheets.date LIKE 'w%' OR confirmsheets.flightNumber LIKE 'w%' OR confirmsheets.userName LIKE 'w%' OR confirmsheets.file_url LIKE 'w%' OR confirmsheets.lastUpdate LIKE 'w%' OR confirmsheets.createDate LIKE 'w%') HAVING userName = 'test@phpflow.com'

Filename: C:/xampp/htdocs/grocery-crud-demo/system/database/DB_driver.php

Line Number: 691`

@Pimnr47

This comment has been minimized.

Copy link

Pimnr47 commented Aug 7, 2018

@hunter205 Have you tried:

$crud->where('confirmsheets.userName', $this->nativesession->get('user_session'));

or

$crud->where('confirmsheets.userName = \''.$this->nativesession->get('user_session')).'\'';

?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment