Drop down menus and searching the DB

[Chrisjc]

I would first like to say that I am very new to PHP/MySQL...

I have a database full of infomation already. to view what my database looks like and how it is set up please see the following link ( www.triplesource.net/db/ )

As far as what I have so far this is what I can get my drop down box to do...

( http://www.triplesource.net/php/) Now that is live from the DB it is pulling from the table from ROW "year" how ever... I need it to be showing FIXED options... (I.E.) ( http://www.triplesource.net/php/year%20and%20make.htm ) This is the fixed format I would like... Now the thing I dont know how to do is code it so that php will know the user lets say picked " 2004" and "Honda" now once they choice that they would click a Submit button that will then search my Database and pull all Part numbers that match " 2004 Honda" I know this is very possiable I am just very new to understand such a complex code... There for I am seeking help of others It would really be a grate help if someone can help me out please.

Here is the example of my code

[PHP]<?php
// Make a MySQL Connection
include ('db functions/db_connect.php' );

echo 'Connected to Database!';

echo '<p>&nbsp;</p>';

echo "<select name=D1>";
while($row = mysql_fetch_arr ay($result)){


echo "<option value=$row[YEAR]>$row[year]</a></option>";
}
echo "</select>";
?>
[/PHP]

and here is a second code I started to play with.

[PHP]<?php
// Make a MySQL Connection
include ('db functions/db_connect.php' );

echo 'Connected to Database!';

echo '<p>&nbsp;</p>';

$sql="SELECT id, year FROM Catalog";
$result=mysql_q uery($sql);

$options="";


while ($row=mysql_fet ch_array($resul t)) {

$id=$row["id"];
$thing=$row["year"];
$options.="<OPT ION VALUE=\"$id\">" .$year.'</option>';
}
?> [/PHP]

[HTML]<SELECT NAME=year>
<OPTION VALUE=0>Choose
<?=$options?>
</SELECT>[/HTML]


Thanks in advanced!

Chris

[ronverdonk]

In order to have PHP process the selected choices you must have the form submitted. Since the form is client-side and PHP is server-side you must pass the values from client to server, hence the submit.

On the other hand, you can use pure JavaScript (client-side) to extract these selections, but question is: when does JS know when both fields are filled in and it can trigger the PHP search program?

Then there is a combination of JavaScript and PHP, Ajax. For this, have a look at the Ajax "Dynamic Client Lookup" and "Ajx Dynamic List" demos at Ajax scripts

That shows how your problem could be tackled, only for you with 2 select boxes.

Ronald :cool:

[Chrisjc]

That kind of helped but not really the example used STATIC INPUT which didn’t help me because I could do the same thing... The biggest question I have is how I can get my code to spit out a Product number after they are done searching... I don’t know how to set that up... And because they used static input I can’t really see how I would come across that goal... I could connect to the DB and have it pull a list but see if I did that for ( YEAR ) You would get something that looks like this ( www.triplesourc e.net/php/ ) and that is live from the DB from ROW (YEAR) I need it to be 1955 - 2007 in a neat line like that then SEARCH that ROW (YEAR) pull all that match with the users pick lets say 2005 so it would pull all (2005) and eliminate the other years... and now wait for the user to pick (MAKE) lets say they picked HONDA now it eliminates all other MAKES and leaves HONDA there and once the user is done picking everything it will give them a list of part numbers that match that input they gave in the drop down boxes...

That example was a bit of a help for sure but I don’t want to make a static choice list when the Database is already made for me and all I need it to do is show then that part number... I hope someone can help me with this! Thanks!

Chris

[Chrisjc]

Here to give a way better example of what I am trying to do...

go to (http://www.aempower.co m/)

That is exactaly what I want... but with a few more options on mine.... but thats how I want it to work...

[ronverdonk]

I don't think you looked closely at the sample I linked you. They do not use static data, they collect the data from the MySQL database real-time.

A php file getClient.php is used in this script. This PHP file is called by Ajax when a new client ID is entered into the first text field. It will connect to the database and try to find a client with the entered client ID. If one is found, it will send data back to AJAX. These data will evalulated by our main script.

You can build on that example to get what you want.

-  connect to db and select years in table,
-  build dynamic drop down list of years,
-  setup JS (Ajax) catcher for select box event
-  request user to fill in year
 
Selection triggers the Ajax process as follows:
-  JS routine is triggered and sends YEAR to server PHP script
-  PHP script pulls make from the data base
-  PHP sends result back to via JS
-  JS/HTML displays make in 2nd select box select
-  request user to select the make
 
When make selected:
-  JS routine triggered passing year and make to PHP script
-  PHP connects to db and pulls data
-  PHP send results back to JS
-  results are displayed

Ronald :cool:

[Chrisjc]

Now when you say make JS ( Ajax) script.... This is where I get all stoped up at... I don't know any Java at all..... and I am also new at php.... maybe I know enough to get me by to connect to the DB and pull the year row... I have done that... what I dont understand still is how to make that code show this

[HTML]
<select class="VehicleS earch" style="WIDTH: 155px">
<option value Selected >Select Year</option>
<option value="2006" >2006</option>
<option value="2005">20 05</option>
<option value="2004">20 04</option>
<option value="2003">20 03</option>
<option value="2002">20 02</option>
<option value="2001">20 01</option>
<option value="2000">20 00</option>
<option value="1999">19 99</option>
<option value="1998">19 98</option>
<option value="1997">19 97</option>
<option value="1996">19 96</option>
<option value="1995">19 95</option>
<option value="1994">19 94</option>
<option value="1993">19 93</option>
<option value="1992">19 92</option>
<option value="1991">19 91</option>
<option value="1990">19 90</option>
<option value="1989">19 89</option>
<option value="1988">19 88</option>
<option value="1987">19 87</option>
<option value="1986">19 86</option>
<option value="1985">19 85</option>
<option value="1984">19 84</option>
<option value="1983">19 83</option>
<option value="1982">19 82</option>
<option value="1981">19 81</option>
<option value="1980">19 80</option>
<option value="1979">19 79</option>
<option value="1978">19 78</option>
<option value="1977">19 77</option>
<option value="1976">19 76</option>
<option value="1975">19 75</option>
<option value="1974">19 74</option>
<option value="1973">19 73</option>
<option value="1972">19 72</option>
<option value="1971">19 71</option>
<option value="1970">19 70</option>
<option value="1969">19 69</option>
<option value="1968">19 68</option>
<option value="1967">19 67</option>
<option value="1966">19 66</option>
<option value="1965">19 65</option>
<option value="1964">19 64</option>
<option value="1963">19 63</option>
</select></td>
[/HTML]

and let it actaully say okay this person picked 1964.... Now lets show MAKE and then okay they said they have a HONDA.....

[PHP]
<?php
// Make a MySQL Connection
include ('db functions/db_connect.php' );

echo 'Connected to Database!';

echo '<p>&nbsp;</p>';

echo "<select name=D1>";
while($row = mysql_fetch_arr ay($result)){


echo "<option value=$row[YEAR]>$row[year]</a></option>";
}
echo "</select>";
?>
[/PHP]

That is the code I used to pull the ROW (year) in my DB..... how ever like I said how do I get it to show the dynamic selection of what I do want it to show...


Sorry I dont really know anyother way to explain what I am thinking all I can hope for is that you understand what I am trying to say :) thanks for your help!

ps the link you gave me only got me this here (http://www.dhtmlgoodies.com/scripts/...ed-select.html) which was close to what I was looking for but I dont see where you got that comment from ???

[Chrisjc]

I have a good feeling that I am very lost... this is what I was thinking from your post... and from seeing the example...


[PHP]<?php
// Make a MySQL Connection
include ('db functions/db_connect.php' );

echo 'Connected to Database!';

echo '<p>&nbsp;</p>';

echo "<select name=Year style="width 160">";
while($row = mysql_fetch_arr ay($result)){


echo "<option value>Select Year</option>
<option value="2006">20 06</option>
<option value="2005">20 05</option>
<option value="2004">20 04</option>
<option value="2003">20 03</option>
<option value="2002">20 02</option>
<option value="2001">20 01</option>
<option value="2000">20 00</option>
<option value="1999">19 99</option>
<option value="1998">19 98</option>
<option value="1997">19 97</option>
<option value="1996">19 96</option>
<option value="1995">19 95</option>
<option value="1994">19 94</option>
<option value="1993">19 93</option>
<option value="1992">19 92</option>
<option value="1991">19 91</option>
<option value="1990">19 90</option>
<option value="1989">19 89</option>
<option value="1988">19 88</option>
<option value="1987">19 87</option>
<option value="1986">19 86</option>
<option value="1985">19 85</option>
<option value="1984">19 84</option>
<option value="1983">19 83</option>
<option value="1982">19 82</option>
<option value="1981">19 81</option>
<option value="1980">19 80</option>
<option value="1979">19 79</option>
<option value="1978">19 78</option>
<option value="1977">19 77</option>
<option value="1976">19 76</option>
<option value="1975">19 75</option>
<option value="1974">19 74</option>
<option value="1973">19 73</option>
<option value="1972">19 72</option>
<option value="1971">19 71</option>
<option value="1970">19 70</option>
<option value="1969">19 69</option>
<option value="1968">19 68</option>
<option value="1967">19 67</option>
<option value="1966">19 66</option>
<option value="1965">19 65</option>
<option value="1964">19 64</option>
<option value="1963">19 63</option>
</select></td>
}
echo "</select>";
?>
[/PHP]


no idea??? I think I am lost...

[Chrisjc]

Okay so now I have stoped because well this is what I have....

my database line looks like this (www.triplesour ce.net/db/ )

Okay so now With what I have is this ( www.triplesourc e.net/php/php0/ )

Now see that is Static because I placed all of that in the get.php file... see the thing is when you select year.. it does nothing.... just sits there.... if we had a 2007 BMW model being M3... but because the 2007 does nothing it will not display the right thing... what if they didnt make a 2007 BMW....

here is the code for what gets pulled after the make is selected...

[PHP]<?php

if(isset($_GET['countryCode'])){

switch($_GET['countryCode']){

case "ACURA":
echo "obj.option s[obj.options.len gth] = new Option('RSX','3 ');\n";

break;
case "AMERICAN MOTORS":
echo "obj.option s[obj.options.len gth] = new Option('CLASSIC ','11');\n";
echo "obj.option s[obj.options.len gth] = new Option('CONCORD ','12');\n";
echo "obj.option s[obj.options.len gth] = new Option('EAGLE', '13');\n";
echo "obj.option s[obj.options.len gth] = new Option('GREMLIN ','14');\n";

break;
case "AUDI":
echo "obj.option s[obj.options.len gth] = new Option('A4','23 ');\n";
echo "obj.option s[obj.options.len gth] = new Option('A4 QUATTRO','20'); \n";
echo "obj.option s[obj.options.len gth] = new Option('80 QUATTRO','21'); \n";
echo "obj.option s[obj.options.len gth] = new Option('90 QUATTRO','22'); \n";
echo "obj.option s[obj.options.len gth] = new Option('100 QUATTRO','23'); \n";
echo "obj.option s[obj.options.len gth] = new Option('200 QUATTRO','24'); \n";

break;
case "BMW":
echo "obj.option s[obj.options.len gth] = new Option('','');\ n";
echo "obj.option s[obj.options.len gth] = new Option('','');\ n";
echo "obj.option s[obj.options.len gth] = new Option('','');\ n";
echo "obj.option s[obj.options.len gth] = new Option('','');\ n";
[/PHP]
See how we have (AUDI) well if I picked 2005 I am still going to get all those models listed when they dont make a 80 QUATTRO in 2005... so This is why I need it to pull from my database because if it did it would say okay all (2005)
now all AUDI for year 2005 okay
now all AUDI with year 2005 submodel
now all AUDI 2005 A4 with FUEL GAS

and so on once if got to the end of the select it will have only what it has found in the db..... and display Part Number: 10-54001

Thats the goal.... how ever I need to drop downs to show the right info at the right time.... like they have to be live.... so 2007>AUDI>Selec t Model is now live with only showing 2007 AUDIS there for should only show A4.... This is where I need the help....

Please advice...

Sorry for being a bit of a nag but no tut shows things like this.... Thus I am searching for live help... Thank you

[ronverdonk]

I am sorry, I guided you to another sample then I had in mind. But still, you can do this easily in Ajax. Now I have a little snippet for you (2 actually: 1 client-side and 1 PHP server-side). Only, to display the code here takes some space, client side code is 71 lines, server side code 32 lines. However, test table 'cars' setup is included.

What it does:
1. go to the server and requests all years in the cars table (grouped by).
2. build a drop down list of the years found
3. upon select of a year goes to the server and selects all cars of that year
4. builds a drop down list of these cars
5. upon select of a car, goes to the server and gets the description for that car of that year
6. display the description.

All without refreshing/reloading the active page.

If you want me to display the code I will.

Ronald :cool:

[Chrisjc]

I dont fully understand your replay...

Now I have a little snippet for you (2 actually: 1 client-side and 1 PHP server-side). Only, to display the code here takes some space, client side code is 71 lines, server side code 32 lines. However, test table 'cars' setup is included.

Then if I want you to, to disply it??? I am afraid I am lost once more.

are you saying you will show me something you have???
If so sure.