• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

How do I echo the results of the SELECT statement?


I need to echo the results of the categories/subcategories below.  I am having trouble with the correct select statement.

Thanks for any assistance you can give me!!



<?php


$sql = "SELECT DISTINCT category FROM ddcart_products";
$result = mysql_query($sql) or die('Error: '.mysql_error());
echo '<option value="">- Pick a Category -</option>';
while($data = mysql_fetch_assoc($result)) {
  echo '<option value="'.$data['category'].'">'.$data['category'].'</option>';
 $sql2 = "SELECT DISTINCT subcategory FROM ddcart_products
           WHERE category = '{$data['category']}'";
  $result2 = mysql_query($sql2) or die('Error: '.mysql_error());
  if(mysql_num_rows($result2)>0) {
    /*echo '<optgroup>'*/;
    while($data2 = mysql_fetch_assoc($result2))
      echo '<option value="'.$data2['subcategory'].'">- ' .
           $data2['subcategory'].'</option>';
    echo '</optgroup>';
  }
}

  echo $res;
?>
</select> <input type="submit" value="Go"></form></div>
<div class="clear" style="height: 1px;"></div>
</div>

        <div class="clear" style="height: 1px;"></div>

    <?php

            //number of items per page
            $numitems = getOptionValue('itemsperpage');

            if(isset($_GET['page'])) {
                    $page = $_GET['page'];
                } else {
                        $page = 0;
                }
            //determine offset
            $offset = $page*$numitems;


//    NEED HELP GETTING RESULTS


                $sql = "SELECT * FROM ddcart_products WHERE category = ".$res." LIMIT ".$offset.", ".$numitems;
                $result = mysql_query($sql);
                $i = 1;
                while($data = mysql_fetch_assoc($result)) {
                        ?>
            <div class="productblock draggablediv" product="<?php echo $data['id'].'|'.$data['name'].'|'.$data['price']; ?>">
                    <div class="pbimage">  <img width=150 src="<?php echo $data['photourl']; ?>">   <!--<?php echo displayProductImage($data['id'], 'thumb'); ?>--></div>
                <div class="pbinfo"><a href="productdetail.php?product=<?php echo $data['id']; ?>" class="detailslink">View Details</a></div>
                <div class="pbtitle"><?php echo stripslashes($data['name']); ?></div>
                <div class="pbprice"><?php echo "$".number_format($data['price'], 2, '.', ','); ?></div>
            </div>
            <?php

            if(($i % 4) == 0) echo '<div class="clear"></div>';

            $i++;


                }

                echo '<div class="clear"></div><div id="pagenums" style="text-align: center;">';

                //total number of pages
                $sql = "SELECT * FROM ddcart_products WHERE cat = '".$cat."'";
                $result = mysql_query($sql);
                $numpages = floor(mysql_num_rows($result)/$numitems);

                for($i = 1; $i <= $numpages; $i++) {
                        if($page == $i) {
                                echo '<strong><span style="font-size: 12px; text-decoration: underline;"><a href="index.php?cat='.$cat.'&page='.$i.'">'.$i.'</a></strong>';
                        } else {
                                echo '<a href="index.php?cat='.$cat.'&page='.$i.'">'.$i.'</a>';
                        }

                }

                echo '</div>';

        ?>
0
rlb1
Asked:
rlb1
  • 5
  • 3
  • 3
1 Solution
 
hieloCommented:
try:
<?php 
$sql = "SELECT DISTINCT category FROM ddcart_products";
$result = mysql_query($sql) or die('Error: '.mysql_error());
if( 1 > mysql_num_rows($result) )
{
	echo "No Categories Found!";
}
else
{
	echo "<select name='category'>";
	echo '<option value="">- Pick a Category -</option>';
	while($category = mysql_fetch_assoc($result))
	{
		$sql2 = "SELECT DISTINCT subcategory FROM ddcart_products WHERE category = '{$category['category']}'";
		$subcategory = mysql_query($sql2) or die(mysql_error());
		
		if( mysql_num_rows($subcategory) > 0)
		{
			echo "<optgroup label='{$category['category']}'>";
				while($subcat = mysql_fetch_assoc($subcategory) )
				{
					echo "<option value='{$subcat['subcategory']}'>{$subcat['subcategory']}</option>";
				}
			echo "</optgroup>";
		}
		else
		{
			echo "<option value='{$category['category']}'>{$category['category']}</option>";
		}
	}
	echo "</select>";
} 
?>

Open in new window

0
 
rlb1Author Commented:
hielo,
Thanks for your help!!  I have found a solution to the Select drop down question, now I need to pull the items in each category and post them on my website using the code below..  

<?php

            //number of items per page
            $numitems = getOptionValue('itemsperpage');

            if(isset($_GET['page'])) {
                    $page = $_GET['page'];
                } else {
                        $page = 0;
                }
            //determine offset
            $offset = $page*$numitems;


//    NEED HELP GETTING RESULTS


                $sql = "SELECT * FROM ddcart_products WHERE category = ".$res." LIMIT ".$offset.", ".$numitems;    
                $result = mysql_query($sql);
                $i = 1;
                while($data = mysql_fetch_assoc($result)) {
                        ?>
            <div class="productblock draggablediv" product="<?php echo $data['id'].'|'.$data['name'].'|'.$data['price']; ?>">
                    <div class="pbimage">  <img width=150 src="<?php echo $data['photourl']; ?>">   <!--<?php echo displayProductImage($data['id'], 'thumb'); ?>--></div>
                <div class="pbinfo"><a href="productdetail.php?product=<?php echo $data['id']; ?>" class="detailslink">View Details</a></div>
                <div class="pbtitle"><?php echo stripslashes($data['name']); ?></div>
                <div class="pbprice"><?php echo "$".number_format($data['price'], 2, '.', ','); ?></div>
            </div>
            <?php

            if(($i % 4) == 0) echo '<div class="clear"></div>';

            $i++;


                }

                echo '<div class="clear"></div><div id="pagenums" style="text-align: center;">';

                //total number of pages
                $sql = "SELECT * FROM ddcart_products WHERE cat = '".$cat."'";
                $result = mysql_query($sql);
                $numpages = floor(mysql_num_rows($result)/$numitems);

                for($i = 1; $i <= $numpages; $i++) {
                        if($page == $i) {
                                echo '<strong><span style="font-size: 12px; text-decoration: underline;"><a href="index.php?cat='.$cat.'&page='.$i.'">'.$i.'</a></strong>';
                        } else {
                                echo '<a href="index.php?cat='.$cat.'&page='.$i.'">'.$i.'</a>';
                        }

                }

                echo '</div>';

        ?>
0
 
hieloCommented:
Well that was not useful feedback. Not clear what the problem is.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hieloCommented:
do you know if you are even getting any results? Try:

$sql = "SELECT * FROM ddcart_products WHERE category = ".$res." LIMIT ".$offset.", ".$numitems;
                $result = mysql_query($sql) or die(mysql_error() );
 
echo "Categories Found: " . mysql_num_rows($result);

Open in new window

0
 
Roger BaklundCommented:
$res has no value, it is a leftover from an earlier attempt in the Related Solution (ID:22826957).  
0
 
Roger BaklundCommented:
You probably want to list the products from the category selected in the dropdown? The below code will only work with categories, not with the sub-categories. You need to change the select dropdown to be able to list products within a sub-category.
if(isset($_POST['category'])) {
  $category = $_POST['category'];
  $sql = "SELECT * FROM ddcart_products WHERE category = ".$res." LIMIT ".$offset.", ".$numitems;    
  $result = mysql_query($sql);
  $i = 1;
  while($data = mysql_fetch_assoc($result)) {
    echo 
    '<div class="productblock draggablediv">'.
      '<div class="pbimage">'.
        '<img width=150 src="'.$data['photourl'].'" alt="" />'.
      '</div>'.
      '<div class="pbinfo"><a href="productdetail.php?product='.$data['id'].
        '" class="detailslink">View Details</a></div>'.
      '<div class="pbtitle">'.stripslashes($data['name']).'</div>'.
      '<div class="pbprice">$'.number_format($data['price'], 2, '.', ',').'</div>'.
    '</div>';
    if(($i % 4) == 0) 
      echo '<div class="clear"></div>';
      $i++;
  }
  echo '<div class="clear"></div><div id="pagenums" style="text-align: center;">';
  //total number of pages
  $sql = "SELECT count(*) FROM ddcart_products WHERE category = '".$category."'";
  $result = mysql_query($sql);
  $numpages = ceil(mysql_num_rows($result)/$numitems);
  for($i = 1; $i <= $numpages; $i++) {
    if($page == $i) {
      echo '<strong><span style="font-size: 12px; text-decoration: underline;">'.
           '<a href="index.php?category='.$category.'&amp;page='.$i.'">'.$i.'</a></strong>';
    } else {
      echo '<a href="index.php?category='.$category.'&amp;page='.$i.'">'.$i.'</a>';
    }
  }
  echo '</div>';
}

Open in new window

0
 
Roger BaklundCommented:
Sorry, line 3 should read:


  $sql = "SELECT * FROM ddcart_products WHERE category = '".$category."' LIMIT ".$offset.", ".$numitems;    

Open in new window

0
 
Roger BaklundCommented:
...and line 19 has wrong indentation, it is not part of the if in line 17/18.
0
 
rlb1Author Commented:
cxr, OK, I have a blank screen with no results.  Line 19 looks OK to me, how should it read?  Please clarify.  Does EE have private messaging?  Is there a way I can email you direct through EE?

Thanks!

Randy Bass  
0
 
Roger BaklundCommented:
Line 19 is ok, its just the indentation (too many spaces at the start of the line). It's not an error, it just "looks wrong".

EE does not have private messageing, as far as I know.

As for the blank screen... you should put in some error checking:
ini_set('display_errors', 1);
error_reporting(E_ALL);
 
if(isset($_POST['category'])) {
  $category = $_POST['category'];
  $sql = "SELECT * FROM ddcart_products 
          WHERE category = '".$category."' LIMIT ".$offset.", ".$numitems;    
  $result = mysql_query($sql) or die('Error: '.mysql_error());
  $i = 1;
  while($data = mysql_fetch_assoc($result)) {
    echo 
    '<div class="productblock draggablediv">'.
      '<div class="pbimage">'.
        '<img width=150 src="'.$data['photourl'].'" alt="" />'.
      '</div>'.
      '<div class="pbinfo"><a href="productdetail.php?product='.$data['id'].
        '" class="detailslink">View Details</a></div>'.
      '<div class="pbtitle">'.stripslashes($data['name']).'</div>'.
      '<div class="pbprice">$'.number_format($data['price'], 2, '.', ',').'</div>'.
    '</div>';
    if(($i % 4) == 0) 
      echo '<div class="clear"></div>';
    $i++;
  }
  echo '<div class="clear"></div><div id="pagenums" style="text-align: center;">';
  //total number of pages
  $sql = "SELECT count(*) FROM ddcart_products WHERE category = '".$category."'";
  $result = mysql_query($sql) or die('Error: '.mysql_error());
  $numpages = ceil(mysql_num_rows($result)/$numitems);
  for($i = 1; $i <= $numpages; $i++) {
    if($page == $i) {
      echo '<strong><span style="font-size: 12px; text-decoration: underline;">'.
           '<a href="index.php?category='.$category.'&amp;page='.$i.'">'.$i.'</a></strong>';
    } else {
      echo '<a href="index.php?category='.$category.'&amp;page='.$i.'">'.$i.'</a>';
    }
  }
  echo '</div>';
}

Open in new window

0
 
rlb1Author Commented:
Thanks for your help!!  I appreciate it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now