how to update multiple fields from just one php/mysql dynamic drop down

I have a php form into which I need to populate 5 fields from one MySQL table after choosing an item from a dynamic drop down select list (from MySQL database).
Stephen NorrisDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
How are you identifying the row that you need to update?  Here http://www.w3schools.com/php/php_mysql_update.asp is the general syntax for an update.
0
Stephen NorrisDeveloperAuthor Commented:
Thanks for looking at this.  I am at wits end and I have a deadline for this Friday to have this working for customer.  I don't care how scathing the remarks, I've just had too many sleepless night to worry about that now.  

Here are the links and code for both:
http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/search.php

-------------------------------Code for link 1:
<?

error_reporting(E_ALL);

mysql_connect("server", "username", "password");
mysql_select_db("PharmaTrials");

if (isset($_GET['search_query'])) {
$search_query = mysql_real_escape_string(htmlentities($_GET['search_query']));


$construct ="ID_TE LIKE '%$search_query%'";      

$construct ="SELECT DISTINCT * FROM T_And_E_Table WHERE $construct ORDER BY ID_TE ASC LIMIT 1";
$run = mysql_query($construct);

$foundnum = mysql_num_rows($run) or die(mysql_error());

if ($foundnum==0) {
echo "not found";
} else
{


while($runrows = mysql_fetch_assoc($run))
{


$Progeny_Period_1 = $runrows ['Progeny_Period_1'];
$Story_Period_2 = $runrows ['Story_Period_2'];
$Diet_Period_3 = $runrows ['Diet_Period_3'];
$RomeIII_Period_4 = $runrows ['RomeIII_Period_4'];
$Qol_Period_5 = $runrows ['Qol_Period_5'];
$Exit_Ques_Period_6 = $runrows ['Exit_Ques_Period_6'];

echo "$Progeny_Period_1" . "|" . "$Story_Period_2" . "|" . "$Diet_Period_3" . "|" . "$RomeIII_Period_4" . "|" . "$Qol_Period_5" . "|" . "$Exit_Ques_Period_6";
}
}
}
else
{
echo "|";      
}
?>
---------------------------------------
link 2:
http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/test3.php?Type=New

---------------------------------------------------code for above link 2:

<html>
<head>
<script type="text/javascript" src="http://code.jquery.com/jquery-1.7.2.min.js"></script>
<script type="text/javascript"><!--
function populate_ID_TE {
   var fieldname = '#ID_TEno';

   var ID_TEnumber = $(fieldname).val();

   $.ajax({
     url:  "http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/search.php",
     type:  "GET",
     data: {"search_query": ID_TEnumber},
     success: function(response) {

          var data = response.split("|");
          $('#Progeny_Period_1').val(data[0]);
          $('#Story_Period_2').val(data[1]);
          $('#Diet_Period_3').val(data[2]);
          $('#RomeIII_Period_4').val(data[3]);
          $('#Qol_Period_5').val(data[4]);
          $('#Exit_Ques_Period_6').val(data[5]);
         
     },
     error: function(xhr) {
         alert("A problem was encountered updating values.");
     }
   });


}



function compute_aei(form)
{      
 
 
}
 
//--></script>


<title>New Page</title>
</head>
<?php
 // Connects to your Database
mysql_connect("server", "username", "password");
mysql_select_db("PharmaTrials");


if (isset($_GET["Type"])) {
   $Type = $_GET["Type"];
      if (isset($_GET["AppID"])) {
        $AppID = $_GET["AppID"];         
        }
        else {
          $AppID = 0;
        }
      $sql = "select * from T_And_E_Table where ID_TE = '$AppID'";
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_array($result);

   

}
//Type Not Set...
else {
  echo "<a href='http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/test2.php?Type=New'>New</a>";
  echo "<br><br>";
  echo "Recall Existing:<br>Enter ID Number to Recall: <form name='1' action='http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/test2.php?Type=New' method='GET'><input type='hidden' name='Type' value='Update'><input type='text' size='20' name='AppID'><input type='Submit'></form>";
  exit();
}

?>

<body>
      <table border="0" cellpadding="0" cellspacing="0" width="500" height="100" id="table20" summary="feedback form" class="style57" >
      <form name='step_1_aeicontactform' action="http://www.sentrisoft.com/programbase/service/step1/step-1-fb.php" method="post">
      
      <div align="left">
            <table border="0" cellpadding="0" cellspacing="0" width="100%" height="39%">
                  <tr>
                        <td height="119" width="250">
<select size="1" name="ID" tabindex="30">
                  
                  <option selected>Select ID:</option>
                  <option value="Description"><?php if ($row["ID"] == "Description" ) echo " SELECTED "; ?>Description</option>Description
                  <option value="Price"><?php if ($row["ID"] == "Price" ) echo " SELECTED "; ?>Price</option>Price
                  <option value="Category"><?php if ($row["ID"] == "Category" ) echo " SELECTED "; ?>Category</option>Category
                  <option value="UOM"><?php if ($row["ID"] == "UOM" ) echo " SELECTED "; ?>UOM</option>UOM
                  </select>
                        </form>
</td>

                  
</td>            
            


                  
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Story_Period_2 id="Story_Period_2" onChange=compute_aei(this.form) value="<?php echo $row["Story_Period_2"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Story_Period_2 id="Story_Period_2" onChange=compute_aei(this.form) value="<?php echo $row["Story_Period_2"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Diet_Period_3 id="Diet_Period_3" onChange=compute_aei(this.form) value="<?php echo $row["Diet_Period_3"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=RomeIII_Period_4 id="RomeIII_Period_4" onChange=compute_aei(this.form) value="<?php echo $row["RomeIII_Period_4"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Qol_Period_5 id="Qol_Period_5" onChange=compute_aei(this.form) value="<?php echo $row["Qol_Period_5"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Exit_Ques_Period_6 id="Exit_Ques_Period_6" onChange=compute_aei(this.form) value="<?php echo $row["Exit_Ques_Period_6"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  </tr>
                  <tr>
                        <td width="250"></td>
                        <td>Of course, not necessarily in this order</td>
                        <td></td>
                        <td></td>
                        <td></td>
                  </tr>
            </table>
            </form
      </div>
      
      
      
      
</table>      
</body>

</html>
0
Stephen NorrisDeveloperAuthor Commented:
I'm sorry the code for link 2 is as follows, I grabbed the code in transition from a working code I have:

<html>
<head>
<script type="text/javascript" src="http://code.jquery.com/jquery-1.7.2.min.js"></script>
<script type="text/javascript"><!--
function populate_ID_TE {
   var fieldname = '#ID_TEno';

   var ID_TEnumber = $(fieldname).val();

   $.ajax({
     url:  "http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/search.php",
     type:  "GET",
     data: {"search_query": ID_TEnumber},
     success: function(response) {

          var data = response.split("|");
          $('#Progeny_Period_1').val(data[0]);
          $('#Story_Period_2').val(data[1]);
          $('#Diet_Period_3').val(data[2]);
          $('#RomeIII_Period_4').val(data[3]);
          $('#Qol_Period_5').val(data[4]);
          $('#Exit_Ques_Period_6').val(data[5]);
         
     },
     error: function(xhr) {
         alert("A problem was encountered updating values.");
     }
   });


}



function compute_aei(form)
{      
 
 
}
 
//--></script>


<title>New Page</title>
</head>
<?php
 // Connects to your Database
mysql_connect("server", "username", "password");
mysql_select_db("PharmaTrials");


if (isset($_GET["Type"])) {
   $Type = $_GET["Type"];
      if (isset($_GET["AppID"])) {
        $AppID = $_GET["AppID"];         
        }
        else {
          $AppID = 0;
        }
      $sql = "select * from T_And_E_Table where ID_TE = '$AppID'";
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_array($result);

   

}
//Type Not Set...
else {
  echo "<a href='http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/test2.php?Type=New'>New</a>";
  echo "<br><br>";
  echo "Recall Existing:<br>Enter ID Number to Recall: <form name='1' action='http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/test2.php?Type=New' method='GET'><input type='hidden' name='Type' value='Update'><input type='text' size='20' name='AppID'><input type='Submit'></form>";
  exit();
}

?>

<body>
      <table border="0" cellpadding="0" cellspacing="0" width="500" height="100" id="table20" summary="feedback form" class="style57" >
      <form name='step_1_aeicontactform' action="http://www.sentrisoft.com/programbase/service/step1/step-1-fb.php" method="post">
      
      <div align="left">
            <table border="0" cellpadding="0" cellspacing="0" width="100%" height="39%">
                  <tr>
                        <td height="119" width="250">
<select size="1" name="ID" tabindex="30">
                  
                  <option selected>Select ID:</option>
                  <option value="Trial_Week_1"><?php if ($row["ID_TE"] == "Trial_Week_1" ) echo " SELECTED "; ?>Trial_Week_1</option>Trial_Week_1
                  <option value="Trial_Week_2"><?php if ($row["ID_TE"] == "Trial_Week_2" ) echo " SELECTED "; ?>Trial_Week_2</option>Trial_Week_2
                  <option value="Trial_Week_3"><?php if ($row["ID_TE"] == "Trial_Week_3" ) echo " SELECTED "; ?>Trial_Week_3</option>Trial_Week_3
                  <option value="Trial_Week_4"><?php if ($row["ID_TE"] == "Trial_Week_4" ) echo " SELECTED "; ?>Trial_Week_4</option>Trial_Week_4
                  <option value="Trial_Week_5"><?php if ($row["ID_TE"] == "Trial_Week_5" ) echo " SELECTED "; ?>Trial_Week_5</option>Trial_Week_5
                  <option value="Trial_Week_6"><?php if ($row["ID_TE"] == "Trial_Week_6" ) echo " SELECTED "; ?>Trial_Week_6</option>Trial_Week_6
                  </select>
                        </form>
</td>

                  
</td>            
            


                  
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Story_Period_2 id="Story_Period_2" onChange=compute_aei(this.form) value="<?php echo $row["Story_Period_2"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Story_Period_2 id="Story_Period_2" onChange=compute_aei(this.form) value="<?php echo $row["Story_Period_2"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Diet_Period_3 id="Diet_Period_3" onChange=compute_aei(this.form) value="<?php echo $row["Diet_Period_3"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=RomeIII_Period_4 id="RomeIII_Period_4" onChange=compute_aei(this.form) value="<?php echo $row["RomeIII_Period_4"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Qol_Period_5 id="Qol_Period_5" onChange=compute_aei(this.form) value="<?php echo $row["Qol_Period_5"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  <td height="119"><input size=28 onBlur="parseelement(this)" name=Exit_Ques_Period_6 id="Exit_Ques_Period_6" onChange=compute_aei(this.form) value="<?php echo $row["Exit_Ques_Period_6"]; ?>" tabindex="18" onBlur="parseelement(this)"></td>
                  </tr>
                  <tr>
                        <td width="250"></td>
                        <td>Of course, not necessarily in this order</td>
                        <td></td>
                        <td></td>
                        <td></td>
                  </tr>
            </table>
            </form
      </div>
      
      
      
      
</table>      
</body>

</html>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dave BaldwinFixer of ProblemsCommented:
First problem I see is that you are re-using a variable and changing it's contents in a way that may not work.  Take the first '$construct' and change it to something like '$cwhere' like below.
$cwhere ="ID_TE LIKE '%$search_query%'";      

$construct ="SELECT DISTINCT * FROM T_And_E_Table WHERE $cwhere ORDER BY ID_TE ASC LIMIT

Open in new window


And in this line, I don't think you should be using 'htmlentities()'.  'mysql_real_escape_string()' is all you need to protect the database.  You might want to filter $_GET['search_query'] before you use it there.  On the pages that I have 'search' functions, they are actually limited to a specific set of keywords.  I never let raw data into the query.
$search_query = mysql_real_escape_string(htmlentities($_GET['search_query']));

Open in new window

0
Dave BaldwinFixer of ProblemsCommented:
"request attention" is at the top right under your question.
0
Stephen NorrisDeveloperAuthor Commented:
Thanks.  I changed the things you mentioned.  The problem is that it still doesn't work.  her are the updated links:
http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/search-expert.php
and
http://sentrisoft.com/Xzy5aBBU1200pjEahe19anY43/trials/search/test3.php?Type=New
0
Dave BaldwinFixer of ProblemsCommented:
I won't be able to look at this until tomorrow.  But the first link is just a blank page and the second one puts up the table but doesn't do anything.
0
Stephen NorrisDeveloperAuthor Commented:
I was able to fix it by rewriting it using a new approach.  It works perfectly across all browsers, which was a major issue  as well, thanks for trying.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen NorrisDeveloperAuthor Commented:
I had to look at the syntax and take another approach, the client had added additional criteria which made it a whole new coding involving ajax and jquery.  I was able to move through it fairly quickly and both I and the client was happy with the result.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.