$sql_related = "SELECT p.name,p.seoname, p.productid
FROM relatedproducts rp
JOIN products p ON p.productid = rp.relatedproductid
JOIN stock s ON s.productid = p.productid
WHERE rp.productid = '" . mysql_real_escape_string($row_product['productid']) . "'
AND p.status = 'live'
AND s.status = 'live'
AND (s.stock > '0' OR p.stockaction = 'show')
GROUP BY p.productid
ORDER BY p.name
LIMIT " . $related_products . "";
$result_related = $db->query($sql_related);
$col = 1;
while ($row_related = $result_related->fetch()) {
$sql_category = "SELECT c.parent AS parent1, c2.parent AS parent2 FROM productcategories pc
JOIN categories c ON pc.categoryid = c.categoryid
LEFT JOIN categories c2 ON c2.categoryid = c.parent
WHERE pc.productid = '" . mysql_real_escape_string($row_related['productid']) . "'";
$result_category = $db->query($sql_category);
$row_category = $result_category->fetch();
if ($row_category['parent2'] == 0) {
$parents = $row_category['parent1'] . '/';
} else {
$parents = $row_category['parent1'] . '/' . $row_category['parent2'] . '/';
}
$sql_image = "SELECT pi.filename
FROM productimages pi
WHERE pi.productid = '" . mysql_real_escape_string($row_related['productid']) . "'
ORDER BY pi.order
LIMIT 1";
$result_image = $db->query($sql_image);
if ($result_image->size() == 0) {
$filename = 'no_image.jpg';
} else {
$row_image = $result_image->fetch();
$filename = $row_image['filename'];
}
$sql_prices = "SELECT *, r.fldPrice AS reducedTo
FROM stock s
LEFT JOIN
(
SELECT * FROM reductions
WHERE
(
fldDateStart IS NULL
OR fldDateStart <= NOW()
)
AND
(
fldDateEnd IS NULL
OR fldDateEnd >= NOW()
)
) AS r
ON r.fldProductID = s.productid
WHERE s.productid = '" . mysql_real_escape_string($row_related['productid']) . "'
AND s.status = 'live'
ORDER BY s.price";
$result_prices = $db->query($sql_prices);
$row_prices = $result_prices->fetch();
$price = $row_prices['price'];
$reducedPrice = $row_prices['reducedTo'];
// Figure out image size for markup
$tmpImagePath = 'http://'.$serverName.'/product_images/image_resize.php?image='.urlencode($filename).'&max_width=85&max_height=125';
$tmpImageData = getimagesize($tmpImagePath);
if(is_null($reducedPrice))
$priceDisplay = '£'.number_format(round($price,2),2);
else
$priceDisplay = '
was £'.number_format(round($price,2),2).' now £'.number_format(round($reducedPrice,2),2).'';
echo '
'
// echo '
'
.'
'."\n"
.'
'.$priceDisplay.'
'
.'
![Image preview of '.htmlentities($row_related['name'], ENT_QUOTES, 'UTF-8').'](/shop/product_images/image_resize.php?image='.urlencode($filename).'&max_width=85&max_height=125)
'
.'
'."\n";
if($col == 3)
$col = 1;
else
$col++;
$related_prods .= '\'' . $row_related['productid'] . '\',';
}
if (strlen($related_prods) > 0) {
$related_prods .= 'END';
$related_prods = str_replace(',END','',$related_prods);
$related_sql = "AND p.productid NOT IN (" . $related_prods . ")";
}
$leftover_related = $related_products - $result_related->size();
if ($leftover_related > 0) {
$sql_leftover = "SELECT p.name,p.seoname, p.productid, p.description
FROM products p
JOIN stock s ON s.productid = p.productid
WHERE p.productid != '" . mysql_real_escape_string($row_product['productid']) . "'
" . mysql_real_escape_string($related_sql) . "
AND p.status = 'live'
AND s.status = 'live'
AND (s.stock > '0' OR p.stockaction = 'show')
AND s.price > 0
GROUP BY p.productid
ORDER BY RAND()
LIMIT 6";
$result_leftover = $db->query($sql_leftover);
while ($row_leftover = $result_leftover->fetch()) {
$sql_category = "SELECT c.parent AS parent1, c2.parent AS parent2 FROM productcategories pc
JOIN categories c ON pc.categoryid = c.categoryid
LEFT JOIN categories c2 ON c2.categoryid = c.parent
WHERE pc.productid = '" . mysql_real_escape_string($row_leftover['productid']) . "'";
$result_category = $db->query($sql_category);
$row_category = $result_category->fetch();
if ($row_category['parent2'] == 0) {
$parents = $row_category['parent1'] . '/';
} else {
$parents = $row_category['parent1'] . '/' . $row_category['parent2'] . '/';
}
$sql_image = "SELECT pi.filename
FROM productimages pi
WHERE pi.productid = '" . mysql_real_escape_string($row_leftover['productid']) . "'
ORDER BY pi.order
LIMIT 1";
$result_image = $db->query($sql_image);
if ($result_image->size() == 0) {
$filename = 'no_image.jpg';
} else {
$row_image = $result_image->fetch();
$filename = $row_image['filename'];
}
$sql_prices = "SELECT *, r.fldPrice AS reducedTo
FROM stock s
LEFT JOIN
(
SELECT * FROM reductions
WHERE
(
fldDateStart IS NULL
OR fldDateStart <= NOW()
)
AND
(
fldDateEnd IS NULL
OR fldDateEnd >= NOW()
)
) AS r
ON r.fldProductID = s.productid
WHERE s.productid = '" . mysql_real_escape_string($row_leftover['productid']) . "'
AND s.status = 'live'
ORDER BY s.price";
$result_prices = $db->query($sql_prices);
$row_prices = $result_prices->fetch();
$price = $row_prices['price'];
$reducedPrice = $row_prices['reducedTo'];
// Figure out image size for markup
$tmpImagePath = 'http://'.$serverName.'/product_images/image_resize.php?image='.urlencode($filename).'&max_width=85&max_height=125';
$tmpImageData = getimagesize($tmpImagePath);
if(is_null($reducedPrice))
$priceDisplay = '£'.number_format(round($price,2),2);
else
$priceDisplay = '
was £'.number_format(round($price,2),2).' now £'.number_format(round($reducedPrice,2),2).'';
if(!empty($row_leftover['description'])){
$description = $row_leftover['description'];
} else {
$description = "No description set";
}
echo '
'
.'
![Image preview of '.htmlentities($row_leftover['name'], ENT_QUOTES, 'UTF-8').'](/shop/product_images/image_resize.php?image='.urlencode($filename).'&max_width=141&max_height=141)
'
.'
'
.'
'."\n"
.'
'.substr(strip_tags($description), 0, 40).'...
'
.'
'
.'
'."\n";
$col++;
}
}
// Slightly messy markup but we need to pad the columns out for this design to work
if($col > 1)
{
while($col < 4)
{
echo '
';
$col++;
}
}
?>