Search, Results, Details page - advanced search query question

[dstantdog3]

Hello - noob extreme here. I've spent the last 5 or 6 days reading through online tutorials and using examples to build a simple MySQL database and simple search.php, results.php and details.php pages using Dreamweaver CS3.  I've got the simple results and details pages displaying A-OK.  My problem is I need to add multiple List/Menu/Drop down boxes to the search page. From what I've read I need to create an advanced Query with some "if" & "and" but I'm stumped. I've tried doing this so many different ways my head is spinning!!! I'm pretty sure there is an easy explaination and I appreciate any help in advanced!~

 

MySQL version - 4.1 - successfully connected to database

 

Database is 1 simple table with about 200 records of industrial inventory equipment:

TYPE  |  SKU#  | MAKE  | MODEL | ETC.

------------------------------------------

FORKLIFT|0102|HYSTER|E30XL|ETC.

etc. etc. etc.

 

I want to have 5 or 6 drop down boxes on the search page. The client does not have to click on any of them if they dont want. But they can also click on 1 or 2 or as many as they want to narrow the results down to a specific item or 2.  Search by TYPE, MAKE, FUEL, TIRE, CAPACITY is what I'm going for.

 

The part that's killing me is in the recordset. Do I need more than 1 recordset?  If so do I need multiple recordsets on both search and results pages?  I'm guessing its just my Query is messed up but I've tried it 2 dozen different ways and haven't gotten anywhere!  If a moderator or someone with great expertise can help me through this project I'll glady donate to phpfreaks!!!

 

Here is the Query -

mysql_select_db($database_stysinc7, $stysinc7);
$query_Recordset1 = sprintf("SELECT * FROM stysinc7 WHERE TYPE = %s ORDER BY TYPE DESC", GetSQLValueString($colname_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $stysinc7) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

Here is the Search page form:

<form id="form2" name="form2" method="post" action="inventory3.php">
  <table>
    <tr>
      <td width="70px">TYPE</td>
      <td><select name="TYPE" class="inputbox" id="TYPE"  style="width:190px;" >
        <option value="" selected="selected">All</option>
<option value="FORKLIFT">FORKLIFT</option>
        <option value="TURRET">TURRET</option>
        <?php
do {  
?>
        <option value="<?php echo $row_Recordset1['TYPE']?>"><?php echo $row_Recordset1['TYPE']?></option>
        <?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset1, 0);
  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  }
?>
                    </select>
      </td>
    </tr>
  </table>
    <table>
    <tr>
      <td width="70px">MAKE</td>
      <td><select name="MAKE" class="inputbox" id="MAKE"  style="width:190px;" >
          <option value="" selected="selected" <?php if (!(strcmp("", $_POST['Make']))) {echo "selected=\"selected\"";} ?>>All</option><option value="HYSTER" <?php if (!(strcmp("HYSTER", $_POST['Make']))) {echo "selected=\"selected\"";} ?>>HYSTER</option>
          <option value="TCM" <?php if (!(strcmp("TCM", $_POST['Make']))) {echo "selected=\"selected\"";} ?>>TCM</option>
          <option value="TOYOTA" <?php if (!(strcmp("TOYOTA", $_POST['Make']))) {echo "selected=\"selected\"";} ?>>TOYOTA</option>
          <option value="NISSAN" <?php if (!(strcmp("NISSAN", $_POST['Make']))) {echo "selected=\"selected\"";} ?>>NISSAN</option>
          <?php
do {  
?>
          <option value="<?php echo $row_Recordset1['MAKE']?>"<?php if (!(strcmp($row_Recordset1['MAKE'], $_POST['Make']))) {echo "selected=\"selected\"";} ?>><?php echo $row_Recordset1['MAKE']?></option>
          <?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset1, 0);
  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  }
?>
                    </select>
      </td>
    </tr>
  </table>
  <p>
    <label>SEARCH
    <input type="submit" name="SEARCH" id="SEARCH" value="Submit" />
    </label>
  </p>
</form>

 

 

[fenway]

Sounds like you need to build the where clause on the fly.

[dstantdog3]

when you say on the fly I'm assuming you mean in the code and not in Dreamweaver's wizards?

 

I've read through every SQL manual link listed in the stickies and I spent about 3hrs going through posts back from 2004 on this site trying to find a good example to base mine off of.  I just can't get it to work.  The worst thing is I know it's something really simple too as my Table and Query seem so amatuerish compared to other's peoples posts on this forum.

 

Only the first drop down box filters the search results...the 2nd drop down box does nothing.  I need 5 or 6 drop down boxes!! haha

[fenway]

when you say on the fly I'm assuming you mean in the code and not in Dreamweaver's wizards?

Precisely.

 

Are all of these fields in a single table?

[dstantdog3]

TYPE SKU # MAKE MODEL YEAR MAST TIRE FUEL CAPACITY COMMENTS

FORKLIFT 10449 Hyster  E30XL  0  Cushion  Not Recorded  5400 

FORKLIFT 10448 Caterpillar  2EC15  0  Not Recorded  Not Recorded  5200 

TOW TRACTOR / TUGGER 10447 Caterpillar  NPC60  2000  Not Recorded  Not Recorded  2500 

FORKLIFT 10446 TCM  FCB15E3  1994  Not Recorded  Not Recorded  5000 

 

 

Yes, 1 table. This is what my table looks like.  Using Dreamweavers dynamic table insert, the results.php page displays just fine.  I'm thinking it's my sql query that is the main problem.  I just need to figure out how to filter the following from the search page:

 

Drop down box: Type; forklift, tow tractor, etc.

Drop down box: Make; Hyster, Caterpillar, etc.

Drop down box: Fuel; Battery, Diesel, Electric, gas

Drop down box: Tire; Cushion, Poly, solid

Drop down box: Capacity; >20,000lb, 10,000-20,000lb, 6,000-10,000lb, <6,000lb

 

I need each drop down box to have "all" as the default so someone doesn't need to select a field from each list.

[fenway]

First, "Not Recorded" should be stored as NULL in a database.

 

Second, adding "all" to the default of a dropdown should be easily accomplished in HTML.

 

Third, you need to examine each one of the fields that is POSTed to your form, and build each part of the WHERE clause sucessively.  If it's "all", then leave it out.  You'll also need to decide how "Not Recorded" is treated.  Same goes for Capacity, where you'll have to translate your options.

[dstantdog3]

first off fenway, I appreciate you taking the time to help out.  I know this is a mess since it's coded in dreamweaver and it's not helping that I'm a noob.  I'm trying my best to learn this on my own.

 

Question - So from the 5 search boxes that I listed in my last post, is my SQL query going to be huge? 

 

Also, in my Database, do I need to create multiple tables for each "type" of product available?  ie 1 table for forklift, 1 table for turret, 1 table for reach truck, 1 table for battery, etc.  Right now I just have 1 large table with all inventory in there. Maybe thats my problem.

[fenway]

Question - So from the 5 search boxes that I listed in my last post, is my SQL query going to be huge? 

No, not huge... just one extra "AND" for each chosen drop-down (assuming you want it narrow the results... )

 

Also, in my Database, do I need to create multiple tables for each "type" of product available?  ie 1 table for forklift, 1 table for turret, 1 table for reach truck, 1 table for battery, etc.  Right now I just have 1 large table with all inventory in there. Maybe thats my problem.

That depends... if there are "custom fields" for each product type, then yes.

[dstantdog3]

No - no custom fields at all for any of the product types.  All inventory will carry the same fields in the 1 database table.

 

I am however truely lost in getting my MySQL query to post the selection of the 2nd, 3rd, 4th and 5th drop down boxes. Only that first one works.

 

This is what I have right now:

$query_Recordset1 = sprintf("SELECT * FROM stysinc7 WHERE TYPE = %s ORDER BY TYPE DESC", GetSQLValueString($colname_Recordset1, "text"));

$Recordset1 = mysql_query($query_Recordset1, $stysinc7) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

 

Can you be so kind as to giving me suggestion on what my next(first) step should be in building this query?  Thanks fenway

[fenway]

Like I said earlier, you need to gather all of the individual field restrictions first, and then combine them.

[dstantdog3]

Alright. Finally got this all figured out. Had to do everything by hand and bypass Dreamweaver for some reason.  Anyways...

 

Everything with the inventory search is now working properly.  My next step is to create a "flash navigation image" that lists out each of the 14 "TYPES" of products/services we offer. When you mouse over a certain TYPE the details appear in the middle of the flash which would then be clickable and take you to the .php results page showing only the selected "TYPE". 

 

How do I go about setting this up?  Do I need to use the Query within flash? Do I need to somehow setup category/TYPE ID's in MySQL admin?  ie - link would be:  /inventory_grid.php?category=1    etc. to link to "forklifts" or "reach trucks" or whatever.

 

Any suggestions to lead me on my way would be appreciated!