Searching MySQL db with PHP...

[jamescover]

You can put the amount of knowledge of PHP I have in a thimbel...

Anyway, I have set up a MySQL db (Using PHPMyAdmin), with a single table, and four columns: Year, Make, Model, Link.

On the main page, I will have a two selects (dropdown menus): Make, and Model.

The Model select will be empty, until a Make has been selected.

The first time the page loads, I need to populate the Make select with all available [Make] options stored in the db. Then once a Make has been chosen (onChange), I need to populate the Model select with all available [Model] options stored in the db.


Then, once the Make and Model have been chosen, the PHP script will return that Make and Model for all years stored in the db, along with a link to each. In other words, I will need to search the db by each key*.

I have no idea how to get the appropriate values out of the db, and populate an HTML form with them. I suppose, I have to turn form element names and/or values into PHP variables, but any help is appreciated. Do I use arrays, or what?


I started this a while back, but got sidetracked...


thanks,


-james

[Oeldroop]

well, for the first dropdown, the php-code should be something like this

<?php
$link = mysql_connect("hostname", "username", "password") or die(mysql_error());

mysql_select_db("db_name") or die(mysql_error());

$makes = mysql_query("SELECT Make FROM tbl_name") or die(mysql_error());
if(mysql_num_rows($makes) != 0) {
print "<select name='Make'>";

while ($row = mysql_fetch_array($makes)) {
$r_make = $row[Make];

print "<option value='$r_make'>$r_make</option>";

} 

print "</select>";
} else {
print "No Make in Table.";
}
mysql_close($link);
?>

but on general i think, if you do this whole thing with 1 table, you'll generate some redundancy. further, i dont understand why the models should wait til the onchange, if they dont depend on the make-selection
also, check back on php.net, there are nice examples to each function.
peace, droop

[jamescover]

Hi Oeldroop:

Thanks for the code. I will give it a try. But don't I have to use $_POST['var'], or some such thing...? At any rate, I will see if I can get it to work.

but on general i think, if you do this whole thing with 1 table, you'll generate some redundancy.

1 table, 4 columns, Year, Make, Model, Link.

I'm asking a sincere question: How would the above generate redundancy? I have no clue. When queirying the db???

[b]further, i dont understand why the models should wait til the onchange, if they dont depend on the make-selection[b]

They do depend on the Make selection.

Let's say you have 10 or 12 different Makes, like, Ford, Chrysler, Honda, Toyota, Chevy, GMC, Cadillac, etc. Each of these will have a number of Models associated with it. I don't want the surfer to wade through every Make and Model. So once they select a Make, they will only have to select from the Models for that particular brand/Make, instead of all of the vehicles (Makes and Models) in the entire inventory. Then once they choose a Model, they will be taken to the results page, where they might have, say, a 1x 1999 Honda Prelude, 2x 2002 Honda Preludes, 4x 2003 Honda Preludes, etc.

also, check back on php.net, there are nice examples to each function.

Yeah, I've been there a few times, and will continue to return. Thanks, again, for the info....



-james

[Oeldroop]

hi james

But don't I have to use $_POST['var'], or some such thing...?

not in the first step, when only showing all makes. but after a make is submitted (with something like onChange='Javascript:document.formname.submit()'), you'll need it, yes.

How would the above generate redundancy? I have no clue. When queirying the db???

if you use only 1 table, you'll have something like
Make Model Year
row 1 Honda Prelude 1999
row 2 Honda Prelude 2000
row 3 Ford Model 1 2000

which is not so good. i'd use 5 tables.

this would give something like

tbl_make
col id
col name

tbl_model
col id
col name
col make_id

tbl_year
col id
col number

tbl_link
col id
col name

tbl_mod_link_year
col mod_id
col year_id
col link_id

the benefit here is, that you'll save some time, if you, for example, named a make 'Hunda' instead of 'Honda'. you'd only have to update 1 record, instead of probably thousands with the 1-table-solution.

hope it helps
droop

[jamescover]

not in the first step, when only showing all makes. but after a make is submitted (with something like onChange='java script:document.formname.submit()'), you'll need it, yes.

Right. That's kinda what I meant, like, to populate the second (Model) select menu, if have to grab the selected Model as a PHP variable.

the benefit here is, that you'll save some time, if you, for example, named a make 'Hunda' instead of 'Honda'. you'd only have to update 1 record, instead of probably thousands with the 1-table-solution.

Makes, sense. I'll be working with your suggestions later tonight.
Thanks, again, for all of your help.



-james