Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column in where clause

Question

Having a problem with this here function. It's supposed to return a json string of data gathered using the sql call below. The problem is that I'm getting an error code "500" when accessing the page through my server (for example - localhost/app/API/states/Alabama/1/10.json). The odd thing is that the data is showing up as expected using both .json and without the appending .json. Controllers and Models are set up just fine as far as I can tell, yet the the error code remains:

{"code":500,"url":"/VOI2/API/states/Alabama/2/10.json","name":"SQLSTATE[42S22]: >Column not found: 1054 Unknown column 'states' in 'where >clause'","error":{"errorInfo":["42S22",1054,"Unknown column 'states' in 'where >clause'"]

public function index($stateName, $page, $limit = 10, $user = null) {
$this->State->recursive = 0;
$limit;
$set_limit = $page * $limit - ($limit);
$states = $this->State->query("SELECT * 
FROM states, issues
WHERE stateName =  '" . $stateName ."'
AND issues.state_id = states.id LIMIT " .  $set_limit . "," . $limit)       
$this->set('states', $this->paginate($states));
$this->set('_serialize', array('states'));  
}

Issues is another table with model/controller. I wanted to have the urls setup in a meaningful manner is why I chose to separate states from issues. Thanks all for your help.

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

It is not allowed to use aliases in WHERE clause. See Problems with Column Aliases and Can you use an alias in the WHERE clause in mysql?

edited May 23 '17 at 12:04

Community

1
1

answered Apr 04 '13 at 09:17

cen

2,873
3
31
56

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

You should use your Models as intended by CakePHP. See Retrieving Your Data.

By using 'custom' queries, CakePHP will not prevent SQL-injection, and you need to do things 'by hand', causing many problems in the long end;

Also, because you want to show all Issues, but filter them on the State they belong to, you should retrieve the data from the Issue model, not State

Specify model-relations

app/Model/State.php

class State extends AppModel {
    public $hasMany = array(
        'Issue'
    );
}

app/Model/Issue.php

class Issue extends AppModel {
    public $belongsTo = array(
        'State'
    );
}

To paginate your data, see Pagination

And inside your controller:

class MyController extends AppController {

    public $paginate = array(
        'Issue' => array(
            // Default limit, can be overridden via 'limit' named parameter
            // e.g. /MyController/index/limit:20
            'limit' => 10,

            // Probably required to get 'State' in the results
            'recursive' => 1,
        );
    );
    
    public function index($stateName)
    {
        $conditions = array(
            'State.stateName' => $stateName
        );
    
        // I'll leave this name for now, better call it 'issues'?
        $states = $this->paginate('Issue', conditions)
    
        $this->set('states', states);
        $this->set('_serialize', array('states'));
    }
}

To present paginated data, you should also use the PaginatorHelper

Manually joining tables/models

If the default Model-relationships do not fit your requirements, you can manually join tables together; see this chapter in the documentation:

Joining tables

NOTE: I haven't tested these examples, it will probably work, otherwise it's a good starting-point to get you going

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column in where clause

2 Answers2

Specify model-relations

Manually joining tables/models