Help with drop down list and keyword search.

[Motorider]

I am seeking help to create a html search form that has a drop down box listing tables within a mysql database that contain parts for different makes of buses.

The idea is that the drop down list would select the table and the part would be found within the selected table by keyword description or part number with a text box.

The table catagories and contents can be seen here. http://www.jimsbusparts.com

A working simple search without the drop down can be seen here. http://www.jimsbusparts.com/search1.html

My html is;

<form action="results.php" method="post"> 
    
    <p>
      <select name="select" id="select">
        <option>MCI</option>
        <option>GM</option>
        <option>Engines</option>
      </select>
    </p>
    <p>Part Name: 
      <input type="text" name="term" />
      <br /> 
   
      <input type="submit" name="submit" value="Submit" /> 
        </p>
  </form>

My php code so far is;

mysql_select_db("xxxxxxx", $con);

$term = $_POST['term'];

$sql = mysql_query("select * from MCI where Description like '%$term%'");

while ($row = mysql_fetch_array($sql))
{
	echo 'ID: '.$row['ID'];
	echo '<br/> Part No: '.$row['Part No'];
	echo '<br/> Description: '.$row['Description'];
	echo '<br/> Price: '.$row['Price'];
	echo '<br/><br/>';
	}

?>

I realize that there is more involved regarding form validation and security but I am having trouble wrapping my mind around php coding. If someone could first help me get the basic search working step by step and then add the refinements after I think I might be able to grasp it.

I would be most grateful.

What's the problem? Seems to work. Do you want an autosuggest text box once the table has been selected? If so, there are hundreds of free scripts out there. If you want to do this you'll need to use Ajax, but a framework like Prototype or JQuery can do most of the hard work for you.

With regard to security, I'd suggest (as a start) that you clean the input from the form ($_POST) - you'll need to 'real_escape' it or at least 'addslash' and 'html_entities' it.

///SORRY!

I see your point now, the search table doesn't change as you change the dropdown.

<select name="select" id="select">
        <option value="MCI">MCI</option>
        <option value="GM">GM</option>
        <option value="Engines">Engines</option>
</select>

Then in your form handling script:

$table = $_POST['select'];

$sql = mysql_query("select * from {$table} where Description like '%$term%'");

This ain't that secure, so you'll need to check that the table exists and 'clean' the table name.

[Motorider]

Ardav you the man! I once again feel like I am getting somewhere. However, now that the drop down is selecting the tables, the keyword search is not functioning. It pulls all records within the designated table ignoring the keyword and or part number. Any suggestions?

I am a newly certified web designer but the school I went to didn't cover any programming lessons other than web page design. Since I am out of money I am hoping I can learn PHP on my own. Or rather, with help from folks such as yourself. I thank you!!

I understand what you mean by ($_POST) cleanup but, I am lost as to what you mean by 'real_escape' , 'addslash' and 'html_entities' .

Thanks again for your help.

I assume you forgot to include the $_POST in your handling script.

Do this:

<form action="results.php" method="post"> 
    
    <p>
      <select name="select" id="select">
        <option value="MCI">MCI</option>
        <option value="GM">GM</option>
        <option value="Engines">Engines</option>
      </select>
    </p>
    <p>Part Name: 
      <input type="text" name="term" />
      <br /> 
   
      <input type="submit" name="submit" value="Submit" /> 
        </p>
  </form>

Your form handler code:

//the escape functions 'clean' the input
$table = mysql_real_escape_string($_POST['select']);
$term = mysql_real_escape_string($_POST['term']);

$sql = mysql_query("select * from {$table} where Description like '%$term%'");

while ($row = mysql_fetch_array($sql))
{
	echo 'ID: '.$row['ID'];
	echo '<br/> Part No: '.$row['Part No'];
	echo '<br/> Description: '.$row['Description'];
	echo '<br/> Price: '.$row['Price'];
	echo '<br/><br/>';
	}

?>

//JUST A THOUGHT, why have you got 3 tables for the same data fields?? It would be much better for you to create a relational model:

TABLES:
Products
Product_Type

(it could be argued that you should split your tables further with numbers and prices being separated - but we'll keep it simple).

products Table Fields:

prod_id: autoincrement; integer
part_no: varchar (20) - your part numbers
quantity: tinyint (2 or 3) depending on max no. in stock
description: text
condition: varchar (4) - good / used / 'n/a'
price: decimal (7,2) - this should be a fixed length decimal with NO $ sign
product_type_id: tinyint (1) - foreign key on the type_id field in the next table

product_type Table Fields:
type_id: autoincrement; tinyint (1)
label: varchar (40) - name of the type or category of product

Now you can add to the product type field as and when a new category or product type rears its head.

You can also dynamically produce your select dropdown - no need to change it every time a new category is added to the db:

<select id="select" name="select">
<?php
   $q = "SELECT * FROM product_type";
   $r = mysql_query($q);
   $option_string = "";
   while($data = mysql_fetch_array($r)){
       $option_string .= "\n\t<option value=\"{$data['type_id']}\">{$data['label']}</option>";
   }
   echo $option_string;
?>
</select>

Then in your data handling script:

$product_type_id = mysql_real_escape_string($_POST['select']); //this is the 'id' (a number)
$term = mysql_real_escape_string($_POST['term']);

$sql = mysql_query("select * from products where Description like '%$term%' AND product_type_id = '{$product_type_id}'");

while ($row = mysql_fetch_array($sql))
{
	echo '<br/> Part No: '.$row['part_no'];
	echo '<br/> Description: '.$row['description'];
	echo '<br/> Price: '.$row['price'];
	echo '<br/><br/>';
	}

?>

I haven't tested the above - it's off the top of my head, but it's pretty close. I didn't include the preparation of the 'label' data in the form, you could probably do this by using 'stripslashes' - see the php manual for details.

Good luck.

[Motorider]

I do have the $_POST in the file so I am assuming that I have a syntax error somewhere.

I started the project this way because I thought this would be the elementary way to begin learning and do have plans to improve it to a relational database as I move along. This is my dads website and while the scroll bars are functional It seemed to be the ideal real world situation to learn from. Perhaps I am taking the wrong approach to learn PHP.

I appreciate your comments because it seems to make more sense to me as I further investigate the manual and other resources.

Thank you.

[Josh Connerty]

I do have the $_POST['term'] in the file so I am assuming that I have a syntax error somewhere.

I started the project this way because I thought this would be the elementary way to begin learning and do have plans to improve it to a relational database as I move along. This is my dads website and while the scroll bars are functional It seemed to be the ideal real world situation to learn from. Perhaps I am taking the wrong approach to learn PHP.

I appreciate your comments because it seems to make more sense to me as I further investigate the manual and other resources.

Thank you.

If it a parse error it would state so. If it doesn't either say Fatal Error: or Parse Error: .......... On Line: ... In *Document Name* or something similar.

If neither of these ar returned then it is more likely a fault in the logic as to why it is not working.

What exactly is it that is not working?

[Motorider]

The drop down chooses the appropiate table and the keyword text box should cull the appropiate part from the selected table. For example, if MCI is selected in the drop down and the keyword is hose, the results should list all of the hoses in the MCI table.

Or, if MCI is chosen in the drop down and a part number is entered into the keyword text, it should return the corresponding part as a result. (I havent entered the part numbers into the table yet)

Thanks for your response.

[Josh Connerty]

Okay then in that case you will need to use AJAX, are you aware of this?

You will need to add a javascript event to your "term" text box.

<input type="text" name="term" onkeyup="javascript: returnTerms( $("#select").val() );" />

Now every time you enter a key into this text box we will make javascript contact your PHP document to retreive the relevent information.

Now if you are happy to do a little learning then I will continue on your say. I will be using jQuery as I program JavaScript with the jQuery library.

jQuery: http://jquery.com/

So if you are happy to use this method then I will continue.

[Motorider]

Ok Josh I have read through the jquery website and I am going to need your help to continue. If your still willing.

I have added the above code to my search form.

Before you start, be aware that you may be 'bloating' your app without need. The basis for a good app/site is solid data structure. I don't think you've got it yet. If your data structures are all to hell, a javascript framework won't help.

I'd suggest redesigning a streamlined version of your tables - make them relational (as I suggested earlier). If you need to add ajax later on, fine, your database will be set up to deal with it. Ajax ain't a magic bullet.

Good luck again!

PS. @ Josh - sorry mate, didn't mean to diss your suggestion.

[Motorider]

ardav,

Thank you for your response. I am so sorry that it is taking so long to resolve this. Demands have been placed on me this week.

I have set up the tables as you have suggested, except leaving out the quantity and condition fields within the products table and have uploaded a database.

If I did this correctly the label field contains MCI GM and Engines in the product_types table.

I am unsure how to set up the id fields.

Thanks again.

[Josh Connerty]

Before you start, be aware that you may be 'bloating' your app without need. The basis for a good app/site is solid data structure. I don't think you've got it yet. If your data structures are all to hell, a javascript framework won't help.

I'd suggest redesigning a streamlined version of your tables - make them relational (as I suggested earlier). If you need to add ajax later on, fine, your database will be set up to deal with it. Ajax ain't a magic bullet.

Good luck again!

PS. @ Josh - sorry mate, didn't mean to diss your suggestion.

No youre right in what you are saying, however I think what he is wanting is something that will perform these things without page refresh.

Okay, please export your database using phpmyadmin and then post it here so as I know exactly what you database structure is liek and as ardav said try and make it more efficient.

[Josh Connerty]

Another point, with MySql the advice given is you use as many tables as possible as to releive the stress on any one particular table.

[Motorider]

prod_id	part_no	description          price product_type_id
0	23501554Fuel crossover hose	              0	0
0	9 r 512	primary pump filter hose	0	0
0	4l 20 199	Air compressor hose	0	0
0	10g 264	Transmission to filter hose	0	0
0	16k 11 91	Discharge compressor	0	0
0	6k 71	Air compressor hose	0	0
0	10 g 257	Air compressor hose	0	0
0	700092	Discharge compressor	0	0


type_id	label
0	GM
0	MCI
0	Engines

If you don't have a product_type_id linked to the type_id you're buggered.

Change the type_id datatype to:

tinyint(2) unsigned PRIMARY KEY auto_increment

This will populate the data_id with a unique value (starting with 1).

Although this is a bit of a muddle, memorize that GM = 1 and then go through your other table changing the product_type_id field for all GM products to 1. Do the same for MCI (=2) and Engines (=3). CHECK that these are the values assigned before changing the other table!

Now your table should be relational. Although it may look weird, believe me, the alternative would send you to hari kari after ten records.

You can now access your data thus:

$mytype = $_POST['mytype']; //this is an integer
$term = $_POST['desc']; //this is a string

$sql = mysql_query("select * from products where Description like '%$term%' AND product_type_id = '{$mytype}'");";

(etc)

Remember that this method will allow you to add any number of product types in the future. You can create the form options 'statically':

<label for="mytype">Type:</label>
<select name="mytype" id="mytype">
    <option value="1">GM</option>
    <option value="2">MCI</option>
    <option value="3">Engines</option>
</select>

<label for="desc">Description:</label>
<input type="text" name="desc" id="desc" />

BTW:
phpmyadmin (pma) is a real pain. Perhaps you'd be better off betting a Windows-based database front end like SQLyog (free) from Webyog - it's similar to Access with regard to table creation and data filling, so much easier than pma.

[Motorider]

Oh boy Iv'e done something wrong I get;

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/j/i/m/jimsbusparts/html/results2.php on line 30

Line 30 is;

echo 'ID: '.$row['ID'];

This usually happens when you've placed a variable within a string without bracing it out (if I remember). Check to see you've got a ';' at the end of the preceding line. Just 'coz it says line 30, doesn't mean to say that the error occurs at line 30. It could be a few lines before that - e.g. if you forget a ';' or forget a closing brace '}' or put an extra one in! Quotes, concatenators (or dots .), semicolons, braces are usually the culprits.

For your problem, do you have something like '$row'?

I usually 'brace-out' all my variables within double quotes, whether they need it or not (I'm a creature of habit). echo "ID: {$row['ID']}"; This means that I can use single quotes anywhere within the string and it won't affect my variable, well as long as I don't mess with the stuff inside the braces. It's horses for courses, everybody's got their own pet way of building strings.

[Motorider]

Okay solved the error. It was the extra quote and semicolon;

$sql = mysql_query("select * from products where Description like '%$term%' AND product_type_id = '{$mytype}'");";

Now my DB connects, but echos the headings without the retrieved results.

ID:
Part No:
Description:
Price:

Am I correct to believe the that there is still an error within the above mysql_query statement? Or, did I not link up my tables correctly? Also the prod_id within my products table has no data or value.

Oh my head hurts. LOL

$sql = mysql_query("select * from products where Description like '%$term%' AND product_type_id = '{$mytype}'");

That's it. Have you manually inserted values into the products table? If not, they will be blank.

[Motorider]

I did manually insert the corresponding values in the product table.

Following the suggestions in post #4 I setup my products table with a column for prod_id. Perhaps you suggested this because I did have an id field in the old table. May I delete this column because I don't need it. And if this is not the cause can I assume that I need to set my primary key again in the product_types table?

IMO: nearly every table needs a primary key (usually an id set to autoincrement) - the prod_id field in the products table is such a field. DO NOT get rid of this. You will not be accessing this in the implementation I set out anyway, although it may be useful in the future when you develop the app further, by creating delete product or edit product forms.

The related fields are the product_type_id (in the products table) and the type_id (in the products type table).

The example I gave you should work. If it doesn't, you're not far off. Just keep with it. You'll get there in the end.

[Motorider]

Final error solved. The offenses were the capitol letters in echo results. (see code first post's)

while ($row = mysql_fetch_array($sql))
{
    echo 'ID: '.$row['ID'];
    echo '<br/> Part No: '.$row['Part No'];
    echo '<br/> Description: '.$row['Description'];
    echo '<br/> Price: '.$row['Price'];
    echo '<br/><br/>';
}

Since the label fields in the tables are lower case.

Works Great!