Get and Insert info into Database

[doc1355]

Hi,

I am a web designer and just started to learn MySQL with PHP.

I have a small problem that I don't know how to figure it out.

Here is my situation:

I have one table (form) that has multiple fields. Here are few fields we need to know: ID, NAME, CITY, STATE, PROFESSION, x, y, z (x, y, z means more than 50 fields that give me more information about each name).

The ID and NAME are unique and they don't change (each name has it's own ID). A NAME and its ID can be in any CITY, STATE or PROFESSION but this is fixed. That means if ID# 123 for Mr JOHN is Austin, TX, that will stay in Austin, TX for ever. I have more than 5000 NAME and it's ID, 9 professions, all US States and all Cities in my database.

 

Here is what I need to do:

I want to have a form on my site that my visitor finds his name first and then enters more information about him/her that will complete my database fields of x,y,z. What I'm thinking to do is to create a drop down list that the visitor first selects the profession. Based on the profession the second list shows the states that has that profession. Then by selecting the state, all the available cities will show up in the third list, and by selecting the city all the names related to that profession in that city, state will show up and he can select the name from the forth list. Then he enters other required information about him that will be send and saved in the database.

 

Here is what I have done and I know to do so far:

I have created the database with all the information in one table. I also know how to send data to database to be saved. But my problem at this point is how to create this list selection that narrows the search to find the right person.

 

Any suggestion is truly appreciated.

 

Thanks

[NArc0t1c]

Well, Using variables and foreach.

Foreach to display cites as a dropdown.

Variables to post/get to the page and then having another query to retrive that dropdown data.

[doc1355]

Thanks for reply, but can you please be more specific. Is there anyway you can post a small example.

Thanks

 

 

[coolphpdude]

Hey,

 

Do you mean:

 

user enters his name, then you give him options to enter information into a kind of profile, then take information from the form fields and insert into his record in the database?

[doc1355]

Here is what I mean:

I have partial information about the user. I want to complete the information. But because I have thousands of them. I need to make it easy for the user to find his name.

The method that I need is that the user first select the profession from a drop down list, based on that profession, all the available states that has that profession will show up in the second list. Then he selects the state, that will show up all the cities from that state that has that profession. By selecting the city, all the names of the people in that city, state and profession will appear in the forth list. He/she will select his name, and all the partial information will appear in the other part of the form. Then the user can edit the info and enter the missing info.

 

Lets do this step by step.

Here is what I have now. I have the database, with all the info. I created a page that has two drop down list, first the profession and the second the state. But now the state shows all the available states in the database. I need to change the second list in the way that it should be blank (or a message like "Select profession first") then as soon as the user picks the profession from the first field, all the states that has that profession shows up in the second one.

Here is the code that I have for that page:

 

<?php require_once('../../Connections/connection.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_connection, $connection);
$query_speciality = "SELECT DISTINCT speciality FROM usmle";
$speciality = mysql_query($query_speciality, $connection) or die(mysql_error());
$row_speciality = mysql_fetch_assoc($speciality);
$totalRows_speciality = mysql_num_rows($speciality);

mysql_select_db($database_connection, $connection);
$query_state = "SELECT DISTINCT `state` FROM usmle ORDER BY `state` ASC";
$state = mysql_query($query_state, $connection) or die(mysql_error());
$row_state = mysql_fetch_assoc($state);
$totalRows_state = mysql_num_rows($state);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="">
  <label>Speciality
  <select name="speciality" id="speciality">
    <option value="">Select Please</option>
    <?php
do {  
?>
    <option value="<?php echo $row_speciality['speciality']?>"><?php echo $row_speciality['speciality']?></option>
    <?php
} while ($row_speciality = mysql_fetch_assoc($speciality));
  $rows = mysql_num_rows($speciality);
  if($rows > 0) {
      mysql_data_seek($speciality, 0);
  $row_speciality = mysql_fetch_assoc($speciality);
  }
?>
  </select>
  </label>
  <p>
    <label>State
    <select name="state" id="state">
      <option value="">Select Please</option>
      <?php
do {  
?>
      <option value="<?php echo $row_state['state']?>"><?php echo $row_state['state']?></option>
      <?php
} while ($row_state = mysql_fetch_assoc($state));
  $rows = mysql_num_rows($state);
  if($rows > 0) {
      mysql_data_seek($state, 0);
  $row_state = mysql_fetch_assoc($state);
  }
?>
        </select>
    </label>
  </p>
</form>
</body>
</html>
<?php
mysql_free_result($speciality);

mysql_free_result($state);
?>

 

Thanks for help

[doc1355]

Anybody please?