Solved

Populate html table based on drop-down form value without submitting the form

Posted on 2011-09-22
8
553 Views
Last Modified: 2012-05-12
I have a form on a PHP page that allows a user to enter billing information into a MySQL database. One of the form fields is a drop-down menu that allows the user to select the Client to which to apply the billing input.

Beneath that form is a table which lists all recent transactions from the billing database. I need to modify the table to only display billing entries for a specific client, as selected in the drop-down in the form.

How can I get the client selected from the drop-down menu to execute my MySQL query with the appropriate value and populate the table on the same page, WITHOUT submitting the entire form (which would be blank, since the Client Dropdown is the first input)? The Client drop-down selection can't be reset after it passes the value to the table, since it's still required to submit the input form.

Thanks in advance for your suggestions!
0
Comment
Question by:goodie069
  • 3
  • 3
  • 2
8 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36583957
Use two forms.  You can put any number of forms on a page.  Or use two SUBMIT controls with different values.  You can see the value of a SUBMIT control if you give the control a name.  It will be in the $_POST array.

If you have got an programming already written, please post that and I'll try to show you a good design pattern based on what it looks like you're trying to do.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36584163
0
 

Author Comment

by:goodie069
ID: 36586829
I have a good start - I just haven't figured out how to tie in the drop-down selection as a POST without resetting the original form.

The attached file works if you add the "client" string to the URL (e.g. billing.php?client=8).

Thanks for your help.

 billing.php.txt
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36586881
you need two scripts : billing.php and getClient.php (for example) :
<?
include ('config.php');
$ccc = $_GET['client'];

$resultP = mysql_query("SELECT billing.*, client.*
                FROM   billing, client
                WHERE billing.client = client.id HAVING billing.client = $ccc
                         ORDER by date asc ");

If ($ccc == '') {echo ""; die;}

while ($r = mysql_fetch_array($resultP))
{

                $dateP       = $r["date"];
                $clientP     = $r["client"];
                $jobtypeP    = $r["job_type"];
                $employeeP   = $r["employee"];
                $descP       = $r["description"];
                $chargeP     = $r["charge"];
                $paymentP    = $r["payment"];
                $billedfromP = $r["billed_from"];
                $fnameP      = $r["fname"];
                $lname       = $r["lname"];

round ($chargeP, 2);
round ($paymentP,2);
$name = "$fnameP $lname";
$charge2P = number_format ($chargeP,2);

if($color == "silver") {
     $color = "white";
    } else {
      $color = "silver";
    }
                echo "<tr bgcolor = '$color'>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$dateP &nbsp;</td>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$clientP &nbsp;</td>";
                echo "<td nowrap valign='top'><font size='2'>$name &nbsp;</td>";
                echo "<td nowrap valign='top'><font size='2'>$jobtypeP &nbsp;</td>";
                echo "<td nowrap valign='top'><font size='2'>$billedfromP &nbsp;</td>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$chargeP</td>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$paymentP</td>";
                echo "<td nowrap valign='top'><font size='2'>$descP &nbsp;</td>";
                echo "</tr>";
}
?>

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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 36586992
Here is the billing.php script.  It's useful to post code at EE in clear text using the code snippet - we get a unispace font and line numbers.  That makes communication about the code easier.

I would like to suggest three things here to help you understand the code.  First, get rid of the compound statements like the collection of stuff on line 1 of the script.  It's hard to read that sort of thing and if it is hard to read, it is also hard to understand, debug and test.

Next, add error_reporting(E_ALL) to the top of the script.  When you are developing program code, you really want to see all the notices and errors.  By doing that you will be able to see if the program code accidentally relies on undefined variables or constants (usually the result of a typographical or coding error).  Hint: this script does.

And third, learn how to visualize query errors.  MySQL is not a black box -- it can and will fail, sometimes for reasons that are outside of your control.  You must test the return values from mysql functions in accordance with the instructions given on php.net and you will find that your script needs to print out the values of mysql_errno() and mysql_error() in certain cases, such as when mysql_query() returned FALSE.

You might enjoy this book.  It will teach you a lot of what you need to know in order to do what you're trying to do.
http://www.sitepoint.com/books/phpmysql4/

Best of luck with your project, ~Ray
<? ini_set("session.gc_maxlifetime", "18000"); session_start(); if ($_SESSION[auth] !== "12345") {echo "You are not authorized to view this page."; unset($_SESSION[auth]); unset($_SESSION[username]); exit;} if ($_SESSION[username] == "") {echo "You are not authorized to view this page."; unset($_SESSION[auth]); unset($_SESSION[username]); exit;} //{echo "You are an authorized user.";} 
$username = $_SESSION[username];

include ('config.php');

echo "<title>$coname Transaction Input</title>"; ?>

<style type="text/css"> @import url("EMZ.css"); </style>
<script language="JavaScript" src="datetimepicker.js"></script> 
<script src="gen_validatorv4.js" type="text/javascript"></script>
<style>
<!--
.hide { position:absolute; visibility:hidden; }
.show { position:absolute; visibility:visible; }
-->
</style>


<link rel="stylesheet" type="text/css" href="ddsmoothmenu.css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="ddsmoothmenu.js">

/***********************************************
* Smooth Navigational Menu- (c) Dynamic Drive DHTML code library (www.dynamicdrive.com)
* This notice MUST stay intact for legal use
* Visit Dynamic Drive at http://www.dynamicdrive.com/ for full source code
***********************************************/

</script>

<script type="text/javascript">
ddsmoothmenu.init({
	mainmenuid: "smoothmenu1", //menu DIV id
	orientation: 'h', //Horizontal or vertical menu: Set to "h" or "v"
	classname: 'ddsmoothmenu', //class added to menu's outer DIV
	//customtheme: ["#1c5a80", "#18374a"],
	contentsource: ["smoothmenu", "navmenu.php"] //"markup" or ["container_id", "path_to_menu_file"]
})
</script>

<body>
<TABLE width="800px" border="2" cellpadding="1" bordercolor="gray" align="center" bgcolor="white">
<TR><TD>
<font face = "arial" size="2">

<?php  

   if ($submit) {  

        if ($clientsubmit) {  
            $sql = "  
                INSERT INTO client (client, active) 
                VALUES ('$newclient', '1') 
            ";  
            $result = mysql_query($sql);

      } else if ($submitbill)  {  
	$sql = "INSERT INTO billing (date, username, client, job_type, billed_from, charge, payment, description) VALUES ('$date', '$username', '$client', '$jobtype', '$billedfrom', IF('$charge'='',NULL,'$charge'), IF('$payment'='',NULL,'$payment'), '$desc')";  
            
	$result = mysql_query($sql);  

{
              echo "Transaction added succesfully! <a href='billing.php?employee=Staff_1&submit=Return'>Click here</a> if you are not redirected. <meta http-equiv=refresh content=2;url=billing.php?employee=Staff_1&submit=Return><br /></body></html>";//meta refresh gets rid of old POST data
              exit;
  
//$display_block2 = "<h5><font color=\"red\">Record added! Use Return to continue. DO NOT Refresh this page or use the Back Arrow because it will add the transaction again.</font></h5>";

$employee = "Staff_1";

$submit = $_POST[submit];

//$greeting = "<script src=\"xaramenu.js\"></script><script Webstyle4 src=\"tbpro.js\"></script>";

//If ($display_block2 !="") {echo $greeting;}

//echo "<form action=\"$_SERVER[PHP_SELF]\" methhod=\"post\">
//       <p><input type=\"submit\" name=\"submit\"  value=\"Return\"></p>
//       </FORM>";

}

echo $display_block2; exit;

}

?>

<div width="100%" align="center" style="margin-top:5px;"><img src="logo-transactionInput.png">
<div id="smoothmenu">
</div>

<form name="form01" method="POST" action="billing.php"> 

    <table cellpadding="1" align="center" width="100%"> 
        <tr bgcolor="green"> 
	    <th><font color="white" size="2">Date</th>	
            <th><font color="white" size="2">Account</th> 
            <th><font color="white" size="2">Transaction Type</th> 
            <th><font color="white" size="2">Billed From</th> 
           
        </tr> 
        <tr> 

	   <td>
<input name="ddate" id="ddate" type="date" size="11" value="<? echo date('m-d-Y'); ?>" disabled="disabled">
       </td>

        <td> 
                <select name="client" id="client">
		<option value="000">Select a Client</option> 
<?php  

        $sql = " 
            SELECT id, client, active, fname, lname 
            FROM client WHERE active = 1 order by client asc 
        ";  
        $result = mysql_query($sql);  

        while ($row = mysql_fetch_array($result))   
        {  
            $client = $row["client"];  
            $id = $row["id"];  
	    $firstname = $row["fname"];
	    $lastname = $row["lname"];
            echo "<option value='$id'>$client | $lastname, $firstname</option>";  
        }  

?>  
                </select> 
            </td>
 
            <td> 
                <select name="jobtype"> 
			 <option style="background-color:Gray;color:White;font-weight:bold;" value="000">Select a Transaction Type</option>
          		 <option value="Filing Fee">Filing Fee</option>
			 <option value="Service Fee">Service Fee</option>			
			 <option value="Photocopy Charge">Photocopy Charge</option>
               		 <option value="Witness Fee">Witness Fee</option> 
                    	 <option value="Court Reporter">Court Reporter</option> 
			 <option value="Travel">Travel</option> 
                    	 <option value="Expert Witness Fee">Expert Witness Fee</option> 
                    	 <option value="Pre-Depo Conference">Pre-Depo Conference</option>
			 <option value="Telephone Conference">Telephone Conference</option>
			 <option value="Attorneys Fee">Attorneys Fee</option> 
			 <option value="Courier">Courier</option>
			 <option value="FedEx">FedEx</option>
			 <option value="Parking">Parking</option>
			 <option value="Other Charge">Other Charge (See Notes)</option>
			 <option style="background-color:Gray;color:White;font-weight:bold;" value="000">--- Misc. Payment | Credit ---</option>				<option value="Client Reimbursement">Client Reimbursement</option>
              		 <option value="Settlement">Settlement</option>
			 <option value="Void">Void</option>
			 <option value="Other Credit">Other Credit (See Notes)</option>	
		</select> 
            </td> 
            
		  <td> 
                <input type="text" name="billedfrom" id="billedfrom" size="30"> 
            </td> 
            
		  </tr>

		  <tr bgcolor="green">
		  <th><font color="white" size="2"></th>
		  <th><font color="white" size="2">Charge | Debit</th> 
            <th><font color="white" size="2">Payment | Credit</th> 
            <th><font color="white" size="2">Notes</font></th></tr><tr> 
		  
 		  <td nowrap valign="top"> 
		</td>

		  <td nowrap valign="top"> 
                <input type="text" name="charge" id="charge" size="11" value=""><font color="gray" size="2">&nbsp;[No $ Sign]</font> 
            </td>

            <td nowrap valign="top"> 
                <input type="text" name="payment" id="payment" size="11" value="">&nbsp;<font color="gray" size="2">[No $ Sign]</font> 
            </td> 

            <td> 
                <textarea name="desc" rows="3" cols="25" ></textarea> 
            </td> 

        </tr> 

        <tr> 
            <td align="center" colspan="4" nowrap>
                <input type="hidden" name="submit" value="1">
		<input type="hidden" name="username" value="<? echo $username; ?>">
		<input type="hidden" name="date" value="<? print date('m-d-Y');?>">
                <input style="text-align:center;" type="submit" name="submitbill" value="Add Transaction" style="float:left"></form> 
<script type="text/javascript">
var frmvalidator = new Validator("form01");
frmvalidator.addValidation("jobtype","dontselect=000","Please select a Transaction Type");
frmvalidator.addValidation("client","dontselect=000","Please select a Client");
</script>
            </td> 
        </tr>
</table>
<hr>
<?
    } /* End of if ($submit) */

echo "<font face='Arial' size='2' align='center'><H3>Transaction Viewer</H3></font>";
?>
<table width="800px" border="0" cellpadding="1" align="center" bgcolor="white">
    <tr bgcolor="green">
        <th nowrap><font color="white" face="arial" size="2">Date</th>
        <th nowrap><font color="white" face="arial" size="2">Client ID</th>
        <th nowrap><font color="white" face="arial" size="2">Client Name</th>
        <th nowrap><font color="white" face="arial" size="2">Transaction Type</th>
        <th nowrap><font color="white" face="arial" size="2">Billed From</th>
        <th nowrap><font color="white" face="arial" size="2">Charge</th>
        <th nowrap><font color="white" face="arial" size="2">Payment</th>
        <th><font color="white" face="arial" size="2">Description</th>
    </tr>

<?
include ('config.php');
$ccc = $_GET['client'];

$resultP = mysql_query("SELECT billing.*, client.*
                FROM   billing, client
                WHERE billing.client = client.id HAVING billing.client = $ccc
                         ORDER by date asc ");

If ($ccc == '') {echo ""; die;}

while ($r = mysql_fetch_array($resultP))
{

                $dateP       = $r["date"];
                $clientP     = $r["client"];
                $jobtypeP    = $r["job_type"];
                $employeeP   = $r["employee"];
                $descP       = $r["description"];
                $chargeP     = $r["charge"];
                $paymentP    = $r["payment"];
                $billedfromP = $r["billed_from"];
                $fnameP      = $r["fname"];
                $lname       = $r["lname"];

round ($chargeP, 2);
round ($paymentP,2);
$name = "$fnameP $lname";
$charge2P = number_format ($chargeP,2);

if($color == "silver") {
     $color = "white";
    } else {
      $color = "silver";
    }
                echo "<tr bgcolor = '$color'>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$dateP &nbsp;</td>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$clientP &nbsp;</td>";
                echo "<td nowrap valign='top'><font size='2'>$name &nbsp;</td>";
                echo "<td nowrap valign='top'><font size='2'>$jobtypeP &nbsp;</td>";
                echo "<td nowrap valign='top'><font size='2'>$billedfromP &nbsp;</td>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$chargeP</td>";
                echo "<td nowrap valign='top' align='center'><font size='2'>$paymentP</td>";
                echo "<td nowrap valign='top'><font size='2'>$descP &nbsp;</td>";
                echo "</tr>";
}
?>
</body>
</html>

Open in new window

0
 
LVL 82

Accepted Solution

by:
leakim971 earned 400 total points
ID: 36587018
with : <select name="client" id="client" onchange="if(this.value!='000') updateNext(this.value)">

and your javascript+ajax :


<script>
function updateNext(clientid) {
  var xmlhttp;
  if(window.XMLHttpRequest)xmlhttp=new XMLHttpRequest();else xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  xmlhttp.onreadystatechange=function()
  {
      if (xmlhttp.readyState==4 && xmlhttp.status==200)
      {
             var tables = document.getElementsByTagName("table");
             tables[tables.length-1].innerHTML = xmlhttp.responseText; // we update the table
      }
  }
  xmlhttp.open("GET","getClient.php?client="+clientid,true);
  xmlhttp.send(null);      
}
</script>

Open in new window

0
 

Author Comment

by:goodie069
ID: 36587371
Thank you, Ray. I'll definitely check out the book and implement your suggestions. I've been hacking existing php scripts for a few months now... I suppose it's high time I actually learn the proper way of doing things and maybe write my own script for a change! Btw I really enjoyed the "20 Ways to Save Kittens and Learn PHP" article linked in your profile -- good stuff!

Leakim971 - your solution worked perfectly. I've never used Ajax (outside of some jQuery stuff), but it seems to be the perfect solution for this issue. I'll disect your code and learn from this implementation.

Thanks to both of you for your tips, suggestions and solution. You're both tremendous assets to the EE community and guys like myself would be lost without you. I hope you both have a great weekend!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36590737
Have a look at this (I hope it is available to you).  It shows the thought processes used in test-driven development, with some examples along the way.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_7830-A-Quick-Tour-of-Test-Driven-Development.html
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Modify PHP Code on the Fly? 8 43
sql sentence 2 13
showing numeric numbers 2 11
Birthdays 3 22
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

747 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

11 Experts available now in Live!

Get 1:1 Help Now