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

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

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: [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!