Solved

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

Posted on 2014-09-22
9
188 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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

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

Author Comment

by:Stephen Norris
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now