Need help writing a link with a php parameter to pull all records.

I need help writing a link that will pull all records from a recordset. The link is in the main navigation and it's linking to a products page where I would like to return all the items in descending order. The recordset that currently resides on the products page already has two INNER JOINS and a conditional WHERE statement with an OR operator, but I would like to continue to utilize only one recordset for this page to pull records given any condition. The mySQL looks like this.

SELECT *
FROM product INNER JOIN category ON idcategory_product=id_category INNER JOIN collection ON idcollection_product=id_collection
WHERE idcategory_product=colname OR idcollection_product=colname2
ORDER BY id_product DESC

Variables:

Name: colname
Type: numeric
Default Value: -1
Runtime Value: $_GET['id_category']

Name: colname2
Type: numeric
Default Value: -1
Runtime Value: $_GET['id_collection']

Should I add another condition to the WHERE statement for the id_product, and how should the link for the products page, <a href="products.php?..."></a>, be written?

I'm not quite sure what you are trying to achieve.

Looking at the SELECT statement, you are filtering by product category, so I assume you want to show all items within that category when the menu link is clicked.

Assuming your recordset returns all items without any parameters, then you need only to filter by product category.

the link in this case would be something like...

<a href="products.php?id_category_product=1"></a> where the number in red is the product id url parameter value

That is the mySQL for the recordset that I'm currently using. I have bottom navigation that I already have setup to pull the id of both the category and collections table to return the items with the foreign keys to match. What I was asking was how to use this recordset to return "all" items in the products table in DESC order by the id_product regardless of what collection or category they are from. A way for the user to select the products link from the main navigation which would then return all items to the products page allowing them to look through the entire catalog. Sorry about not being clearer in my initial post.

There might be a couple of ways you can do this. You can either selectively include or exclude the where clause that is filtering the recordset, or you can modify it with a statement that will return all rows if there are no matches for the first two statements in the where clause.

If you have an auto incrementing id column for the table you could add to the where clause to say

WHERE idcategory_product=colname OR idcollection_product=colname2 OR product.id > 0

If there were no matches for the first two then this one should allow for all records where the product id is greater than 0.

If you wanted to include or exclude the where clause you could do so by assigning the where clause to a variable, then based on weather you want to see all the results you can set this variable to be blank.

That was exactly what I was looking for. I chose to add to the WHERE statement using the OR id_product > 0, everything is working wonderfully. I really appreciate what everyone in the Web Assist community does to help each other out. Thanks to all who took the time to read this post and attempted to help me out. I'm sure you'll hear from me again.

I thought it was working fine, but have noticed that when clicking a category or collection in will pull all items of the products table instead of only the items that meet one of the two conditions within the WHERE statement. How would I go about fixing the OR id_product > 0 or how would I go about correctly assigning the where clause to a variable?

I have looked over the query again and the results you are getting make sense since no matter what the values are the last condition looking for the id being greater than 0 will always be true.

You should be able to update this so that the last condition will only be true if the first two are not and get the result you are looking for. I think the where clause will need to look like this:

WHERE (idcategory_product=colname) OR (idcollection_product=colname2) OR (idcategory_product <> colname AND idcollection_product <> colname2 AND product.id > 0)

Let me know if you have any problems with this and we can do some php to hold the where clause in a variable. In my testing this returned the results I was looking for.

The query is still returning the same results no matter what. How would I go about assigning the WHERE clause to a variable?

The first thing you will need to do is copy the text in the query for the current WHERE clause. Next make use the set session value server behavior to set a session variable. For the trigger will need to make it based on both $_GET['id_category'] and $_GET['id_collection'] not being empty, it might look like this:

if(  (isset($_GET['id_category']) && $_GET['id_category'] != "") || (isset($_GET['id_collection']) && $_GET['id_collection'] != "") ){

Give your variable a name that is meaningful, then for the value set it to the copied text of your WHERE clause. Next you will want to add in an else to the set session value like this:

if(!session_id()) session_start();
if(  (isset($_GET['id_category']) && $_GET['id_category'] != "") || (isset($_GET['id_collection']) && $_GET['id_collection'] != "") ){
 $_SESSION['your variable name'] = "your where clause text";
} else {
 $_SESSION['your variable name'] = "";
}

The final part is to replace the where clause in the rs with this session variable like this:

"SELECT *
FROM product INNER JOIN category ON idcategory_product=id_category INNER JOIN collection ON idcollection_product=id_collection" . $_SESSION['your variable name'] . 
"ORDER BY id_product DESC"

This should make it so you have no where clause if the values are not present. Please give this a try and post back with any questions that you have.

I created a session variable called productVars then tried using the set session value server behavior that can be found with the eCart extension to the trigger you left in your last post. I attempted applying the trigger manually through the set session value panel, but was having trouble. How would you go about setting the session trigger for something as complex as the line that you provided me using the eCart set session value panel? I finally resorted to copying and pasting your trigger into the field, selected my session name, and then clicked ok. Once, that was done I applied my WHERE clause to the value of the session variable, added the else statement, and then altered my recordset to reflect the session variable. Now, the page responds by throwing me a syntax error. I will attach my page code along with my sql database if you would please take a look at.