Small where clause issue
12 posts by 2 authors in: Forums > CMS Builder
Last Post: April 19, 2010 (RSS)
Another question :)
I'm working on a page where I only want to show the items of a certain category, in a specific time period. This is my code:
<?php
list($fpvideoRecords, $fpvideoMetaData) = getRecords(array(
'tableName' => 'fpvideo',
'limit' => '5',
'where' => "category LIKE '%\t5\t%' AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",
));
?>
This shows only the items in tab5, that are created in March 2010. Exactly what I want, except for one minor thing. I've got quite some categories and naming them "t1" until "t1000" is rather confusing, so I was wondering whether I could use the category name instead. The name of "t5" is "ATP", so I tried this code:
'where' => "category LIKE '%\tATP\t%' AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",
But it doesn't give me any results. I tried removing the t before ATP, and the one after but that didn't make a difference.
Any suggestions?
I'm working on a page where I only want to show the items of a certain category, in a specific time period. This is my code:
<?php
list($fpvideoRecords, $fpvideoMetaData) = getRecords(array(
'tableName' => 'fpvideo',
'limit' => '5',
'where' => "category LIKE '%\t5\t%' AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",
));
?>
This shows only the items in tab5, that are created in March 2010. Exactly what I want, except for one minor thing. I've got quite some categories and naming them "t1" until "t1000" is rather confusing, so I was wondering whether I could use the category name instead. The name of "t5" is "ATP", so I tried this code:
'where' => "category LIKE '%\tATP\t%' AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",
But it doesn't give me any results. I tried removing the t before ATP, and the one after but that didn't make a difference.
Any suggestions?
Re: [Hansaardappel] Small where clause issue
By Jason - April 16, 2010
Hi,
Is the category a multi-list field, or can it only hold one value?
'\t' is an escape character used to represent a tab, which is what is used to separate multiple values in a list. If there are multiple values, and the category is called t5, you can try this:
Give that a try and let me know if you run into any more issues.
Is the category a multi-list field, or can it only hold one value?
'\t' is an escape character used to represent a tab, which is what is used to separate multiple values in a list. If there are multiple values, and the category is called t5, you can try this:
'where' => "category LIKE '%\tt5\t%' AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",Give that a try and let me know if you run into any more issues.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Small where clause issue
Hey Jason, thanks for replying.
I think you misunderstood my post, the function already works when I use the code
'where' => "category LIKE '%\t5\t%' AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",
Instead of using using tab 5 (t5 in code) I want to use the name of tab 5 (which is ATP). Is that possible and if so, how?
Thanks!
I think you misunderstood my post, the function already works when I use the code
'where' => "category LIKE '%\t5\t%' AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",
Instead of using using tab 5 (t5 in code) I want to use the name of tab 5 (which is ATP). Is that possible and if so, how?
Thanks!
Re: [Hansaardappel] Small where clause issue
By Jason - April 16, 2010
Hi,
Okay, what we'll need here is a nested query. Before the line where use the list() function, put this line.
You'll have to change the names to match what's in your database. This is selecting the category number from the category table that has the name "ATP".
Next, change your 'where' clause to this:
Give this a try and let me know if this works.
Okay, what we'll need here is a nested query. Before the line where use the list() function, put this line.
$query ="Select num from cms_category where name='ATP'";You'll have to change the names to match what's in your database. This is selecting the category number from the category table that has the name "ATP".
Next, change your 'where' clause to this:
'where' => "category IN ($query) AND MONTH(createdDate) = 3 AND YEAR(createdDate) = 2010",Give this a try and let me know if this works.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Small where clause issue
Hi Jason,
It works once again, there's just one little problem. When an entry is assigned to multiple categories, I'm having a bit of trouble to get the content displayed. There's this entry that's assigned to two categories, namely "ATP" and "Australian Open". I use this code:
$query ="Select num from cms_categories where name='Australian Open'";
It doesn't show me the content and I think it's because of the fact that the entry is also assigned to ATP, because when I add the entry only to the category Australian Open, it does work.
Suggestions?
It works once again, there's just one little problem. When an entry is assigned to multiple categories, I'm having a bit of trouble to get the content displayed. There's this entry that's assigned to two categories, namely "ATP" and "Australian Open". I use this code:
$query ="Select num from cms_categories where name='Australian Open'";
It doesn't show me the content and I think it's because of the fact that the entry is also assigned to ATP, because when I add the entry only to the category Australian Open, it does work.
Suggestions?
Re: [Hansaardappel] Small where clause issue
By Jason - April 16, 2010
Hi,
I think we found a solution. We are going to need two queries.
First we'll get the record numbers of categories we're looking for and build them into a string:
Next, the second query get's the records whose that have that category:
You may have to change some of the names, but this should work.
Give it a try and let me know.
I think we found a solution. We are going to need two queries.
First we'll get the record numbers of categories we're looking for and build them into a string:
<?php
list($catsRecords,$catsMetaData)=getRecords(array(
'tableName'=>'category',
'where'=>"title='Australian Open'",
));
$catlist="'";
foreach($catsRecords as $cats){
$catlist.="%\t".$cats['num']."\t%";
}
$catlist.="'";
?>Next, the second query get's the records whose that have that category:
<?php
list($catRecords,$catMetaData)=getRecords(array(
'tableName'=>'fpvideo',
'where'=>"category LIKE $catlist",
));
?>You may have to change some of the names, but this should work.
Give it a try and let me know.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Small where clause issue
Just what I wanted. Thanks for all the help today Jason it's really appreciated!
Greets,
Hansaardappel
Greets,
Hansaardappel
Re: [Hansaardappel] Small where clause issue
*Sigh* ran into another problem...
I wanted to display the records that belonged to BOTH "ATP" and "Australian Open" category. The code I used:
<?php list($catsRecords,$catsMetaData)=getRecords(array(
'tableName'=>'categories',
'where'=>"name='ATP' AND name='Australian Open'",
));
$catlist="'";
foreach($catsRecords as $cats){
$catlist.="%\t".$cats['num']."\t%";
}
$catlist.="'";
?>
<?php list($catRecords,$catMetaData)=getRecords(array(
'tableName'=>'fpvideo',
'where'=>"category LIKE $catlist",
'orderBy'=>'createdDate ASC',
));
?>
This doesn't show any results though. What am I missing here?
I wanted to display the records that belonged to BOTH "ATP" and "Australian Open" category. The code I used:
<?php list($catsRecords,$catsMetaData)=getRecords(array(
'tableName'=>'categories',
'where'=>"name='ATP' AND name='Australian Open'",
));
$catlist="'";
foreach($catsRecords as $cats){
$catlist.="%\t".$cats['num']."\t%";
}
$catlist.="'";
?>
<?php list($catRecords,$catMetaData)=getRecords(array(
'tableName'=>'fpvideo',
'where'=>"category LIKE $catlist",
'orderBy'=>'createdDate ASC',
));
?>
This doesn't show any results though. What am I missing here?
Re: [Hansaardappel] Small where clause issue
By Jason - April 19, 2010
Hi,
This one is an easy fix. We use the first query to get a list of all the category numbers that we want. So you need to change your "where" to this line:
'where'=>"name='ATP' OR name='Australian Open'",
You weren't getting anything because there would never be a category with 2 names (ATP AND Australian Open).
Hope this helps.
This one is an easy fix. We use the first query to get a list of all the category numbers that we want. So you need to change your "where" to this line:
'where'=>"name='ATP' OR name='Australian Open'",
You weren't getting anything because there would never be a category with 2 names (ATP AND Australian Open).
Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Small where clause issue
Thanks for replying Jason. I changed the code to what you said, but still no results...