Array argument array return

[Guest]

I'm using Flash remoting and I've only been able to use very simple CF code in the past. Now I need to pass my function an array as an argument and them search the database and return the query a nested array.

I have a database that I need to pass an array of numbers which are ids (these are ids of sales people) in the database. For each one I need to check a column for a number and all the numbers for this id up. So each sold contract has its own line in the database. The sales person's id is a column in the database. I'm just counting the number of contract this person has made. I can do this one by one and then add in flash but it's not very efficent.

So my resulting array returned should be something like this: (sales person id, number of contracts)
array item [0][0] = 17 - sales id
array item [0][1] = 4 - contracts sold
array item [1][0] = 29 - sales id
array item [1][1] = 2 - contracts sold

Can anyone point me in the direction of how I would set this up in CF?

Thanks

[Dan_Bracuk]

Assuming the incoming array is 1D, then, inside your function.

Step 1 - convert it to a list
Step 2 - Use that list in your query. This query should return the id and the count of their contracts sold.
Step 3 - create an empty 2D array
Step 4 - populate it from your query
Step 5 - return it

[Guest]

I'm not real sure how to convert the array to a list to start off. I'm very novice at CF and there's not a whole lot out there for .cfc documents and functions. I can sent the info as a string or an array. The string would be a comma separated string.

Then I'm not sure how to count each contract for the specific sales person.

Ian, I'm sending the array or string from flash to a function so I'm looking at something like this:



Thanks for the help so far everyone. I'm sorry about being a novice.

[Newsgroup_User]

C-Rock wrote:
> Thanks for the help so far everyone. I'm sorry about being a novice.
>
> <cffunction name="getSalesDetails" access="remote" returnType="query">
> <cfargument name="salesIds" type="string" required="yes">
> <cfquery name="getSalesDetailsQuery" datasource="rexmap"
> username="****" password="****">
> SELECT DISTINCT cvpSalesId, cvpContract
> FROM tblcvp
> WHERE cvpSalesDUNS = ??? lost here?? </cfquery>
> <cfreturn getSalesDetailsQuery>
> </cffunction>

At this point this is an SQL question on the IN operator of the WHERE
clause. A bit of Googling of using the IN operator in a WHERE clause of
a SQL statement will probably be rather informative. I also here good
things about Ben Forta's "Teach yourself SQL in 10 minutes" book.

In CF terms your syntax will look something like this.

<cffunction ...>
<cfargument name="salesIds" type="string" required="yes" hint="Comma
separated list of sales Ids">

<cfquery ...>
SELECT fields
FROM aTable
WHERE cfpSalesDUNS IN (<cfqueryParam value="#arguments.salesIds#"
list="yes" cfSQLtype="CF_SQL_INTEGER">)
</cfquery>

Using <cfqueryparam...> is the easiest way to make sure the list is
properly formated for the database management system. Otherwise you
must do so.

[Newsgroup_User]

C-Rock wrote:
> I'm using Flash remoting and I've only been able to use very simple CF code in
> the past. Now I need to pass my function an array as an argument and them
> search the database and return the query a nested array.
>
> I have a database that I need to pass an array of numbers which are ids (these
> are ids of sales people) in the database. For each one I need to check a column
> for a number and all the numbers for this id up. So each sold contract has its
> own line in the database. The sales person's id is a column in the database.
> I'm just counting the number of contract this person has made. I can do this
> one by one and then add in flash but it's not very efficent.
>
> So my resulting array returned should be something like this: (sales person
> id, number of contracts)
> array item [0][0] = 17 - sales id
> array item [0][1] = 4 - contracts sold
> array item [1][0] = 29 - sales id
> array item [1][1] = 2 - contracts sold
>
> Can anyone point me in the direction of how I would set this up in CF?
>
> Thanks
>

<cfscript>
arrayItems = arrayNew(2);
arrayItems[1][0] = 17;
arrayItems[1][1] = 4;
arrayItems[2][0] = 29;
arrayItems[2][1] = 2;
</cfscript>

Of course you would probably do this with some kind of loop over the
record set return from you database query.

I would also tend to use a structure (associative array) as I find them
more descriptive.

<cfscript>
arrayItems = arrayNew(1);
arrayItems[1] = structNew(); //technically not required but good form.
arrayItems[1]["salesID"] = 17;
arrayItems[1]["contracts"] = 4;
arrayItems[2] = structNew();
arrayItems[2]["salesID"] = 29;
arrayItems[2]["contracts"] = 2;
</cfscript>

Note: that ColdFusion arrays do not start with an index of 0, they start
with 1.

[Guest]

actually i need to convert the string to a list? create a for loop to create a query for each item in the list first right? i'm not sure what this looks like. I think the sql is easy enough to figure out I just don't know the cf syntax or setup of how to do this.

[Newsgroup_User]

C-Rock wrote:
> actually i need to convert the string to a list? create a for loop to create a
> query for each item in the list first right? i'm not sure what this looks like.
> I think the sql is easy enough to figure out I just don't know the cf syntax or
> setup of how to do this.
>

What form is this string? If it is a string of numbers with commas, or
other delimiter, it is a list. A 'list' is simply a string with some
character(s) [comma is the default character] declared as a delimiter
between list items.

No need for a loop, querying the database for each individual list item.
That is what the IN operator is for. It expects a list of items and
will return any records from the database that match any one of the
items contained in the list. This is with a single database operation
rather then one database operation for each item in the list. Much more
efficient.

[Guest]

I understand it much better now. So I got that working with what I needed except this. I can't get the count to work in CF. Everything I've read says I'm going it correctly. The database is returning id number 17 twice with the contract number in each one. The id 29 is returned only once because it has only one contract. so the result looks like this:

item [1][1] = 17
item [1][2] = 62356 - just a contact number
item[2][1] = 17
item[2][2] = 89564
item[3][1] = 29
item [3][2] = 12478

instead of the contract number i want the number of contracts for each id. so it should look like this based on the above result:

item [1][1] = 17
item [1][2] = 2 - number of contracts
item[2][1] = 29
item[2][2] = 1


I'm trying to use the count feature but I'm getting an error so this code isn't working. I was able to get the top code to return and work, I'm trying to get to the second set, which is the result I really want.


Thanks!

[Guest]

This is the code for the above post to get the first result, the undesirable result. I can I morph this code to make the second result happen?

[Newsgroup_User]

C-Rock wrote:
> <!-- shippers contracts -->
> <cffunction name="getShippersDetails" access="remote" returnType="query">
> <cfargument name="shippersList" type="string" required="yes">
> <cfargument name="shipperId" type="numeric" required="yes">
> <cfquery name="getShippersDetailsQuery" datasource="rexmap"
> username="****" password="****">
> SELECT DISTINCT(cvpContract), cvpPplFERC
> FROM tblcvp
> WHERE cvpPplFERC IN (<cfqueryParam value="#arguments.shippersList#"
> list="yes" cfSQLtype="CF_SQL_INTEGER">)
> AND cvpShprDUNS = #arguments.shipperId#
> </cfquery>
> <cfreturn getShippersDetailsQuery>
> </cffunction>

Your previously posted results is what I would expect from this query.
DISTINCT does not count records it prevents duplicates from showing up.

IF you did 'SELECT COUNT(cvpContract), cfpPplFERC' OR 'SELECT COUNT(*),
cfPplFERC'; I would expect you to get your desired results.

[Guest]

Ian, thanks again for your help. I'm sorry I'm in the wrong forum I though I was doing something wrong with the count in CF once I got going here. Now I'm too close to switch forums. Here is what is going on, It works and brings back all of the contracts for each cvpPplFERC just like it should according to the code. However the count still isn't working like I need it too. The contract number is not unique and can show up in mulitple listings. I know doesn't make sense to someone not able to see the whole data and scope of the project. For cvpPplFERC shipper number 17 I'm getting 27 for the count of the number of contracts. However it should be 2 since these two contracts are repeated one or the other throughout each result. That is why I was using distinct.

With the code attached I need it to count only distinct cvpContracts. No matter where I place distinct I'm getting errors. Any ideas?

[Guest · Accepted solution]

I got it. I will post the code. Thanks for your help Ian. I can help anytime if you need any Flash help, I'm great at that.

[Newsgroup_User]

C-Rock wrote:
> Ian, thanks again for your help. I'm sorry I'm in the wrong forum.


I don't know if you are in the wrong forum. As ColdFusion developers we
are well accustomed to mixing many technologies, SQL, HTML, JavaScript,
AJAX, and more.

I was just trying to point out in which one you where dealing with as
that often helps.

I.E. If you Googled for this problem confined to CF you would get a much
smaller set of results then if you identified it as SQL where you might
find discussions on doing this kind of group counting from an Oracle or
MS SQL or Mysql or just general ansii 92 SQL resource.

[Newsgroup_User]

Your SQL statement is a bit unusual. I can't say it matters, but I
would normally write such a query like this.

SELECT COUNT(*),tblcvp.cfpPplFERC

FROM tblcvp

WHERE cvpPplFERC IN (<cfqueryParam value="#arguments.shippersList#"
list="yes" cfSQLtype="CF_SQL_INTEGER">) AND
cfpShprDUNS = <cfqueryParam value="#argumetns.shipperId#"
cfSQLtype="CF_SQL_INTEGER">

GROUP BY tblcvp.cvpPplFERC

I have read that COUNT(*) is a bit better then counting a specific
field. The only thing I can think of is the distinct is causing some
kind of weird difference to come up or possible having the GROUP BY
before the WHERE clause. But I do not know if either of those matter.

P.S. You may note the subtle direction to encourage you to use
<cfqueryparam...> tags. They have many benefits and should usually be used.

C-Rock wrote:
> I can't get the count to work in CF.

P.P.S. Just a point of clarification you are dealing with SQL syntax
issues here and it does not matter that you are asking ColdFusion to
make the SQL request. You would be having the same problems if you
where running this code in .NET, PHP or straight in the database.