Convert recordset to text

Forum|Forum|19 years ago
March 1, 2007
5 replies
716 views

Hi all,

I have a query returning the contents of a shopping cart. I am trying to find a way to convert this recordset into text to be inserted into the database as an order description.

Any ideas?

TIA
Paul

This topic has been closed for replies.

Correct answer

no, you're not being stupid. there's usually a number of ways to accomplish something and you're just trying to get a feel for the best way.

i'd still kinda go with my original suggestion... storing the query object at the time of purchase (storing it as text). you can convert it back to a query object later and perform whatever operations on it you need to.

storing it as text you're limited to...well...just outputting text.

you might also want to consider that when an item changes (any change, be it price, description, etc), you don't simply over-write the existing data. first copy the original record to a "product archive" table...-then- update the price in your active table.

doing this is a little more work, but there can be benefits to this. let's say you want to look over the last 3 years to see which product has changed price most frequently. or which product has had the biggest increase in price over the last year.

when you simply overwrite data, your history is effectively gone. not a lot of reporting that you can do... and reporting often comes in handy to look at (or look for) certain trends that can help you to decide which direction you want to move going forward.

my $0.02 :)

A

Anonymous

Correct answer

no, you're not being stupid. there's usually a number of ways to accomplish something and you're just trying to get a feel for the best way.

i'd still kinda go with my original suggestion... storing the query object at the time of purchase (storing it as text). you can convert it back to a query object later and perform whatever operations on it you need to.

storing it as text you're limited to...well...just outputting text.

you might also want to consider that when an item changes (any change, be it price, description, etc), you don't simply over-write the existing data. first copy the original record to a "product archive" table...-then- update the price in your active table.

doing this is a little more work, but there can be benefits to this. let's say you want to look over the last 3 years to see which product has changed price most frequently. or which product has had the biggest increase in price over the last year.

when you simply overwrite data, your history is effectively gone. not a lot of reporting that you can do... and reporting often comes in handy to look at (or look for) certain trends that can help you to decide which direction you want to move going forward.

my $0.02 :)

O

Outside5 LtdAuthor

Inspiring

CJ,

After some trial and error, WDDX is just the ticket. I was assuming it would convert the contents of the recordset into an unformatted string rather than the recordset itself.

I see what you are saying about the tracking of changes to content, and in future builds I will certainly take your advice there, though it is not in budget for the current site.

Much appreciated mate, thanks for sticking by me.

Regards,
Paul

A

Anonymous

i'm more perplexed.

you're trying to query your database to return data that you can store in your database.

?

O

Outside5 LtdAuthor

Inspiring

Hi CJ,

My aim is to store a text equivalent of an order in the database.

I have a products table (product information) and an orders table (order information), and a product order link table (linking many products with individual orders).

This setup works fine, until my client changes a price for an item in the shop, and looks through any historical orders as all items will always show the most recent price from the products table and not the price at the time of the order.

This is my thinking behind when the order is made, taking a snapshot of the order, converting it to text and inserting it as a field in the database to show the actual order and not the current values of the items.

Am I being really stupid about this? Have I missed something glaringly obvious?

Your help, as always is greatly appreciated.

Paul

A

Anonymous

why would you need to store that when it can be extrapolated by the data that's already part of the cart?

O

Outside5 LtdAuthor

Inspiring

I am a little perplexed by this whole thing. If I try to insert the value of the query it says:

Invalid data [Table (rows 1 columns pId, quantity): [pId: coldfusion.sql.QueryColumn@c363cd] [quantity: coldfusion.sql.QueryColumn@1cd6297] ] for CFSQLTYPE CF_SQL_VARCHAR.

I have never tried doing this before so have no idea if what I am trying to achieve is possible (although surely it must be).

If my query returns 3 rows, and the values of these rows are item, quantity and price, I would like to insert something along the lines of:

Short Sleeved Tshirt, 1, 25.00
Shorts, 1, 12.50
Long Sleeved Tshirt, 2, 22.85

Should I be looking at an array for this?

I am thinking something along the lines of:
<cfset theText =
<cfloop query="getCart">
#prodQty# x #prodTitle# at £#prodPrice#
</cfloop>
>

Although this is syntactically wrong, it is kind of logically what I am looking for.

Thanks,
Paul

I

insuractive

Inspiring

CJ's suggest is the way to go. WDDX is a great way to serialize ColdFusion data structures and save them in a database. My only suggestion is to make sure you have pretty robust error handling in place. If you try and call <cfwddx> and your wddx packet is malformed you can get some crazy errors. We've even had a case when the applicaiton generated a ColdFusion error on screen even though the whole thing was handled with a <cftry>/<cfcatch> block. That being said, we use it extensively on our site.

O

Outside5 LtdAuthor

Inspiring

Thanks CJ, insuractive ,

This gives me the following:

<wddxPacket version='1.0'><header/><data><recordset rowCount='1' fieldNames='pId,quantity' type='coldfusion.sql.QueryTable'><field name='pId'><string>21</string></field><field name='quantity'><number>1.0</number></field></recordset></data></wddxPacket>

I was hoping for something more like a string along the lines of:

21 x 1 (item x qty)
26 x 2 (item x qty)

Is that possible using WDDX? I have not encountered this before so know nothing of it.

Thanks,
Paul

A

Anonymous

WDDX?

<cfwddx action="cfml2wddx" input="#yourQueryName#" output="queryText" />

...will result in a string representation of the query data (can be stored in the db).

<cfwddx action="wddx2cfml" input="#queryText#" output="queryObject" />

...will convert the wddx text back into a query object.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded