close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

mySQli AND OR bug errors?

Thread began 3/09/2015 1:28 pm by Warrio | Last modified 3/12/2015 2:20 pm by Jason Byrnes | 2857 views | 11 replies |

Warrio

mySQli AND OR bug errors?

I have an SQL statement that works with parameters, I'm trying to add to the statement with fields that reference one of the earlier parameters which fails with "Unknown Column"

The same statement worked in the old version mySQL

See attachment

Cheers

Sign in to reply to this post

Jason ByrnesWebAssist

The problem is that you are reusing the paramVoucherID parameter.

a defined parameter can only be used once in the where clause. if you need to use the same perimeter settings more than once, you need to create a separate perimeter with those settings for each instance.

This is true for a standard DW recordset also.

Sign in to reply to this post

Warrio

Thanks Jason

But not true of the standard DW, the code below works perfectly well on the old page and brings up no errors.

I tried creating paramVoucherID1 paramVoucherID2 and so on for the parameter that is required in a number of places, however it still produces the same error

Cheers
Norman

Old Working Code

SELECT view_coupons.Discount, view_coupons.VoucherCode
FROM view_coupons
WHERE ((view_coupons.catItemId=paramItemID AND (view_coupons.VoucherCode=paramVoucherID) ) OR ((view_coupons.catItemId= paramGrpID)) AND ((view_coupons.catalogueID=16) AND (view_coupons.VoucherCode=paramVoucherID)) OR ((view_coupons.catItemId= paramBrandID)) AND ((view_coupons.catalogueID=16) AND (view_coupons.VoucherCode=paramVoucherID))OR((view_coupons.VoucherCode=paramVoucherID) AND view_coupons.VoucherTypeID=4))

Sign in to reply to this post

Jason ByrnesWebAssist

I'm my experience, Dreamweaver does not allow you to reuse parameter names, but i'm not going to argue the point.

please send a copy of the page with the MySQLi recordset on it that is causing the error and a dump of the table structure so that I can recreate the issue on my end to troubleshot.

Sign in to reply to this post

Warrio

Thanks for the quick reply Jason

Attached are the files

Cheers

Attached Files
catalogue.sql
item.php
Sign in to reply to this post

Jason ByrnesWebAssist

1) the page you sent has a number of recordset on it, which one is the problem recordset?

2) The SQL Dump you sent does not include the create table statement, so I do not have the table structure.

Sign in to reply to this post

Warrio

Sorry Jason,

I was away with the fairies the other day, up far too early.

rsCoupon is the one I'm referencing, it's a view of two tables vouchers and voucher types

There are two tables and a view attached

Cheers
Norman

Attached Files
SQL1View.sql
SQL1vouchers.sql
SQL1vouchertype.sql
Sign in to reply to this post

Jason ByrnesWebAssist

but on the page which is the recordset that is giving the error so I now what to look at to reproduce.

Sign in to reply to this post

Warrio

Hi Jason

rsCoupon is the is the name of recordset, it's data is sourced from view_coupons.

The purpose is to return from the coupon value entered on the page, a discount for either a single item, items from categories or items from brands or a global discount.

Cheers
Norman

Sign in to reply to this post

Jason ByrnesWebAssist

I'm not able to reproduce with the page you sent.

From what i see in the view, it only uses the vouchers table:

CREATE
DEFINER = 'root'@'127.0.0.1'
VIEW catalogues.view_coupons
AS
SELECT
`vouchers`.`VoucherID` AS `VoucherID`,
`vouchers`.`VoucherCode` AS `VoucherCode`,
`vouchers`.`Description` AS `Description`,
`vouchers`.`StartDate` AS `StartDate`,
`vouchers`.`EndDate` AS `EndDate`,
`vouchers`.`Discount` AS `Discount`,
`vouchers`.`VoucherTypeID` AS `VoucherTypeID`,
`vouchers`.`catalogueID` AS `catalogueID`,
`vouchers`.`catItemId` AS `catItemId`
FROM `vouchers`
WHERE (((TO_DAYS(CURDATE()) - TO_DAYS(`vouchers`.`StartDate`)) >= 0)
AND ((TO_DAYS(CURDATE()) - TO_DAYS(`vouchers`.`EndDate`)) <= 0));


I need more specifics on how to reproduce the error in order to troubleshoot.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...