close ad
WARNING PC USERS: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

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

Thread began 2/06/2010 7:20 pm by blackweldermhe400113 | Last modified 2/12/2010 8:13 pm by Eric Mittman | 2924 views | 16 replies |

blackweldermhe400113

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?

Sign in to reply to this post

CraigRBeta Tester

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

Sign in to reply to this post

blackweldermhe400113

Pulling all items by the id_product...

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.

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

blackweldermhe400113

Thank you!

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.

Sign in to reply to this post

blackweldermhe400113

Still a problem...

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?

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

blackweldermhe400113

Still having the same problem...

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

Sign in to reply to this post

Eric Mittman

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:

php:
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:

php:
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:

php:
"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.

Sign in to reply to this post

blackweldermhe400113

Not sure if I set it up correctly?

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.

Attached Files
JBstuff.zip
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...