How to do DISTINCT database queries

10 posts by 5 authors in: Forums > CMS Builder
Last Post: August 1, 2012   (RSS)

Re: [dougdrury] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507

By Dave - April 8, 2009

Hi Doug,

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507

By dougdrury - April 8, 2009

I have a cms table called 'projects'. It has a field name 'projectType'. I would like to just get a DISTINCT return of the project types and display them on a page. I will be using this data to create some HTML, not populate a list field on the site or in the cmsAdmin pages.

Thanks!!
Doug

Re: [dougdrury] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507

By ross - April 9, 2009

Hi Doug

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

Ross,
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

Hi Doug,

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!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Notice: Undefined offset: 1 in ['DOCUMENT_ROOT']/CMS/lib/database_functions.php on line 507

By dougdrury - April 10, 2009

Dave,
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

Hi!

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

Hi,

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

By alissibronte - August 1, 2012

Hi Jason,

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!