Solved

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

Posted on 2011-09-22
8
602 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
[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
  • 3
  • 3
  • 2
8 Comments
 
LVL 110

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
 

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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
 
LVL 110

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 110

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

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!

Question has a verified solution.

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

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
This video teaches users how to migrate an existing Wordpress website to a new domain.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

707 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