PHP & MySQL List

Discussion in 'Web Design and Development' started by MegaMan1311, Oct 9, 2008.

  1. MegaMan1311 macrumors regular

    Joined:
    Jun 30, 2007
    Location:
    USA
    #1
    Hello.

    I am working on a project where I need to have a dropdown list of states that when you choose one, it returns a list of clients out of a database. For instance, you choose Texas, and John Doe and Bob White who live in Houston and Dallas respectively is returned.

    I'm new with returning info from a database, and can pull all results, but I cannot get the dropdown menu to work. I don't even really have any idea at all on how to... Any suggestions?

    My current code:
    PHP:
    <?php
    $con 
    mysql_connect("****","****","****");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }
    // Select DB
    mysql_select_db("****"$con);

    $result mysql_query("SELECT * FROM graduates");
    while(
    $row mysql_fetch_array($result))
      {
      echo 
    "<div style='border:1px solid black; width: 600px; margin: 5px; padding: 5px;'><div style='float:left;' class='gradfind_left'><div><strong>" $row['first_name'] . " " $row['last_name'] . "</strong></div><div>" $row['city'] . ", " $row['state'] ." "$row['zipcode'] ."</div></div>";
      echo 
    "<div style='float:right;' class='gradfind_right'>" "(" $row['area_code'] . ") " $row['phone'] . "</div><br style='clear:both;' /></div>";
      }
    ?>
    My database is set up simply. Each of the states is abbreviated (TX, NJ, TN, WA, etc).
     
  2. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #2
    This is what i use, bit simplistic but works.

    PHP:
    $sql "SELECT 
    `title`,
    `id`,
    `about`
    FROM `forum-topics`ORDER BY `id`"

    ///start result//
    $result mysql_query($sql) or die("Query failed");
    while (
    $row mysql_fetch_assoc($result)) 

        if (
    $row['id'] == $selected
            {
        
            }
        else 
            {
             
    printf ("<option value=\"%s" "\">%s" "</option>"$row['id'], $row['title']);
            }
    }
     
  3. MegaMan1311 thread starter macrumors regular

    Joined:
    Jun 30, 2007
    Location:
    USA
    #3
    Doesn't that create a list with the results? I need to make a list, that when you select a option (a state in this case), it returns an array of names. This is going to be like a directory of people...

    Example:
    Drop-down of states on page.
    Visitor chooses a state.
    Visitor sees a page with the names, phone numbers, etc. of everyone in that state that is in our database.
     
  4. cmk08 macrumors newbie

    Joined:
    Oct 3, 2008
    #4
    Do you have a separate table for states? How are you linking states with people in the database?

    I would create an array of states

    Code:
    
    $state =  array(1 => 'New York', 'Florida',....);
    
    echo "<select name= states>";
    
    foreach ($state as $key => $stateName){
    
          echo "<option value = $key>$stateName<option>";
    }
    
    
    The you can use the selected item to create a query returning the users for that state...

    On another subject, I would use a separate file to connect to mysql. It is risky to keep all information in the same script that will be used by your visitors.

    I didn't test the script as I am about to leave the office... I will be happy to help you with this.
     
  5. cmk08 macrumors newbie

    Joined:
    Oct 3, 2008
    #5
    Also:

    Code:
    $result = mysql_query("SELECT * FROM graduates where state = $selectedState");
    This will return only those located in the selected state
     
  6. ChrisA macrumors G4

    Joined:
    Jan 5, 2006
    Location:
    Redondo Beach, California
    #6
    Rather then make an array with 50 states. Better to query your database for the list of unique state names. That way if there are states where there are no people they do not show up in the list.

    If you don't do this then you have to handle the case of the list of people having zero length.
     
  7. MCRunning macrumors 6502

    Joined:
    Sep 8, 2008
    #7
    Looks like your question has been answered, but I would create a separate file for your MYSQL connect instead of inserting into your main code.
     
  8. SC68Cal macrumors 68000

    Joined:
    Feb 23, 2006
    #8
    That code has like one WTF per line.
     
  9. MegaMan1311 thread starter macrumors regular

    Joined:
    Jun 30, 2007
    Location:
    USA
    #9
    Hello everyone. Thanks for all of your help, I got the form working thanks to you guys. I just got the form working, but I have one issue. When you choose a state that doesn't have a result, it doesn't output anything.

    I tried an if else statement, but couldn't get it working. My code:
    PHP:
    <!--After --><?php
    $con 
    mysql_connect("localhost","admin_****","*****");
    mysql_select_db("*****_testing"$con);

    $posted $_POST['states'];
    $postedr "'$posted'";

    $result mysql_query('SELECT * FROM graduates WHERE state=' .$postedr.'');

    $state = array('','AL'=>'Alabama','AK'=>'Alaska','AZ'=>'Arizona','AR'=>'Arkansas','CA'=>'California','CO'=>'Colorado','CT'=>'Connecticut','DE'=>'Delaware','FL'=>'Florida','GA'=>'Georgia','HI'=>'Hawaii','ID'=>'Idaho','IL'=>'Illinois','IN'=>'Indiana','IA'=>'Iowa','KS'=>'Kansas','KY'=>'Kentucky','LA'=>'Louisiana','ME'=>'Maine','MD'=>'Maryland','MA'=>'Massachusetts','MI'=>'Michigan','MN'=>'Minnesota','MS'=>'Mississippi','Missouri','Montana','Nebraska','Nevada','New Hampshire','New Jersey','NM'=>'New Mexico','NY'=>'New York','NC'=>'North Carolina','ND'=>'North Dakota','OH'=>'Ohio','OK'=>'Oklahoma','OR'=>'Oregon','PA'=>'Pennsylvania','RI'=>'Rhode Island','SC'=>'South Carolina','SD'=>'South Dakota','TN'=>'Tennessee','TX'=>'Texas','UT'=>'Utah','VT'=>'Vermont','VA'=>'Virginia','WA'=>'Washington','WV'=>'West Virginia','WI'=>'Wisconsin','WY'=>'Wyoming');

    echo 
    "<form action='".$_SERVER["PHP_SELF"]."' method='post'><select name='states'>";
    foreach (
    $state as $key => $stateName){
          echo 
    "<option value = $key>$stateName</option>";
    }
    echo 
    "</select><input type='submit' value='Go!'></form>";

    while(
    $row mysql_fetch_array($result))
     {
    if(
    $row != ""){
    echo 
    "Test<div style='border:1px solid black; width: 600px; margin: 5px; padding: 5px;'><div style='float:left;' class='gradfind_left'><div><strong>" $row['first_name'] . " " $row['last_name'] . "</strong></div><div>" $row['city'] . ", " $row['state'] ." "$row['zipcode'] ."</div></div>";
      echo 
    "<div style='float:right;' class='gradfind_right'>" "(" $row['area_code'] . ") " $row['phone'] . "</div><br style='clear:both;' /></div>";
      }
    else{echo 
    'No Results...';}
    }
    ?>
    Additionally, how would I go about separated the database into another file? A PHP Include / Require?
     

Share This Page