Solved

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

Posted on 2014-09-22
9
224 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

717 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