ORDER BY price, DESC, etc.. Really stuck

Submitted by Paul1107 on Wed, 2008-12-10 21:57 in Price Tapestry

Hi David,

Hardly a day passes where I ask for your help; if you can enlighten me I would again be indebted. 2 questions related

1. I am wanting to display product by price in descending or ascending order, I know how to do this in a normal sql query, but haven't a clue where to put to implement in PT.
Again, If I wanted to make a drop down menu for arguments sake, and wanted to show items less than £100, between £100-£200, over £200 and in Price Descending order again know how to do that

<?php
 $sql = "SELECT * FROM $tbl_name WHERE price BETWEEN 100 AND 200 ORDER BY price DESC LIMIT $start, $limit";
?>

but again don't know where to put it and for several different pages, as ordinarily I would have the code at the top of page

Can you help again please

Any plans on a tutorial of sorts?

thanks once again

Paul

Hi Paul, Price Tapestry's

Submitted by support on Thu, 2008-12-11 09:54

Hi Paul,

Price Tapestry's main search page is already set-up to sort by price (ascending or descending), relevance or rating (if you have the reviews feature enabled). On a normal search, you will see links to change the sort order at the top on the right hand side. Now, I know you are using searchExternal.php to show search results on your WordPress site - it would be straight forward to change the sort order of the results shown in the same way. If that is what you're looking to do, edit searchExternal.php and change the following on code on line 54:

$sort = "relevance";

to:

$sort = (isset($_GET["sort"])?$_GET["sort"]:"relevance");

Then, in your calling code (within your WordPress page), where you currently have:

$_GET["q"] = "Some Query";

...you can add the sort parameter, like this:

  $_GET["q"] = "Some Query";

  $_GET["sort"] = "priceDesc";

(you can see the sort values supported by the searchExternal.php starting at line 60)

It is straight forward to add a price restriction to the code also as I already have a min/max price mod that several users have implemented, so if you want this, let me know and I'll merge the changes into your searchExternal.php!

Regarding creating drop-down menus etc.; your SQL is essentially correct - you can see the fields required either by looking at the SQL created by search.php - or studying the table structures in setup.sql. With regards where to add the code; you can really add it anywhere you want within the Price Tapestry code, as long as the common includes have already been require()'d in, and you make sure that you are generating output after the HTML header has been sent.

If you wanted to use Price Tapestry's database library to do this, an example based on the SQL in your post might be:

<?php
  $sql = "SELECT * FROM products WHERE price BETWEEN 100 AND 200 ORDER BY price DESC LIMIT $start, $limit";
  if (database_querySelect($sql,$rows))
  {
    print "<select>";
    foreach($rows as $row)
    {
      print "<option value='".htmlentities($row["name"])."'>".$row["name"]."</option>";
    }
    print "</select>";
  }
?>

Hope this helps!

Cheers,
David.

Hi david, First bit no

Submitted by Paul1107 on Thu, 2008-12-11 10:30

Hi david,

First bit no problem, works great!

Price restrictions ie under £100, between £100-200 and above £200 would be great. I was hoping that it could be done perhaps as a parameter with in th ecalling code or something, but presumably not?

Its the drop down menus where you've lost me I' afraid. apart from studdying the structure of the code, I can try to do that which may take me a little time, and I guess I would be back on to you after something has gone wrong?

Also, whemn you say put the code anywhere, do you mean on the on the displaying page? or in search? this is where I get lost

really sorry about this and my immense thickness.

regards

Paul

Hi Paul, No probs - thanks

Submitted by support on Thu, 2008-12-11 12:35

Hi Paul,

No probs - thanks for your email - i've added the min/max price mods to your searchExternal.php and emailed it back to you. To use this, modify the calling code as per above as follows:

  $_GET["q"] = "Some Query";

  $_GET["sort"] = "priceDesc";

  $_GET["minPrice"] = "100";

  $_GET["maxPrice"] = "200";

I also included the modified html/navigation.php, but it just occurred to me that you would not need this as the min/max price parameters are being set in the calling code so they don't need to come from the URL.

Regarding writing your own code using the SQL, sure - let me know if you have any problems. With regards where to incorporate the code, it would have to be (as it stands) within one of the Price Tapestry scripts (e.g. search.php, products.php (main product page) etc.), and within these files you could either add your code anywhere after the following line:

require("html/header.php");

...or anywhere within the included /html/ files, e.g. html/prices.php. The only thing to be careful of is that you do not overwrite any variable names used by the scripts, e.g $product which is used in many places, normally as part of a foreach() loop.

Hope this helps!

Cheers,
David.