Populate Form from Database

eits · September 6, 2007

Could somebody please tell me how to make the form autofill when you select a client name from clientdd?

Basically I need to somehow change the SQL query so that when I select the client name from the selectbox 'clientdd' it fills the form out with their data.

<?php require_once('../Connections/timedb.php'); ?>
<?php require_once('../Connections/timedb.php'); 
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE tblcustomer SET clientname=%s, hoursworked=%s, hoursleft=%s, hoursallocated=%s WHERE clientid=%s",
                       GetSQLValueString($_POST['clientname'], "text"),
                       GetSQLValueString($_POST['hoursworked'], "text"),
                       GetSQLValueString($_POST['hoursleft'], "text"),
                       GetSQLValueString($_POST['hoursallocated'], "text"),
                       GetSQLValueString($_POST['clientid'], "int"));

  mysql_select_db($database_timedb, $timedb);
  $Result1 = mysql_query($updateSQL, $timedb) or die(mysql_error());
  print 'Query Ran: '.$updateSQL.' Result: '.$Result1;
}

<?php
mysql_select_db($database_timedb, $timedb);
$query_rscustomers = "SELECT * FROM tblcustomer";
$rscustomers = mysql_query($query_rscustomers, $timedb) or die(mysql_error());
$row_rscustomers = mysql_fetch_assoc($rscustomers);
$totalRows_rscustomers = mysql_num_rows($rscustomers);
?>
?>
<?php
function hourstodecimal ($timeinhours) {
  $timeparts = explode(':', $timeinhours);
  return ($timeparts[0] + ($timeparts[1]/60));
}

function decimaltohours ($timeindecimal) {
  $hours = floor($timeindecimal);
  $minutes = str_pad( (($timeindecimal - $hours)*60), 2, '0');
  return $hours.':'.$minutes;
}

// $hourstoday = hourstodecimal($_POST['hourstoday']); 
$remintime = decimaltohours ($row_rscustomers['hoursallocated'] - $row_rscustomers['hoursworked']); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<link href="stylesheet.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="600" border="0" align="center" cellpadding="0" cellspacing="0" id="box">
  <tr>
    <td height="346" valign="top" bgcolor="#D8E9EC"><div align="center" class="headstyle">
      <p>Customer Time Logging</p>
      <p class="contstyle">This page allows you to log your time each time you visit a customer. This will then be removed from their monthly allowance and tell you the remaining time left for their support contract. </p>
      <p class="contstyle"><?php echo $totalworked; ?></p>
    
      <form method="post" name="form1" action="<?php echo $editFormAction; ?>">
        <table width="263" align="center">
          <tr valign="baseline">
            <td width="99" align="right" valign="middle" nowrap class="contstyle"><div align="left">Clientname:</div></td>
            <td width="106" valign="top" class="contstyle"><label>
              <div align="left">
           <select name="clientdd" id="clientdd" onchange=\"reload(this.form)\">
           <?php do { ?>
           <option value="<?php echo $row_rscustomers['clientname']?>"> <?php echo $row_rscustomers['clientname']?></option>
           <?php
} while ($row_rscustomers = mysql_fetch_assoc($rscustomers));
  $rows = mysql_num_rows($rscustomers);
  if($rows < 10) {
      mysql_data_seek($rscustomers, 0);
  $row_rscustomers = mysql_fetch_assoc($rscustomers);
  }
?>
         </select>
      </div>
              </label></td>
          </tr>

          <tr valign="baseline">
            <td align="right" valign="middle" nowrap class="contstyle"><div align="left">Job Number: </div></td>
            <td valign="middle" class="contstyle"><div align="left">
              <select name="jobdd" id="jobdd">
                <?php do { ?>
                <option value="<?php echo $row_rscustomers['clientname']?>"> <?php echo $row_rscustomers['clientname']?></option>
                <?php
} while ($row_rscustomers = mysql_fetch_assoc($rscustomers));
  $rows = mysql_num_rows($rscustomers);
  if($rows < 10) {
      mysql_data_seek($rscustomers, 0);
  $row_rscustomers = mysql_fetch_assoc($rscustomers);
  }
?>
              </select>
            </div></td>
          </tr>
          <tr valign="baseline">
            <td align="right" valign="middle" nowrap class="contstyle"><div align="left">Hours Worked:</div></td>
            <td valign="middle" class="contstyle"><div align="left">
                  <input name="hourstoday" type="text" value="00:00" size="5" maxlength="5"> 
              HH:MM </div></td>
          </tr>
          <tr valign="baseline">
            <td align="right" valign="middle" nowrap class="contstyle"><div align="left">Remaining Hours:</div></td>
            <td valign="middle" class="contstyle"><div align="left">
                  <input name="txtremhours" type="text" id="txtremhours" value="<?php echo $remintime; ?>" size="5" maxlength="5"> 
              HH:MM </div></td>
          </tr>
          <tr valign="baseline">
            <td colspan="2" align="right" valign="middle" nowrap class="contstyle"><div align="center">
              <input type="submit" value="Update record">
            </div></td>
            </tr>
        </table>
        <p>
          <input type="hidden" name="MM_update" value="form1">
          <input type="hidden" name="clientid" value="<?php echo $row_rscustomers['clientid']; ?>">
	  <input type="hidden" name="clientname" value="<?php echo $row_rscustomers['clientname']; ?>">
          <input type="hidden" name="hoursleft" value="<?php echo $row_rscustomers['hoursleft']; ?>">
      <input type="hidden" name="hoursallocated" value="<?php echo $row_rscustomers['hoursallocated']; ?>">
          <input type="hidden" name="hoursworked" value="<?php echo $row_rscustomers['hoursworked'] +hourstodecimal($_POST['hourstoday']) ?>">
        </p>
        <p class="contstyle">This month, the customer has currently used <?php echo decimaltohours($row_rscustomers['hoursworked']); ?> of their allocated <?php echo decimaltohours($row_rscustomers['hoursallocated']); ?> hours.</p>
        </form>
      </div></td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($rscustomers);
?>

ToonMariner · September 6, 2007

if you want it on the fly then ajax is the only option - send an ajax request to a php script that queries the database and returns a delimited string of the results. The javascript function that handles the request repsonse should then split the string and set the values of the input elements to the correct val - you will of course need to know which value shoudl go where as you'll only have a numerically index array to play with.

alternatively if you submit the form you can do it all with just php - same kind of method as what you currently have in place. query the database grab the results and put the relevant values in the corresponding input field with value="<?php echo $val; ?>"

hope that helps....

eits · September 6, 2007

Is it possible I could have a submit button next to the select that would just submit form to get updated information and then the original button which will actually submit data to database? If so could anybody possibly show me some code that would do so?

I am also not hot on MySQL (learning!) so how would I make

$query_conn = "SELECT * FROM tblcustomer";

get data from just the record that has been selected in the select box??

Thanks!!

ToonMariner · September 6, 2007

1. yes

2. $qry = "SELECT * FROM `tblcustomer` WHERE `client_dd` = " . $_POST['client_dd'];

$qry = mysql_query($qry);

$data = mysql_fetch_assoc($qry);

eits · September 6, 2007

1. yes

2. $qry = "SELECT * FROM `tblcustomer` WHERE `client_dd` = " . $_POST['client_dd'];

$qry = mysql_query($qry);

$data = mysql_fetch_assoc($qry);

$query_conn = "SELECT * FROM 'tblcustomer' WHERE 'client_dd' = " . $_POST['client_dd'];

$conn = mysql_query($query_conn, $timedb) or die(mysql_error());

$row_conn = mysql_fetch_assoc($conn);

$totalRows_conn = mysql_num_rows($conn);

gives me an error when the page loads:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

ToonMariner · September 6, 2007

NOT single quotes around the table name anf field name but the back tick (top left button on standard keyboard)

` - NOT - '

Sign In

Populate Form from Database

Recommended Posts

eits

Link to comment

Share on other sites

ToonMariner

Link to comment

Share on other sites

eits

Link to comment

Share on other sites

ToonMariner

Link to comment

Share on other sites

eits

Link to comment

Share on other sites

ToonMariner

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information