Databases
StackOverflow2013
Postsdbo
POUsing a generated list of options to refine PHP/MySQL search
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing a generated list of options to refine PHP/MySQL search
    primarykey
    data
    text
    <p>I want to user a generated list of links to refine a search.</p> <p>I have a database of products (panel meters), each row with a unique model number and information about that model. I've created a search that allows a user to search against model number and input type (these are process meters that can recieve signals like standard 4-20mA or custom ranges). Along with the search, <strong>the script also creates 3 unique arrays of links that <em>I'd like to be clickable to narrow the results.</em></strong> It does that, so far, with these snippets of php to create the link list:</p> <pre><code>//create a series list $uniqueseries[] = "&lt;a href='#' onclick='document.searchform.submit()'&gt;{$row['series']}&lt;/a&gt;"; //create a inputs list $uniqueinputs[] = "&lt;a href='#' onclick='document.searchform.submit()'&gt;{$row['sio']}&lt;/a&gt;"; //create a series list $uniquepowers[] = "&lt;a href='#' onclick='document.searchform.submit()'&gt;{$row['pso']}&lt;/a&gt;"; </code></pre> <p>AND</p> <pre><code>//create array of unique series types $uniqueseries = array_unique($uniqueseries); //create array of unique input types $uniqueinputs = array_unique($uniqueinputs); //create array of unique power types $uniquepowers = array_unique($uniquepowers); </code></pre> <p>Which are located in the "backend" script (just a bit below.)</p> <p>For each set of links, it first creates an array of non unique links (one for EVERY result MySQL originally returned) and then I use array_unique to cut down the array to only unique links. Then on my search page I use something like ", $uniqueseries); ?> to print out the array of links.</p> <p>What I would like to happen is when a user clicks a link it narrows search results by resubmitting the original MySQL query with the refining query appended. An example of this behavior appears on Amazon search where a user can click a link on the left that will keep the same search but narrow it down to a certain department or even an option unique to that product (ie. amount of ram when searching for a computer or pixel count when searching for cameras.)</p> <p>In my case, if my user starts the search by searching against model number and input type (the default two keys), say with the term "4 to 20", my user will have over 3500 results returned to them (we have a very large catalog of products.) On the left will be the list of unique links that I'd like to have refine the result. So if the user sees under "Power" options "120 V AC with battery backed data", I want clicking that link to return all meters with 4 to 20 mA inputs (there's multiple variations as you can see under the generated Input Options) but only if they have "120 V AC with battery backed data" as a "Power" source.</p> <p>I feel like I'm on the right track but I'm still a bit of a novice when it comes to PHP. </p> <p>To help along, here is the code I'm using for my search "backend":</p> <pre><code>&lt;?php //creates DB connection $dbHost = 'host'; $dbUser = 'user'; $dbPass = 'pass'; $dbDatabase = 'db'; $con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error()); mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error()); $time_start = microtime(true); //create arrays for results and errors $error = array(); $results = array(); //creates arrays holding values for narrowing results $uniqueseries = array(); $uniqueinputs = array(); $uniquepowers = array(); $uniquedigsize = array(); $uniquedignum = array (); $uniquedigcol = array(); $uniquedistype = array(); //reset some variables if needed isset($_GET['page'])?"":$_GET['page'] = 1; isset($resultcount)?"":$resultcount = 0; if (isset($_GET['search'])) { //removes HTML/JS $searchString = trim($_GET['search']); $searchStripped = strip_tags($searchString); //prevents SQL injections $searchSan = mysql_real_escape_string($searchStripped); if (count($error) &lt;1) { //search continues if no errors exist $searchSQL = "SELECT series, model, img, pso, sio, dig_size, dig_num, dig_col FROM meters WHERE levenshtein('%{$searchSan}%',model) &lt; 4 OR levenshtein('%{$searchSan}%',sio) &lt; 4 ORDER BY model"; $searchResult = mysql_query($searchSQL) or trigger_error("There was an error with MySQL. " . mysql_error() . "&lt;br&gt; Query was {$searchSQL}."); if (mysql_num_rows($searchResult) &lt; 1) { $error[] = "The search term '{$searchString}' yielded no results."; } else { $i = 1; while ($row = mysql_fetch_assoc($searchResult)) { $results[] = "&lt;tr&gt;&lt;td align='center'&gt;{$i}&lt;/td&gt; &lt;td align='center'&gt;&lt;a href='/catalog/{$row['series']}.pdf'&gt;{$row['series']}&lt;/a&gt;&lt;/td&gt; &lt;td align='center'&gt;&lt;img src='/img/productimg/small/{$row['img']}'&gt;&lt;/td&gt; &lt;td align='center'&gt;{$row['model']}&lt;/td&gt; &lt;td&gt;&amp;nbsp;{$row['sio']}&lt;/td&gt; &lt;td&gt;&amp;nbsp;{$row['pso']}&lt;/td&gt; &lt;td align='center'&gt;{$row['dig_num']}&lt;/td&gt; &lt;td align='center'&gt;{$row['dig_size']}\"&lt;/td&gt; &lt;td align='center'&gt;{$row['dig_col']}&lt;/td&gt;&lt;/tr&gt;"; //create a series list $uniqueseries[] = "&lt;a href='#' onclick='document.searchform.submit()'&gt;{$row['series']}&lt;/a&gt;"; //create a inputs list $uniqueinputs[] = "&lt;a href='#' onclick='document.searchform.submit()'&gt;{$row['sio']}&lt;/a&gt;"; //create a series list $uniquepowers[] = "&lt;a href='#' onclick='document.searchform.submit()'&gt;{$row['pso']}&lt;/a&gt;"; $i++; } } //create array of unique series types $uniqueseries = array_unique($uniqueseries); //create array of unique input types $uniqueinputs = array_unique($uniqueinputs); //create array of unique power types $uniquepowers = array_unique($uniquepowers); //results paginator $resultcount = count($results); //number of results $perpage = 5; //number of results per page $totalpages = ceil($resultcount / $perpage); //calculates total number of pages $startresult = (($_GET['page'] - 1) * $perpage); //calculates the first result number for page $brokenresults = array_slice($results, $startresult, $perpage); //slices array into groups set by $perpage $nextpage = $_GET['page'] + 1; //calculate next page number $lastpage = $_GET['page'] - 1; //calculates last page number } } function removeEmpty($var) { return (!empty($var)); } $execution_time = (microtime (true) - $time_start); //calculates script processing time ?&gt; </code></pre> <p>And this is the code I'm using for my "frontend":</p> <pre><code> &lt;body&gt; &lt;!-- Google Analytics Script --&gt; &lt;?php include_once("scripts/analyticstracking.php") ?&gt; &lt;div class="wrapper"&gt; &lt;!-- Sticky Footer Wrapper --&gt; &lt;div id="panorama"&gt;&lt;/div&gt; &lt;div id="header"&gt; &lt;?php include("include/header/banner.php") ?&gt; &lt;?php include("include/header/nav.php") ?&gt; &lt;?php include("include/header/quicksearch.php") ?&gt; &lt;/div&gt; &lt;form method="GET" action="&lt;?php echo $_SERVER['PHP_SELF'];?&gt;" name="searchform" id="searchform"&gt; &lt;div id="content"&gt; &lt;?php include("scripts/searchbackend.php") ?&gt; &lt;?php include("scripts/searchform.php") ?&gt; &lt;?php include("scripts/searchoptions.php") ?&gt; &lt;div id="searchresults"&gt; &lt;center&gt; &lt;?php echo (count($error) &gt; 0)?"The following had errors:&lt;br /&gt;&lt;span id=\"error\"&gt;" . implode("&lt;br /&gt;", $error) . "&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;":""; echo ($resultcount &gt; 0)?"&lt;table id='searchtable' &gt;&lt;thead align='center'&gt;&lt;tr&gt;&lt;th width='50px'&gt;&lt;/th&gt;&lt;th width='50px'&gt;Series&lt;/th&gt;&lt;th width='50px'&gt;Image&lt;/th&gt;&lt;th width='85px'&gt;Model&lt;/th&gt;&lt;th width='50px'&gt;Input&lt;/th&gt;&lt;th width='50px'&gt;Power Supply&lt;/th&gt;&lt;th width='50px'&gt;# of Digits&lt;/th&gt;&lt;th width='50px'&gt;Digit Size&lt;/th&gt;&lt;th width='50px'&gt;Display Color&lt;/th&gt;&lt;/tr&gt;&lt;/thead&gt;&lt;tbody&gt;" . implode("", $brokenresults) . "&lt;/tbody&gt;&lt;/table&gt;":""; //returns results ?&gt; &lt;div id="resultsfooter"&gt; &lt;?php if (count($results) &gt; 5) { echo ($_GET['page'] &gt; 1)?"&lt;br&gt;&lt;a href='/search.php?search={$_GET['search']}&amp;page={$lastpage}'&gt;Previous&lt;/a&gt;" . "&amp;nbsp;&amp;nbsp;Page {$_GET['page']}/{$totalpages}&amp;nbsp;&amp;nbsp;" . "&lt;a href='/search.php?search={$_GET['search']}&amp;page={$nextpage}'&gt;Next&lt;/a&gt;":"&lt;br&gt;Page {$_GET['page']}/{$totalpages}&amp;nbsp;&amp;nbsp;" . "&lt;a href='/search.php?search={$_GET['search']}&amp;page={$nextpage}'&gt;Next&lt;/a&gt;"; } echo ($resultcount &gt; 0)?"&amp;nbsp;&amp;nbsp;&amp;nbsp;|&amp;nbsp;&amp;nbsp;&amp;nbsp;Search found {$resultcount} results in {$execution_time} seconds.":""; //result count and time ?&gt; &lt;/div&gt; &lt;/center&gt; &lt;/div&gt; &lt;/div&gt; &lt;/form&gt; &lt;/div&gt; &lt;div class="footer"&gt; &lt;?php include("include/footer/footer.php") ?&gt; &lt;/div&gt; &lt;/body&gt; </code></pre> <p>As well, this is the contents of my "searchoptions.php" include:</p> <pre><code> &lt;div id="searchoptions"&gt; &lt;fieldset id="searchin" class="searchoptions"&gt; &lt;legend&gt;Serach In&lt;/legend&gt; &lt;input type="checkbox" class="checkbox" name="model" &lt;?php echo isset($_GET['model'])?"checked":''; ?&gt; /&gt;&lt;label class='checklabel'&gt;Model&lt;/label&gt;&lt;br&gt; &lt;input type="checkbox" class="checkbox" name="input" &lt;?php echo isset($_GET['input'])?"checked":''; ?&gt; /&gt;&lt;label class='checklabel'&gt;Input&lt;/label&gt; &lt;/fieldset&gt; &lt;fieldset class="searchoptions" style="&lt;?php if (count($uniqueseries) &lt; 2) {echo "Display: none;";} ?&gt;"&gt; &lt;legend&gt;Series&lt;/legend&gt; &lt;?php echo implode("&lt;br&gt;", $uniqueseries); ?&gt; &lt;/fieldset&gt; &lt;fieldset class="searchoptions" style="&lt;?php if (count($uniqueinputs) &lt; 2) {echo "Display: none;";} ?&gt;"&gt; &lt;legend&gt;Input&lt;/legend&gt; &lt;?php echo implode("&lt;br&gt;", $uniqueinputs); ?&gt; &lt;/fieldset&gt; &lt;fieldset class="searchoptions" style="&lt;?php if (count($uniquepowers) &lt; 2) {echo "Display: none;";} ?&gt;"&gt; &lt;legend&gt;Power&lt;/legend&gt; &lt;?php echo implode("&lt;br&gt;", $uniquepowers) ?&gt; &lt;/fieldset&gt; &lt;input type="submit" class="" value="Update Search" style="" &lt;?php if (!isset($types)) { if (isset($_GET['model'])) {"`model` LIKE '%{$searchSan}%'";} elseif (isset($_GET['input'])) {"`input` LIKE '%{$searchSan}%'";} } ?&gt; /&gt; &lt;/div&gt; </code></pre> <p>I have my latest changes live on our test site which you can reach here: <a href="http://new.foxmeter.com/" rel="nofollow">new.foxmeter.com</a> Try searching "FM" or "4 to 20" as that's what I used in my examples. As of now, "4-20" and "4 20" don't return the same results as "4 to 20" but that I can do and it's not at the top of my list yet.</p> <p>I'd also like previous searches to persist. So the search starts off "4 to 20", then narrows down to only 4 to 20 process meters with "120 V AC with battery backed data" power supply, then narrowing again will keep the "4 to 20 process with 120 V AC with battery backed data" refinement from before WITH new refinements as well. </p> <p>If I've been unclear at any point, I'll be here all day so don't be afraid to question or correct me if you've got an idea for me. Thanks in advance to any help from anyone. I've gotten most of the features for our new site completed except for this search which I've had some trouble with so completing this means my task is (almost) complete.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USZZZZZZZZZZ
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POUsing a generated list of options to refine PHP/MySQL search
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing a generated list of options to refine PHP/MySQL search
      1. USZZZZZZZZZZ
      1. VTFavorite
    1. COthis is just a wall of text, what is your exact problem? show only the relevant pieces of code; what's not working for you? not many people are gonna read this novel...
      singulars
      1. POUsing a generated list of options to refine PHP/MySQL search
      1. USNDM
    2. COIt's in the first paragraph. _The third sentence._ How is this a wall of text? I have my question, my script and a few descriptions.
      singulars
      1. POUsing a generated list of options to refine PHP/MySQL search
      1. USZZZZZZZZZZ
    3. COSorry, I bolded the line that states my goal! Hope its a bit easier now.
      singulars
      1. POUsing a generated list of options to refine PHP/MySQL search
      1. USZZZZZZZZZZ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload