PHP & MySQL List

[MegaMan1311]

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
$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).

 

[Cabbit]

This is what i use, bit simplistic but works.

$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']);
        }
}

 

[MegaMan1311]

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.

 

[cmk08]

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

$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.

 

[cmk08]

Also:

$result = mysql_query("SELECT * FROM graduates where state = $selectedState");

This will return only those located in the selected state

 

[ChrisA]

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

$state =  array(1 => 'New York', 'Florida',....);

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.

 

[MCRunning]

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.

 

[SC68Cal]

This is what i use, bit simplistic but works.

That code has like one WTF per line.

 

[MegaMan1311]

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:

<!--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?