• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1897
  • Last Modified:

Pull value from database and populate dropdown

Hi,
I have a SQL server database table called "log" which stores all my main records. This table has "CallID" and "Priority" fields.

I have a lookup table called "priorities" which has "Priority" and "ResponseResolution" fields.

What I'm trying to do is let a user choose a priority from the lookup list (I'm using PHP) and on doing so, this will display the corresponding ResponseResolution underneath it.
The code below works perfectly on my "addrecord" form because the record is new. My problem is on my "editrecord" form; here I'm selecting a record from the "log" table and want to show both the Priority (as stored on the "log" table for this record) and it's corresponding "ResponseResolution". I presume there will be an onLoad event in the BODY tag. The value of the "Priority" field needs to be combined with all the rest of the possible values.

Can someone please help me? Many thanks in advance



<?
include('helpdeskconnect.php');
$con = mssql_connect ($hostname, $username, $password);
mssql_select_db ($database, $con);

$linkcallid = $_GET['CallID'];      //get call id from URL
$query = "SELECT * from log WHERE CallID = $linkcallid";

?>

<HTML>
<HEAD>

<script language="javascript">
function ShowResponseTimes(theId){
  var selVal = theId.value;

  if(selVal!="-1"){
      document.getElementById('ResponseResolution').innerHTML = theId.options[theId.options.selectedIndex].getAttribute("vResponseResolution");

  }
}
</script>

</HEAD>
<BODY>

<form method="POST" action="helpdeskaddnewcall.php" name="addrecord"><p>

<SELECT ID="Priority" NAME="Priority" onchange="ShowResponseTimes(this)">
<OPTION VALUE="-1"></OPTION>
<?php

$query="SELECT Priority,ResponseResolution FROM priorities ORDER BY Priority ASC";
$result = mssql_query($query) or die("Query failed : " . mssql_error());

while ($row = mssql_fetch_array($result))
      {

      ?>
      <option value="<?=$row["Priority"]?>" vResponseResolution="<?=$row["ResponseResolution"]?>"> <?=$row["Priority"]?></option>

      <?php
      }
?>
</SELECT>

<div id="ResponseResolution"></div>

</FORM>
</BODY>
</HTML>
0
kbit
Asked:
kbit
  • 2
1 Solution
 
ll_jaxnCommented:
Simply add a hidden input field with the record ID
<INPUT TYPE=HIDDEN ID=REC_ID VALUE="<?php echo $linkcallid?>">
0
 
kbitAuthor Commented:
I'll try to explain my issue a bit better:

I have a form for adding a support call to a database (table is called "log"). This form has a dropdown list called Priority whose possible values are High, Normal and Low among some others.
Each Priority has a target response time assigned to it on my database (table is called "priorities"). High = 1 hour, Normal = 2 hours and Low =4 hours.
On choosing a Priority from the dropdown list on my form, the target response time needs to be shown underneath it. This target response time wont be an input field, it will just be an ECHO. The code I posted above does all this perfectly.

Moving on, I have a page with a list of calls on it as below...each displayed as a hyperlink:
Call ID 123456
Call ID 123457
Call ID 123458

I click on a call and it opens that Call ID on my "edit a call" form
My PHP queries my database (the "log" table) for this Call ID and retrieves all the values for that record. Simple stuff.

HOWEVER, my target response time is not held on this "log" table for this record...only the Priority (we'll say 'High') is on this table.

And so to my problem. Seeing that this call has a Priority of "High", how do I get the equivalent 1 hour response time to appear underneath it? Yes if I manually choose "High" Priority from my list it will work fine (because of the OnChange event in the field) but I need the form to load up and run this function automatically.

Again my scant knowledge of both Javascript and PHP suggest that I need to call a Javascript OnLoad function in the BODY tag, pass my Priority (which I've just retrieved from my database) for this call into the function and output the appropriate target response time.
0
 
kbitAuthor Commented:
For those interested, here is the code I got working. It's not elegant but it's all I got. I excluded the database Priority value from the Priority query so it wouldn't appear twice on the dropdown list. The code is called from both the BODY OnLoad and the OnChange of the dropdown list:

<?
include('helpdeskconnect.php');
$con = mssql_connect ($hostname, $username, $password);
mssql_select_db ($database, $con);

$linkcallid = $_GET['CallID'];      //get call id from URL
?>

<HTML>
<HEAD>

<script language="javascript">
function ShowResponseTimes(theId)
{
  var selVal = theId.value;

  if(selVal!="-1")
  {
document.getElementById('ResponseResolution').innerHTML = theId.options[theId.options.selectedIndex].getAttribute("vResponseResolution");
  }
}
</script>

</HEAD>

<BODY BGCOLOR="#FAFADB" VLINK="#0000FF" ALINK="#0000FF" topmargin="0" onLoad="ShowResponseTimes(document.forms[0].Priority)">

<form method="POST" action="helpdeskeditcall.php" name="editrecord">

<?php
$query = "SELECT * FROM log INNER JOIN priorities ON log.Priority = priorities.Priority  WHERE CallID = $linkcallid";

$result = mssql_query($query);
$get_info = mssql_fetch_array($result);

?>

<SELECT ID="Priority" NAME="Priority" onchange="ShowResponseTimes(this)">
<?php
$currentpriority = $get_info['Priority'];
$query="SELECT Priority,ResponseResolution FROM priorities WHERE Priority <> '$currentpriority' ORDER BY Priority ASC";
?>

<option selected value="<?=$get_info["Priority"]?>" vResponseResolution="<?=$get_info["ResponseResolution"]?>"> <?=$get_info["Priority"]?></option>
<?php
$result = mssql_query($query) or die("Query failed : " . mssql_error());

while ($row = mssql_fetch_array($result))
{
?>
       <option value="<?=$row["Priority"]?>" vResponseResolution="<?=$row["ResponseResolution"]?>"> <?=$row["Priority"]?></option>
<?php
}
?>
</SELECT>

<div id="ResponseResolution"></div>

</FORM>
</BODY>
</HTML>
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now