Limiting display of records based on multiple lists from db
6 posts by 2 authors in: Forums > CMS Builder
Last Post: January 15, 2010 (RSS)
I've searched the forums and tried several things, but can't seem to get the code right. Here's my scenario:
I'm building a document repository with CMS Builder. Each document is assigned to several different categories, based on existing tables. In this case,
Document Type - a category menu I created
Application - a multi value check box list getting its values from the applications table (option values=num, option labels=title)
Related Products - a multi value pull down list getting its values from the products table (option values=num, option labels=title)
The code generated by CMSB for the head as well as Applications and related Products field is:
On various pages within the site I want to only show specific combinations. For instance,
On individual applications pages (which are generated from a 'multi' menu type) , I want to show the specific document type of 'application note', and documents which have the same application as the application page I'm viewing (i.e. surface water application notes on the surface water application page).
In the code below, document_type '1' equals 'application note'
That code successfully shows all application notes on all application pages, which was not my goal [:P]
It seems that when I attempt to filter for application type, it's eliminating all types because of the multi value options. How do I tell the system essentially
"if your document_type is an application note, and if one of the selected application check box's names matches the title of the application page you're on, display in this list"? And how do I accomplish that on a 'detail page' viewer? My concern is that if I started using 'where' clauses in the head section that I would be setting up the viewer to only work for one application page as opposed to having it 'universal'. By the way, the code for the 'applications page title' is:
You can see a manually coded list example of what I'm trying to attempt on this page: http://www.ysi.com/applicationsdetail.php?Aquaculture-1
Check the Documents tab - I'll have multiple lists based on document_type on this tab, all hopefully coming from the document repository (manually keeping track of all our documents is a huge pain! :)
I'm going to be attempting to do the same thing with product pages, in the sense that on a product page, I'll have multiple lists of documents organized based on doc type. I'm thinking if I could understand how this application page stuff works, I'd have a better chance of correctly coding the product pages. That tab is currently hidden until I can figure this out.
I'm hoping the answer is simple, and it's eluding me simply because I've been staring at the code too long. Any insight would be a great help. Thanks!
I'm building a document repository with CMS Builder. Each document is assigned to several different categories, based on existing tables. In this case,
Document Type - a category menu I created
Application - a multi value check box list getting its values from the applications table (option values=num, option labels=title)
Related Products - a multi value pull down list getting its values from the products table (option values=num, option labels=title)
The code generated by CMSB for the head as well as Applications and related Products field is:
list($documentsRecords, $documentsMetaData) = getRecords(array(
'tableName' => 'documents',
'allowSearch' => '0',
));
Application: <?php echo join(', ', getListLabels('documents', 'application', $record['application'])); ?><br/>
Related Products: <?php echo join(', ', getListLabels('documents', 'related_products', $record['related_products'])); ?>On various pages within the site I want to only show specific combinations. For instance,
On individual applications pages (which are generated from a 'multi' menu type) , I want to show the specific document type of 'application note', and documents which have the same application as the application page I'm viewing (i.e. surface water application notes on the surface water application page).
In the code below, document_type '1' equals 'application note'
<?php foreach ($documentsRecords as $record): ?>
<?php if ($record['document_type']=="1" && $record['application']=$applicationsRecord['num']): ?>
<?php foreach ($record['pdf'] as $upload): ?>
<a href="<?php echo $upload['urlPath'] ?>"><?php echo $record['title'] ?></a><br/>
<?php endforeach ?>
<?php endif ?>
<?php endforeach; ?>That code successfully shows all application notes on all application pages, which was not my goal [:P]
It seems that when I attempt to filter for application type, it's eliminating all types because of the multi value options. How do I tell the system essentially
"if your document_type is an application note, and if one of the selected application check box's names matches the title of the application page you're on, display in this list"? And how do I accomplish that on a 'detail page' viewer? My concern is that if I started using 'where' clauses in the head section that I would be setting up the viewer to only work for one application page as opposed to having it 'universal'. By the way, the code for the 'applications page title' is:
<?php echo $applicationsRecord['title'] ?>You can see a manually coded list example of what I'm trying to attempt on this page: http://www.ysi.com/applicationsdetail.php?Aquaculture-1
Check the Documents tab - I'll have multiple lists based on document_type on this tab, all hopefully coming from the document repository (manually keeping track of all our documents is a huge pain! :)
I'm going to be attempting to do the same thing with product pages, in the sense that on a product page, I'll have multiple lists of documents organized based on doc type. I'm thinking if I could understand how this application page stuff works, I'd have a better chance of correctly coding the product pages. That tab is currently hidden until I can figure this out.
I'm hoping the answer is simple, and it's eluding me simply because I've been staring at the code too long. Any insight would be a great help. Thanks!
Re: [cricket7475] Limiting display of records based on multiple lists from db
By Dave - January 14, 2010
Hi cricket7475,
I'm going to give this one a try. If this post doesn't solve it please attach some of the viewer files and/or post some more example urls so we can get a better idea. Thanks!
All the multi value fields are stored in a tab separated format. So if you had 1, 2, and 3 selected it would be stored as "\t1\t2\t3\t" (where \t means tab).
Here's how you match a single value in MySQL:
'where' => " colors LIKE '%\tred\t%' ",
And in PHP:
<?php if (preg_match("/\tred\t/", $record['colors']): ?>
Also, when comparing values in PHP we sure to use two == for comparison, as one = means assign the value.
$record['application'] == $applicationsRecord['num'])
Otherwise it will return true all the time if the assigned value isn't 0.
When you want to show related records on a detail page, all you have is the record you've already loaded, so you have to use those values to decide what to lookup, or add fields if needed to the original record to indicate what related records should be displayed with it.
For example, something like this. A detail page that shows a specific record and loads related records (untested code):
It helps make a lot more sense when you rename the variables. Now I have the selected record that I can show like this: <?php echo $selectedDocument['title'] ?> and I can use foreach to loop over the related records.
On individual applications pages (which are generated from a 'multi' menu type) , I want to show the specific document type of 'application note', and documents which have the same application as the application page I'm viewing (i.e. surface water application notes on the surface water application page).
// for your related records use something like:
'where' => " document_type = '1' ", // 1 == application note
And here's what it gets tricky, the pages you're viewing can have multiple applications values right?
If it's a single value then you can just add to the where:
AND 'application' = '' .mysql_escape($selectedDocument['application']). ''
But if it can have multiple values then you need to generate some SQL like this:
'application IN ('value1','value2','value3','etc').
Let me know if you need that and I'll write you some code for that. Working with multi value fields is a little tricky sometimes.
Hope that helps! Let me know any further questions or details you need.
I'm going to give this one a try. If this post doesn't solve it please attach some of the viewer files and/or post some more example urls so we can get a better idea. Thanks!
All the multi value fields are stored in a tab separated format. So if you had 1, 2, and 3 selected it would be stored as "\t1\t2\t3\t" (where \t means tab).
Here's how you match a single value in MySQL:
'where' => " colors LIKE '%\tred\t%' ",
And in PHP:
<?php if (preg_match("/\tred\t/", $record['colors']): ?>
Also, when comparing values in PHP we sure to use two == for comparison, as one = means assign the value.
$record['application'] == $applicationsRecord['num'])
Otherwise it will return true all the time if the assigned value isn't 0.
When you want to show related records on a detail page, all you have is the record you've already loaded, so you have to use those values to decide what to lookup, or add fields if needed to the original record to indicate what related records should be displayed with it.
For example, something like this. A detail page that shows a specific record and loads related records (untested code):
// load selected document
list($selectedDocuments, $selectedDocumentsMetaData) = getRecords(array(
'tableName' => 'documents',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$selectedDocument = @$selectedDocuments[0]; // get first record;
// load related documents
list($relatedDocuments, $relatedDocumentsMetaData) = getRecords(array(
'tableName' => 'documents',
'where' => " colors LIKE '%\t" .mysql_escape(@$selectedDocument['color']). "\t%' ",
'allowSearch' => false,
'limit' => '1',
));It helps make a lot more sense when you rename the variables. Now I have the selected record that I can show like this: <?php echo $selectedDocument['title'] ?> and I can use foreach to loop over the related records.
On individual applications pages (which are generated from a 'multi' menu type) , I want to show the specific document type of 'application note', and documents which have the same application as the application page I'm viewing (i.e. surface water application notes on the surface water application page).
// for your related records use something like:
'where' => " document_type = '1' ", // 1 == application note
And here's what it gets tricky, the pages you're viewing can have multiple applications values right?
If it's a single value then you can just add to the where:
AND 'application' = '' .mysql_escape($selectedDocument['application']). ''
But if it can have multiple values then you need to generate some SQL like this:
'application IN ('value1','value2','value3','etc').
Let me know if you need that and I'll write you some code for that. Working with multi value fields is a little tricky sometimes.
Hope that helps! Let me know any further questions or details you need.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Limiting display of records based on multiple lists from db
By cricket7475 - January 14, 2010 - edited: January 14, 2010
Thanks Dave,
So if I understand correctly, I should be able to set up a 'where' clause for the documents array that limits the documents pulled to just those whose selected application includes the applicationsRecords['num'] ...right?
Hypothetical:
If the 'application' selection list is no longer a multi value but rather a single radio button option, then I would think this (or something similar to this) would work:
and the php on the page
However, even after I make those changes and reassign values to the document entry, it still doesn't display. Which makes me think my 'where' clause isn't working. Before I get into the multi value fields I'd like to see if I can get the single value field to work.
I've attached the applicationsDetail.php viewer which is the one I'm currently working on and the one that is currently populating the live page (minus the db locations). This viewer applies to all applications available from this page: http://www.ysi.com/applications.php
Right now I have 5 documents in the system for testing, all of which should show at the bottom of the documents tab below the last horizontal rule.
1 under ocean application (record number 2):http://www.ysi.com/applicationsdetail.php?Ocean-and-Coastal-Monitoring-2
2 under surface water application (record number 5): http://www.ysi.com/applicationsdetail.php?Surface-Water-5
2 under wastewater application (record number 7): http://www.ysi.com/applicationsdetail.php?Wastewater-7
Thanks again!
So if I understand correctly, I should be able to set up a 'where' clause for the documents array that limits the documents pulled to just those whose selected application includes the applicationsRecords['num'] ...right?
Hypothetical:
If the 'application' selection list is no longer a multi value but rather a single radio button option, then I would think this (or something similar to this) would work:
list($applicationsRecords, $applicationsMetaData) = getRecords(array(
'tableName' => 'applications',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$applicationsRecord = @$applicationsRecords[0]; // get first record
// show error message if no matching record is found
if (!$applicationsRecord) {
print "This application is no longer active. Please go back and select another. Thank you!";
exit;
}
list($documentsRecords, $documentsMetaData) = getRecords(array(
'tableName' => 'documents',
'where' => "'application' = ".mysql_escape($applicationsRecord['num'])."",
'allowSearch' => '0',
));and the php on the page
<?php foreach ($documentsRecords as $record): ?>
<?php if ($record['document_type']=="1"): ?>
<?php foreach ($record['pdf'] as $upload): ?>
<a href="<?php echo $upload['urlPath'] ?>"><?php echo $record['title'] ?></a><br/>
<?php endforeach ?>
<?php endif ?>
<?php endforeach; ?>However, even after I make those changes and reassign values to the document entry, it still doesn't display. Which makes me think my 'where' clause isn't working. Before I get into the multi value fields I'd like to see if I can get the single value field to work.
I've attached the applicationsDetail.php viewer which is the one I'm currently working on and the one that is currently populating the live page (minus the db locations). This viewer applies to all applications available from this page: http://www.ysi.com/applications.php
Right now I have 5 documents in the system for testing, all of which should show at the bottom of the documents tab below the last horizontal rule.
1 under ocean application (record number 2):http://www.ysi.com/applicationsdetail.php?Ocean-and-Coastal-Monitoring-2
2 under surface water application (record number 5): http://www.ysi.com/applicationsdetail.php?Surface-Water-5
2 under wastewater application (record number 7): http://www.ysi.com/applicationsdetail.php?Wastewater-7
Thanks again!
Re: [cricket7475] Limiting display of records based on multiple lists from db
By Dave - January 15, 2010
Hi cricket7475,
Yea, it looks right to me as far as I can tell.
I'd recommend adding some debugging code at different steps to see where the problem is.
For the documents:
That will show you exactly what the SQL is that's being generated. So we can make sure that's correct.
Next, if that looks good, we can check what's being returned with this:
That will list all the records that are getting returned.
If neither of those clear it up I can take a look at that if you like. Just email me CMS and FTP login details (if available) to dave@interactivetools.com (Note: Email, don't post login details to the forum). Or just call in and ask for Dave and we can do some phone support. I think we could probably sort this one out in a few minutes on the phone.
Hope that helps!
Yea, it looks right to me as far as I can tell.
I'd recommend adding some debugging code at different steps to see where the problem is.
For the documents:
list($documentsRecords, $documentsMetaData) = getRecords(array(
'tableName' => 'documents',
'where' => "'application' = ".mysql_escape($applicationsRecord['num'])."",
'allowSearch' => '0',
'debugSql' => true,
));That will show you exactly what the SQL is that's being generated. So we can make sure that's correct.
Next, if that looks good, we can check what's being returned with this:
list($documentsRecords, $documentsMetaData) = getRecords(array(
'tableName' => 'documents',
'where' => "'application' = ".mysql_escape($applicationsRecord['num'])."",
'allowSearch' => '0',
));
showme($documentsRecords);That will list all the records that are getting returned.
If neither of those clear it up I can take a look at that if you like. Just email me CMS and FTP login details (if available) to dave@interactivetools.com (Note: Email, don't post login details to the forum). Or just call in and ask for Dave and we can do some phone support. I think we could probably sort this one out in a few minutes on the phone.
Hope that helps!
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Limiting display of records based on multiple lists from db
OK - So here's the SQL
Which looks like it's doing what it's supposed to be doing, assuming that for 'documents.hidden' the '0' means false, as I have no hidden documents in that section right now.
When I apply the second test I get this:
Which is telling me that it's not displaying any documents that meet the 'where' criteria even though they exist.
If you see anything out of sorts with the above let me know - I'm not sure I'm seeing things right between the lack of sleep and the sinus meds :)
I'll send you what I can in an email - in the meantime, I'm going to try to recreate the scenario on a page which has the bare minimum and see if I can get it to work.
Thanks again!
SELECT SQL_CALC_FOUND_ROWS `documents`.* FROM `ysicms_documents` as `documents` WHERE ('application' = 5) AND documents.hidden = 0 ORDER BY idWhich looks like it's doing what it's supposed to be doing, assuming that for 'documents.hidden' the '0' means false, as I have no hidden documents in that section right now.
When I apply the second test I get this:
Array ( )Which is telling me that it's not displaying any documents that meet the 'where' criteria even though they exist.
If you see anything out of sorts with the above let me know - I'm not sure I'm seeing things right between the lack of sleep and the sinus meds :)
I'll send you what I can in an email - in the meantime, I'm going to try to recreate the scenario on a page which has the bare minimum and see if I can get it to work.
Thanks again!
Re: [cricket7475] Limiting display of records based on multiple lists from db
By Dave - January 15, 2010
Just a follow up on this one for anyone reading.
The issue was the single quotes in the query:
'where' => "'application' = ".mysql_escape($applicationsRecord['num'])."",
It was causing the text 'application' to be compared, not the value of the field called application.
The solution was to remove the single quotes:
'where' => " application = ".mysql_escape($applicationsRecord['num'])."",
And this was the query to match a value within multi-value field:
'where' => "application LIKE '%\t".mysql_escape($applicationsRecord['num'])."\t%'",
Hope that helps!
The issue was the single quotes in the query:
'where' => "'application' = ".mysql_escape($applicationsRecord['num'])."",
It was causing the text 'application' to be compared, not the value of the field called application.
The solution was to remove the single quotes:
'where' => " application = ".mysql_escape($applicationsRecord['num'])."",
And this was the query to match a value within multi-value field:
'where' => "application LIKE '%\t".mysql_escape($applicationsRecord['num'])."\t%'",
Hope that helps!
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com