mySQL and Array

[adamjbecker]

Hi all-

I have defined an array using:

<cfset notesattached = ArrayNew(1)>

and added some data like this:

<cfset notesattached[1] = 4>
<cfset notesattached[2] = 8>

But I can't figure out how to transfer #notesattached# to an SQL database; it
says that it is a complex value.

I've searched but haven't found my answer. Any help would be appreciated.

Thanks,
Adam

[Dan Bracuk]

Insert each element into a record/field. individually. The details depend on
what you are trying to do.

Originally posted by: adamjbecker
Hi all-

I have defined an array using:

<cfset notesattached = ArrayNew(1)>

and added some data like this:

<cfset notesattached[1] = 4>
<cfset notesattached[2] = 8>

But I can't figure out how to transfer #notesattached# to an SQL database; it
says that it is a complex value.

I've searched but haven't found my answer. Any help would be appreciated.

Thanks,
Adam

[adamjbecker]

I'm trying to make it so that I can insert multiple numbers into one field of a mySQL database, and then retrieve the individual numbers dynamically.

Hope that made sense.

Adam

[Dan Bracuk]

It makes sense to the extent that I understand your question. It doesn't make
sense to the extent that you think you will be able to actually use the numbers
once you stuff them into a single field of a single record.

But, if you insist, make sure the field is text. Then insert
'#arraytolist(yourarray)#'. Your numbers will be separated by commas.

Originally posted by: adamjbecker
I'm trying to make it so that I can insert multiple numbers into one field of
a mySQL database, and then retrieve the individual numbers dynamically.

Hope that made sense.

Adam

[mxstu]

It sounds like you're essentially trying to store a comma-delimited list of
values in a single column. This is a bad design for a number of reasons, one
of which is that it is very difficult to work with. If a single record in your
main table can have zero or more "notes" a better design might be to store the
notes in a separate table. For example, let's say the notes pertained to
records in an "accounts" table:

ACCOUNTS

AccountID | AccountCode
---------------------------------
1 | ABC
---------------------------------
2 | EFG
---------------------------------
3 | XYZ
---------------------------------

NOTES

NotesID | AccountID | Description
----------------------------------------------------
1 | 1 | Created ABC account for ...
----------------------------------------------------
2 | 1 | Changed information for ......
----------------------------------------------------
3 | 2 | Opened EFG account for ......
----------------------------------------------------
4 | 3 | Opened new account for .....
----------------------------------------------------
5 | 1 | Updated XYZ address .....
----------------------------------------------------