By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,699 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,699 IT Pros & Developers. It's quick & easy.

Drop down menus and searching the DB

Chrisjc
100+
P: 375
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_array($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_query($sql);

$options="";


while ($row=mysql_fetch_array($result)) {

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

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


Thanks in advanced!

Chris
Nov 9 '06 #1
Share this Question
Share on Google+
23 Replies


ronverdonk
Expert 2.5K+
P: 4,258
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:
Nov 9 '06 #2

Chrisjc
100+
P: 375
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.triplesource.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
Nov 13 '06 #3

Chrisjc
100+
P: 375
Here to give a way better example of what I am trying to do...

go to (http://www.aempower.com/)

That is exactaly what I want... but with a few more options on mine.... but thats how I want it to work...
Nov 13 '06 #4

ronverdonk
Expert 2.5K+
P: 4,258
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.
Expand|Select|Wrap|Line Numbers
  1. -  connect to db and select years in table,
  2. -  build dynamic drop down list of years,
  3. -  setup JS (Ajax) catcher for select box event
  4. -  request user to fill in year
  5.  
  6. Selection triggers the Ajax process as follows:
  7. -  JS routine is triggered and sends YEAR to server PHP script
  8. -  PHP script pulls make from the data base
  9. -  PHP sends result back to via JS
  10. -  JS/HTML displays make in 2nd select box select
  11. -  request user to select the make
  12.  
  13. When make selected:
  14. -  JS routine triggered passing year and make to PHP script
  15. -  PHP connects to db and pulls data
  16. -  PHP send results back to JS
  17. -  results are displayed
Ronald :cool:
Nov 13 '06 #5

Chrisjc
100+
P: 375
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="VehicleSearch" style="WIDTH: 155px">
<option value Selected >Select Year</option>
<option value="2006" >2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
<option value="1999">1999</option>
<option value="1998">1998</option>
<option value="1997">1997</option>
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option>
<option value="1991">1991</option>
<option value="1990">1990</option>
<option value="1989">1989</option>
<option value="1988">1988</option>
<option value="1987">1987</option>
<option value="1986">1986</option>
<option value="1985">1985</option>
<option value="1984">1984</option>
<option value="1983">1983</option>
<option value="1982">1982</option>
<option value="1981">1981</option>
<option value="1980">1980</option>
<option value="1979">1979</option>
<option value="1978">1978</option>
<option value="1977">1977</option>
<option value="1976">1976</option>
<option value="1975">1975</option>
<option value="1974">1974</option>
<option value="1973">1973</option>
<option value="1972">1972</option>
<option value="1971">1971</option>
<option value="1970">1970</option>
<option value="1969">1969</option>
<option value="1968">1968</option>
<option value="1967">1967</option>
<option value="1966">1966</option>
<option value="1965">1965</option>
<option value="1964">1964</option>
<option value="1963">1963</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_array($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 ???
Nov 13 '06 #6

Chrisjc
100+
P: 375
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_array($result)){


echo "<option value>Select Year</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
<option value="1999">1999</option>
<option value="1998">1998</option>
<option value="1997">1997</option>
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option>
<option value="1991">1991</option>
<option value="1990">1990</option>
<option value="1989">1989</option>
<option value="1988">1988</option>
<option value="1987">1987</option>
<option value="1986">1986</option>
<option value="1985">1985</option>
<option value="1984">1984</option>
<option value="1983">1983</option>
<option value="1982">1982</option>
<option value="1981">1981</option>
<option value="1980">1980</option>
<option value="1979">1979</option>
<option value="1978">1978</option>
<option value="1977">1977</option>
<option value="1976">1976</option>
<option value="1975">1975</option>
<option value="1974">1974</option>
<option value="1973">1973</option>
<option value="1972">1972</option>
<option value="1971">1971</option>
<option value="1970">1970</option>
<option value="1969">1969</option>
<option value="1968">1968</option>
<option value="1967">1967</option>
<option value="1966">1966</option>
<option value="1965">1965</option>
<option value="1964">1964</option>
<option value="1963">1963</option>
</select></td>
}
echo "</select>";
?>
[/PHP]


no idea??? I think I am lost...
Nov 13 '06 #7

Chrisjc
100+
P: 375
Okay so now I have stoped because well this is what I have....

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

Okay so now With what I have is this ( www.triplesource.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.options[obj.options.length] = new Option('RSX','3');\n";

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

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

break;
case "BMW":
echo "obj.options[obj.options.length] = new Option('','');\n";
echo "obj.options[obj.options.length] = new Option('','');\n";
echo "obj.options[obj.options.length] = new Option('','');\n";
echo "obj.options[obj.options.length] = 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>Select 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
Nov 13 '06 #8

ronverdonk
Expert 2.5K+
P: 4,258
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:
Nov 13 '06 #9

Chrisjc
100+
P: 375
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.
Nov 13 '06 #10

Chrisjc
100+
P: 375
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.
That sounds exactly like what I want mine to do :) lol and all this time I have been very confusing and you happened to sum it all up in a 6 step way of how it works. I feel kind of dumb... ha... I think I been trying to figure this out so hard its making me start to get all foggy in the head..
Nov 13 '06 #11

ronverdonk
Expert 2.5K+
P: 4,258
Here is the code. Look at my previous reply for its functions.

1. download the Ajax class, name simple ajax.js and store that in an ajax subdirectory.
2. create the test table cars with the following sql
Expand|Select|Wrap|Line Numbers
  1. create table cars (id int(2) primary key auto_increment, 
  2.                     year int(2), 
  3.                     make varchar(20), 
  4.                     description varchar(255));
  5.  insert into cars (year, make, description) values(1961, 'honda', 'Description Honda 1961');
  6.  insert into cars (year, make, description) values(1962, 'honda', 'Description Honda 1962');
  7.  insert into cars (year, make, description) values(1963, 'honda', 'Description Honda 1963');
  8.  insert into cars (year, make, description) values(1963, 'ford', 'Description Ford 1963');
  9.  insert into cars (year, make, description) values(1964, 'ford', 'Description Ford 1964');
  10.  insert into cars (year, make, description) values(1967, 'ford', 'Description Ford 1967');
  11.  insert into cars (year, make, description) values(1961, 'mazda', 'Description Mazda 1961');
  12.  insert into cars (year, make, description) values(1962, 'mazda', 'Description Mazda 1962');
  13.  
Then run CARS.PHP to invoke the functions.

CARS.PHP
[php]
<html><head><title>Test drop down cars</title>
</style>
<script type="text/javascript" src="ajax/ajax.js"></script>
<script type="text/javascript">
var ajax = new sack();
var selYear;
function getMake(sel) {
selYear = sel.options[sel.selectedIndex].value;
if(selYear.length>0){
ajax.requestFile = 'getCarInfo.php?getmake=1&year='+selYear; // Specifying what to get
ajax.onCompletion = createDropdownMake; // Specify function that will be executed after file has been found
ajax.runAJAX(); // Execute AJAX function
}
}
function createDropdownMake() {
document.getElementById("divmsg").innerHTML = ajax.response;
}
function getDesc(sel) {
var selMake = sel.options[sel.selectedIndex].value;
if(selMake.length>0){
ajax.requestFile = 'getCarInfo.php?getdesc=1&make='+selMake+'&year='+ selYear; // Specifying what to get
ajax.onCompletion = createDesc; // Specify function that will be executed after file has been found
ajax.runAJAX(); // Execute AJAX function
}
}
function createDesc() {
document.getElementById("divmsg").innerHTML = ajax.response;
}
</script>
</head>
<body>
<?php
// ================================================== ===
// Populate the YEARS selection list from the database
// ================================================== ===
echo '<u><b>View the description of a car here</b></u>
<form action="" method="post">
Select year:&nbsp;&nbsp;&nbsp;
<select id="years" name="years" onchange="getMake(this)">
<option value="">Select</option>';
$conn = mysql_connect("localhost","xxxx","yyyyy")
or die("Could not connect to the database server: ".mysql_error());
mysql_select_db("zzz",$conn)
or die("Could not select the database: " . mysql_error());
$res = mysql_query("SELECT year FROM cars GROUP BY year ORDER BY year")
or die("Invalid query: " . mysql_query());
while ($row = mysql_fetch_assoc($res)) {
$yr = $row['year'];
echo "<option value=$yr>$yr</option>";
}
echo '</select>';
?>
<div id="divmsg" />

</form>
</body></html>
[/php]

GETCARINFO.PHP is called by CARS.PHP
[php]
<?php
$conn = mysql_connect("localhost","xxxx","yyyy")
or die("Could not connect to the database server: ".mysql_error());
mysql_select_db("zzz",$conn)
or die("Could not select the database: " . mysql_error());
// Get the make info
if(isset($_GET['getmake']) AND isset($_GET['year'])){
$yr = $_GET['year'];
$res = mysql_query("SELECT make FROM cars WHERE year=$yr GROUP BY make ORDER BY make")
or die("Invalid query: " . mysql_query());
echo 'Select make:&nbsp;&nbsp;';
echo '<select id="make" name="make" onchange="getDesc(this)">';
echo '<option value="">Select</option>';
while ($row = mysql_fetch_assoc($res)) {
$mk = $row['make'];
echo "<option value=$mk>$mk</option>";
}
echo '</select>';
}
// Get the description
if(isset($_GET['getdesc']) AND isset($_GET['make']) AND isset($_GET['year'])){
$mk = $_GET['make'];
$yr = $_GET['year'];
$res = mysql_query("SELECT description FROM cars WHERE year=$yr AND make='$mk'")
or die("Invalid query: " . mysql_query());
echo "Make:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;$mk<br />";
while ($row = mysql_fetch_assoc($res)) {
$ds = $row['description'];
echo "Description:&nbsp;&nbsp;&nbsp;&nbsp;$ds";
}
}
?>
[/php]

All is tested and it works. Any questions left: in this thread, so everyone can follow it. Good luck.

Ronald :cool:
Nov 13 '06 #12

Chrisjc
100+
P: 375
okay well here is all is

http://triplesource.net/php/0php/cars.php

how ever... ummm idk y it wont show years... ??? idk if my ajax script is wrong... I got it from one of the examples... help?
Nov 13 '06 #13

Chrisjc
100+
P: 375
okay well here is all is

http://triplesource.net/php/0php/cars.php

how ever... ummm idk y it wont show years... ??? idk if my ajax script is wrong... I got it from one of the examples... help?

okay well the years show but nothing more then that?????
Nov 13 '06 #14

ronverdonk
Expert 2.5K+
P: 4,258
Did you download ajax, i.e. the Simple Ajax Code Kit from SACK ? Did you store ajax.js in the correct subdirectory named ajax?

Look at my site at cars and you'll see exactly what I sent you.

Ronald :cool:
Nov 13 '06 #15

Chrisjc
100+
P: 375
Okay I got the right one now... Thanks alot time to learn all of this.. I really appreciete all of your help!!!

Chris
Nov 13 '06 #16

ronverdonk
Expert 2.5K+
P: 4,258
The link is in my previous reply. link named SACK

Ronald :cool:
Nov 13 '06 #17

Chrisjc
100+
P: 375
okay Mybe I am dumb or something but even with that link it still is not working at all...... and it could only be the js file..... I know it is.. I downloaded the sample.... but its likw tw-sack.js I even tryed to rename it....

BLAH HELP!
Nov 13 '06 #18

ronverdonk
Expert 2.5K+
P: 4,258
Did you try the demo at my link? That one works okay using the SACK ajax.js.

Ronald :cool:
Nov 13 '06 #19

Chrisjc
100+
P: 375
Did you try the demo at my link? That one works okay using the SACK ajax.js.

Ronald :cool:
Yours works grate but id like to make it work on mine so I can play with the code.... I dont understand I downloaded the (SACK thing) and its no working.... hmmm can u post up ur ajax.js on in a file so I can download that?
Nov 13 '06 #20

ronverdonk
Expert 2.5K+
P: 4,258
Okay, let's take the short cut.

- Go to this link Ajax chained select
- Go to paragraph 'Download Javascript file'
- Click on 'Download here'
- It will download ajax.js from twilight university.
- Store this ajax.js in your ajax subdirectory.

Ronald :cool:
Nov 13 '06 #21

Chrisjc
100+
P: 375
Okay, let's take the short cut.

- Go to this link Ajax chained select
- Go to paragraph 'Download Javascript file'
- Click on 'Download here'
- It will download ajax.js from twilight university.
- Store this ajax.js in your ajax subdirectory.

Ronald :cool:
Okay now I know it is not me... I tryed that.....

and look

http://triplesource.net/php/0php/cars.php

Now what could it be???
Nov 13 '06 #22

Chrisjc
100+
P: 375
hahahahaha I figured it out.... dang thing is CASE Sensitive.... REMEMBER THAT>>>>> I had getcarinfo.php when the ajax.js called it getCarInfo.php....


Well thanks again.. Hopefully this is the last you will hear from me for awhile until I find my self lost... again :)

Chris
Nov 13 '06 #23

ronverdonk
Expert 2.5K+
P: 4,258
Okay, good luck then!

Ronald :cool:
Nov 13 '06 #24

Post your reply

Sign in to post your reply or Sign up for a free account.