Not pulling records from database, what gives?

[jbrill]

Hey Guys,

Im having troubles pulling records from the database using the code below, but first let me explain the situation.

 

I have two types of URLS that lead to the page below.

 

on style of URL is for only when the category(cat) is set, it appears like so:

http://www.domain.com/detailed_products.php?cat=10

 

The other style is if the category(cat) and subcategory(subcat) are both set, this url looks like this:

http://www.domain.com/subcat_products.php?subcats=57&cat=10

 

here is the code for the page that is used to query the database and display the results:

<? include 'includes/header.php';


$cat = (isset($_GET["cat"])) ? $_GET["cat"] : '';
$subcat = (isset($_GET["subcat"])) ? $_GET["subcat"] : '';

?>

  <td width="722" valign="top">
<!---Display Subcategories--->
<table width="100%" border="0" valign="top">
<tr>
<td valign="top" style="border-bottom: 1px solid #cccccc">
<form id="subcats" name="subcats" method="get" action="subcat_products.php"> 

   <span class="subcatTitle">Sub Categories: </span>

  
  <select name="subcats" id="subcats">
  <option>Select a Subcategory</option>

<?php
$subcat = mysql_query("SELECT * FROM subcategory WHERE cat='".$cat."' ORDER by subcat");
while($sub = mysql_fetch_array($subcat))
{
?>

    <option value="<?php echo $sub['subc']; ?>"><?php echo $sub['subcat']; ?></option>

<?php
}
?>
      </select> 

      <input name="cat" type="hidden" value="<?php echo $cat; ?>">
      <input type="submit" value="Go" name="submit">
</form>
</td>
<td align="right" width="35%" valign="top" style="border-bottom: 1px solid #cccccc">
<?
$showcat = mysql_query("SELECT * FROM category WHERE cat='".$cat."'");
while($row = mysql_fetch_array($showcat))
	{
	echo"<span class=\"titlelarge\">".$row['category']."</span>";
	}	

echo "<br>";

echo "<img src=\"images/quicklist.gif\"> <a class=\"quicklisting\" href=\"quick_products.php?cat=".$cat."\">Quick Listing</a>";

?>

</td>
</tr>
</table>





</td>
  </tr>
<tr>
	<td width="722" height="5" valign="top" align="center">
<table width="100%" border="0" height="400" >
<tr>
<td valign="top">
<?

$featuredresults = "SELECT * FROM products WHERE cat = '" . $cat . "'" . ($subcat != "" ? " AND subcat = '" . $subcat . "' " : "") . " AND featured = 1 AND publish = 1";
$featuredresultsquery = mysql_query($featuredresults);
              
              while($row = mysql_fetch_array($featuredresultsquery))
	{



echo"<table border=\"0\" class='featuredcenterproduct' width='100%'>";

echo"<tr>";
echo"<td align=\"center\" height=\"260\">";
if($row['photo']==null || !file_exists($row['photo'])) {
						echo "<img src=\"images/nophoto.gif\" width=66 height=39 border=0>";
					} else {
						// if image exists, resize and display

						$size = getimagesize($row['photo']);
						$width = $size[0];
						$height = $size[1];

						$max = 708;

						if($width > $max || $height > $max) {
							$ratio = $width/$height;

							if($width < $height) {
								// width is smaller than height, hence the height should be adjuste to max first. 
								$resize = $max/$height;
								$nheight = 708;
								$nwidth = ceil($width*$resize);
							}

							if($width > $height) {
								// height is smaller than width, hence the width should be adjuste to max first. 
								$resize = $max/$width;
								$nwidth = 708;
								$nheight = ceil($height*$resize);
							}

						} else {
							$nwidth=$width;
							$nheight=$height;
						}

						echo "<img src=\"".$row['photo']."\" border=0 width=\"".$nwidth."\" height=\"".$nheight."\">";
					}"</td>";
echo"</tr>";

echo"<tr>";
echo"<td width=\"340\" align=\"center\"><span class=\"listingtext\">Product Number(s) in photo&#58;  ". $row['prod_number'] . "</span></td>";
echo"</tr>";

echo"</table>";

}
?>	
</td>
  </tr>
  <tr>
    <td valign="top">

<?

$query = "SELECT * FROM products WHERE cat = '" . $cat . "'" . ($subcat != "" ? " AND subcat = '" . $subcat . "' " : "") . " AND publish = 1";
$rs = mysql_query($query);

echo $query;

              
      while($row2 = mysql_fetch_array($rs))
{

echo"<table  height=\"275\" border=\"0\" class=\"productdisplay\">";
echo"<tr>";
echo"<td  align=\"center\">";


if($row2['photo']==null || !file_exists($row2['photo'])) {
						echo "<img src=\"images/nophoto.gif\" width=66 height=39 border=0>";
					} else {
						// if image exists, resize and display

						$size = getimagesize($row2['photo']);
						$width = $size[0];
						$height = $size[1];

						$max = 170;

						if($width > $max || $height > $max) {
							$ratio = $width/$height;

							if($width < $height) {
								// width is smaller than height, hence the height should be adjuste to max first. 
								$resize = $max/$height;
								$nheight = 170;
								$nwidth = ceil($width*$resize);
							}

							if($width > $height) {
								// height is smaller than width, hence the width should be adjuste to max first. 
								$resize = $max/$width;
								$nwidth = 170;
								$nheight = ceil($height*$resize);
							}

						} else {
							$nwidth=$width;
							$nheight=$height;
						}

						echo "<img src=\"".$row2['photo']."\" border=0 width=\"".$nwidth."\" height=\"".$nheight."\">";
					}"</td>";
echo"</tr>";
echo"<tr>";
echo"<td width=\"220\" align=\"center\"><span class=\"listingtext\"><strong>". $row2['name'] ."</strong></span><br><span class=\"listingtext\">Product Number&#58;  ". $row2['prod_number'] . "</span><br><a class=\"details\" href=\"details.php?id=".$row2['id']."&cat=".$row2['cat']."&table=products\">Details</a></td>";
echo"</tr>";

echo"</table>";

	}
?>
    
    </td>
  </tr>
</table>


<? include 'includes/footer.php' ?>

 

The problem is that it is not displaying anything at all, i echoed the $query

and this text appeared:

 

SELECT * FROM products WHERE cat = '4' AND subcat = 'Resource id #3' AND publish = 1

 

so obviously its looking for the subcat no matter what, and the isset is not working... any ideas?

 

[Barand]

You set $subcat to 'Resource id #3'  here, when you assign the query results to it.

 

$subcat = mysql_query("SELECT * FROM subcategory WHERE cat='".$cat."' ORDER by subcat");

[jbrill]

it needs to SELECT * FROM products WHERE subcat=$subcat AND cat=$cat

 

however $subcat is not always assigned, so only if it is in the URL do i want it to be put into the query

 

does that clarify it a bit more?let me know if anything needs explained i know i ramble alot

[Barand]

I gave you code for this in your original topic with this problem.

[jbrill]

this is for a different page, and when i applied your code to this page it didn't work. however my previous topic regarding the similar is solved but this page is giving me problems...

[jbrill]

any ideas?

[jbrill]

can someone please help me?

[a1phanumeric]

Hi,

 

Have you tried replacing the Ternary Operators within your MySQL queries with if statements before the actual construction of the query? It may not help, but it's an idea?

 

Let me know if this does not help, as I'll think of some more ideas

 

Ed.

[jbrill]

if i remove this line from the query, it displays the results for all the products under the appropriate cat.

 

" . ($subcat != "" ? " AND subcat = '" . $subcat . "' " : "") . "

 

however if the subcat isset i NEED it to narrow down to display products Where subcat=$subcat and cat=$cat

 

so the problem is either here:

$subcat = (isset($_GET["subcat"])) ? $_GET["subcat"] : '';

or here:

$featuredresults = "SELECT * FROM products WHERE cat = '" . $cat . "'" . ($subcat != "" ? " AND subcat = '" . $subcat . "' " : "") . " AND featured = 1 AND publish = 1";

or both

[a1phanumeric]

Hi,

 

Just before this line:

 

$featuredresults = "SELECT * FROM products WHERE cat = '" . $cat . "'" . ($subcat != "" ? " AND subcat = '" . $subcat . "' " : "") . " AND featured = 1 AND publish = 1";

 

insert:

 

$sSubCatAndClause = '';
if($subcat != ''){
  $sSubCatAndClause = " AND subcat = '" . $subcat . "' ";
}

 

Then change your query to:

 

$featuredresults = "SELECT * FROM products WHERE cat = '" . $cat . "'" . $sSubCatAndClause . " AND featured = 1 AND publish = 1";

[jbrill]

you are a life saver! thank you!