[SOLVED] using SELECT two ways at once

[gwydionwaters]

hi i would like to be able to have a form that if values are entered, the script will

SELECT * FROM Gear WHERE cost='$cost' OR name='$name' OR type='$type' ORDER BY type

and if nothing is entered into those values from the form just do

SELECT * FROM Gear ORDER BY type

thanks for reading

[xtopolis]

Sounds like a good idea.  What does your form code look like so far?

 

If you haven't started.. you will need a form the does the following:

1) Accept 3 inputs, one for cost, name, and type. [or none]

2) PHP to validate the input (cost is a number/currency, name contains valid characters, type is a valid type)

3) Construct the query.

  a) The query will always have your base: "SELECT * FROM Gear"

  b) The query will always have your order: " ORDER BY type"

  c) You will only fill in the middle if 1 or more options are selected.

4) Options selected?

  a) Yes.  Query from 3a) + " WHERE option='$optionvalue'"

  b) More than one?  3a) + 4a) + " OR option='$optionvalue'"

 

5) Combine all necessary queries (usually as you go along)

  a) The query will always be at least: 3a) + 3b).  You will add stuff to the middle if the user requests it.

 

Hope that gets you going.

[gwydionwaters]

thanks for the guidelines, i'm still learning php (started a few day's ago) so it might be harder than it sounds lol my form actually has more values than those mention it looks like this

<form name="form" action="gearsearch.php" method="post">
   <table align="center" width="60%" border="0">
    <tr>
     <td align="center">By Name<BR><input type="text" name="Name"></td><td align="center">Author<BR><input type="text" name="Author"></td><td align="center">Keyword<BR><input type="text" name="Key"></td>
    </tr>
    <tr>
     <td align="center">By Type<BR><select name="Type"><option value="" selected="selected">No Type</option><option value="Knife-Fixed">Knife - Fixed</option><option value="Knife-Folding">Knife - Folding</option><option value="Stove-Canister">Stove - Canister</option></select></td><td align="center">By Use<BR><select name="IUse"><option value="" selected="selected">No Use</option><option value="BackPacking">BackPacking</option><option value="Ultra">UltraLight</option><option value="Multi">Multi-Purpose</option></select></td><td align="center">By Cost<BR><select name="Cost"><option value="" selected="selected">No Cost</option><option value="10">$10 or less</option><option value="20">$20 or less</option><option value="30">$30 or less</option><option value="40">$40 or less</option></select></td>
    </tr>
    <tr>
     <td colspan="3" align="center">By Rating   <select name="Rating"><option value="" selected="selected">No Rating</option><option value="10">10/10</option><option value="9">9/10</option><option value="8">8/10</option><option value="7">7/10</option><option value="6">6/10</option><option value="5">5/10</option><option value="low">< 5/10</option></select></td>
    </tr>
    <th colspan="3"><input type="submit" name="Submit" value="Search" /></th>
   </table>
  </form>

and my search page script is

<?
include("dbase.incl.php");

$Name=$_POST['Name'];
$Author=$_POST['Author'];
$Type=$_POST['Type'];
$IUse=$_POST['IUse'];
$Cost=$_POST['Cost'];
$Rating=$_POST['Rating'];
$Description=$_POST['Description'];
$Key=$_POST['Key'];

mysql_connect($hostname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM Gear  WHERE IUse='$IUse' OR Type='$Type' OR Cost='$Cost' OR Rating='$Rating' OR Author='%$Author%' OR Name='%$Name%'";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();
?>
<BR>
<center><H3>Gear Database Search Results</H3></center>
<BR><BR><BR>
<div style="overflow:auto;">
<table border="0" cellpadding="2">
  <th><H4>Name</H4></th>
  <th><H4>Author</H4></th>
  <th><H4>Type</H4></th>
  <th><H4>Use</H4></th>
  <th><H4>Cost <= $</H4></th>
  <th><H4>Rating X/10</H4></th>
  <th><H4>Description</H4></th>
<?
$i=0;
while ($i < $num) {

$Name=mysql_result($result,$i,"Name");
$Author=mysql_result($result,$i,"Author");
$Type=mysql_result($result,$i,"Type");
$IUse=mysql_result($result,$i,"IUse");
$Cost=mysql_result($result,$i,"Cost");
$Rating=mysql_result($result,$i,"Rating");
$Description=mysql_result($result,$i,"Description");
?>

  <tr>
   <td><? echo $Name ?></td>
   <td><? echo $Author ?></td>
   <td><? echo $Type ?></td>
   <td><? echo $IUse ?></td>
   <td align="center"><? echo $Cost ?></td>
   <td align="center"><? echo $Rating ?></td>
   <td><? echo $Description ?></td>
  </tr>
<?
$i++;
}
echo "</table></div>";
?>

i am not currently using the $Key variable because i can not figure out how to use it to search all columns at once. also the text box queries in the form seem to return no results. only the selection ones will work. but you can use only one or more than one of them like i want it to do

[xtopolis]

Ah.. I see.

 

Before we continue, I'd like to point you to this article here: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

It covers a very important topic called  (database) "normalization".  It looks like in your query you have all the data lumped into one table.  You can still do stuff with it, but it will hurt you later on to leave it like that.

 

 

Regarding your searching:

The drop downs "Type" and "Use" should probably be built from the database by polling for a list of each and building the select menu out.  This means using PHP and Mysql to generate your lists for you.  Also instead of putting a value like "Knife-Fixed", you should reference a 'typeid' which would refer to the table that contains types. [this will make sense if you read the normalization article]

 

Cost is ok, depending on how you use it...same with rating.

 

Name(should be title), Author, Keyword are different types of searches.  Here you're not looking for an exact value, but something that is LIKE it, or contains it.

In these cases you would probably use: "WHERE Name LIKE '%$name%'".  I think you had the right idea with the ='%name%'.. but I'm unsure that that's the proper syntax.

 

 

All this being said, you have the right ideas, just gotta tweak them around to make them more manageable.

[gwydionwaters]

i'll read over that article and get back to our convo thanks

[gwydionwaters]

so i've broken it down into multiple tables;

now there is a table each for: cost, use(which is now func), type, rating(now rate), and one called index for title, author and description. but i don't know where to go next, like joinging them. i am tired so i will have to pick it up later.

[xtopolis]

Well, the best way is to start small.

 

First, learn to build your Select boxes (Type, Use).. from mysql data.

-Query all the types, echo out them with a value of (typeid [whatever you call it]) and typename[name like Knife-Fitted].

Same with the use, but start with one at a time.

 

Then when you have it building that select box, test it to see that it works.  Select a type and have it look up any items with that type. (the hint is: join the items table with the type table based on items.typeid = types.typeid).

 

Good luck, let us know if you get stuck.

[gwydionwaters]

ok ..

so far i have redone my script, which is now many smaller scripts. one for the form (which is dynamicaly generated ) one for search, which uses a keyword *Problem* i can't figure out how to search a few columns with one variable at the same time. here is the search script

<?
include("dbase.incl.php");
mysql_connect($hostname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM Gear WHERE Author='%$Key%' OR Name='%$Key%' OR Description='%$Key'";
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();
?>

there is also one for showing the whole database (which works fine)

and one for browsing depending on what you chose from the form page (not worried about that one yet)

also i was wondering, about my dynamic form script, how do you stop the same value from being listed more than once. i have more than one thing by the same author and so the select box for author shows three of the same author, i would like to show only one for every value. the code for the form is here

     <td colspan="2" align="center">
      <form name="browse" action="scripts/gearbrowse.php" method="post">Author<BR><select name="Author">
     <?
  $i = 0;
  while ($i < $num) {
	  $Author=mysql_result($result,$i,"Author");
 ?>
     <option value="<? echo $Author; ?>"><? echo $Author; ?></option><? $i++ ; } ?></select></td>

 

thanks for helping me with all this by the way

[fenway]

You can use GROUP BY as required in your mysql query, but that really depends on how you're dealing with the downstream output.

[gwydionwaters]

ok, so i switch to my normalized database and now i don't know how to query it at all lol

i have the db gear;

tables are:

index=item_id,Title,author_id,Description

then i have one table each for author, cost, function, type, and rating.

and a linking table for each as index_cost index_func index_type and index_rate

i have left out author because the id is in the index and i have no use for an author on it's own like i do with cost, type, etc.

how would i just show my whole db? like i used to with

SELECT * FROM Gear

and all fields were in one table. now everything is broken up it is faster and more efficient but also more confusing

[gwydionwaters]

this discussion has been moved here

 

http://www.phpfreaks.com/forums/index.php/topic,231345.0.html

 

this post was becoming very confusing