CakePHP: What's the way to use an SQL query statement in an MVC-like structure

I wonder how do we use this in an MVC framework, CakePHP in particular.

$query = oci_parse($c, "SELECT * FROM JOB ORDER BY job_title");
                        oci_execute($query);

                        while($row = oci_fetch_assoc($query)){
                            $showRowJ .= "<tr>\n";
                            $showRowJ .= "<td><a href = 'job_delete.php?jobid=".$row['JOB_ID']."'> Delete </a></td>\n";
                            $showRowJ .= "<td><a href = 'job_update.php?jobid=".$row['JOB_ID']."'> ".$row['JOB_ID']." </a></td>\n";
                            $showRowJ .= "<td>".$row['JOB_TITLE']."</td>\n";
                            $showRowJ .= "<td>".$row['REQUEST_DATE']."</td>\n";
                            $showRowJ .= "<td>".$row['START_DATE']."</td>\n";
                            $showRowJ .= "<td>".$row['NUMBER_OF_DAYS']."</td>\n";
                            $showRowJ .= "<td>".$row['STATUS']."</td>\n";
                            $showRowJ .= "<td>".$row['CLIENT_ID']."</td>\n";
                            $showRowJ .= "</tr>\n";
                            }

and if for MySQL: (example)

$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

I'm confused some say it should be in a model and some are using find('all')

UPDATE:

Hey sorry I meant when we want to query joint tables. The above is just an example when we need to include WHERE 2-3 times for joining tables.

Let's say I want to query:

SELECT users.name, foods.name FROM users, foods WHERE users.id='1' AND users.id=foods.id 

Something like above.. How should we do it in CakePHP ?

Thanks.

Since Cake includes a complete database abstraction layer, you wouldn't write any sort of SQL at all. You'd set up your models correctly and then to something along these lines:

Controller

$jobs = $this->Job->find('all', array('order' => array('Job.title' => 'asc')));
$this->set(compact('jobs'));

View

<table>
<?php foreach ($jobs as $job) : ?>
    <tr><?php echo htmlentities($job['Job']['title']); ?></tr>
    ...
<?php endforeach; ?>
</table>

Maybe you should follow the tutorial. :)

CakePHP will do all the SQL for you, all you need to do is load the model, you will automatically have this as $this if using the MVC setup and you're in the right controller.

// Get jobs and assign them to a var.
$jobs = $this->findAllByUserId($id, array('order' => 'job_title');
// Send the var to the view.
$this->set(compact($jobs));

Where findAllByUserId($id) means "Find all rows where user_id = $id".

Then simply loop through $jobs in your view. Cake automatically gets all associated models as long as you have the relationships defined properly with $hasOne, $belongsTo etc in your models. See database relationships in CakePHP for more info.

CakePHP is strict to its MVC model, you will want to read the blog tutorial, which outlines the basics and has some decent examples.

Update: Updated the code to use findAllbyX which can gather all rows based on a certain field. If you want to do anything else with CakePHP you will need to fully read the CakePHP tutorial, as it will cover all the basic stuff like this.

First, you have to declare at least a "User" class as you model like:

class User extends AppModel {
     public $actsAs = array('containable');
     public $hasMany = array('Food');

}

$actsAs is the way you can attach a behavior to your model, in this case say that this model can "contain" or join with other models.

$hasMany tell that your model can join to what models.

Then in your controller you can tell your main model what other models it should contain in a query:

public function doSomething() {
     $this->User->contain(array('Food'));
     $user = $this->User->findById(1);
     $this->set(compact($user));
}

See more detail on how can you link your models together here: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

By the way in your query

SELECT users.name, foods.name FROM users, foods WHERE users.id='1' AND users.id=foods.id 

It should be

 users.id = foods.user_id