Show related products in Dreamweaver

[johndm1971]

hi all i'm pretty new to all this so forgive me! I'm doing a website for my brother that will sell guitar bodies and their relevant parts. I've setup a single mysql database table - one of the fields determines if the entry is a body or a part - there is also a field in which (if it's a part) the relevant body its linked to is entered. So, part54 maybe related to body1 for example. I've setup a page in DW CS5 that correctly shows bodies, i've then created a second recordset which i'm trying to filter by the current record in the first recordset. So, if the user is viewing item number 1 then any parts related to that product can be listed on the same page (and eventually clicked for more info). I'm really struggling to find out how to reference the current record being displayed in the first recordset from within the second. Please can you advise me? So, in English Recordset2 needs to show all records where the relateditemfield = the currently being displayed record. I hope i've given enough info here, please shout if not. Thanks in advance. John.

[Guest]

Do some research on MySQL JOIN or create first recordset and get value of row then in second recordset use WHERE argument to filter data WHERE table_column = row_first_recordset['value']

[johndm1971]

Thank you. I looked up JOIN and think that would be more suited if I was using two tables, but in this case all products are stored in one table.

Your second idea is the one I've been trying to work on. Would you mind giving me some guidance how it should look?

I've tried ...

SELECT *
FROM bodies
WHERE relateditems = row_first_recordset1['itemid']

but no luck. The itemid is showing on the page from recordset1, I need to use this value as the filter for recordset2.

Thanks so much.

John

[bregent]

You still need to use a join. Your table has a "fishhook" type relation with itself. That is, each row in the table references another row in the table. You need to create an alias to the table for the related products:

SELECT a.column1, a.column2, b.column1, b.column2, etc.
FROM bodies a, bodies b
WHERE a.product_id = b.relateditems

AND ....   (whatever other criteria you need)

[Guest]

There isn't a $ symbol indicating a variable in your query so it's trying to filter the value row_first_recordset1['itemid'] instead of the value of the variable $row_first_recordset1['itemid']

Use either of these methods. Note the $ character indicating a variable. The first example uses GetSQLValueString function to sanitize the variable against injection attacks. This is not needed since the value is assumed to be sanitary. I assume that value of row_first_recordset1['itemid'] is an integer. If it's text then you would replace "int" with "text" in the first example.

sprintf("SELECT * FROM bodies WHERE related_items = %s", GetSQLValueString($row_first_recordset1['itemid'], "int"));

You can also try this method which doesn't use sanitary functions. It's more vulnerable to attacks but you should be safe for the method for which you're using it.

"SELECT * FROM bodies WHERE related_items = $row_first_recordset1['itemid']";

[johndm1971]

mmm I've entered SELECT * FROM bodies WHERE relateditems = $row_first_recordset1['itemid']; and it says error 1064 there is an error in syntax - check for right syntax to use near ['itemis']

I'm entering this into the advanced section of Recordset2 - that is the correct place I guess? So, what can I be doing wrong?

Thanks

[Guest]

Your code doesn't match mine. There's missing quotations. Don't rely on DW's recordset wizard. Code it into code view manually for best results. Also ['itemid'] does not = ['itemis'] in your post. Either a typo in the forum post or a typo in the code you're entering into DW. Make sure the code matches exactly.

[johndm1971]

Ah OK thanks. I'm not good enough to do that I don't think. I'm unsure where to enter it directly into code. Best get reading up I guess. Thanks for trying. Mmmmm.

[johndm1971]

I'm back! Sorry if I'm annoying you, trying my best.

I've looked in code and think i've found the part i need to change, it now reads ...

mysql_select_db($database_ssdljl, $ssdljl);
$query_relatedprod = "SELECT * FROM bodies WHERE relateditems = $row_first_recordset1['itemid']";
$relatedprod = mysql_query($query_relatedprod, $ssdljl) or die(mysql_error());
$row_relatedprod = mysql_fetch_assoc($relatedprod);
$totalRows_relatedprod = mysql_num_rows($relatedprod);

But it's saying there is a syntax error on the second line?

[Guest]

$xxxxxx = $row_first_recordset1['itemid'];
mysql_select_db($database_ssdljl, $ssdljl);
$query_relatedprod = "SELECT *
FROM bodies
WHERE relateditems = $xxxxxx";
$relatedprod = mysql_query($query_relatedprod, $ssdljl) or die(mysql_error());
$row_relatedprod = mysql_fetch_assoc($relatedprod);
$totalRows_relatedprod = mysql_num_rows($relatedprod);

OR

mysql_select_db($database_ssdljl, $ssdljl);
$query_relatedprod = sprintf("SELECT *
FROM bodies
WHERE relateditems = %s",
GetValueString($row_first_recordset1['itemid'], "int"));
$relatedprod = mysql_query($query_relatedprod, $ssdljl) or die(mysql_error());
$row_relatedprod = mysql_fetch_assoc($relatedprod);
$totalRows_relatedprod = mysql_num_rows($relatedprod);

If neither of those work then provide the exact syntax error that is being displayed.

[johndm1971]

I'm sure it's me not you but when I try to view the page with the first set of code you suggested. ....

$xxxxxx = $row_first_recordset1['itemid'];

mysql_select_db($database_ssdljl, $ssdljl);
$query_relatedprod = "SELECT * FROM bodies WHERE relateditems = $xxxxxx";
$relatedprod = mysql_query($query_relatedprod, $ssdljl) or die(mysql_error());
$row_relatedprod = mysql_fetch_assoc($relatedprod);
$totalRows_relatedprod = mysql_num_rows($relatedprod);

It says ....

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[Guest]

Provide entire php code for page please.

[johndm1971]

OK - I think this is what you are after, I'm very grateful for you help.

Just one query in my mind, is your code looking only at the first record of Recordset1 and performing the filter of the second recordset on that value? That's fine for now but as the user clicks to view a different guitar body then the second recordset needs to re-filter based on the now currently displayed main record. Hope that makes sense. So, the second recordset always displays the related parts for the main guitar body currently being shown. Your code may well do that but I'm just checking. Thanks. So ....

<?php require_once('Connections/ssdljl.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$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;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_ssdljl, $ssdljl);
$query_Recordset1 = "SELECT * FROM bodies";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $ssdljl) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$xxxxxx = $row_first_recordset1['itemid'];

mysql_select_db($database_ssdljl, $ssdljl);
$query_relatedprod = "SELECT * FROM bodies WHERE relateditems = $xxxxxx";
$relatedprod = mysql_query($query_relatedprod, $ssdljl) or die(mysql_error());
$row_relatedprod = mysql_fetch_assoc($relatedprod);
$totalRows_relatedprod = mysql_num_rows($relatedprod);

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_Recordset1") == false &&
stristr($param, "totalRows_Recordset1") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>

[Guest]

<?php require_once('Connections/ssdljl.php');
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$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;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_ssdljl, $ssdljl);
$query_Recordset1 = "SELECT * FROM bodies";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $ssdljl) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$xxxxxx = $row_Recordset1['itemid'];

mysql_select_db($database_ssdljl, $ssdljl);
$query_relatedprod = "SELECT * FROM bodies WHERE relateditems = $xxxxxx";
$relatedprod = mysql_query($query_relatedprod, $ssdljl) or die(mysql_error());
$row_relatedprod = mysql_fetch_assoc($relatedprod);
$totalRows_relatedprod = mysql_num_rows($relatedprod);

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_Recordset1") == false &&
stristr($param, "totalRows_Recordset1") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>

[johndm1971]

Wow - well, it seems to work - I'd love to know why! lol

A couple of things ...

I note you only now have one Recordset - interesting. Although the page works i now have a ! next to Recordset1 - is that a problem?

Also, where I'm showing related products it is showing the first related product correctly but when I try to make that a repeat region (to show all related products) it falls over again.

[Guest]

Don't worry about the "!" that will appear when you manually edit code. This is normal.

No one can help with the repeat region unless you supply the code and error message.

[johndm1971]

OK thanks.

The page works and then when I add a repeat region to the field showing the related product it reports a syntax error at line 71, line 71 simply says <?php

And when I then try to view the page I get ...

Parse error: syntax error, unexpected '<' in C:\xampp\htdocs\pipguitar\homepagetest.php on line 71

[Guest]

Your previous post said you'd love to know why it works. The reason why it works is highlighted in bold in the code I provided in post #13. The reason for your current error message in line 71 is because you're trying to add an opening php tag inside a php code that hasn't been closed. The following is an example of syntax that will not produce an error message:

<?php

// php code here

?>

(line 71) <?php

// repeat region here

?>

Now here is probably what you're doing which is causing an error message:

<?php

// php code here

(line 71) <?php

//repeat region here

?>

Since the php code wasn't properly closed before starting the php code on line 71 you get an error message.

[johndm1971]

OK - almost there and you've taught me loads, thank you. The Repeat Region code was much further down but I've now made lines 67 to 71 ..

.$xxxxxx = $row_Recordset1['itemid'];

?>
<?php

which took away the syntax error.

Now, the repeat region is working, everything is working - but - at the top of the page when I look in browser it now suddenly says ...

$queryString_Recordset1 = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Recordset1") == false && stristr($param, "totalRows_Recordset1") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1); ?>

[Guest · Accepted solution]

Take the information from my last post and reverse the logic... The reason the php code is displaying on your page and not processed by the server is because you do not have an opening tag.

[johndm1971]

Done - thank you so much for you help.

[Guest]

whew, sigh. Glad you got it figured out.

[johndm1971]

Spoke too soon. Now I've added a move to next page link which (before we started solving other issues) would correctly go to the next body in the recordset. It doesn't now, it just stays on record 1. I need the user to be able to browse through (and eventually search for) bodies and relevant parts then show for each body. I guess we've limited the recordset to only the first record? How can I sort this one? Thanks.

[Guest]

I understand the problem, but this thread is getting a little too long for my taste. There's also no code or programming logic presented which makes it difficult to provide a solution.

Basically, when you click on a new product you should be running a new query that filters the database to display the filtered items. The filtered parameter is sent to the query by different means: either URL parameter, form POST value, session variable, cookie, dynamic value, and other methods (direct value, magic, etc.).

Your programming logic should include a method of filtering the recordset for products and then applying a query to filter related items based off the itemid of the first query result. Up until now you're just pulling ALL items from the first query and then filtering based off the last itemid that was entered into the DB.

You should change your method to filter the first query based off a parameter. For instance if you had a URL to click on guitar bodies the URL could be something like products.php?item=guitar_bodies

Then in php create your first query much like the one you used to retrieve related items but filter the query of table WHERE item = $_GET['item'] instead of SELECT all from table with no filter applied. You will then have results only for items in the database that match the column item with the value guitar_bodies. With this query result you can then filter for related items based on the result of the first, dynamic, filtered recordset.

When filtering a query from values that can be manipulated you should absolutely sanitize your variables before they're applied to the query using the method I explained earlier with the GetValueString function. Otherwise a hacker could enter something like products.php?item=guitar_bodies'; DROP table bodies; and delete your entire database table! Make sure you do your research on injection attacks.

Good luck!