Query items display

mallen · June 22, 2012

I have a search I am doing n a large list of products. When products are entered they are assigned to three different "divisions of the company such as company1, cpmpany2 and company3.

When I do a search it displays the product description and includes what company is associated with. But If the same item is listed in two companies it will only display 1 company. I have tested with either company name and have decided this is the issue. Here is the code I have for the search. I don't see anything keeping duplicated from showing.

$searchRequest = strtolower($_REQUEST['searchQuery']);
$formQuery = "SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name,prod.prod_mainImage, prod.prod_model, prod_company1, prod_company2, prod_company3 FROM products AS prod
					   LEFT JOIN category_assoc AS assoc
					   ON assoc.prod_id = prod.prod_id
					   LEFT JOIN categories AS cat						   
					   ON cat.cat_id = assoc.cat_id
					   WHERE LOWER(prod.prod_name) LIKE '%". $searchRequest ."%'
					   OR LOWER(prod.prod_model) LIKE '%". $searchRequest ."%'
					   OR LOWER(cat.cat_name) LIKE '%". $searchRequest ."%'
					   OR LOWER(prod.prod_description) LIKE '%". $searchRequest. "%'
					   ORDER BY prod.prod_name ASC";
					   
					  
$results = $wpdb->get_results($formQuery, ARRAY_A);

get_header(); 

?>
<div id="mainContent">	
	<div id='interiorLeft'>
<?php if (have_posts()) : while (have_posts()) : the_post(); ?>
	        <h2><?php the_title();?> For <?php echo $searchRequest; ?></h2>
			<?php the_content(); ?>

	<?php endwhile; endif; ?>
<hr/>

<?php 
if(count($results) > 0) :
	foreach($results as $res)
	{
		$prImage = $res['prod_mainImage'];
			if(empty($res['prod_mainImage']))
				$prImage = "noImage.png";

		if($res['prod_company3'] == 1)
		{
			$theCompany = "100";
			$theName = "Company3";
		}
		elseif($res['prod_company2'] == 1)
		{
			$theCompany = "95";
			$theName = "Company 2";
		}
		else
		{
			$theCompany = "15";
			$theName = "Company 1";
		}
	?>
			<div class='productHolder'>
				<div class='mainThumbnail'>
					<img src='<?php echo PRODUCT_IMAGE . "/thumbnails/". $prImage;?>' alt='MainThumbnail'  />
                    </div>
				<h3><?php echo stripslashes($res['prod_name']);?></h3>
				<h5>Model : <?php echo $res['prod_model']; ?> <small>(<?php echo $theName;?>)</small></h5>
				<span class='viewBtn'>
                    <a href='?page_id=169&company=<?php echo $theCompany;?>&singleProduct=<?php echo $res['prod_id'];?>'>

Psycho · June 22, 2012

OK, you have one problem with your DB structure. Ideally, you should not have three columns for company1, company2, and company3. You should have a separate to associate each record with each applicable company. So if a product is associated with two companies it would require two records in that 2nd table. If you don't do that it will make some processes more difficult - e.g. doing a search of products by company.

But, that is not the reason you have the current problem. The reason you are only getting one company listed in your results is how you are processing the data. Your code STOPS processing company info at the first column where the value is 1 because you are using an if/elseif/else logic. So, if company1 is a 1, it doesn't check columns for the other two columns:

<?php
		if($res['prod_company3'] == 1)
		{
			$theCompany = "100";
			$theName = "Company3";
		}
		elseif($res['prod_company2'] == 1)
		{
			$theCompany = "95";
			$theName = "Company 2";
		}
		else
		{
			$theCompany = "15";
			$theName = "Company 1";
		}
?>

Does that make sense?

using what you have now (which is not ideal) you can list out all the applicable companies for a single records pretty easily. I'm not sure how you want to output those results though. Here's one possibility:

<?php

function productViewBttn($prodID, $companyID, $companyName)
{
    $output  = "<small>({$companyName})</small></h5>\n";
    $output .= "<span class='viewBtn'>";
    $output .= "<a href='?page_id=169&company={$companyID}&singleProduct={$prodID}'>";
    $output .= "</span><br>\n";
    return $output;
}

$searchRequest = strtolower($_REQUEST['searchQuery']);
$formQuery = "SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name,prod.prod_mainImage, prod.prod_model, prod_company1, prod_company2, prod_company3 FROM products AS prod
					   LEFT JOIN category_assoc AS assoc
					   ON assoc.prod_id = prod.prod_id
					   LEFT JOIN categories AS cat						   
					   ON cat.cat_id = assoc.cat_id
					   WHERE LOWER(prod.prod_name) LIKE '%". $searchRequest ."%'
					   OR LOWER(prod.prod_model) LIKE '%". $searchRequest ."%'
					   OR LOWER(cat.cat_name) LIKE '%". $searchRequest ."%'
					   OR LOWER(prod.prod_description) LIKE '%". $searchRequest. "%'
					   ORDER BY prod.prod_name ASC";

$results = $wpdb->get_results($formQuery, ARRAY_A);

get_header(); 

?>
<div id="mainContent">	
	<div id='interiorLeft'>
<?php if (have_posts()) : while (have_posts()) : the_post(); ?>
	        <h2><?php the_title();?> For <?php echo $searchRequest; ?></h2>
			<?php the_content(); ?>

	<?php endwhile; endif; ?>
<hr/>

<?php


if(count($results) > 0) :
	foreach($results as $res)
	{
            $prImage = (!empty($res['prod_mainImage'])) ? $res['prod_mainImage'] : "noImage.png";

            $prodLinks = '';
		if($res['prod_company1'] == 1)
		{
                $prodLinks .= productViewBttn($res['prod_id'], '15', 'Company 1');
            }
		if($res['prod_company2'] == 1)
		{
                $prodLinks .= productViewBttn($res['prod_id'], '95', 'Company 2');
		}
		if($res['prod_company3'] == 1)
		{
                $prodLinks .= productViewBttn($res['prod_id'], '100', 'Company 3');
            }
	?>
			<div class='productHolder'>
				<div class='mainThumbnail'>
					<img src='<?php echo PRODUCT_IMAGE . "/thumbnails/". $prImage;?>' alt='MainThumbnail'  />
                    </div>
				<h3><?php echo stripslashes($res['prod_name']);?></h3>
				<h5>Model : <?php echo $res['prod_model']; ?>
                    <?php echo $prodLinks; ?>

mallen · June 25, 2012

Thanks for the reply. Yes I know its not ideal. The reason the 3rd company is separate is because the data is entered in a separate form. The product numbers, description and everything has to be entered separate becuase of metric measurements and descriptions. So I need for all three listing to show if the item is contained in all three companies.

Psycho · June 25, 2012

Thanks for the reply. Yes I know its not ideal. The reason the 3rd company is separate is because the data is entered in a separate form. The product numbers, description and everything has to be entered separate becuase of metric measurements and descriptions. So I need for all three listing to show if the item is contained in all three companies.

You apparently did not understand my response. There is no problem with supporting three companies (or three hundred). The problem is how that has been implemented in the database. There should not be a separate column to specify the association with each company. That creates significant problems in being able to perform simple queries and will not scale. If you ever needed to add a new company you would have to go back and modify the code and database to support it. If it was implemented correctly you could easily add more companies by adding just one record to a companies table.

There should be tables such as:

companies: a table to describe just the companies

products: a table to describe JUST the product details

prod_comp: a table to describe each individual product-company association.

So, the companies table would have three records such as

comp_id | comp_name
1        Company 1
2        Company 2
3        Company 3

Then let's say you have a product "ABC" that should be associated with company 1 and company 3. The product table would ONLY contain the product specific information: product name, product id, description etc. Such as:

prod_id | prod_name | description
13       ABC         Description for product ABC

Then in the product_company table you would have two records to associate the product to the two companies:

prod_id | comp_id
13       1
13       3

But, if you want to stay with the DB structure you have, which I do not advise, I've provided an explanation of why your current code does not work as well as sample code to get the results you want. But, I do not have your database nor am I willing to invest the time necessary to test the code. but, if you run into problems and can't figure it out you can post back.

mallen · June 25, 2012

Thank you so much. I did realize the original ifelse statement was where my problem was. I am following what someone else set up and do agree its a mess. Now here is what I got so far using your code.

It will list the item like this:

Image name and model. Then it will list (company 1) (Company 2)

And the second company will always have a hyperlink link on it. The good news is its reading all the records now. I would like to either get the hyperlink on the second company name or better yet list the item again with name, image, and another hyperlink.

mallen · June 25, 2012

Ok sorry this might clear up my issue. If two companies appear in the query with the item, The item name and company will be a one link and the second company will be second link. Its combining the item name and company in the hyperlink.

Psycho · June 25, 2012

OK, if you look at my signature I do state

I do not always test the code I provide . . .

Typically I am providing "logic" to solve a particular problem. In these instances I expect the recipient to put forth some effort to fix any minor display/output type issues. A quick look at the function to create the links shows that an ending </a> tag was omitted. Aside from that I also see what is probably another bug if different products are returned. But, to test it would require me to set up a database and test data - which is more work than I am willing to invest. But, I will provide some mock code for you to figure out what you want. The main thing to figure out is EXACTLY how you want the output displayed for multiple companies. If you can write out the HTML exactly as you would expect it to be dislayed when there are two companies associated with a product I can provide some code.

mallen · June 26, 2012

I am trying to get the item to list multiple times like this.


<div class="productHolder">
<div class="mainThumbnail">
<img src="http://www......." alt="MainThumbnail">
    </div>
<h3>Product Name Appear here</h3>
<h5>Model : Model#1 here <small>(Company 1)</small></h5>
<span class="viewBtn">
    <a href="?page_id=169&company=95&singleProduct=1628"><img src="http://www......./products/_images/x.png" alt="Click to View" height="18" width="137"></a>
				</span>
				</div>


<div class="productHolder">
<div class="mainThumbnail">
<img src="http://www......." alt="MainThumbnail">
    </div>
<h3>Product Name Appear here</h3>
<h5>Model : Model#2 here <small>(Company 2)</small></h5>
<span class="viewBtn">
    <a href="?page_id=169&company=100&singleProduct=1629"><img src="http://www......./products/_images/x.png" alt="Click to View" height="18" width="137"></a>
				</span>
				</div>

mallen · June 26, 2012

No edit button so I have to post again. I meant for Model 1 to show twice.

mallen · June 26, 2012

I got it working now. It was a formatting issue. I checked the HTML on the page that rendered and kept tweaking the formatting. Also the CSS I had to correct so the button was working and lined up. I wasn't seeing the correct links on the page because how it was lining up. Thanks again Psycho for your help with this.

Sign In

Query items display

Recommended Posts

mallen

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

mallen

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

mallen

Link to comment

Share on other sites

mallen

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

mallen

Link to comment

Share on other sites

mallen

Link to comment

Share on other sites

mallen

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information