SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Vancouver, WA
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php newbie needs help with query statement

    I’ve recently started using PHP/mySQL and have gone through every tutorial I could get my hands on, but I need to create a query statement a little more advanced than what I’ve done up to this point.

    I have a table called “vehicles” with the following columns:
    Column 1: Vehicle Type – car, truck, van
    Column 2: Manufacturer – Ford, Dodge, Honda, etc…
    Column 3: Model – Pinto, Ram, Prelude, etc…
    Column 4: Color – red, blue, green, etc…
    Column 5: Dealer – dealer1, dealer2, dealer3, etc…
    Column 6: Dealer URL – www.dealer1.com, www.dealer2.com, www.dealer3.com, etc….
    Column 7: Price – 1234.00, 2345.00, 3456.00, etc…

    I have a web page with the following:
    Drop down box 1 (Vehicle Type): Car (default), Truck, Van
    Drop down box 2 (Manufacturer): No Preference (default), Ford, Dodge, Honda, etc…
    Drop down box 3 (Model): No Preference (default), Pinto, Mustang, Escort, etc…
    Text box: Max Price

    Each successive drop down box is dependent on the prior one such that if a user selects vehicle type=car, manufacturer=Ford, then all the models in the third drop down box will be Ford models. The text box can be used to specify a max price the user is willing to pay.

    My goal is to output it to a table such that a query for Vehicle Type=Car, Manufacturer=Ford, Model=Mustang would result in the following output:

    Ford Mustang (blue) 6789.00
    Ford Mustang (green) 6890.00
    Ford Mustang (green) 6999.00
    Ford Mustang (red) 7450.00

    The whole string (ie Ford Mustang (blue)) would be a hyper link to the dealer URL.

    And now to my problem. I’ve been racking my brain and searching the internet for the past two weeks trying to think of how to write a query statement to retrieve the appropriate information from the database based on the users inputs. I've messed around with if statements and tried to figure out if I needed to incorporate arrays and it quickly got over my head. At this point I just don’t have the necessary knowledge or experience to do this all by myself. I’m hoping one of you seasoned veterans can walk me through this or give me some pointers. At this point anything would be greatly appreciated. Time to refill my Excedrin bottle

    Thanks guys!

  2. #2
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First things first:


    You're talking about using Javascript when you say you want the drop down menus to depend on the one selected prior to it. That is if they are on the same page.



    Now, if you want to do it one page at a time, that can be done in php easy.



    First, have each one named specifically.

    ie the drop down menus are "Vehicle_Type ", "Manufacturer", and "Model"



    then the only special thing you have to do is test to see which ones you have and construct the right page according to that like this:

    PHP Code:
    if(!$Vehicle_Type && !$Manufacturer && !$Model) {

    // no selection made yet, give them a choice of vehicle type

    $sql "SELECT Vehicle_Type FROM vehicles";
    $result mysql_db_query($db$sql$cid);

    echo(
    "<select name=\"Vehicle_Type\">";

    while ( 
    $row=mysql_fetch_row($results)
    echo(
    "<option value=$row[0]>$row[0]</option>";
    }

    echo(
    '</select>');




    }elseif(
    $Vehicle_Type && !$Manufacturer && !$Model) {

    // they've chosen a vehicle type - make a menu based on the types of vechicles

    $sql "SELECT Manufacturer FROM vehicles WHERE Vehicle_Type = $Vehicle_Type";
    $result mysql_db_query($db$sql$cid);

    echo(
    "<select name=\"Manufacturer\">";

    while ( 
    $row=mysql_fetch_row($results)
    echo(
    "<option value=$row[0]>$row[0]</option>";
    }

    echo(
    '</select>');




    }elseif(
    $Vehicle_Type && $Manufacturer && !$Model) {

    // they chose a vehicle type and manufacture

    $sql "SELECT Model FROM vehicles WHERE Vehicle_Type = $Vehicle_Type and Manufacturer = $Manufacturer";
    $result mysql_db_query($db$sql$cid);

    echo(
    "<select name=\"Model\">";

    while ( 
    $row=mysql_fetch_row($results)
    echo(
    "<option value=$row[0]>$row[0]</option>";
    }

    echo(
    '</select>');





    }elseif(
    $Vehicle_Type && $Manufacturer && $Model) {

    // they have chose all the nessecary fields


    $sql "SELECT * FROM vehicles WHERE Vehicle_Type = $Vehicle_Type and Manufacturer = $Manufacturer and Model = $Model;";


    } else {

    // this shouldn't happen, but if it does it's very bad...





    Also, go read this forum about making urls search engine friendly....this could make for a very nice addition to your system:
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  3. #3
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Vancouver, WA
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Milamber,

    I already have a javascript menu system setup to adjust the menus according to the user's selections so that is already taken care of.

    Each one of the drop down boxes is named individually just like you had suggested so that's in order too.

    So now the question is how to write one statement to cover all the possibilities. Is it difficult to do it all on one page?

    In the sample code you gave will the drop down box created by the <select name=\"Vehicle_Type\"> tag have repeat entries in it like the database or does it know to only have one of each item?

    Thanks, Milamber, for the help. You've been a big help to me. I'll review your code and see what I can do with it.

  4. #4
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by adamcornwell
    Milamber,

    I already have a javascript menu system setup to adjust the menus according to the user's selections so that is already taken care of.

    Each one of the drop down boxes is named individually just like you had suggested so that's in order too.

    So now the question is how to write one statement to cover all the possibilities. Is it difficult to do it all on one page?

    In the sample code you gave will the drop down box created by the <select name=\"Vehicle_Type\"> tag have repeat entries in it like the database or does it know to only have one of each item?

    Thanks, Milamber, for the help. You've been a big help to me. I'll review your code and see what I can do with it.


    Oh hehe, yeah....if you did select like that you'd get a lot of duplicates...sorry i was thinking of something else when i did that.



    :::uhm::: I know there is a way to remove duplicate entries from the query so you get only unique entrie....but i don't know it. Sorry!
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  5. #5
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Vancouver, WA
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Milamber,

    You're script gave me an idea on how to write my query for all options on a single page and it works. Thanks a million!

    Adam


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •