Solved

Cakephp query 2 tables

Posted on 2010-08-17
6
665 Views
Last Modified: 2013-11-10
Hi all,
I have an issue with Cakephp...

! have a Model ..call it "User", then a controller and a view.

Model1 currently lists records in table "users" but I have another table that has a user_id field and I need to be able to display records from both tables as they are related and should be linking through "users.id" and "othertable.user_id" fields.

In the View, I need to list records using both tables.

How do I do this please?

I need a simple short example so I can understand it as I've been all over the net and none of those samples work for me.

Hope someone can help

Thanks

0
Comment
Question by:error77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 8

Accepted Solution

by:
ropenner earned 500 total points
ID: 33459995
create view my_new_view as select * from users u, othertable o where u.id =o.user_id

this SQL statement will create your view ... however in this example, if user_id cannot be matched to u.id in the USERS table, it will not showup as a row in the view.
0
 
LVL 8

Expert Comment

by:ropenner
ID: 33460005
there are many other ways to do this with JOINS (right, left and inner joins) but I think this is the simplest way for you to start.
0
 
LVL 8

Assisted Solution

by:ropenner
ropenner earned 500 total points
ID: 33460019
If you need to know the syntax in PHP.

<?PHP
# assuming you've connected to a database somewhere before this point.

mysql_query("create view my_new_view as select * from users u, othertable o where u.id =o.user_id");
?>
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:error77
ID: 33460084
Hmm ... Isn't it done differently with CakePhp?

This is how I've done it:

The Model:

<?php
class User extends AppModel {
 var $name = 'User';
 }
?>

The Controller:

<?php
class UsersController extends AppController {
var $name = 'Users';
function index() {
$this->set('users', $this->User->find('all'));
}
}
?>

And the View:

<?php
foreach ($users as $user):

echo $user['User']['id'];
echo $html->link($user['User']['title'],
array('controller' => 'users', 'action' => 'view', $user['User']['id']));
 endforeach;
?>

So if I wanted to display extra fields from another table where theOtherTable.user_id is the same as user.id ...

How would I do that in Cake please?

thanks.

 





0
 

Author Comment

by:error77
ID: 33461952
Anyone please? It's urgent :o/
0
 

Author Comment

by:error77
ID: 33497218
Hi ropenner,

I am not using the baking tool. Doing it manually.

I'm almost there .. Just need to be able to submit (update) a form into fields from different tables.

Can you please help?


0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question