How to do DISTINCT database queries
10 posts by 5 authors in: Forums > CMS Builder
Last Post: August 1, 2012 (RSS)
By dougdrury - April 8, 2009 - edited: April 9, 2009
Sort of on topic...
is there anyway to use the CMS Builder database_functions.php to send a DISTINCT query to the database?
Thanks,
Doug
Moderator Edit: Detached and renamed thread.
Re: [dougdrury] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By Dave - April 8, 2009
Do you mean as a general query to load some data and display it or to get the values for a list field? What do you want to do with it?
Let me know and I'll try and help.
interactivetools.com
Re: [Dave] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By dougdrury - April 8, 2009
Thanks!!
Doug
Re: [dougdrury] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By ross - April 9, 2009
This one is going to get a little technical but I do have some code for you to try out. The example I am using will create a drop down menu with all the options from a database field.
<select>
<option value="">Any</option>
<?php
$tablename = 'TABLENAME';
$fieldname = 'FIELDNAME';
$schema = loadSchema($tablename);
$fieldSchema = $schema[$fieldname];
$fieldOptions = getListOptionsFromSchema($fieldSchema);
foreach ($fieldOptions as $valueAndLabel) {
list($value, $label) = $valueAndLabel;
$encodedValue = htmlspecialchars($value);
$encodedLabel = htmlspecialchars($label);
print "<option value='$encodedValue'>$encodedLabel</option>\n";
}
?>
</select>You'll need to make sure you include the /cmsAdmin/lib/viewer_functions.php file on this page if you aren't already.
Give it a shot and let me know how you make out. Thanks!
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com
Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/
Re: [ross] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By dougdrury - April 9, 2009
Thanks for this. I am not looking to populate a select field though. I am just trying to select a DISTINCT return from and SQL query and iterate through that list to show an HTML table.
In other words, something like a $useDistinct=true; that would convert the SELECT query in CMS builder from:
SELECT projectType FROM tablename;
to
SELECT DISTINCT projectType FROM tablename;
...so that I can get only one return per distinct project type.
Re: [dougdrury] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By Dave - April 9, 2009
The simplest way is just to use straight PHP/MySQL as follows (note that you'll need to change the table and fieldnames. Also, if you require viewer_functions.php first, it will connect to mysql for you with your mysql login details stored in CMS Builder. Otherwise you'd need to call mysql_connect().
require_once "../lib/viewer_functions.php";
$query = "SELECT DISTINCT title FROM {$TABLE_PREFIX}news";
$result = mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
while ($record = mysql_fetch_assoc($result)) {
print "title: {$record['title']}<br/>\n";
}
if (is_resource($result)) { mysql_free_result($result); }Or if you have a more recent version of CMS Builder you can try this helper function I made to simplify that:
$records = mysql_query_fetch_all_assoc("SELECT DISTINCT title FROM {$TABLE_PREFIX}news");
foreach ($records as $record) {
print "title: {$record['title']}<br/>\n";
}Hope that helps!
interactivetools.com
Re: [Dave] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By dougdrury - April 10, 2009
Thanks. That is perfect!!
The 'mysql_query_fetch_all_assoc()' call worked great!!
Thanks again.
Doug
Re: [Dave] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By alissibronte - July 31, 2012 - edited: July 31, 2012
I used the 'mysql_query_fetch_all_assoc' to create a custom query with GROUP BY clause, but I got a problem.
This works great:
$pressRecords = mysql_query_fetch_all_assoc("SELECT * FROM {$TABLE_PREFIX}press GROUP BY country");
foreach ($pressRecords as $record) {
print "title: {$record['title']}<br/>\n";
}but I cant access to Uploads fields like:
foreach ($pressRecords as $record):
foreach ($record['images'] as $upload):
<img src="<?php echo $upload['urlPath']; ?>" alt="<?php echo $upload['info1']; ?>" />
endforeach;
endforeach;What can I do?
Re: [alissibronte] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
By Jason - July 31, 2012
When using mysql_query_fetch_all_assoc, the function will not gather uploads. This is a function of the getRecords() function.
The easiest solution here would be to use getRecords and add in:
'groupBy' => 'country',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/
Re: [Jason] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507
I dont know why but I tried groupBy sentence in getRecords method and didn't work. Now I tried it again and now its works, maybe I typed it wrong [laugh]
Thank you!