Re: Custom query Pagination in cakephp
Hi Sricharan,
You haven't given enough information for us to be able to help you.
Without knowing more about your models, joins, queries, parameters and
data in the database we would be making guesses as to where your
problem lies.
The only thing I can say is it does not look as though you are using
paginate at all, your simply running a custom query. I've been using
Cake for around 3 years now and never yet failed to get what I want
using Cake's built-in pagination (never had to create a custom
paginate - have had to force some joins from time to time) so I highly
reccomend you go back and read about paginate some more.
http://book.cakephp.org/view/1231/Pagination
http://book.cakephp.org/view/1237/Custom-Query-Pagination
HTH, Paul.
On Aug 29, 7:01 am, sricharan wrote:
> Hi,
>
> I am using a custom query pagination in cakephp and getting problem
> with the model name.
>
> i am using join query to retrieve the data from 5 tables. I am using
> the query as :
>
> $lim=@$this->passedArgs['limit'];
> if(empty($lim))
> {
> $lim=20;
> }
> i am passing the limit from the.ctp page and
>
> $result_wait=$this->Waitingroom->query("My sql query...LIMIT $lim");
> $this->set(compact('result_wait', 'lim'));
> $num=count($result_wait);
>
> $this->set('waitingrooms', $this->paginate());
>
> Here actually i had to get the count from the join query, but i am
> getting the count from the model name 'waitingrooms'.
>
> Actually i had to get the count as 3 records, but it is showing the
> count as 24 records..
>
> Please help me to fix this problem. Thanks in Advance
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Custom query pagination
In this case just use plain SQL in paginate() and paginateCount(). On Aug 31, 9:52 pm, Dean C wrote: > Hi folks, > > I can't use a View for the union query as it requires parameters. Even > if I take away the fulltext, and then do a fulltext search on the > view, MySQL can't then delegate the indexes across the union within > the view, so it doesn't work. Any more ideas or sample pieces of code > would be appreciated :) > > Dean > > On Aug 28, 9:09 am, "Dr. Loboto" wrote: > > > > > If you can do view for your union query you will be able to work with > > it as with normal table. > > > On Aug 28, 5:58 am, Dean C wrote: > > > > Hi folks, > > > > Before I begin, yes I've read this > > > page:http://book.cakephp.org/view/249/Custom-Query-Pagination > > > > I've also read many of the other pages on Google but I'm still stuck! > > > > First of all if I may introduce you to my > > > query:http://bin.cakephp.org/view/1064020598 > > > > It's a union query that allows me to search across two tables and > > > return the results, ordered by relevance. To put it simply I want to > > > paginate these results, but I haven't a clue how to do it. I tried, > > > and failed, over-riding the paginate and paginateCount methods in my > > > model, but I shan't even begin to post my long list of errors. > > > > Where should I start? > > > > Thanks! > > > > Dean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom query pagination
Hi folks, I can't use a View for the union query as it requires parameters. Even if I take away the fulltext, and then do a fulltext search on the view, MySQL can't then delegate the indexes across the union within the view, so it doesn't work. Any more ideas or sample pieces of code would be appreciated :) Dean On Aug 28, 9:09 am, "Dr. Loboto" wrote: > If you can do view for your union query you will be able to work with > it as with normal table. > > On Aug 28, 5:58 am, Dean C wrote: > > > > > Hi folks, > > > Before I begin, yes I've read this > > page:http://book.cakephp.org/view/249/Custom-Query-Pagination > > > I've also read many of the other pages on Google but I'm still stuck! > > > First of all if I may introduce you to my > > query:http://bin.cakephp.org/view/1064020598 > > > It's a union query that allows me to search across two tables and > > return the results, ordered by relevance. To put it simply I want to > > paginate these results, but I haven't a clue how to do it. I tried, > > and failed, over-riding the paginate and paginateCount methods in my > > model, but I shan't even begin to post my long list of errors. > > > Where should I start? > > > Thanks! > > > Dean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom query pagination
Hi,
This is haw I paginate and it works OK,
I have 3 join tables ( I can set different type of joins ),
Did you understand what is happening ?
function MyFunctionThatReturnPaginateArrayInfo {
code code code
...
return array(
'MortgageDetail' => array(
'limit'=> 10,
'fields' => array(
'id',
'mortgage_provider_id',
'name',
'mortgage_category_id',
.
.
'( (' . $initial_monthly_payment_formula . ' )/
' .
(float)$m_borrow/1000 . ' ) as cpt',
.
.
'name',
'specialoffer',
'summary',
'directlink',
'ads_link',
'MortgageProvider.name'
// 'UNIX_TIMESTAMP(createddate) as created,
UNIX_TIMESTAMP(updatedate) as updated'
),
'recursive' => -1,
'joins' => array(
array(
'table' => 'mortgage_categories',
'alias' => 'MortgageCategory',
'type' => 'LEFT',
'conditions' =>
array('MortgageDetail.mortgage_category_id =
MortgageCategory.id')
),
array(
'table' => 'mortgage_providers',
'alias' => 'MortgageProvider',
'type' => 'LEFT',
'conditions' =>
array('MortgageDetail.mortgage_provider_id =
MortgageProvider.id')
),
array(
'table' =>
'mortgage_providers_general_limits',
'alias' =>
'MortgageProvidersGeneralLimit',
'type' => 'INNER',
'conditions' =>
array('MortgageProvidersGeneralLimit.mortgage_provider_id =
MortgageProvider.id') ),
),
'conditions' =>
array("{$sWhereCond}"),
'order' => array('initialrate')
)
wrote:
>
> Hi folks,
>
> Before I begin, yes I've read this page:
> http://book.cakephp.org/view/249/Custom-Query-Pagination
>
> I've also read many of the other pages on Google but I'm still stuck!
>
> First of all if I may introduce you to my query:
> http://bin.cakephp.org/view/1064020598
>
> It's a union query that allows me to search across two tables and
> return the results, ordered by relevance. To put it simply I want to
> paginate these results, but I haven't a clue how to do it. I tried,
> and failed, over-riding the paginate and paginateCount methods in my
> model, but I shan't even begin to post my long list of errors.
>
> Where should I start?
>
> Thanks!
>
> Dean
>
> >
>
--
Paul-Cosmin Constandachi
Tel: 0723.46.46.42
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom query pagination
If you can do view for your union query you will be able to work with it as with normal table. On Aug 28, 5:58 am, Dean C wrote: > Hi folks, > > Before I begin, yes I've read this > page:http://book.cakephp.org/view/249/Custom-Query-Pagination > > I've also read many of the other pages on Google but I'm still stuck! > > First of all if I may introduce you to my > query:http://bin.cakephp.org/view/1064020598 > > It's a union query that allows me to search across two tables and > return the results, ordered by relevance. To put it simply I want to > paginate these results, but I haven't a clue how to do it. I tried, > and failed, over-riding the paginate and paginateCount methods in my > model, but I shan't even begin to post my long list of errors. > > Where should I start? > > Thanks! > > Dean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom Query in Controller: How to check if it was successful?
=>To make custom queries use the foll
*Syntax:*
$this->model name->query(" sql query here “ );
Eg:
$this->Employee->query("SELECT
employee_name,employee_ID,company,basic_salary FROM Employees WHERE
id='$id';"));
=>To access one model in another model for querying purpose use the foll
*Syntax:*
* *
$this->current model name -> reqd. model name ->query(“”);
Eg:
$this->Payroll->Employee->find('list', array('fields' =>
array('Employee.employee_name')));
$this->Payroll->Employee->query("SELECT
employee_name,employee_ID,company,basic_salary FROM Employees WHERE
id='$id';");
=>To save the data after form submission or insertion use the foll link:
http://book.cakephp.org/view/75/Saving-Your-Data
=>To delete the data
http://book.cakephp.org/view/516/Deleting-Data
On Fri, Jul 10, 2009 at 10:17 AM, DigitalDude
wrote:
>
> Hey,
>
> you are right but I did not know how to make such an update-query with
> cake's built-in functions. If you could tell me how to update all
> entries in a table with a certain condition, please tell me,
> especially to the example I gave in my first post.
>
> Thanks in advance,
>
> DD
> >
>
--
--
(¨`•.•´¨) I may be busy,
`•.¸(¨`•.•´¨) but I assure you,
(¨`•.•´¨)¸.•´ you are always in my heart
`•.¸.•´
With Lots of Love.
THANKS AND REGARDS
Jeffery Jacob
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query in Controller: How to check if it was successful?
USE THE FOLLWOING IN UR CONTROLLER/MODEL
FOR GENERATING CUSTOM QUERIES...
$this->model name->query(" sql query here “ );
Eg:
$this->Employee->query("SELECT
employee_name,employee_ID,company,basic_salary FROM Employees WHERE
id='$id';"));
=>To access one model in another model for querying purpose use the foll
*Syntax:*
* *
$this->current model name -> reqd. model name ->query(“”);
Eg:
$this->Payroll->Employee->find('list', array('fields' =>
array('Employee.employee_name')));
$this->Payroll->Employee->query("SELECT
employee_name,employee_ID,company,basic_salary FROM Employees WHERE
id='$id';");
=>To save the data after form submission or insertion use the foll link:
http://book.cakephp.org/view/75/Saving-Your-Data
On Fri, Jul 10, 2009 at 10:17 AM, DigitalDude
wrote:
>
> Hey,
>
> you are right but I did not know how to make such an update-query with
> cake's built-in functions. If you could tell me how to update all
> entries in a table with a certain condition, please tell me,
> especially to the example I gave in my first post.
>
> Thanks in advance,
>
> DD
> >
>
--
--
(¨`•.•´¨) I may be busy,
`•.¸(¨`•.•´¨) but I assure you,
(¨`•.•´¨)¸.•´ you are always in my heart
`•.¸.•´
With Lots of Love.
THANKS AND REGARDS
Jeffery Jacob
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query in Controller: How to check if it was successful?
Hey, you are right but I did not know how to make such an update-query with cake's built-in functions. If you could tell me how to update all entries in a table with a certain condition, please tell me, especially to the example I gave in my first post. Thanks in advance, DD --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom Query in Controller: How to check if it was successful?
Yes, I most certainly agree.
All my cake applications utilize the cake conventions for query
handling.
I was simply trying to give DigitalDude a direct answer to his
question. Which in hindsight may not have been the right thing to
do ;)
Therefore, DigitalDude, altho my suggestion works.. I agree with
Vijay. You would be much better off using the cake conventions,
rather than writing your own queries.
-rb
On Jul 8, 4:24 pm, Vijay Kumbhar wrote:
> Hello rb,
>
> You are right that is the way to do it.
> But my suggestion is that please dont break the basic conventions of
> cakephp.
> You have to write custom query at very high complex level otherwise there is
> no need to use custom query.
> So please check the update method in the model.
>
>
>
> On Thu, Jul 9, 2009 at 1:10 AM, rb wrote:
>
> > Can't you just do something like this?:
>
> > if($this->Model->query("")){
> > //success
> > } else {
> > //failed
> > }
>
> > On Jul 8, 12:56 pm, DigitalDude wrote:
> > > Hey,
>
> > > I'm doing a manual update to my database with a custom query, like so:
>
> > > $this->Model->query("UPDATE table SET attribute='1' WHERE condition='".
> > > $variable);
>
> > > It's all good, it works fine. But how can I check if the result, in
> > > this case the update, was successful?
>
> > > I need to check if it was successful, so I can redirect after it, and
> > > post a message to the user.
>
> > > Does query return true or false after it has been executed?
>
> > > Would be nice if someone could help me with this!
>
> --
> Thanks & Regards,
> Vijayk.
> Co-founder (www.weboniselab.com)
>
> "You Bring the Dreams, We'll Bring the Means"
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query in Controller: How to check if it was successful?
Hello rb,
You are right that is the way to do it.
But my suggestion is that please dont break the basic conventions of
cakephp.
You have to write custom query at very high complex level otherwise there is
no need to use custom query.
So please check the update method in the model.
On Thu, Jul 9, 2009 at 1:10 AM, rb wrote:
>
> Can't you just do something like this?:
>
> if($this->Model->query("")){
> //success
> } else {
> //failed
> }
>
>
>
> On Jul 8, 12:56 pm, DigitalDude wrote:
> > Hey,
> >
> > I'm doing a manual update to my database with a custom query, like so:
> >
> > $this->Model->query("UPDATE table SET attribute='1' WHERE condition='".
> > $variable);
> >
> > It's all good, it works fine. But how can I check if the result, in
> > this case the update, was successful?
> >
> > I need to check if it was successful, so I can redirect after it, and
> > post a message to the user.
> >
> > Does query return true or false after it has been executed?
> >
> > Would be nice if someone could help me with this!
> >
>
--
Thanks & Regards,
Vijayk.
Co-founder (www.weboniselab.com)
"You Bring the Dreams, We'll Bring the Means"
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query in Controller: How to check if it was successful?
Can't you just do something like this?:
if($this->Model->query("")){
//success
} else {
//failed
}
On Jul 8, 12:56 pm, DigitalDude wrote:
> Hey,
>
> I'm doing a manual update to my database with a custom query, like so:
>
> $this->Model->query("UPDATE table SET attribute='1' WHERE condition='".
> $variable);
>
> It's all good, it works fine. But how can I check if the result, in
> this case the update, was successful?
>
> I need to check if it was successful, so I can redirect after it, and
> post a message to the user.
>
> Does query return true or false after it has been executed?
>
> Would be nice if someone could help me with this!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: custom query dispplay in view
$this->set('result', $this->Admin->query($query));
On Fri, Apr 3, 2009 at 5:01 AM, Ranju wrote:
>
> hello
>
>
> $query = "SELECT * from admins";
> $result = $this->Admin->query($query);
> print_r($result);
>
>
> I want show result in view file. which type i show result in view
> file.
>
> Thanks
>
>
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
RE: Custom Query
Thanks again,
That works perfectly the way I want. I really appreciate your help and
advice.
Dave
-Original Message-
From: brian [mailto:bally.z...@gmail.com]
Sent: March-12-09 1:20 AM
To: cake-php@googlegroups.com
Subject: Re: Custom Query
On Wed, Mar 11, 2009 at 10:59 PM, Dave Maharaj :: WidePixels.com
wrote:
> I been at this for hours and still no luck. What I am trying to do is
> run a simple query to find a user_id based on the Auth->id and pass
> the result to the view.
>
> This is being run in the students_controller which is checking the
> TEACHERS table. In the view where I have wantsAccess all that gets
> displayed is "Array".
>
> view:
>
> $this->set('wantsAccess', $this->Teacher->find('all', array(
> 'conditions' => array('Teacher.user_id' =>
> $this->Auth->user('id')),'fields' =>
> array('Teacher.id')
> )
> ));
If you want just a single row, use find('first', ...). If you really want
just the ID, use the field() method:
$this->set(
'wantsAccess',
$this->Teacher->field(
'id',
array(
'Teacher.user_id' => $this->Auth->user('id')
)
)
);
This will return just the ID (no array). Note that you don't put the
'conditions' key because that 2nd param is *only* for conditions.
Another bit of advice: I've gotten in the habit of naming my vars much more
specifically (ie. $user_id rather than $wantsAccess) in order to keep things
clearer months down the road when I need to make changes (and curse my lack
of comments ;-)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query
On Wed, Mar 11, 2009 at 10:59 PM, Dave Maharaj :: WidePixels.com
wrote:
> I been at this for hours and still no luck. What I am trying to do is run a
> simple query to find a user_id based on the Auth->id and pass the result to
> the view.
>
> This is being run in the students_controller which is checking the TEACHERS
> table. In the view where I have wantsAccess all that gets displayed is
> "Array".
>
> view:
>
> $this->set('wantsAccess', $this->Teacher->find('all', array( 'conditions' =>
> array('Teacher.user_id' => $this->Auth->user('id')),'fields' =>
> array('Teacher.id')
> )
> ));
If you want just a single row, use find('first', ...). If you really
want just the ID, use the field() method:
$this->set(
'wantsAccess',
$this->Teacher->field(
'id',
array(
'Teacher.user_id' => $this->Auth->user('id')
)
)
);
This will return just the ID (no array). Note that you don't put the
'conditions' key because that 2nd param is *only* for conditions.
Another bit of advice: I've gotten in the habit of naming my vars much
more specifically (ie. $user_id rather than $wantsAccess) in order to
keep things clearer months down the road when I need to make changes
(and curse my lack of comments ;-)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query
Its find('first') and your query result will always be an array.
Try echo $wantsAccess['Teacher']['id']. You need to debug
($wantsAccess) to see its array values.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Result
If you are displaying a table of projects with each row containing a count of messages associated with that project then this should all be done as part of a single request within the controller. You may want to look at letting Cake keep a running count of messages within the project table. See ... http://book.cakephp.org/view/490/counterCache-Cache-your-count You should not be making any logic/controller calls from within your views as the controller fetches and sends all data to the view before it is rendered. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom Query Result
But how can i invoked funtion from view file send id and get appropriate
result for ongoing project
you can invoke funtion as by link
link('get count', array('action'=>'getCount',
$project['Project']['id']))." "; ?>
I have to pass variable $id for each project with in foreach loop
automatically as query is
SELECT count(*) as countval FROM messages where project_id=$id AND
status='1'
But how can invoke funtion simply in view file with id with out link...
WebbedIT wrote:
>
>
>> How can i get data in view file?
>
> As I wrote above, your $this->set('numval',$numval); command sends the
> variable to the view in a variable called $numval, you are already
> passing that data to the view, but if you then use redirect then that
> view no longer exists as you've loaded a new http request.
> >
>
>
--
View this message in context:
http://www.nabble.com/Custom-Query-Result-tp21216264p21216936.html
Sent from the CakePHP mailing list archive at Nabble.com.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Result
> How can i get data in view file?
As I wrote above, your $this->set('numval',$numval); command sends the
variable to the view in a variable called $numval, you are already
passing that data to the view, but if you then use redirect then that
view no longer exists as you've loaded a new http request.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Result
How can i get data in view file?
i write foreach condition for each entity.
but how to call funtion with passing values.
what code i write?
WebbedIT wrote:
>
>
>> I know it is much appropriate if function is called by action
>> but i dont know how to return the variable to view.
>
> In most of my controllers I have a private __formData() function as
> both the add() and edit() actions need various variables sent to the
> view so this avoids repetition.
>
> function __formData() {
> $statuses = $this->Staff->Status->find('list', array(
> 'conditions'=>array('Status.option_list_id'=>'15'),
> 'fields'=>array('Status.value'),
> 'order'=>'Status.value ASC'
> ));
> $this->set(compact('statuses'));
> $ethnicities = $this->Staff->Person->Ethnicity->find('list', array(
> 'conditions'=>array('Ethnicity.option_list_id'=>'13'),
> 'fields'=>array('Ethnicity.value'),
> 'order'=>'Ethnicity.value ASC'
> ));
> $this->set(compact('ethnicities'));
> }
>
> Your $this->set('numval',$numval); command sends the variable to the
> view in a variable called $numval, but your redirect then reloads the
> page and therefore loses that $numval variable. You can also send
> data to the view by placing everything within the $this->data array
> which is automatically available to the view.
> >
>
>
--
View this message in context:
http://www.nabble.com/Custom-Query-Result-tp21216264p21216750.html
Sent from the CakePHP mailing list archive at Nabble.com.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Result
> I know it is much appropriate if function is called by action
> but i dont know how to return the variable to view.
In most of my controllers I have a private __formData() function as
both the add() and edit() actions need various variables sent to the
view so this avoids repetition.
function __formData() {
$statuses = $this->Staff->Status->find('list', array(
'conditions'=>array('Status.option_list_id'=>'15'),
'fields'=>array('Status.value'),
'order'=>'Status.value ASC'
));
$this->set(compact('statuses'));
$ethnicities = $this->Staff->Person->Ethnicity->find('list', array(
'conditions'=>array('Ethnicity.option_list_id'=>'13'),
'fields'=>array('Ethnicity.value'),
'order'=>'Ethnicity.value ASC'
));
$this->set(compact('ethnicities'));
}
Your $this->set('numval',$numval); command sends the variable to the
view in a variable called $numval, but your redirect then reloads the
page and therefore loses that $numval variable. You can also send
data to the view by placing everything within the $this->data array
which is automatically available to the view.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Result
piyushsharmajec wrote:
> I have Project model in which function is as
>
> function getMsgCount($id)
> {
> $this->id = $id;
>
> return $this->query("SELECT count(*) as countval FROM
> messages where
> project_id=$id AND status='1'");
> }
>
>
function getMsgCount($id){
$this->find('count', $conditions);
}
> In Project Controller code is..
>
> function getCount($id = null)
> {
> $numval = $this->Project->getMsgCount($id);
>$this->set('numval',$numval);
> $this->redirect(array('action'=>'index'));
> }
>
function getCount($id){
$this->set('numval', $this->Project->getMsgCount());
}
> How to get result in view file? if i can pass numval through url or directly
> access variable. Please help!
>
>
>
Not tested. Might not work but will get you started.
http://book.cakephp.org/view/73/Retrieving-Your-Data
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Result
Thanks for reply, But when i redirect with passing variable it gets array, I know it is much appropriate if function is called by action but i dont know how to return the variable to view. WebbedIT wrote: > > > Your redirect loads a new request without passing through the $numval > variable. > > I wouldn't expect that you need to be using a redirect within the > getCount() function. I assume this function is likely to be called > from an action and therefore should simply return the variable or use > set() to send it to the view before returning to the action which > called it. > > > > -- View this message in context: http://www.nabble.com/Custom-Query-Result-tp21216264p21216465.html Sent from the CakePHP mailing list archive at Nabble.com. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom Query Result
Your redirect loads a new request without passing through the $numval variable. I wouldn't expect that you need to be using a redirect within the getCount() function. I assume this function is likely to be called from an action and therefore should simply return the variable or use set() to send it to the view before returning to the action which called it. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom Query Pagination
I also found that the group by value was correct for some paginate
calls and what I needed was the effected rows, I modified the
paginateCount method override as follows :-
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
$parameters = compact('conditions');
$this->recursive = $recursive;
$count = $this->find('count', array_merge($parameters, $extra));
if (isset($extra['group'])) {
$count = $this->getAffectedRows();
}
return $count;
}
Hope this helps.
Brett Wilton
http://wiltonsoftware.com
On Tue, Dec 9, 2008 at 10:34 AM, Brett Wilton <[EMAIL PROTECTED]> wrote:
> You need to override the pageCount() method with a custom count query,
> for a similar type of query to yours I used the following method in my
> model...
>
> function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
>$parameters = compact('conditions');
>$this->recursive = $recursive;
>$count = $this->find('count', array_merge($parameters));
>return $count;
> }
>
> Essentially this is the same as the default count method but I ignore
> the extra field which contains the group by clause i.e. $extra is not
> passed to the find().
>
>
> Brett Wilton
> http://wiltonsoftware.com
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Pagination
You need to override the pageCount() method with a custom count query,
for a similar type of query to yours I used the following method in my
model...
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
$parameters = compact('conditions');
$this->recursive = $recursive;
$count = $this->find('count', array_merge($parameters));
return $count;
}
Essentially this is the same as the default count method but I ignore
the extra field which contains the group by clause i.e. $extra is not
passed to the find().
Brett Wilton
http://wiltonsoftware.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom query Doubt/problem please help me..
Hi.
The error messages indicates the undifined index of "total". Is it right?
I think you mistake the data structure which Model::query returns.
If you put the statement "print_r ($t);" in the model code, you can
understand the problem quickly.
The structure of $t is below:
Array
(
[0] => Array
(
[0] => Array
(
[total] => 4500
)
)
)
Thus, the correct code of model is below:
$totalAmount = $t[0][0]['total'];
return $totalAmount;
Best Wishes,
Takuo Doi
renju wrote:
> Any body can explain Custom Queries with example...
> My problem is that ..i have two table
> 1)campaigns
> 2)donations
> i need the total amount of particular campaign by using the sql
> query..
> CODE
> SELECT sum(amount) as total FROM `v1_donations` WHERE campaign_id=1
>
> campaign Model
> CODE
> function getTotalAmount(){
>$t = $this->query("SELECT sum(amount) as total FROM
> `v1_donations` WHERE campaign_id=1");
> $totalAmount = $t[0]['total'];
> return $totalAmount;
> }
>
> campaign Controller
> CODE
> function index() {
>$this->Campaign->recursive = 0;
>$t = $this->Campaign->getTotalAmount();
>$this->set('totolAmount',$t);
>$this->set('campaigns', $this->paginate());
>}
>
>
> But when i go to the url i am getting the error as
>
> Undefined index: amount [APP\models\campaign.php, line 25]
>
> Error Context
>
> $t=array(
> array(
> "v1_donations" => array()
> )
> )
>
> Error Code in
>
> $t = $this->query("SELECT amount FROM `v1_donations` WHERE
> campaign_id=1");
> $totalAmount = $t[0]['total']; //this line is highlighted
>
>
> So please help me to solve this problem...
>
> >
>
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Insert Into
and if anyones wants to know my other problem was that I assigned this
data like this:
$this->data['note']['user_id'] = $user_id;
and should be:
$this->data['Note']['user_id'] = $user_id;
On Jan 16, 11:51 pm, Fabian <[EMAIL PROTECTED]> wrote:
> Hi I ended up doing this:
>
> Remove the autoincrement from the table
>
> $ret = $this->Note->query("ALTER TABLE notes MODIFY COLUMN notes_id
> BIGINT(20) UNSIGNED NOT NULL DEFAULT 0");
>
> Then insert the data
> $ret = $this->Note->query("INSERT INTO Notes
> (notes_id,user_id,date_added,date_modified,details) VALUES ( $notes_id,
> $user_id,'$date_added','$date_modified','$details')");
>
> Change the table back to how it originally was.
> $ret = $this->Note->query("ALTER TABLE `hawk_database`.`notes`
> MODIFY COLUMN `notes_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
> AUTO_INCREMENT = 66");
>
> And I ended up doing this on the controller instead of using the
> model, since in the model it didn't work.
>
> On Jan 16, 11:20 pm, Fabian <[EMAIL PROTECTED]> wrote:
>
> > my query is :
>
> > $ret = $this->query("INSERT INTO Notes
> > (user_id,date_added,date_modified,details) VALUES (1,'2008-01-01
> > 17:22','2008-01-01','Test')");
> > Had a typo before.
>
> > On Jan 16, 11:06 pm, Fabian <[EMAIL PROTECTED]> wrote:
>
> > > Hi all,
>
> > > I'm trying to create a custom query but I keep getting errors. I tried
> > > this query on mysql command prompt and it works perfectly however in
> > > here it doesn't seem to work.
>
> > > Create table 'notes' (
> > > notes_id int(20) NOT NULL auto_increment,
> > > 'user_id' int(11) NOT NULL,
> > > 'date_added' TIMESTAMP NOT NULL,
> > > 'date_modified' DATE NOT NULL,
> > > 'details' TEXT NOT NULL,
> > > PRIMARY KEY ('notes_id')
> > > )
>
> > > class Note extends AppModel{
> > > var $name = 'Note';
> > > var $primaryKey = 'notes_id';
>
> > > }
>
> > > function saveNote() {
>
> > > $ret = $this->query("INSERT INTO Notes
> > > (user_id,date_added,date_modified,details) VALUES (1,'2008-01-01
> > > 17:22','2008-01-01','Test');");
>
> > > }
>
> > > I'm using a custom query since when I try to use the
> > > this->Note->Save($this->data); I get and error this is what I tried to do
> > > in the
>
> > > controller:
>
> > > function save ($user_id = null, $date_added = null, $date_modified=
> > > null, $details = null) {
> > > $this->data['note']['user_id'] = $user_id;
> > > $this->data['note']['date_added'] = date("Y-m-d H:i",
> > > strtotime($date_added));
> > > $this->data['note']['date_modified'] = date("Y-m-d H:i",
> > > strtotime($date_modified));
> > > $this->data['note']['details'] = $details;
>
> > > if($this->Note->save($this->data)) {
> > > echo 'save successful';
> > > }
> > > else {
> > > echo 'no good';
>
> > > }
>
> > > }
>
> > > This is the error I get :
>
> > > SELECT COUNT(notes_id) AS count FROM `notes` WHERE `notes_id` = 1
>
> > > I don't know why it tries to do a select where notes_id = 1 since in
> > > the parameters I don't even ask for the notes_id since it's a
> > > autoincrement. This is why I tried the custom query but had no luck
> > > with that either.
>
> > > Can anyone shed some light on any of the 2 solutions I'm trying to
> > > develop thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Insert Into
Hi I ended up doing this:
Remove the autoincrement from the table
$ret = $this->Note->query("ALTER TABLE notes MODIFY COLUMN notes_id
BIGINT(20) UNSIGNED NOT NULL DEFAULT 0");
Then insert the data
$ret = $this->Note->query("INSERT INTO Notes
(notes_id,user_id,date_added,date_modified,details) VALUES ( $notes_id,
$user_id,'$date_added','$date_modified','$details')");
Change the table back to how it originally was.
$ret = $this->Note->query("ALTER TABLE `hawk_database`.`notes`
MODIFY COLUMN `notes_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 66");
And I ended up doing this on the controller instead of using the
model, since in the model it didn't work.
On Jan 16, 11:20 pm, Fabian <[EMAIL PROTECTED]> wrote:
> my query is :
>
> $ret = $this->query("INSERT INTO Notes
> (user_id,date_added,date_modified,details) VALUES (1,'2008-01-01
> 17:22','2008-01-01','Test')");
> Had a typo before.
>
> On Jan 16, 11:06 pm, Fabian <[EMAIL PROTECTED]> wrote:
>
> > Hi all,
>
> > I'm trying to create a custom query but I keep getting errors. I tried
> > this query on mysql command prompt and it works perfectly however in
> > here it doesn't seem to work.
>
> > Create table 'notes' (
> > notes_id int(20) NOT NULL auto_increment,
> > 'user_id' int(11) NOT NULL,
> > 'date_added' TIMESTAMP NOT NULL,
> > 'date_modified' DATE NOT NULL,
> > 'details' TEXT NOT NULL,
> > PRIMARY KEY ('notes_id')
> > )
>
> > class Note extends AppModel{
> > var $name = 'Note';
> > var $primaryKey = 'notes_id';
>
> > }
>
> > function saveNote() {
>
> > $ret = $this->query("INSERT INTO Notes
> > (user_id,date_added,date_modified,details) VALUES (1,'2008-01-01
> > 17:22','2008-01-01','Test');");
>
> > }
>
> > I'm using a custom query since when I try to use the
> > this->Note->Save($this->data); I get and error this is what I tried to do
> > in the
>
> > controller:
>
> > function save ($user_id = null, $date_added = null, $date_modified=
> > null, $details = null) {
> > $this->data['note']['user_id'] = $user_id;
> > $this->data['note']['date_added'] = date("Y-m-d H:i",
> > strtotime($date_added));
> > $this->data['note']['date_modified'] = date("Y-m-d H:i",
> > strtotime($date_modified));
> > $this->data['note']['details'] = $details;
>
> > if($this->Note->save($this->data)) {
> > echo 'save successful';
> > }
> > else {
> > echo 'no good';
>
> > }
>
> > }
>
> > This is the error I get :
>
> > SELECT COUNT(notes_id) AS count FROM `notes` WHERE `notes_id` = 1
>
> > I don't know why it tries to do a select where notes_id = 1 since in
> > the parameters I don't even ask for the notes_id since it's a
> > autoincrement. This is why I tried the custom query but had no luck
> > with that either.
>
> > Can anyone shed some light on any of the 2 solutions I'm trying to
> > develop thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom Query Insert Into
my query is :
$ret = $this->query("INSERT INTO Notes
(user_id,date_added,date_modified,details) VALUES (1,'2008-01-01
17:22','2008-01-01','Test')");
Had a typo before.
On Jan 16, 11:06 pm, Fabian <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I'm trying to create a custom query but I keep getting errors. I tried
> this query on mysql command prompt and it works perfectly however in
> here it doesn't seem to work.
>
> Create table 'notes' (
> notes_id int(20) NOT NULL auto_increment,
> 'user_id' int(11) NOT NULL,
> 'date_added' TIMESTAMP NOT NULL,
> 'date_modified' DATE NOT NULL,
> 'details' TEXT NOT NULL,
> PRIMARY KEY ('notes_id')
> )
>
> class Note extends AppModel{
> var $name = 'Note';
> var $primaryKey = 'notes_id';
>
> }
>
> function saveNote() {
>
> $ret = $this->query("INSERT INTO Notes
> (user_id,date_added,date_modified,details) VALUES (1,'2008-01-01
> 17:22','2008-01-01','Test');");
>
> }
>
> I'm using a custom query since when I try to use the
> this->Note->Save($this->data); I get and error this is what I tried to do in
> the
>
> controller:
>
> function save ($user_id = null, $date_added = null, $date_modified=
> null, $details = null) {
> $this->data['note']['user_id'] = $user_id;
> $this->data['note']['date_added'] = date("Y-m-d H:i",
> strtotime($date_added));
> $this->data['note']['date_modified'] = date("Y-m-d H:i",
> strtotime($date_modified));
> $this->data['note']['details'] = $details;
>
> if($this->Note->save($this->data)) {
> echo 'save successful';
> }
> else {
> echo 'no good';
>
> }
>
> }
>
> This is the error I get :
>
> SELECT COUNT(notes_id) AS count FROM `notes` WHERE `notes_id` = 1
>
> I don't know why it tries to do a select where notes_id = 1 since in
> the parameters I don't even ask for the notes_id since it's a
> autoincrement. This is why I tried the custom query but had no luck
> with that either.
>
> Can anyone shed some light on any of the 2 solutions I'm trying to
> develop thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: custom query function and associated views
You have to make it yourself. On Jul 29, 9:22 pm, rtanz <[EMAIL PROTECTED]> wrote: > hi if i make a custom sql query in my model and create a function that > calls it from a models controller then bake my view will it create a > view for this function or do i have to create any views that are not > automatically generated by scaffolding myself? thanks --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: custom query returns odd array
Anselm, Thanks that makes sense to me. It's actually not much more work to work with the data in this format. It's just good to know this is by design and not my limited knowledge of Cake at work. :) Thanks, Zoltan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: custom query returns odd array
I'm wondering why Cake is returning each row as two separate arrays instead of just adding the total to the [cities] array. I'm thinking of just post-processing this to put the total into the cities array, but I'm wondering if I need to re-state the query a different way? When there is more than one table in queries, Cake splits the result into an array, to let you access the data efficently : SELECT post.title, author.name FROM post, author WHERE author.id = post.author_id AND post.id = 1 will return something like array( 'post' => array( 'title' => '.' ), 'author' => array( 'name' => '' ) ) Other fields, such as the 'count', will go in the '0' index. I'm relatively new to Cake, so I may be wrong, but as far as I know this is the way to do this. Also beware - it seems that depending on your mysql version/configuration, you will get a random stirng instead of '0'. See those threads : http://groups-beta.google.com/group/cake-php/browse_thread/thread/9809136278269a49/a57a3ed61b65223f?lnk=gst&q=count&rnum=3#a57a3ed61b65223f http://groups-beta.google.com/group/cake-php/browse_thread/thread/78fa3ff448d860f/96b5d69df4b42bf8?lnk=gst&q=count&rnum=1#96b5d69df4b42bf8 Anselm --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
