WordPress WP-ecommerce Search Page with Related Products Hack

If you are using WP-ecommerce on your WordPress site, what about displaying related products on your theme’s search page?

Just copy and paste the following code on your search.php file, on your theme folder. It will bring as results the title and first image of each product found, linked to the detail page.

<?php
/**
 * @package WordPress
 * @subpackage Toolbox
 */

get_header(); ?>

        <section id="primary">
            <div id="relprods" style="float:left;width:140px;margin-right:20px;">
            <?
            /*
                New code by Alvaron - brings two first product images - 08-22-2011
            */
                $conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
                mysql_select_db(DB_NAME) or die ("Could not select database, check you wp-config file" . mysql_error());
                $sqlp ="select ID, post_title, post_name from wp_posts where post_type='wpsc-product' ";
                $sqlp .="and post_status='publish' ";
                $sqlp .="and (post_title like '%".$_GET['s']."%' ";
                $sqlp .="or post_content like '%".$_GET['s']."%') ";
                $sqlp .="order by id ASC ";
                $qryp = mysql_query($sqlp);
                $nrtp = mysql_num_rows($qryp);
                if($nrtp>0):
                    $mxctst= "max-width:550px";                     
                    echo "<b>".$nrtp. " Related Products for ".$_GET['s'].":</b><br><br>";
                    while($rsp = mysql_fetch_array($qryp)):
                        ++$prdcnt;
                    ?>
                        <p><?=$prdcnt;?>.
                        <a rel="<?php echo $rsp[1]; ?>" href="<?php echo get_bloginfo('siteurl')."/".$rsp[2]; ?>"><?php echo $rsp[1]; ?></a><br />
                    <?
                        $sqli = "select guid from wp_posts where post_type='attachment' and post_mime_type='image/jpeg' and post_parent=".$rsp[0];
                        $sqli .= " ORDER BY ID DESC ";
                        $sqli .= " LIMIT 1 ";
                        $qryi = mysql_query($sqli);
                        $nrti = mysql_num_rows($qryi);
                        while($rsi = mysql_fetch_array($qryi)):
                        ?>                                               
                                <a rel="<?php echo $rsp[1]; ?>" href="<?php echo get_bloginfo('siteurl')."/".$rsp[2]; ?>"><img class="product_image" id="product_image_<?php echo $rsp[0]; ?>" alt="<?php echo $rsp[1]; ?>" title="<?php echo $rsp[1]?>" src="<?php echo $rsi[0]; ?>" width="140"/></a>   
                        <?
                        endwhile;
                        ?>
                        </p>
                        <?
                    endwhile;
                endif;
                ?>
            </div>
            <div id="content" role="main" style="float:left;<?=$mxctst?>">

            <?php if ( have_posts() ) : ?>

                <header class="page-header">
                    <h1 class="page-title"><?php printf( __( 'Search Results for: %s', 'toolbox' ), '<span>' . get_search_query() . '</span>' ); ?></h1>
                </header>

                <?php /* Display navigation to next/previous pages when applicable */ ?>
                <?php if ( $wp_query->max_num_pages > 1 ) : ?>
                    <nav id="nav-above">
                        <h1 class="section-heading"><?php _e( 'Post navigation', 'toolbox' ); ?></h1>
                        <div class="nav-previous"><?php next_posts_link( __( '<span class="meta-nav">←</span> Older posts', 'toolbox' ) ); ?></div>
                        <div class="nav-next"><?php previous_posts_link( __( 'Newer posts <span class="meta-nav">→</span>', 'toolbox' ) ); ?></div>
                    </nav><!-- #nav-above -->
                <?php endif; ?>
               
                <?php /* Start the Loop */ ?>
                <?php while ( have_posts() ) : the_post(); ?>
                   
                    <?php get_template_part( 'content', 'search' ); ?>

                <?php endwhile; ?>
               
                <?php /* Display navigation to next/previous pages when applicable */ ?>
                <?php if (  $wp_query->max_num_pages > 1 ) : ?>
                    <nav id="nav-below">
                        <h1 class="section-heading"><?php _e( 'Post navigation', 'toolbox' ); ?></h1>
                        <div class="nav-previous"><?php next_posts_link( __( '<span class="meta-nav">←</span> Older posts', 'toolbox' ) ); ?></div>
                        <div class="nav-next"><?php previous_posts_link( __( 'Newer posts <span class="meta-nav">→</span>', 'toolbox' ) ); ?></div>
                    </nav><!-- #nav-below -->
                <?php endif; ?>               

            <?php else : ?>

                <article id="post-0" class="post no-results not-found">
                    <header class="entry-header">
                        <h1 class="entry-title"><?php _e( 'Nothing Found', 'toolbox' ); ?></h1>
                    </header><!-- .entry-header -->

                    <div class="entry-content">
                        <p><?php _e( 'Sorry, but nothing matched your search criteria. Please try again with some different keywords.', 'toolbox' ); ?></p>
                        <?php get_search_form(); ?>
                    </div><!-- .entry-content -->
                </article><!-- #post-0 -->

            <?php endif; ?>

            </div><!-- #content -->
        </section><!-- #primary -->

<?php get_sidebar(); ?>
<?php get_footer(); ?>

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: