Forums

Form to Search DB Fields & Return Results

NiceIce 14 Jul, 2009
Does anybody have an example of a form that will enable a user to search a DB for data from a few select fields?

I've already created the DB and can connect through ChronoConnectivity (2.0 RC1) I can create forms to submit data, but I'm stuck when it comes to retrieving data and displaying it on a page within Joomla (1.5.9)

Basically I want to create a form which will search the following fields within the DB:

lab_grade_by,lab_report_number,primary_inscription,secondary_inscription,diamond_shape,gem_type

And display the following results based on whether there is a "hit" on any of the information:

lab_grade_by,lab_report_number,primary_inscription,secondary_inscription,diamond_shape,gem_type,date_registered,id

Lab Grade By = Dropdown
Lab Report # = text
Primary / Secondary inscription = text
Diamond Shape = Dropdown
Gem Type = Dropdown
Date Registered = text (this will not be provided by the user conducting the search, I just want it to return as a result if the other data such as Lab Report # returns because the entry exists within the DB.
ID - this is the primary key, same condition on return as Date Registered.

I've already built the form, I just don't know how to make it pull the data and display it when submit is clicked. The search form looks like this:
GreyHead 14 Jul, 2009
Hi NiceIce,

There's a complex search example in this post. It's probably more than you need but there are examples of several different kinds of database filter contained in a single search form and being 'AND'ed together.

Bob
NiceIce 14 Jul, 2009
Thanks Bob, I have read that thread and might be doing it right, I can't tell because every time I click "Submit" the form processes and the home page loads in its place. I figure this is due to a few possibilities which I will begin to explore, but maybe somebody can identify the problem faster than I will be able to poke around in the dark for a solution...

I figure:

1. I need to create a form to receive the search form data?
2. I need to specify 'something' for Redirect URL?

The challenge with the tutorial you reference for people new to CF is that it appears to be written for a previous version of CF and I believe I read somewhere that you indicated the hacks written to work with that tutorial were not to be used with the current version - thus none of the screen shots or directions are really applicable to the current version of CF which makes the discussion void.

I searched the internet and found an example of "how to make a simple search form" that looks like this (with the field names, table settings, etc. specific to the form / table I am working on):

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
     
      $searchSQL = "SELECT id, lab_grade_by,lab_report_number,primary_inscription,secondary_inscription,diamond_shape,gem_type,date_registered FROM simple_search WHERE ";
            
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['lab'])?"`lab_grade_by` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['title'])?"`lab_report_number` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['inscription'])?"`primary_inscription` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['inscription'])?"`secondary_inscription` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['shape'])?"`diamond_shape` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['gem_type'])?"`gem_type` LIKE '%{$searchTermDB}%'":'';
      
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`lab_grade_by` LIKE '%{$searchTermDB}%'"; // use the lab as a default search if none are checked
      
      $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `lab_report_number'"; // order by title.

      $searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['lab_grade_by']}<br />{$row['lab_report_number']}<br />{$row['primary_inscription']}<br />{$row['secondary_inscription']}<br />{$row['diamond_shape']}<br />{$row['gem_type']}<br />";
            $i++;
         }
      }
   }
}

function removeEmpty($var) {
   return (!empty($var)); 
}

?>

<html>
   <title>Search Registered Gems</title>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
   <body>
      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
      <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />
         Search In:<br />
         Lab Graded by: <input type="checkbox" name="body" value="on" <?php echo isset($_GET['lab_grade_by'])?"checked":''; ?> /> | 
         Lab Report #: <input type="checkbox" name="title" value="on" <?php echo isset($_GET['lab_report_number'])?"checked":''; ?> /> | 
         Inscription #1: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['primary_inscription'])?"checked":''; ?> /><br />
         Inscription #2: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['secondary_inscription'])?"checked":''; ?> /><br />
         Gem Shape: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['diamond_shape'])?"checked":''; ?> /><br />
         Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>


But like I said, when Submit is clicked, the form processes and the home page loads. Now I'm wondering whether I should be breaking all of this down into sections of some sort and placing them in different parts of CC / CF for it to all work? If so, can somebody help me break them apart appropriately... I need "CF for Idiots"😲
Max_admin 14 Jul, 2009
Hi NiceIce,

all the code before the
<html>
should go in the onSubmit after email box!

all the code between
<form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
and
</form> 
should go in the HTML code box!

Max
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
NiceIce 15 Jul, 2009
Thanks Max, that totally did it! I'm having one last problem which I'm hoping is minor... Here's the code from the tutorial
<?php
$search_array = array('lab_graded_by','lab_report_number','primary_inscription','secondary_inscription','diamond_shape','gem_data','date_registered','id');
$where = array();
foreach ( $search_array as $search ) {
  $value = JRequest::getVar($search, '' , 'post');
  if ( $value ) {
    $where[] = " $search LIKE '$value%' ";
  }
}
if ( !empty($where) ) {
  echo " WHERE ".implode(' AND ', $where);
}?>


The problem is that if an entry is made for the field "Lab" then ALL of the lines stored in the database show up in the results and I need only the specific line with all the data input by the user to appear when an inquiry is made. If a selection for "Lab" is not made and only the field for inscription (which is almost always unique) then only one line of results appears.

Is there a way to only produce results when ALL of the field criteria is a match within the DB?
GreyHead 15 Jul, 2009
Hi NiceIce,

It sounds as though you need to extend the WHERE clause for the LAB search to include some user identifier? Is there a user_id in the record?

If you turn on Global Debug for the site in the Global Configuration panel then you can see the SQL query that is being created in each case. This can often be helpful in diagnosing what is happening.

Bob
This topic is locked and no more replies can be posted.