fmwebschool.com
Top Experts [learn more]Top 4-10
webko

9743 K		bandmandq

2458 K		Genx

1525 K		4. tcmeyers
5. kbata
6. Martie
7. Hammerton
8. rrenfrow
9. bneeman
10. plegler
Welcome, Guest. Please login or register.
May 24, 2013, 06:08:10 AM

Login with username, password and session length
Search:     Advanced search
Welcome to the FileMaker Web Masters Exchange.  If you have any questions about how to use this forum, please watch the getting started movie at:
http://www.fmwebschool.com/movies/forum1/forum1.html
27465 Posts in 6066 Topics by 1523 Members
Latest Member: donok
* Home Help Search Calendar Login Register
+  fmwebschool.com
|-+  PHP Web Publishing Technologies
| |-+  FileMaker PHP, FX.php custom web publishing
| | |-+  [SOLVED] select Distinct?		 0 Members and 1 Guest are viewing this topic. « previous next »
Pages: [1] Print
Author Topic: [SOLVED] select Distinct?  (Read 5658 times)
stopha
Newbie
*
Offline Offline

Posts: 4
[SOLVED] select Distinct? [Was worth 50 Kudos points!]
« on: March 14, 2006, 05:58:14 AM »
 See Answer
Hello,
Is there a way for me to do a select distinct using FX.php? 

Thanks!
« Last Edit: January 07, 2008, 06:26:38 AM by Michael Petrov » Logged
andrewbish
Jr. Member
**
Offline Offline

Posts: 40
« Reply #1 on: January 06, 2008, 10:31:49 PM »
I take it there isnt a way to return a DISTINCT recordset.

How does everyone else not have duplicate values on their website?
Logged
Genx
Global Moderator
Hero Member
*****
Offline Offline

Posts: 596
Kudos: 1525


Good Times



Applications:
« Reply #2 on: January 07, 2008, 04:06:08 AM »
Easiest way would be to do something like this (forgive me but i'm going to do this example with the fmapi but the principle is the same):

Code:
foreach($result->getRecords() as $record) {
   $uniqueField = $record->getField(myUniqueField);
   $displayInfo[$uniqueField] = array(someData=>$record->getField(someData),someMoreData=>$record->getField(someMoreData));
}

if( count( $displayInfo ) > 0 ) {
   foreach($displayInfo as $uniqueValue=>$recordInfo){
       echo "Unique Record {$unqiueValue}: ".$recordInfo[someData].' '.$recordInfo[someMoreData];
   }
}

So essentially, given that we know that an array is a set of keys and values, you can apply the above. If a value already exists in that position within the array, it will be overwritten leading to more of a sql "distinct" statement... mind you it doesn't actually increase efficiency but it will let you deal with the information in the same sort of way at the end of the day.

You could probably also use the same concept and just do an array_key_exists() check.

Come to think of it, this would probably be quite a handy FMStudio function.

HTH
« Last Edit: January 07, 2008, 05:50:57 AM by Genx » Logged
Michael Petrov
Chief Software Developer
Administrator
Hero Member
*****
Offline Offline

Posts: 4286
Kudos: 15522




Applications:
« Reply #3 on: January 07, 2008, 06:30:53 AM »
While there is no native "distinct" specification in FM queries on the web (or within the client), I tend to use a value list to get this list built in many cases. With a value list you can specify to get all values from a specific field and that is essentially "distinct". As for it being a handy FMStudio function - certainly an interesting idea, might be hard to figure out how to deal with the FM API structures (such as found count), and also decide which distinct record remains and which duplicate gets thrown out.

Best Regards,
Michael Petrov
Logged
Michael Petrov,
Chief Software Developer
FMWebschool
800.353.7950
michael@fmwebschool.com
Keep up with our development, follow me on Twitter
Genx
Global Moderator
Hero Member
*****
Offline Offline

Posts: 596
Kudos: 1525


Good Times



Applications:
« Reply #4 on: January 07, 2008, 06:35:44 AM »
Quote
decide which distinct record remains and which duplicate gets thrown out.

Hmm, yeh good point.

Then again the SQL Distinct statement is only really used in scenario's where it doesn't matter, is it not?... Or rather it's used where all specified fields should be unique to the table... Could be quite handy...
« Last Edit: January 07, 2008, 06:49:58 AM by Genx » Logged
andrewbish
Jr. Member
**
Offline Offline

Posts: 40
« Reply #5 on: January 07, 2008, 08:12:23 PM »
It would be extremely handy to have a simple distinct function, it would be even better if that function could be implemented after a sort function was applied. eg a business orders something 3 years in a row, sort by the years descending and remove the others.
Logged
Michael Petrov
Chief Software Developer
Administrator
Hero Member
*****
Offline Offline

Posts: 4286
Kudos: 15522




Applications:
« Reply #6 on: January 08, 2008, 07:03:41 AM »
Quote from: Genx on January 07, 2008, 06:35:44 AM
Quote
decide which distinct record remains and which duplicate gets thrown out.

Hmm, yeh good point.

Then again the SQL Distinct statement is only really used in scenario's where it doesn't matter, is it not?... Or rather it's used where all specified fields should be unique to the table... Could be quite handy...

Looks like DISTINCT actually returns the first row and doesn't complain about the other rows - then COUNT and other functions should work just as well on the result set (unlike the FileMaker result data which will have incorrect found set count if distinct is applied after it is returned). I can see a way to modify those with FX relatively easily but the API would cause more trouble since the source code is not available.
Logged
Michael Petrov,
Chief Software Developer
FMWebschool
800.353.7950
michael@fmwebschool.com
Keep up with our development, follow me on Twitter
andrewbish
Jr. Member
**
Offline Offline

Posts: 40
« Reply #7 on: January 08, 2008, 02:42:27 PM »
So do we wait for an FX update that has a DISTINCT function in it? Grin. (I can see my headache fading away)
Logged
Jung
Newbie
*
Offline Offline

Posts: 12
« Reply #8 on: October 10, 2011, 01:08:38 PM »
Quote from: Genx on January 07, 2008, 04:06:08 AM
Easiest way would be to do something like this (forgive me but i'm going to do this example with the fmapi but the principle is the same):

Code:
foreach($result->getRecords() as $record) {
   $uniqueField = $record->getField(myUniqueField);
   $displayInfo[$uniqueField] = array(someData=>$record->getField(someData),someMoreData=>$record->getField(someMoreData));
}

if( count( $displayInfo ) > 0 ) {
   foreach($displayInfo as $uniqueValue=>$recordInfo){
       echo "Unique Record {$unqiueValue}: ".$recordInfo[someData].' '.$recordInfo[someMoreData];
   }
}


Hi there,

How can I apply above in my code? please your guidance please.
<?php
$found_records_find = $course->newFindCommand('Courses_Web');
$found_records_findCriterions = array('OrgSchoolOwnerName'=>'*',);
foreach($found_records_findCriterions as $key=>$value) {
    $found_records_find->AddFindCriterion($key,$value);
}

fmsSetPage($found_records_find,'found_records',7);

$found_records_result = $found_records_find->execute();

if(FileMaker::isError($found_records_result)) fmsTrapError($found_records_result,"error.php");

fmsSetLastPage($found_records_result,'found_records',7);

$found_records_row = current($found_records_result->getRecords());

 
 // FMStudio Pro - do not remove comment, needed for DreamWeaver support ?>
<?php foreach($found_records_result->getRecords() as $found_records_row){ ?>



<?php } ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Untitled Document</title>
</head>

<body>
<table name="school-list_table" style="background-color: #CCCCCC; " cellspacing="1" width="400">
  <tr bgcolor="#EEEEEE">
    <th>School Name</th>
  </tr>
  <?php foreach($found_records_result->getRecords() as $found_records_row){    ?>
 
    <tr bgcolor="#FFFFFF">
      <td><?php echo $found_records_row->getField('OrgSchoolOwnerName'); ?></td>
    </tr>
    <?php } ?>
</table>
</body>
</html>
Logged
webko
Global Moderator
Hero Member
*****
Offline Offline

Posts: 2051
Kudos: 9743



WWW
Applications:
« Reply #9 on: October 10, 2011, 02:54:12 PM »
I use a slightly more brute force and ignorance approach, that relies on the recordset being sorted by the value and then comparing each row with the previous to determine if it is the same or different...

Code:
//Perform the find, sorted by OrgSchoolOwnerName

//In the html section, something like:
<?php 
//Set the temp variable:
 $OrgSchoolOwnerName = "";
 //Now loop the found set, doing a comparison and writing out the value if different
 foreach($found_records_result->getRecords() as $found_records_row){  
 if ($OrgSchoolOwnerName != $found_records_row->getField('OrgSchoolOwnerName') {
  echo $found_records_row->getField('OrgSchoolOwnerName');
  $OrgSchoolOwnerName =  $found_records_row->getField('OrgSchoolOwnerName');
 }
 } 
 ?>
Logged
tim.webko_at_gmail.com
Jung
Newbie
*
Offline Offline

Posts: 12
« Reply #10 on: October 11, 2011, 12:52:18 PM »
Quote from: webko on October 10, 2011, 02:54:12 PM
I use a slightly more brute force and ignorance approach, that relies on the recordset being sorted by the value and then comparing each row with the previous to determine if it is the same or different...

Code:
//Perform the find, sorted by OrgSchoolOwnerName

//In the html section, something like:
<?php 
//Set the temp variable:
 $OrgSchoolOwnerName = "";
 //Now loop the found set, doing a comparison and writing out the value if different
 foreach($found_records_result->getRecords() as $found_records_row){  
 if ($OrgSchoolOwnerName != $found_records_row->getField('OrgSchoolOwnerName') {
  echo $found_records_row->getField('OrgSchoolOwnerName');
  $OrgSchoolOwnerName =  $found_records_row->getField('OrgSchoolOwnerName');
 }
 } 
 ?>

Thank you so much webko, I have followed your instruction and modified my page as below but it gives me Parse error: parse error on line 29.
Would you be kindly review my code below and advice me where I am doing wrong? many thanks for your experty and kind advice.

Code:
<?php require_once('Connections/course.php'); ?>
<?php
$found_records_find = $course->newFindCommand('Courses_Web');
$found_records_findCriterions = array('OrgSchoolOwnerName'=>'*',);
foreach($found_records_findCriterions as $key=>$value) {
    $found_records_find->AddFindCriterion($key,$value);
}
fmsSetPage($found_records_find,'found_records',10); 
$found_records_find->addSortRule('OrgSchoolOwnerName',1,FILEMAKER_SORT_ASCEND); 
$found_records_result = $found_records_find->execute(); 
if(FileMaker::isError($found_records_result)) fmsTrapError($found_records_result,"error.php"); 
fmsSetLastPage($found_records_result,'found_records',10); 
$found_records_row = current($found_records_result->getRecords());

 
 // FMStudio Pro - do not remove comment, needed for DreamWeaver support ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Browse by School - Course Finder</title>
</head>
<body>
<?php 
//Set the temp variable:
 $OrgSchoolOwnerName = "";
 //Now loop the found set, doing a comparison and writing out the value if different
 foreach($found_records_result->getRecords() as $found_records_row){  
 if ($OrgSchoolOwnerName != $found_records_row->getField('OrgSchoolOwnerName') {
  echo $found_records_row->getField('OrgSchoolOwnerName');
  $OrgSchoolOwnerName =  $found_records_row->getField('OrgSchoolOwnerName');
 }
 } 
 ?>
</body>
</html>
Logged
webko
Global Moderator
Hero Member
*****
Offline Offline

Posts: 2051
Kudos: 9743



WWW
Applications:
« Reply #11 on: October 11, 2011, 01:53:39 PM »
Missing a closing ) in Line 29 - should be:

Code:
if ($OrgSchoolOwnerName != $found_records_row->getField('OrgSchoolOwnerName')) {

My fault in the example - apologies
Logged
tim.webko_at_gmail.com
Jung
Newbie
*
Offline Offline

Posts: 12
« Reply #12 on: October 11, 2011, 02:29:44 PM »
Quote from: webko on October 11, 2011, 01:53:39 PM
Missing a closing ) in Line 29 - should be:

Code:
if ($OrgSchoolOwnerName != $found_records_row->getField('OrgSchoolOwnerName')) {

My fault in the example - apologies

Fantasitc! This works like a charm! thank you so very much for your kind help.
Logged
Pages: [1] Print 
« previous next »
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.18 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!