?
Solved

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

Posted on 2014-09-22
9
Medium Priority
?
235 Views
Last Modified: 2014-10-12
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).
0
Comment
Question by:Stephen Norris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40337883
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
 

Author Comment

by:Stephen Norris
ID: 40337944
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
 

Author Comment

by:Stephen Norris
ID: 40337990
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40338044
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40338046
"request attention" is at the top right under your question.
0
 

Author Comment

by:Stephen Norris
ID: 40338137
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40338151
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
 

Accepted Solution

by:
Stephen Norris earned 0 total points
ID: 40367311
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
 

Author Closing Comment

by:Stephen Norris
ID: 40375492
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question