https://interactivetools.com/forum/forum-posts.php?How-to-do-DISTINCT-database-queries-69946 Fri, 15 May 2026 03:41:50 -0700 en-us https://interactivetools.com/forum/forum-posts.php?postNum=2217251#post2217251
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!
]]> Wed, 01 Aug 2012 01:19:01 -0700 forum-posts.php?postNum=2217251#post2217251 https://interactivetools.com/forum/forum-posts.php?postNum=2217244#post2217244
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
]]> Tue, 31 Jul 2012 12:54:43 -0700 forum-posts.php?postNum=2217244#post2217244 https://interactivetools.com/forum/forum-posts.php?postNum=2217240#post2217240
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?
]]> Tue, 31 Jul 2012 10:10:35 -0700 forum-posts.php?postNum=2217240#post2217240 https://interactivetools.com/forum/forum-posts.php?postNum=2196879#post2196879 Thanks. That is perfect!!
The 'mysql_query_fetch_all_assoc()' call worked great!!

Thanks again.
Doug
]]> Fri, 10 Apr 2009 13:39:23 -0700 forum-posts.php?postNum=2196879#post2196879 https://interactivetools.com/forum/forum-posts.php?postNum=2196861#post2196861
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!
]]> Thu, 09 Apr 2009 12:18:16 -0700 forum-posts.php?postNum=2196861#post2196861 https://interactivetools.com/forum/forum-posts.php?postNum=2196858#post2196858 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.
]]> Thu, 09 Apr 2009 11:12:41 -0700 forum-posts.php?postNum=2196858#post2196858 https://interactivetools.com/forum/forum-posts.php?postNum=2196857#post2196857
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!
]]> Thu, 09 Apr 2009 11:08:06 -0700 forum-posts.php?postNum=2196857#post2196857 https://interactivetools.com/forum/forum-posts.php?postNum=2196846#post2196846
Thanks!!
Doug
]]> Wed, 08 Apr 2009 20:25:55 -0700 forum-posts.php?postNum=2196846#post2196846 https://interactivetools.com/forum/forum-posts.php?postNum=2196845#post2196845
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.
]]> Wed, 08 Apr 2009 19:09:26 -0700 forum-posts.php?postNum=2196845#post2196845 https://interactivetools.com/forum/forum-posts.php?postNum=2196844#post2196844 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.
]]> Wed, 08 Apr 2009 19:03:42 -0700 forum-posts.php?postNum=2196844#post2196844