Passing URL variables uniquely 3

[fusionaire]

I have a shopping cart application in which I pass variable values through URL's. The problem is, if someone selects the same product again, it will show up twice on the &quot;items selected&quot; screen. When the user goes to eliminate one of the two already selected products, information on both are deleted from the database.<br><br>So, I have tried to come up with a way to uniquely identify an already inserted product in the database.Here is the code for that:<br><br>&lt;CFQUERY name=&quot;prod&quot; datasource=&quot;wpslive&quot;&gt;<br>SELECT PRODUCT_ID, ORDERNUMBER AS REDUNDANTITEM<br>FROM orders<br>WHERE&nbsp;&nbsp;ORDERNUMBER='#session.cid#'<br>&lt;/cfquery&gt;<br><br>&lt;cfoutput query=&quot;prod&quot;&gt;<br>&lt;input type=&quot;hidden&quot; name=&quot;REDUNDANTITEM&quot; value=&quot;#prod.REDUNDANTITEM#&quot;&gt;<br>&lt;/cfoutput&gt;<br><br> <br>&lt;CFIF IsDefined (&quot;prod.REDUNDANTITEM&quot&gt;<br><br>&lt;CFLOCATION URL =&quot;alreadyordered.cfm&quot; ADDTOKEN=&quot;no&quot;&gt;<br>&lt;/cfif&gt;<br><br>When I use this code, anytime I select any product, the CFLOCATION kicks in and the user is told that they have already entered the chosen product already. <br><br>If anyone knows a better way to do this, I'd very much appreciate it.<br><br>PS~Special thanks to Darkman, who seems to know the answer to everything!

 

[DarkMan]

I think I can see what's going on here....<br><br>Try changing your<br>&lt;CFIF IsDefined (&quot;prod.REDUNDANTITEM&quot&gt;<br><br>to<br>&lt;CFIF prod.RECORDCOUNT gt 0&gt;<br><br>This will only run the CFLOCATION if there were results returned.&nbsp;&nbsp;Checking to see if prod.REDUNDANTITEM exists isn't working because it is defined when you run the query, whether there are any results returned or not....<br><br>DM

 

[fusionaire]

I tried this but the problem is, that the results will be returned because I am running a query before it. here is the code for that page:<br><br><br>&lt;cfinsert datasource=&quot;wpslive&quot;<br> tablename=&quot;orders&quot;<br> formfields=&quot;ORDERNUMBER, PRODUCT_ID, MFG, PRICE, QUANTITY, DESCRIPTION&quot;&gt;<br><br><br>&lt;CFQUERY name=&quot;detail&quot; datasource=&quot;wpslive&quot;&gt;<br>SELECT PRICE, QUANTITY, PRODUCT_ID, MFG, DESCRIPTION, ORDERNUMBER, (price * quantity) AS TOTALPRICE<br>FROM orders<br>WHERE&nbsp;&nbsp;ORDERNUMBER='#FORM.ORDERNUMBER#' AND QUANTITY='#FORM.QUANTITY#'<br>&lt;/cfquery&gt;<br><br><br> <br> <br>&lt;CFQUERY name=&quot;prod&quot; datasource=&quot;wpslive&quot;&gt;<br>SELECT PRODUCT_ID, ORDERNUMBER&nbsp;&nbsp;AS REDUNDANTITEM<br>FROM orders<br>WHERE&nbsp;&nbsp;ORDERNUMBER='#session.cid#'<br>&lt;/cfquery&gt;<br><br>&lt;cfoutput query=&quot;prod&quot;&gt;<br>&lt;input type=&quot;hidden&quot; name=&quot;REDUNDANTITEM&quot; value=&quot;#prod.REDUNDANTITEM#&quot;&gt;<br>&lt;/cfoutput&gt;<br><br><br>&lt;CFIF prod.RECORDCOUNT gt 0&gt;<br><br>&lt;CFLOCATION URL =&quot;alreadyordered.cfm&quot; ADDTOKEN=&quot;no&quot;&gt;<br>&lt;/cfif&gt;<br><br>So, it iwill always be defined initially, because the user has inserted the information into the database. What I need is a way to check to see that if that ordernumber, product ID and quantity are already picked, I need this function to kick in so that it will tell them that they have already selected this item with the exact same amount for the quantity previosly.<br>

 

[iza]

what if you'd define ordernumber as a primary key for the order table ? and just catch the error when you have twice the same ordernumber to redirect the user to the alreadyordered.cfm ...<br>it sounds so obvious that i must have missed something in the question ....

 

[fusionaire]

I have a false number called &quot;Salenumber&quot; that has a value of &quot;autonumber&quot; in the database. The reason for this is because this is the way the system was set up when I got to it (and they don't want it changed) and for each individual product ordered, it would have the ordernumber entered into the database.(The ordernumber value is the sessionID).<br><br>So,Therein lies my problem. Thanks to all have tried to help me.

 

[iza]

ok - so how about having your test done BEFORE inserting the values into the database ???<br>something like doing the 'prod' query at first, and then, if the recordcount is not 0, inserting your datas, else, redirect the user to the alreadyordered.cfm page ...

 

[DarkMan]

&lt;CFQUERY name=&quot;prod&quot; datasource=&quot;wpslive&quot;&gt;<br>SELECT PRODUCT_ID, ORDERNUMBER AS REDUNDANTITEM<br>FROM orders<br>WHERE&nbsp;&nbsp;ORDERNUMBER='#session.cid#' and PRODUCT_ID=#FORM.PRODUCT_ID# AND QUANTITY='#FORM.QUANTITY#'<br>&lt;/cfquery&gt;<br><br>Wouldn't this be the correct query to catch the redundant item?&nbsp;&nbsp;Let me know if this works okay....

 

[fusionaire]

That sounds good, but I am at a loss as to how I would go about doing that...<br><br>I am working on it now...

 

[fusionaire]

Darkman~<br>I tried your code and it still takes me to my CFLOCATION page..<br><br>I do appreciate the help.

 

[iza]

&lt;CFQUERY name=&quot;prod&quot; datasource=&quot;wpslive&quot;&gt;<br>SELECT PRODUCT_ID, ORDERNUMBER&nbsp;&nbsp;AS REDUNDANTITEM<br>FROM orders<br>WHERE&nbsp;&nbsp;ORDERNUMBER='#session.cid#'<br>&lt;/cfquery&gt;<br><br>&lt;cfif prod.recordcount gt 0&gt;<br>&nbsp;&nbsp;&lt;!---- means you've already have it inserted ! ---&gt;<br>&nbsp;&nbsp;&lt;CFLOCATION URL =&quot;alreadyordered.cfm&quot; ADDTOKEN=&quot;no&quot;&gt;<br>&lt;cfelse&gt;<br>&nbsp;&nbsp;&lt;!--- it's the 1st time you got that productid AND oredernb ---&gt;<br>&nbsp;&nbsp;&lt;cfinsert .....&gt;<br>&nbsp;&nbsp;&lt;cfquery name=&quot;detail&quot; ...&gt;&lt;/cfquery&gt;<br>&lt;/cfif&gt;<br><br>and u're done )<br>tell me if it works fine !!<br>&nbsp;<br>

 

[fusionaire]

Iza..<br>I tried your code and it works for the particular item in question, but I am now getting errors elsewhere, but I think I can knock those out, thanks again.

 

[DarkMan]

Wait a minute.....&nbsp;&nbsp;Try changing your &lt;CFIF&gt; tag to this:<br><br>&lt;CFIF prod.RECORDCOUNT gt 1&gt;<br><br>(You already inserted the order, so you need to see if it exists twice....)<br><br>Or, it looks like iza has it correct, if she's saying check for the duplicate BEFORE the product hits the database and only insert if it doesn't already exist.&nbsp;&nbsp;This will save you from the trouble of deleting in the &quot;alreadyordered.cfm&quot; page...<br><br>DM

 

[iza]

) yesss ! sounds like you're not lazy enough guys ! you want to work MORE than required (mmmmm the pleasure of inserting - testing - and then deleting )<br>&nbsp;[sorry for that totaly NOT helpful tip !!]

 

[fusionaire]

Well thanks to both DarkMan and Iza for helping me with this issue, but now, when I enter the fourth item, it also displays my previous item that I selected as well...<br><br>I will keep on hammering...

 

[fusionaire]

Well, now I am totally befuddled, because now on certain products, I can see both items of the exact same value...(sigh) Oh-well, thank god it's friday...

 

[iza]

is it the 'detail' query that retrieves (the 4th item or the items with the same values) ??? if not, could you show the code where it happens ???

 

[fusionaire]

Now I have an issue with Quantity, any time any product has the same quantity amount as another, they will all be displayed...

 

[fusionaire]

Sorry about the earlier input about the fourth item..<br>it is the quantity value that is messing me up!

 

[iza]

? do you mean that the &lt;cfinsert ...&gt; always inserts the SAME value for the quantity ? did you check the form.quantity value before inserting it ? <br>

 

[fusionaire]

Here is the code from the beginning of the page until it hits the HTML:<br><br>&lt;CFQUERY name=&quot;prod&quot; datasource=&quot;wpslive&quot;&gt;<br>SELECT PRODUCT_ID, ORDERNUMBER&nbsp;&nbsp;AS REDUNDANTITEM<br>FROM orders<br>WHERE&nbsp;&nbsp;ORDERNUMBER='#session.cid#'&nbsp;&nbsp;<br>&lt;/cfquery&gt;<br><br>&lt;cfif prod.recordcount gt 0&gt;<br>&nbsp;&nbsp;&lt;!---- means you've already have it inserted ! ---&gt;<br>&nbsp;&nbsp;&lt;CFLOCATION URL =&quot;alreadyordered.cfm&quot; ADDTOKEN=&quot;no&quot;&gt;<br>&lt;cfelse&gt;<br>&nbsp;&nbsp;&lt;!--- it's the 1st time you got that productid AND oredernb ---&gt;<br>&nbsp;&nbsp;&lt;cfinsert datasource=&quot;wpslive&quot;<br> tablename=&quot;orders&quot;<br> formfields=&quot;ORDERNUMBER, PRODUCT_ID, MFG, PRICE, QUANTITY, DESCRIPTION&quot;&gt;<br><br><br>&lt;CFQUERY name=&quot;detail&quot; datasource=&quot;wpslive&quot;&gt;<br>SELECT PRICE, QUANTITY, PRODUCT_ID, MFG, DESCRIPTION, ORDERNUMBER, (price * quantity) AS TOTALPRICE<br>FROM orders<br>WHERE&nbsp;&nbsp;ORDERNUMBER='#FORM.ORDERNUMBER#' <br>&lt;/cfquery&gt;<br>&lt;/cfif&gt;