help with a mySQLi recordset

3/02/2015 11:10 am | #1 Christopher WestCommunity Expert

Hello there, I have a MySQLi recordset that takes in 2 URL parameters:

php:

?Department=&Category=

And I have a dropdown menu, so for example the Menu item 'Living' would be department and it would contain many categories. So that all works fine...however lets say on my homepage I have a info-graphic link for 'Living' so if the user clicks on that it will take them to the page that will display ALL categories items. How can i do this?

The example URL would be:

php:

category.php?Department=Living&Category=Occasional-Chairs

But lets say I have an infor-graphic link that directs to:

php:

category.php?Department=Living

My recordset is:

php:

<?php

if(true){

    

    switch(isset($_GET['Department'])?$_GET['Department']:""){

        

        case "New-Arrivals":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products WHERE ProductNewArrival = 1");

            $rsProducts->execute();

        break;

        

        case "Best-Sellers":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products WHERE ProductBestSeller = 1");

            $rsProducts->execute();

        break;

        

        case "InStore-Now":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products WHERE ProductInStoreNow = 1");

            $rsProducts->execute();

        break;

        

        case "Search":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products");

            

            if (isset($_GET["Price"])) {

                $prices = explode(":",$_GET["Price"]);

                $rsProducts->addFilter("ProductPrice",">=","d",$prices[0]);

                $rsProducts->addFilter("ProductPrice","<=","d",$prices[1]);

            }

            

            $productTypes = array();

            

                if (isset($_GET["Wood"])) {

                    $productTypes[] = "Wood";

                }

                if (isset($_GET["Metal"])) {

                    $productTypes[] = "Metal";

                }

                if (isset($_GET["Glass"])) {

                    $productTypes[] = "Glass";

                }

                if (sizeof($productTypes) > 0) $rsProducts->addFilter("ProductType","=","s",$productTypes);

                if (isset($_GET["Product"])) {

                    $productName = $_GET["Product"];

                    $splitName = explode(" ",$productName);

                    for ($x=0; $x<sizeof($splitName); $x++) {

                        $splitName[$x] = "%" . $splitName[$x]."%";

                    }

                    $rsProducts->addFilter("ProductKeywords","LIKE","s",$splitName);

                }

            $rsProducts->setFilter();

            $rsProducts->execute();

        break;

            

        default:

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT products.*, categories.* FROM products INNER JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE categories.CategoryLink = ? AND categories.CategoryLocation = ?");

            $rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"")  ."", "-1"); //WAQB_Param1

            $rsProducts->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"")  ."", "-1"); //WAQB_Param2

            $rsProducts->execute();

        break;

    }

}

?>

Regards

Chris

3/02/2015 11:39 am | #2 Jason ByrnesWebAssist

so this is working on the default recordset.

it sounds like you want to make the categories.CategoryLink part of the where clause:
WHERE categories.CategoryLink = WAQB_Param1 AND categories.CategoryLocation = WAQB_Param2

be optional

so change the where clause to:
WHERE (categories.CategoryLink = WAQB_Param1 OR -1 = WAQB_Param3) AND categories.CategoryLocation = WAQB_Param2

the create the variable as:
Name: WAQB_Param1
Type: Text
Default Value: -1
Runtime Value: $_GET['Category']

Name: WAQB_Param2
Type: Text
Default Value: -1
Runtime Value: $_GET['Department']

Name: WAQB_Param3
Type: Text
Default Value: -1
Runtime Value: $_GET['Category']

3/02/2015 11:54 am | #3 Christopher WestCommunity Expert

Hi Jason, Do you mean the recordset would be the below (currently it doesnt work)

php:

default:

$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

$rsProducts->setQuery("SELECT products.*, categories.* FROM products INNER JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE (categories.CategoryLink = ? OR -1 = ?) AND categories.CategoryLocation = ?");

$rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"")  ."", "-1"); //WAQB_Param1

$rsProducts->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"")  ."", "-1"); //WAQB_Param2

$rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"")  ."", "-1"); //WAQB_Param3

$rsProducts->execute();

break;

looking at the logic of the recordset...shouldnt this work:

php:

$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT products.*, categories.* FROM products INNER JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE ((categories.CategoryLocation = ? AND categories.CategoryLink = ?) OR categories.CategoryLocation = ?)");

            $rsProducts->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"")  ."", "-1"); //WAQB_Param1

            $rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"")  ."", "-1"); //WAQB_Param2

            $rsProducts->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"")  ."", "-1"); //WAQB_Param3

            $rsProducts->execute();

at the moment its returning ALL records for any of the categories in 'Living'

Chris

3/02/2015 12:11 pm | #4 Jason ByrnesWebAssist

the parameters are reversed.

try:

default:

$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

$rsProducts->setQuery("SELECT products.*, categories.* FROM products INNER JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE (categories.CategoryLink = ? OR -1 = ?) AND categories.CategoryLocation = ?");

$rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"")  ."", "-1"); //WAQB_Param1

$rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"")  ."", "-1"); //WAQB_Param2

$rsProducts->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"")  ."", "-1"); //WAQB_Param3

$rsProducts->execute();

break;

3/02/2015 12:14 pm | #5 Christopher WestCommunity Expert

Well thats solved it! Not sure if you saw my second edit of my post before you submitted yours. I changed around a few parameters that logicall would make more sense as 'department' should go first as it contains many 'categories' so I had to them reorder the parameters 1, 2, and 3. But of course that didnt work.

To be honest I was a little puzzled by your code "categories.CategoryLink = ? OR -1 = ?" I didnt think you could put -1 for the URL parameters markup.

Chris

3/02/2015 12:20 pm | #6 Jason ByrnesWebAssist

To be honest I was a little puzzled by your code "categories.CategoryLink = ? OR -1 = ?" I didnt think you could put -1 for the URL parameters markup.

you wouldn't put -1 in the URL variable.

what this is doing is:

if the URL is:
category.php?Category=

the where clause becomes:

categories.CategoryLink = -1 OR -1 = -1

so: OR -1 = -1 evaluates to true

but if you pass a value for the category:
category.php?Category=Occasional-Chairs

then the comparison is:
categories.CategoryLink = Occasional-Chairs OR -1 = Occasional-Chairs

hopefully that clarifies.

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

help with a mySQLi recordset

3/02/2015 11:10 am | #1 Christopher WestCommunity Expert