Solved

How do I echo the results of the SELECT statement?

Posted on 2008-10-28
11
320 Views
Last Modified: 2013-12-13

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
Comment
Question by:rlb1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 22827972
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
 

Author Comment

by:rlb1
ID: 22828007
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
 
LVL 82

Expert Comment

by:hielo
ID: 22828046
Well that was not useful feedback. Not clear what the problem is.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 82

Expert Comment

by:hielo
ID: 22828083
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22829353
$res has no value, it is a leftover from an earlier attempt in the Related Solution (ID:22826957).  
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22829446
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22829453
Sorry, line 3 should read:


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

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22829460
...and line 19 has wrong indentation, it is not part of the if in line 17/18.
0
 

Author Comment

by:rlb1
ID: 22831861
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 22832789
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
 

Author Closing Comment

by:rlb1
ID: 31511019
Thanks for your help!!  I appreciate it.
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question