close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Order multiple columns in recordset

Thread began 11/19/2012 9:57 am by bill3786 | Last modified 11/21/2012 4:28 pm by Ray Borduin | 1199 views | 18 replies |

bill3786

Order multiple columns in recordset

The clients site has been running sucessfully for 4 months now but they have now asked for the following two changes.

1. Results from the database are displayed on the products_Results page using the default setting of ProductID ascending.
The client would now like to display the products ordered by the product price descending, showing the highest priced products first.
On this site there are two columns for prices, one column for UK customers in sterling (ProductPrice) and another for prices in euros for all non-UK customers (ProductEuroPrice).
The display of prices is controlled by an "if else" statement which depends on the the users country of origin selected at registration. So all UK registered customers (if) see sterling prices and are shown an "add to cart" button and can buy online in sterling. All non-UK customers (else) see euro prices only but dont see an "add to cart" button and are instructed to email their order and subsequently pay over the phone.

To order the display of products for the sterling prices ONLY would be a simple process by adding "ORDER BY ProductPrice DESC" to the recordset.
So my question is, how do you apply the "ORDER BY (for both price columns) DESC" statement in the recordset that will order both the ProductPrice (sterling) and the ProductEuroPrice columns for both UK and non-UK customers.
Attached are the product_Results and product_Detail pages.
Will the solution that is applied to the Results page affect the Detail page?

2. The display of products on the site is contolled in the recordset by a ProductLive column so that products with a value of 1 are displayed and those with a value of 0 are hidden.

The client would now like the facility to choose to display some of the hidden products but not allow them to be purchased, maybe with an "Out of Stock" message displayed somwhere or displayed on clicking the "Add to Cart" button or disabling the "Add to Cart" button etc etc. The client is open to suggestions on how this can be achieved on the site.

There is no stock control facilty on the site nor does the client want to add stock control, they simply want to be able to show some of the hidden products but not allow them to be purchased.

Attached Files
products_Detail.zip
products_Results.zip
Sign in to reply to this post

Jason ByrnesWebAssist

1) use a comma separated list of the columns to include in the ORDER BY Clause:
"ORDER BY ProductPrice, ProductEuroPrice DESC"


2) Use an if statement around the add to cart button to show an Out of stock error message:

php:
<?php if($row_recordsetName['ProductLive'] != 0) { ?>

[add to cart button code]
<?php } else { ?>
This product is out of stock
<?php ?>
Sign in to reply to this post

bill3786

Ok, thanks.

So that I'm crystal clear on how point 2 will work please could you describe in words how the if else will work. Site is live so need to be sure I understand what will happen before adding the new code.

Sign in to reply to this post

Jason ByrnesWebAssist

$row_recordsetName['ProductLive'] is the binding for the ProductLive column of your recordset.

the if statement is checking that the value of the ProductLive column is not equal (!=) to 0.

if it is not equal to 0, it will show the add to cart button or else, it will show the error message.

Sign in to reply to this post

bill3786

Ok, having a bit of a senior moment with this.

This is how I'm interpretting this.

The hidden product wont be visible on the website unless or until the client gives it a ProductLive value of 1 and then the if statement will cause the "add to cart" to show for a product they only want to display but not allow a customer to purchase.

Sign in to reply to this post

Jason ByrnesWebAssist

the if statement would show the error message if the ProductLive column is 0 or the checkout button if it is not.

Sign in to reply to this post

bill3786

The client doesnt want to show all the product in the database which have a value of 0 for the ProductLive column, they want to be able to choose which ones to show but cant be purchased. There are a lot of products in the database still without suitable images, some long term out of stock products and others which they want to display allbeit with the "out of stock" message.

Am I correct in assuming the system you describe will show ALL the products in the database as "out of stock" which have a value of 0?

Sign in to reply to this post

Jason ByrnesWebAssist

yes, that is correct.

sounds like you need to create another column in the table to mark the items as outOfStock or not, and modify the if statement so that items where the outOfStock column equals 1 show the message and 0 shows the add to cart button

Sign in to reply to this post

bill3786

Yes I think so, what about this.

Create a new database column called something like NoStock which had a default value of 1
Then the recordset which currently has only a WHERE ProductLive=1 statement would also need to allow products to be displayed which had a ProductLive value of 0 but a NoStock value of 0

Then the client would change the NoStock value from 1 to 0 for only the products it wanted to be displayed with an "out of stock" message in place of the "Add to Cart" button by using the if statement you gave earlier for the button but incorporating the NoStock column instead of the ProductLive column.

So it would be

<?php if($row_recordsetName['NoStock'] != 0) { ?>
[add to cart button code]
<?php } else { ?>
This product is out of stock
<?php } ?>

Is this a working solution? If so how do I write the statements for the recordset starting with WHERE ProductLive =1

Sign in to reply to this post

Jason ByrnesWebAssist

yes, that sounds right:


  how do I write the statements for the recordset starting with WHERE ProductLive =1  



what do you mean? I dont think you would need to change the recordset at all.

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...