shopstop module / new block creation / mysql help

Nuke Cadet Joined: Jun 21, 2003 Posts: 3

I am in the process of creating a new block for use with the shopstop shopping module. The block will display a random product from the database. So far everything is working ok, I have it setup so that the block only shows products that are marked visible=yes If I have a product marked visible=no then it should not show. The problem is that occasionally the block shows up but with nothing in it. I'm assuming that the nothing would be the products marked visible=no How do I fix it so that it always shows a product ? code is below

<?php

if (eregi("block-Random_Product.php", $_SERVER['PHP_SELF'])) {
Header("Location: index.php");
die();
}

global $prefix, $multilingual, $currentlang, $db, $tipath, $user, $cookie;

$r_options = "";
if (isset($cookie[4])) { $r_options .= "&mode=$cookie[4]"; }
if (isset($cookie[5])) { $r_options .= "&order=$cookie[5]"; }
if (isset($cookie[6])) { $r_options .= "&thold=$cookie[6]"; }

if ($multilingual == 1) {
$querylang = "AND (alanguage='$currentlang' OR alanguage='')"; /* the OR is needed to display stories who are posted to ALL lan$
} else {
$querylang = "";
}

$numrows = $db->sql_numrows($db->sql_query("SELECT * FROM ".$prefix."_store"));
if ($numrows > 1) {
$sql = "SELECT pid FROM ".$prefix."_store";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result)) {
$pid = $row[pid];
$pid_array .= "$pid-";
}
$r_pid = explode("-",$pid_array);
mt_srand((double)microtime()*1000000);
$numrows = $numrows-1;
$pid = mt_rand(0, $numrows);
$pid = $r_pid[$pid];
} else {
$pid = 1;
}
$sql = "SELECT pid, shortdes, name, imgsrc, price FROM ".$prefix."_store WHERE pid='$pid' AND visible=\"yes\" $querylang
ORDER BY pid DESC LIMIT
0,9";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$imgsrc = $row[imgsrc];
$shortdes = $row[shortdes];
$name = $row[name];
$price = $row[price];
$content .= "<table border=\"0\" width=\"100%\">";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result)) {
$content .= "<br><center><a href=\"\"><img src=\"$row[imgsrc]\" border=0<p>";
$content .= "<tr><td valign=\"top\"></td><td><center><a href=\"\">$row[name]</a></td></tr>";
$content .= "<tr><td valign=\"top\"></td><td><center>$row[shortdes]</a></td></tr>";
$content .= "<tr><td valign=\"top\"></td><td><center>$row[price]</a></td></tr>";
}
$content .= "</table>";

?>

General Joined: Mar 22, 2003 Posts: 5233 Location: USA

This is a sidenote and I doubt that it will fix your problem, but always use single quotes in SQL queries. Change

Quote:

$sql = "SELECT pid, shortdes, name, imgsrc, price FROM ".$prefix."_store WHERE pid='$pid' AND visible=\"yes\"

to

Code:

$sql = "SELECT pid, shortdes, name, imgsrc, price FROM ".$prefix."_store WHERE pid='$pid' AND visible='yes'

Reasons being, amonst other things, is that true ANSI syntax requires it and many dbms's will croak, although MySQL is forgiving and double quotes take more compiler time, albeit negligible in just an isolated incident Wink

.

Nuke Cadet Joined: Jun 21, 2003 Posts: 3

thanks, I made that change but it didn't seem to help. I'm kinda new at coding in php/mysql so I'm kinda at a point where I'm stuck

General Joined: Mar 22, 2003 Posts: 5233 Location: USA

There are a couple of ways. You could put a "default" product in your table that has a description like No Products Selected, or whatever. The other way is to have an 'else' clause after you get the num_rows result. Another question for you, I notice that you say if ($numrows > 1). Wouldn't you rather mean if ($numrows > 0) ? Otherwise 2 rows have to be selected before that logic is performed.

Nuke Cadet Joined: Jun 21, 2003 Posts: 3

The else clause would probably be a good idea. Just not sure how to do it yet. i'm kinda new to php and mysql. Would the else clause stop it from displaying no records at all ? Basically the whole idea of the thing is to display random products from the catalog. one of the fields in the catalog is visible if set to yes then it displays ok, if it is set to no then it displays nothing on the screen, all i get is the title for the block with no content in the box. this is going to be a new block for the stopshop shopping module for phpnuke. i really need to solve this one so that i can make sure a product always shows. i would think that if a product is turned off it would skip it display the next random product. weird < appreciate the help, us newbies to php, sql programming need help >