"Filter-function" to generate a selection of pages from a total of 500+

Damn, it's hard to explain in just one subject line what I'm looking for... Here's the long version:

A part of my old website features an alphabetized link list of 500+ record reviews. It's basically an attempt to write an encyclopedia, so now that this project has grown so much through the years I've realized I need a more fancy way of sorting and making selections out of the different albums.

http://thecorroseum.org/comps/index.html

What I want is a line of drop-down menus at the top of the list, with headers like this:

COUNTRY - YEAR - FORMAT - STYLE etc.. ...and then a SEARCH/SUBMIT button.

This way I want to be able to choose to only have a specific selection of records listed. For instance all LPs from France, all American releases from 1985 containing Heavy Metal etc..

Is it doable? Where do I start? Making some sort of database with all the values pertaining to each record is a must I guess? What kind scripts do I need to read up on?

Cheers!
/Total database- and script noob but very motivated to learn.

 

Hi, so create a form with 4 select boxes (COUNTRY - YEAR - FORMAT - STYLE) and a submit button and when the form is submitted (POST request) then query a database based on selected values from the form and then display the result of that select. Form is a "HTML thing" but for database select you will need to use some programming language such as PHP or Ruby.

To get more ideas look for example here: http://www.freewebmasterhelp.com/tutorials/phpmysql/4

Google: PHP MySQL

 

Thanx a lot for the tips! I've already got a MySQL database for the forum attached to the site, so I'm thinking I might just as well use that + learn some more about MySQL and PHP in the process. Will also look into that dataTables+JQuery solution, but I'm less familiar with those tools.

One big problem along the way: (...and PLEASE let me know if it's OK to start a separate thread about this!)

What is the simplest way to extract the relevant data from 500+ html pages and into a database table? Note that all html files are similar in structure and all values that would belong in the same columns are also found between the same tags in all the html files <--this would help, yes?

 

You can write a script with PHP to parse through all the HTML-files, extract the information you want, and put that into the database. Please note that you do NOT want to put image information and such into the database, if there are any - just link the images within the database (a link to where the image is stored) - same for other media-files, that take up a lot of space if they're input into the database. Utilize the file-system for what it's best at.

As long as the HTML-files are similar, or at least contain specific elements that you can hook onto, for parsing, using the built-in PHP DOMDocument-functions should get you through this quite easily. And of course you can create another thread for that specific question, if you need help figuring the PHP out. I would suggest you put that in the correct section, though (PHP, that is)

 

Thanx for the tips so far! I solved the database problem by some search&replace commands on all files + some gadgets like TextMage and managed to combine the data into one CSV-file, and then imported it via MySQLadmin. Now I'm in PHP-school, so expect to hear more from me in that section

 

You did three things that forum members appreciate:

1. You thanked folks for their help;
2. You told everyone how you used their suggestions to solve your problem; and
3. You related your next steps.

Entirely too often, people pop in ask for help, get it and are never heard from again; unless they have another problem. We, and even though I didn't contribute on this thread, I include myself, get a warm and fuzzy feeling when a topic is closed as you did here. So, thank you, too.

I have no doubt that, given your attitude, you will have no problem getting the help you need in the php forum.

cheers,

gary

 

Not to mention that he was able to utilize the help he got, and take the information, and make something by himself, that we didn't really tell him to do. He used the information to elevate his own knowledge, which is VERY much appreciated. We're not here to hold everyone's hand every step of the way - we're here to point in the right direction, give advice (and of course, sometimes code examples showing what to do, by all means) - but what we like most of all are people that can understand general concepts, and twist them into something that works for them. A+ so far.

 

I'm currently experimenting by tweaking code snippets from various instruction videos, just to at least achieve some sort of proof-of-concept. There's some progress, but I've got stuck in what could be a simple html-issue...

How does one set up a form with 4 (or more) separate select-boxes, triggered but just ONE submit, that sends the search-criteria from all boxes at once to the db? I'm getting so many different answers, solutions and opinions on weather this even works at all when googling..

Here's the complete code from the search page.

<?php
//Check if search data was submitted
if ( isset( $_GET['s'] ) ) {
  // Include the search class
  require_once( dirname( __FILE__ ) . '/class-search.php' );

  // Instantiate a new instance of the search class
  $search = new search();

  // Store search term into a variable
  $search_term = htmlspecialchars($_GET['s'], ENT_QUOTES);

  // Send the search term to our search class and store the result
  $search_results = $search->search($search_term);
}
?>
<!DOCTYPE html>
<html>
  <head>
    <title>Search</title>
  </head>
  <body>
    <h1>Search</h1>
    <div class="search-form">
      <form action="" method="get">
        <div class="form-field">
          <label for="search-field">Search</label>
          <select type="search" name="s">
            <option>Italy</option>
            <option>Sweden</option>
            <option>USA</option>
            </select>
          <input type="submit" value="Search">
        </div>
      </form>
    </div>
    <?php if ( $search_results ) : ?>
    <div class="results-count">
      <p><?php echo $search_results['count']; ?> results found</p>
    </div>
    <div class="results-table">
      <?php foreach ( $search_results['results'] as $search_result ) : ?>
      <div class="result">
        <p>
      
        <?php
      
        /// List results as links
      
        echo '<a href="'.$search_result->URL.'">'.$search_result->LinkTitle.'</a>'; ?>
      
        </p>
      </div>
      <?php endforeach; ?>
    </div>
    <div class="search-raw">
      <pre><?php print_r($search_results); ?></pre>
    </div>
    <?php endif; ?>
  </body>
</html>

PHP:

It's working great for just singling out the posts of different countries, but how do I set up the forms if I want more filters?
(Not my code btw, I've only exchanged some parts to at least work with my db)

 

Submitting a form with different select-boxes is default form behavior - ie, all the content will be submitted. However, you're using some sort of search-class, and since you're not providing us with any info about that, it's hard to say how that class handles multiple search terms. It might not do it at all (in which case it's fairly useless). An in-page search is usually better when it's coded to specifically search the site it's on. That way you can create use-cases for every possible potential search you can think of, and tune both your database and your query to work as fast as possible.

Normally, you send a search-query as get (which you do) which results in a query-string added to the url, something like search?criteria1=blah&criteria2=that&criteria3=this and so forth. And depending on how the class you're using is handling that string (splitting on &-chars, for instance, and then parsing each as a key/value pair to match db-columns and values...) there are too many unknowns.

 

Sorry, this is the code for the search-class.php:

<?php
/**
* Performs a search
*
* This class is used to perform search functions in a MySQL database
*/
class search {
  /**
   * MySQLi connection
   * @access private
   * @var object
   */
  private $mysqli;
 
  /**
   * Constructor
   *
   * This sets up the class
   */
  public function __construct() {
    // Connect to our database and store in $mysqli property
    $this->connect();
  }
  /**
   * Database connection
   *
   * This connects to our database
   */
  private function connect() {
    $this->mysqli = new mysqli( 'xxx', 'xxx', 'xxx', 'xxx' );
  }
 
  /**
   * Search routine
   *
   * Performs a search
   *
   * @param string $search_term The search term
   *
   * @return array/boolen $search_results Array of search results or false
   */
  public function search($search_term) {
    // Sanitize the search term to prevent injection attacks
    $sanitized = $this->mysqli->real_escape_string($search_term);
   
    // Run the query
    $query = $this->mysqli->query("
      SELECT Country1, LinkTitle, URL, Year
      FROM comps
      WHERE Country1 LIKE '%{$sanitized}%'
      OR Year LIKE '%{$sanitized}%'
    ");
   
    // Check results
    if ( ! $query->num_rows ) {
      return false;
    }
   
    // Loop and fetch objects
    while( $row = $query->fetch_object() ) {
      $rows[] = $row;
    }
   
    // Build our return result
    $search_results = array(
      'count' => $query->num_rows,
      'results' => $rows,
    );
   
    return $search_results;
  }
}
?>

PHP:

Once again, not my code, merely tweaked to see what can be achieved.

I haven't really used forms much before, so I was of course unsure of exactly how to make them work together w/ php <--a language I'm only just beginning to look into. After a little more googling on multiple forms/one submit button it seems it's no more complicated than this:

<form etc..>
<select name="box1">
   <option>Country 1</option>
  <option>Country 2</option>
</select>

<select name="box2">
  <option>1989</option>
  <option>1990</option>
</select>

<input name="" type="submit" />
</form>

HTML:

I do agree about the benefits of using site-specific code, though for the time being I'd just like to know if the code in the search-class.php file is useful at all to me, or if I need something completely different?

 

You can probably work with the code in the search class, although it will have to be modified to do what you want. Since you seem to be working from select boxes, what you need isn't really a search at all, just a plain fetcher - given that the search boxes are populated from the same database you're fetching from. Search, as it's performed in that class is magnitudes slower than just a fetch based on existing values.

 

Thanx for the tip on 'fetch'! I'm currently googling on examples where and how this is used. Weird how that slipped me by... I guess it's often a question of just finding the right term to search for.

To be continued..

 

Basically, if you're populating the select boxes with content from the database, you don't need to search, you just fetch the rows from the database that matches the selects. That way you don't have to search, and the whole thing will probably be a lot faster.

 

The options of most select boxes matches exactly the values found in the corresponding columns of the db, if that is what's meant by "populated from".
...but then there is the complicated issue of the "Country"-column. In some rows I've entered values like:
England/UK/Western Europe
..because I want this post to appear both in an England-, UK- and Western Europe-selection from the chosen select box. If this causes problem with the fetch-method I guess I could always add on more columns like Country1, Region1, Region2 etc, spread out the data and have one select box filtering through several columns?

Are there any good instructions or examples of this kind of method btw? I'm having a hard time to find something quite similar to what I want out there when googling.