Solved

Php Mysql Refine Search Multiple Queries

Posted on 2015-02-17
6
825 Views
Last Modified: 2015-02-18
Hi I wish to display on category page refine search filters along with products

My tables as below
[b]attributes table:[/b]
id   name     
1    Brand    
2    Model    
3    OS       

att_description
id   name          attributes_id
1    Nokia               1
2    Samsung         1
3    Android           3
4    Windows         3

product_attributes
id    product_id        att_id              attributes_id
1       240                  2(Sam)              1  (Brand)
2       240                  3(Android)        3  (OS)

Open in new window


Example Category Left Sidebar Page
Left Sidebar 

Brand
Samsung
Nokia
HTC

OS
Android
Windows

Open in new window


If user click on refine filters, user should get relevant results, how di I display like above left sidebar using php mysql.
0
Comment
Question by:veeru bolem
[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
  • 3
  • 2
6 Comments
 
LVL 13

Expert Comment

by:Andrew Derse
ID: 40617193
Are you familiar with any PHO or MySQL?  I am asking just to know if you need something from the very beginning or something intermediate.
0
 
LVL 13

Expert Comment

by:Andrew Derse
ID: 40617197
I meant PHP*
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 40617264
Well, we can begin by breaking this down into parts:

1) Get the categories for the filters.  This is a pretty simple query: SELECT id, name FROM attributes.  If you're using mysqli in PHP, that might look something like this:
$db = new mysqli('host','user','password','database');
$q = "SELECT id,name FROM attributes;";
if (!($r=$db->query($q))) { die('error querying database'); }

Open in new window

At this point, $r should be a result set of all the attribute groups.

2) Get the actual filters.  The categories is all well and good, but you need the actual filter values also.  We could just do a second query for them, but why waste that energy?  We can pack this all into a single loop:
$db = new mysqli('host','user','password','database');
$q = "SELECT a.id as group_id, a.name as group_name, b.id as att_id, b.name as att_name, b.attributes_id as att_group_id FROM attributes a INNER JOIN att_description b on a.id=b.attributes_id;";
if (!($r=$db->query($q))) { die('error querying database'); }
$filters = array();
while ($row=$r->fetch_assoc()) {
  if (!array_key_exists($r->group_name, $filters)) { $filters[$r->group_name] = array(); }
  $filters[$r->group_name] = $row;
}

Open in new window

Now, $filters is an array of groups ('Brand', 'Model', 'OS').  Each group is also an array, with each element containing a single attribute definition for that group.

3) Print the filter groups.  Now we need to print some HTML from the filters we pulled in.  This bit can vary widely based on your needs and strategies - feel free to change this as you feel is necessary.
foreach ($filters as $group_name=>$group_rows) {
  echo "<div class=\"filter-group\">" .
            "<div class=\"filter-group-title\">$group_name</div>" .
            "<div class=\"filter-group-options\">";
  foreach ($group_rows as $rownum=>$row) {
    echo "<div class=\"filter-option\">{$row['att_name']}</div>";
  }
  echo "</div></div>";
}

Open in new window


OK, so now you have the HTML of your filters.  Next step is to actually implement the filter selection by the user.  There are MANY strategies to do this, so you'll need to explain a little more about how you intend for this to work.  Once we have an idea of where you want to go, we'll be able to point you in the right direction.

Please note that all of this code is just me typing off the cuff - nothing has been tested, and do not consider this to be a piece of code you can just drop in and have it work.  You'll still need to integrate these ideas into your application.
0
Industry Leaders: 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!

 
LVL 13

Expert Comment

by:Andrew Derse
ID: 40617278
Here's the code I developed for you this is once the user has selected the criteria based on a form built in HTML:

<?php

//this function connects to the database using mysqli connection string 
function setQuery($sql) {
	$host = 'localhost'; // your database host url
	$dbuser = 'dbuser'; // your database username
	$dbpass = 'dbpass'; // your database password 
	$dbname = 'database'; // your database name
	 
    $db = new mysqli($host, $dbuser, $dbpass, $dbname);
 
	// check connection
	if ($db->connect_error) {
  		$error = $db->connect_error;
	}
	
	$result = $db->query($sql);
	
	return $result;	
}

//this function sets the query for search criteria and returns all products with relavant criteria in table att_description...
function getSearchResults($criteria) {
	$sql = "SELECT product_id FROM product_attributes WHERE att_id = $criteria";
	$q = setQuery($sql);
	
	while ($r = mysqli_fetch_assoc($q)) { 
		 echo $r['product_id'] . "<br />";
	} 
}

//this is the actually criteria you are sending to the page via an HTML form and POST...or AJAX POST...
$criteria = $_POST['criteria'];


//this just calls the function getSearchResults and passes in the criteria.
getSearchResults($criteria);

Open in new window

0
 

Author Closing Comment

by:veeru bolem
ID: 40618176
Ok Thank You for your support, Actually I have done this problem myself, but your logic is better than me, I will use it.
0
 

Author Comment

by:veeru bolem
ID: 40618177
Thank You NUKIT.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Having trouble getting Imagick working with php on windows. 2 47
What is this? I've never seen this... 2 28
Length of for loop to be dynamic 2 31
Survey branching tutorial 11 38
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

763 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