Category Titles

[denise234045]

Sorry for the multiple posts -

I would like to have each Category name posted at the top of each of the product results pages, instead of just the static "Product List". I'm going to have quite a few products and would really like it to be as user friendly as possible. I don't want folks to get lost and not know what section they're in...

Thanks ahead.

[Eric Mittman]

The category results page is just a results page for a search and does not actually change for the various products. Are you talking about displaying the category for a product on the product detail page? If so you will need to get the category name for the product you are working with. If you add in an advanced recordset with this query you should get the category for the current item:

SELECT products.ProductName AS 'name', productcategories.CategoryName AS 'category'
From products, productcategories
WHERE products.ProductCategoryID = productcategories.CategoryID AND Products.ProductID =

For the ProductID portion you will need to specify your own SQL parameter and set it's value to the form value for ProductID

[denise234045]

I don't want the product id's listed at the top of the page - just the category id's. - so that when a user clicks on a category - the product results page has the name of the category at the top of the page.


So - instead of a page just saying "Product List" - I would like it to say "Pencil Sharpeners," which is one of my product categories. If the query produces no results, then the default is fine....

Sorry - I really am only familiar with php... and don't know what I'm doing.

Thanks for your time.

[Eric Mittman]

Ok, that clears it up a bit. To get the name of the category to show when a particular category is chosen you will need to do the following on the product results page:

First you will need to create a new recordset and call it categories. In the simple recordset view you will select all columns from the productcategories table. You will need to filter the recordset on the CategoryID = to the URL parameter CategoryID.

Next you will need to locate the display of the Product List text it should look like this:


Product List<br />


You will need to update it to be like this:


<?php if ($totalRows_categories > 0) { // Show if recordset not empty ?><?php echo $row_categories['CategoryName']; ?><br/>
<?php }else{ ?>Product List<br />
<?php } ?>


I think this will get you the result you are looking for. I'm including a screen shot of the categories recordset screen as a reference.

[denise234045]

Thanks Eric - this is leading me to another problem, which I guess I can no longer ignore....My site is connected to the database fine and its working well - but the recordset through dreamweaver is showing as empty, so something is not set up properly within dw... in the MySQL Connection pop-up I have

Connection Name:localhost
MySQL server:localhost

Usrnm, pw and db name are correct, but I'm getting a 404 error code saying that possible reasons include that there is no db running on the server - or the testing server specified for this site does not map to the http:..... URL. Please verify the URL Prefix maps to the root of the site.

Yet - the site is working fine.....

I guess I could change the recordset using phpmyadmin - but I think I need to figure this out as I move forward.

Thanks again for helping me work out the kinks in my knowledge- the site is turning out very nicely indeed.

[Eric Mittman]

This type of 404 error can occur sometimes if the connection in DW is corrupted or not working correctly. One thing that you could try to do while DW is closed is to open the site root and find the _mmServerScripts folder. Copy this folder to a different location on your hard drive and delete the copy that is in your site folder, then open DW and attempt to interact with the recordset. There are some recordsets within the PowerStore that are there by default that you will not be able to edit due to their complexity, but you should be able to edit any recordset that you have created. If you still have problems with it you may need to delete the recordset and start over again with it.

[denise234045]

I'm afraid I'm doing something terribly wrong. I did as instructed, to no avail.

I can't connect to the database with dreamweaver at all - so its not allowing me to create a recordset?? I cannot select a table, no columns are found, etc. and I'm not allowed to edit of course....

I know I'm being awfully thick on this - I'm probably overlooking something simple, but have no idea what that might be.

[Eric Mittman]

That seems odd that DW is not allowing you to connect to the db. As another test you can try creating a new site and define it the same as your other site but with different locations for the files. In this new site create a new page and save it then attempt to create a connection to the db.

This test should let you know if the issue is one with the site you are working in or a larger issue with Dreamweaver.

[denise234045]

I wasn't able to connect doing as you suggested either. But come to think of it - I had purchased WA mySQL tutorial and wasn't able to make a connection with Query Browser either - I posted problem, and in the end got a response of "I can't help you on that - never heard of that"... or something along those lines, so I put it aside. Could it be something to do with my computer?? Or should I contact the host???

I'll probably research all of the above - but if you have any suggestions....

[Eric Mittman]

In general if you are having problems making a connection more than one environment it would indicate an issue with the connection details for the server itself. When you attempt to connect with a GUI tool other than in Dreamweaver what is the specific error message you get?

Is the connection a local one or to your hosted db? It is very common for host to restrict access to the db remotely. If this is the case I think you best bet would be to have a local MySQL server running. Please post back with what you discover and we will gladly help to guide you in the rite direction.

[iggymak198560]

Ok, that clears it up a bit. To get the name of the category to show when a particular category is chosen you will need to do the following on the product results page:

First you will need to create a new recordset and call it categories. In the simple recordset view you will select all columns from the productcategories table. You will need to filter the recordset on the CategoryID = to the URL parameter CategoryID.

Next you will need to locate the display of the Product List text it should look like this:


Product List<br />


You will need to update it to be like this:


<?php if ($totalRows_categories > 0) { // Show if recordset not empty ?><?php echo $row_categories['CategoryName']; ?><br/>
<?php }else{ ?>Product List<br />
<?php } ?>


I think this will get you the result you are looking for. I'm including a screen shot of the categories recordset screen as a reference.

Hi Eric,

I'd very much like to do this too, but I don't see where to create this new recordset... is it in DW or in the database through myPHPAdmin?

Actually I'm not sure if my DW is seeing the database, as under the database tab, the stored procedures, tables and views all say none for localhost...?

[Eric Mittman]

This post is a little older and seems a bit out of context. Please post back and describe your situation and what you would like to accomplish from the perspective of the user. It may be more simple than you are thinking or another approach may need to be taken.

[iggymak198560]

Hi Eric,

Well, it's pretty much exactly like Denise's situation above, we'd like the header on the product results page (from search or clicking on a category in the menu) to display the Category name, such as "Dr. Harvey's Pet Treats" (as opposed to her "Pencil Sharpeners") rather than the generic "Product List" that it shows as standard. That is our goal in this exercise, well mine, as I haven't told the client, but he'll dig it.

I though your answer for her would do it, and in fact I know it will, and I see where to change the line of PHP code you wrote (Dani's working on some lines farther down this page as well)...

but...

I don't know where to make the new recordset you said was necessary. That's what prompted me to post. Is it a Dreamweaver function? It looks rather easy to do, but, where?

[Eric Mittman]

You can add in a recordset in DW by going to either Server Behaviors or Bindings and choosing 'Recordset' from the top of the list. You will need to select the table and columns then apply the filtering.

If you have any troubles with this please let me know and I will help you with whatever I can.

[iggymak198560]

Hi Eric,

Thank you, I found that, but when I go to create it, I get " ***no table found " in the box.

Therefore I don't think DW was (or is) ever really seeing the database...

I uploaded the site, we're waiting for DNS...

I wonder what I should do!

I imagine I could just export / import this new recordset to the database... (this is my first dynamic site, so... noob city)

- thanks, Iggy

I see that it doesn't like where the test server is mapping... since we now have FTP access, should I change the test server settings to match the live server? Or take the test server out?

[Eric Mittman]

In order for your database to connect in DW you will need to have the testing server portion of the site setup with the details for the server you will be testing on that has the database. If your hosts does not allow for remote connections to the database then you must setup and test it locally, then migrate it to the live server when you are ready.

Here are links to setting up a local testing server and defining a site in DW for reference:

setup local testing server
http://www.webassist.com/support/documentation/how-tos/xampp_for_windows.php

define a dynamic site in DW
http://www.webassist.com/support/documentation/how-tos/dynamic_site_definitions.php

[iggymak198560]

Thanks Eric,

I'll let you know how it goes!

Iggy

[iggymak198560]

...and I've tried so many different settings for the mySQL Connection dialog box. I even set the test server to the FTP, wherein it uploaded a couple of pages, but still nothing. Sigh.

[Eric Mittman]

Please review for us the steps you are taking and the results you are getting after each, I imagine that you are either just missing something or one of the steps is not being done correctly.

Do you know if you were successful in defining the testing server? Can you browse to any pages that are in the directory you specified and see them properly?

[iggymak198560]

Hi Eric,

Stepped away from it for a bit to clear my head. I have gotten DW to connect to the database. Basically, it was an embarrassing series of faux-pas in choosing the correct settings for the mySQL connection... sigh... what can I say?

Anyway now that that was sorted I immediately filled out the Recordset box as you showed above... hit ok, and realized it applied it to the first open page which was Globals! So, undoing that...

Closed everything and opened Product Results. Left the cursor at line 1 start.

Did the same and it inserted the following on it's own at line 113...

<?php
$currentPage = $_SERVER["PHP_SELF"];

$colname_rsCategories = "-1";
if (isset($_GET['CategoryID'])) {
$colname_rsCategories = $_GET['CategoryID'];
}
mysql_select_db($database_localhost, $localhost);
$query_rsCategories = sprintf("SELECT * FROM productcategories WHERE CategoryID = %s", GetSQLValueString($colname_rsCategories, "int"));
$rsCategories = mysql_query($query_rsCategories, $localhost) or die(mysql_error());
$row_rsCategories = mysql_fetch_assoc($rsCategories);
$totalRows_rsCategories = mysql_num_rows($rsCategories);
?>

So then I copied your line(s) for the text display an put them in.

Uploaded the page and it returned this:

Fatal error: Call to undefined function getsqlvaluestring() in /home8/prefert5/public_html/Products_Results.php on line 121

which is this bit...

$query_rsCategories = sprintf("SELECT * FROM productcategories WHERE CategoryID = %s", GetSQLValueString($colname_rsCategories, "int"));

So, as you might assume I've no idea what's wrong...

I took out the above code chunk... but left in the text display code, and the page works again, showing the default of what I had typed in instead of 'Product List.'

What do you make of that?

Hope your weekend was cool,

Iggy

[Eric Mittman]

This GetSQLValueString should be defined by the DW recordset code. It is not something that you should need to add manually. Here is the code for this function, it needs to be above the code for the recordset:


<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
?>

[iggymak198560]

Ah, now that's interesting. All it added was what was in my post, however the chunk of code it added when I had globals active was much more, but I didn't really look at it.

I will try it again, and if it doesn't add that I will put in what you wrote.

Thanks, Eric I'll keep ya posted!

(I wonder if it's because it's DW CS3...)

[iggymak198560]

well it looks like it put it in properly... (not that I'd really be able to catch an error...)

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
?>
<?php
$colname_rscategories = "-1";
if (isset($_GET['CategoryID'])) {
$colname_rscategories = $_GET['CategoryID'];
}
mysql_select_db($database_localhost, $localhost);
$query_rscategories = sprintf("SELECT * FROM productcategories WHERE CategoryID = %s", GetSQLValueString($colname_rscategories, "int"));
$rscategories = mysql_query($query_rscategories, $localhost) or die(mysql_error());
$row_rscategories = mysql_fetch_assoc($rscategories);
$totalRows_rscategories = mysql_num_rows($rscategories);

//WA Database Search Include
require_once("WADbSearch/HelperPHP.php");
?>


and here's the display command...

<h1 class="larger"><?php if ($totalRows_categories > 0) { // Show if recordset not empty ?><?php echo $row_categories['CategoryName']; ?><br/>
<?php }else{ ?>Products In This Category</h1><br />
<?php } ?>

Shouldn't that then work? It doesn't, though.

http://preferredpet.com

Do I need to upload anything besides the page? Wouldn't think so...

Can you see anything strange?

[iggymak198560]

What a doof I am... I had copied your line exactly... it didn't work because I named my recordset differently...Doh!

So here it is in all it's glory:

<?php if ($totalRows_rscategories > 0) { // Show if recordset not empty ?><h1><?php echo $row_rscategories['CategoryName']; ?></h1>
<?php }else{ ?><h1>Products In This Category</h1><br />
<?php } ?>

Yay! Thanks, Eric!

[Eric Mittman]

Nice, so it is working as it should now? Let us know if you have any other questions or if you encounter any problems.

[iggymak198560]

Yes, it's working perfectly! Things are pretty well settled now I think, but I'll be back, I'm sure.

Someone registered and said the email link returned a black page, but we've no details. Trying to get her to forward it to us.

And in a bit I need to put the CMS in, so that might have a q or two.

Meanwhile...

Be well and thank you much!

Iggy

[Eric Mittman]

As a test for the user verification you can just register one of your own email addresses. The page that is used should be the same for everyone so this might be a good way to test the link out.

[iggymak198560]

Eric, thank you... sometimes when one stares too deeply at something... such things just whiz on by! Ha! You rock...
Iggy

[iggymak198560]

Just did... and it all worked perfectly for me... even the redirect to https, so I have no idea what happened to that lady.

Pretty sure it'll turn out to be nothing, especially now.