Solved

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

Posted on 2014-09-22
9
199 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

785 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