Auto fill form

General PHP Help
#1

I am trying to create a table where a user selects a last name from an sql query drop-down-box (Select tag with options) that is also an sql query. I can get the combo box to work fine, but I cant get the other results to populate at all.

My code:

  <table style = 'position: relative;' frame='box'>
  <caption>Customer Information</caption>
  <tr><td>Last Name</td><td>
  <select name='Customer'>
      <?php
         $mysqli = new mysqli("mysql", "USER", "PASSWORD", "DBASE");
         $query= $mysqli->query("SELECT DISTINCT * FROM Customer");

      while ($row = mysqli_fetch_array($query)) {
      echo "<option value='" . $row['LastName'] . "'>" .$row['LastName'] . "</option>";
    }   
?>          
   </select>
   </td><td width="50"></td><td>City</td><td>  </td></tr>
   <tr><td>First Name</td><td>
   <?php
   $mysqli = new mysqli("mysql", "USER", "PASSWORD", "DBASE");
   $query = $mysquli->query("SELECT * FROM Customer WHERE LastName = 'Customer'");
    while ($result = mysqli_fetch_array($query)) {
     echo "<td>'" . $result['FirstName'] . " ' " .$result['FirstName'] . "</td>";
    }   
?>         
   </td><td></td><td>State</td><td>  </td></tr>
   <tr><td>Company Name</td><td></td><td></td><td>Zip</td><td>  </td></tr>
   <tr><td>Address</td><td>  </td><td></td><td>Phone</td><td>  </td></tr>
   <tr><td>Email</td><td>  </td></tr>
   
   </table>

This will also cut off everything after the First Name

#2

Welcome to the forum!

Well itā€™s kind of hard to tell what is wrong as the code is cutoff and not formatted.

John

#3

Just so I am clear.

A user comes to your pageā€¦

Makes a selection from a dropdownā€¦and?..

Then what?

From this pointā€¦ does the page submitā€¦ and the ā€˜newā€™ page has a table pre-populated with specific data?

Orā€¦ are you saying you want to NOT submit (reload) the pageā€¦ and the table you want to populate is on the same page as the dropdown the user is interacting with?

Lets start there before anyone offers some advice

#4

Your code contains a spelling mistake in a variable name, used in an object context, resulting in a fatal php run-time error, which is the reason for the cut off output after that point.

Please, everyone trying to use php, find the php.ini on your system that php is using, ALWAYS set error_reporting to E_ALL, and when learning, developing, and debugging code/queries, set display_errors to ON. When on a live/public server, set display_errors to OFF and set log_errors to ON. Stop and start your web server to get any changes made the php.ini to take effect.

Also, why are you repeating the database connection code? Each web page should only make one database connection.

#7

@Strider64 I did a copy paste on the code, it automatically did that.

@whispers I would like to stay on the same page, and not submit and $_POST to a new page

I am very new to php. Iā€™m pretty good at HTML, but never dabbled with php or mysql until recently. I wil double check the spelling

#8

If you want to ā€˜stay on the same pageā€™ without posting/submitting the pageā€¦\

Then you will need to use AJAX to handle the dropdown change/selection.

Call a PHP script to send the data tooā€¦ and have the AJAX callback update the page with the returned data.

Is there an example of your table layout?

Or an example on how you want your table to look/display?

#9

Iā€™ve never dealt with AJAX at all. Yes, I have an example(ish). To give you a better understanding of what Iā€™m trying to do, I am attempting to use this setup to create a work order. The section I am having issues with is the callback information for the customer. There is a second table, which is lower on the page, that contains the service information, such as service address, contact name, work needed, etcā€¦

I would like to be able to select the customer from the dropdown, and have the information about the customer auto-fill with the selection.

#10

A form and its form processing code can be put on the same page. If you want the page to not refresh when data is submitted, you would use AJAX. However, if you are not at the point where you can design, write, test, and debug the php code needed to dynamically produce html elements and process the submitted form data, you are not at the point of needing to using AJAX, as it adds a layer that makes debugging harder. Learn to program using php first, then learn how to add extra things like AJAX to the process.

#11

Well you have to pick your poison here I guess.

If you want to go the AJAX route, you will need a SEPARATE .php file that does the DQ querying and handing of the returned dataā€¦ that will be passed back to the AJAX call to this external .php script.

Does that make sense to you?

Or you can select from the drop down and hit submit, and then have the page reload with that users credentials/work information.

Up to you.

The later is just a simple HTML/PHP submission form using $_POST data and a database query.

The former is a bit more involved because you need a separate/external .php file that does the work for you.

#12

Here is a quick -n- dirty way to do it with a normal HTML/PHP form type of approach:

<?
//PDO should parameterize and sanitize good enough, but in case any screen output
function cleanInput($dirtyData){   
	$cleanedData = htmlspecialchars(strip_tags($dirtyData)); 
	return $cleanedData;
}
$employee_id = isset($_POST["employee_id"]) ? cleanInput($_POST["employee_id"]) : '' ;


//check from 'mode' and set state
$mode = isset($_GET['mode']) ? $_GET['mode'] : '';

?>

<!-- Scripts -->
<script type="text/javaScript"> 

	//jQuery (event actions)
	document.addEventListener("DOMContentLoaded", function(event) { 		
		
		//enter key
		$(document).bind('keypress', function(e) {
			//enter key (code)
			if(e.keyCode==13){
				 $('#submitBtn').trigger('click');
			 }
		});			
			
		//submit button listener
		$("#submitBtn").click(function() {
			//console.log("submit button clicked");			
			
			//validation message holder
			var errormessage = '';	
			
			//any potential field validation goes here
			
			//employee
			if ($("#employee_id").val() === "") {
				errormessage += "\n Please select employee first";		
			}			
						
			//name - example
			/*			
			if($('#name').val().trim() == ''){		
				//update error message for output
				errormessage += "\n Name";						
			}	
			*/	
			
			//check if any errors were found
			if(errormessage != ''){
				event.preventDefault(); //do not submit form
				//output missing data
				alert("The following items need attention: \n" + errormessage);
			}else{
				//submit form
				$('#appForm').submit();
			}			
			
			
		});
	});
					
</script>



<!-- example form -->

<div id='appContainer'>                                
	<form id="appForm" name="appForm" method="post" action="<?=$_SERVER['PHP_SELF']?>?mode=submit">

		<select id="employee_id" name="employee_id">
		  <option value="">Select Employee</option>
		  <option value="1">Name1</option>
		  <option value="2">Name2</option>
		  <option value="3">Name3</option>
		  <option value="4">Name4</option>
		</select> 		
		
		<div id="buttonContainer" name="buttonContainer">
			<br><button id="submitBtn" type="button">Get Work Order Details</button><br>							
		</div>
	</form>
</div>
	
<?
if($mode == 'submit') {	

	echo 'ID CHECKING FOR: ' . $employee_id .'<br>';
	
	$employeeDetails_sql = "SELECT * FROM php_help WHERE employee_id = :employee_id";
	$employeeDetails_stmt = $conn->prepare($employeeDetails_sql);
	$employeeDetails_stmt->bindValue(':employee_id', $employee_id);
	$employeeDetails_stmt->execute();
	$employeeDetails_stmt->setFetchMode(PDO::FETCH_ASSOC);	
	$_employeeDetails = $employeeDetails_stmt->fetch(); //single row
	//$_employeeDetails = $employeeDetails_stmt->fetchAll(); //returns multi-dimensional array (changes way you access object below)
		
	$colcount = $employeeDetails_stmt->columnCount();
	$rowcount = $employeeDetails_stmt->rowCount();	
	//var_dump($_employeeDetails);
	
	if($rowcount <= 0){
		//no member record found
		return 'There was no username/password match<br>Please try again..<br>' . print_r($_employeeDetails);
	}else{		
		$target_id = $_employeeDetails['employee_id'];
		$target_first = $_employeeDetails['employee_first'];
		$target_last = $_employeeDetails['employee_last'];
		$target_work_order = $_employeeDetails['work_order_id'];
		
		echo 'EMPLOYEE ID: ' . $target_id . '<br>';
		echo 'FIRST NAME: ' . $target_first . '<br>';
		echo 'LAST NAME: ' . $target_last . '<br>';
		echo 'WORK ORDER#: ' . $target_work_order . '<br>';
		
	}	

}

I have no clue what your table looks likeā€¦ or what data is in it eitherā€¦

I just output some random stuffā€¦ and DID NOT make the results into a table. (no clue what it does or should look like)

Getting Selected Row from Drop Down List to perform SQL
#13

Thank everyone for the input and help. After looking at the code you laid out, @whispers, I think it will be better to do a selection on another page, and have it load the new page with the information that I need. A lot less headache and probably far less complicated than what Iā€™m trying to make it.

Guess I need to take a few college classes on web programmingā€¦ lol.

#14

Not necessarily. I remember jqueryā€™s ajax function would include an http header and I made a function isAjaxRequest() that would detect that. Based on that I would have the same .php file output html under normal situations and return an array of json data if it was an ajax request.

#15

?

Please explain more.
How would get this new header/content displayed without submitting the page?

#16

You would still need js/jquery/ajax to submit the data from the page in the web browser to the url on your server.

For example if I have a page that lists all houses for sale in my country. On that page I also have a that lists every state and another that lists every major city. By default that page lists everything in the database. If you choose a state, an ajax request is sent and the browser receives a new for the cities the only lists the cities in that state. You can then pick a city and then the whole page will refresh and go to the same url but with ?state=FL&city=miami added to it and then the page shows only houses in miami. So reducing the options of the city did not refresh the whole page but reducing the overall search results returned did involve a full page refresh.

Check out the Location on this page: https://www.bahamasrealty.com/search.php

#17

OK, New question. I changed the form so that the information is submitted from a different page (Effectively, it just looks up the information from the query on p. 1 and returns all the values on p. 2), so that problem is solvedā€¦

NOW, I cannot get the sql to INSERT INTOā€¦ It keeps returning the ā€œRecord not Insertedā€ message. I have checked all my syntax, I think it is right.

<?php
session_start();
?>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Insert</title>
  </head>
  <body>
<?php
require('connect.php');

$CompanyName = $_POST['CompanyName'];
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$Adress = $_POST['Address'];
$City = $_POST['City'];
$State = $_POST['State'];
$ZipCode = $_POST['ZipCode'];
$Office = $_POST['Office #'];
$Home = $_POST['Home #'];
$Cell = $_POST['Cell #'];
$Fax = $_POST['Fax #'];
$CompanyEmail = $_POST['CompanyEmail'];
$PersonalEmail = $_POST['PersonalEmail'];
$BillingType = $_POST['BillingType'];
$NetDays = $_POST['NetDays'];
$BillingContact = $_POST['BillingContact'];
$BillingAddress = $_POST['BillingAddress'];
$BillingCity = $_POST['BillingCity'];
$BillingState = $_POST['BillingState'];
$BillingZip = $_POST['BillingZip'];
$BillingPhone = $_POST['BillingPhone'];
$BillingEmail = $_POST['BillingEmail'];
$PreferredRate = $_POST['PreferredRate'];
$ReferredBy = $_POST['ReferredBy'];
$ReferralDiscount = $_POST['ReferralDiscount'];
$AssurancePlan = $_POST['AssurancePlan'];
$AssuranceLevel = $_POST['AssuranceLevel'];

$sql = "INSERT INTO Customer (CompanyName, FirstName, LastName, Address, City, State, ZipCode, Office#, Home#, Cell#, Fax#, CompanyEmail, PersonalEmail, BillingType, NetDays, BillingContact, BillingAddress, BillingCity, BillingState, BillingZip, BillingPhone, BillingEmail, PreferredRate, ReferredBy, ReferralDiscount, AssurancePlan, AssuranceLevel) VALUES ('$CompanyName', '$FirstName', '$LastName', '$Address', '$City', '$State', '$ZipCode', '$Office', '$Home', '$Cell', '$Fax', '$CompanyEmail', '$PersonalEmail', '$BillingType', '$NetDays', '$BillingContact', '$BillingAddress', '$BillingCity', '$BillingState', '$BillingZip', '$BillingPhone', '$BillingEmail', '$PreferredRate', '$ReferredBy', '$ReferralDiscount', '$AssurancePlan', '$AssuranceLevel')";
 
if ($conn->query($sql) === TRUE) {
   echo "<script type='text/javascript'> alert('Customer Created'); </script>";"
   echo "<script type='text/javascript'> document.location = 'Main.php'; </script>";
} else {
   echo "<script type='text/javascript'> alert('Record Not Inserted'); </script>";
   echo "<script type='text/javascript'> document.location = 'customer.php'; </script>";
}

?>
</body>
</html>

And I canā€™t seem to be able to stop this reply box from actually running script, any help with that would also be appreciated!

#18

try using [ code ] [ /code ] tags (no spaces) so people can read your code better.

#19

Every bit of this code is dangerous junk. Trash it and learn how to use PDO with Prepared Statements.

https://phpdelusions.net/pdo

1 Like
#20

You need to put the bbocde code tags on lines that donā€™t have anything else on them (apparently the script kiddies that wrote this forum software werenā€™t very good programmers.)

Please, when you are first learning, do NOT write out line after line of code for each possible form field. This is just a waste of your time. Start with a form and form processing code for just one form field. Get that to work, then you can deal with the code needed for the other 20+ form fields (which you should process dynamically, using simple, reusable code, not by writing out code for each field.)

Your form processing code should -

  1. Detect that a post method form was submitted.
  2. Trim all the data at once, as a set, using a single line of code (if I/someone has time they will post an example showing how to do this.)
  3. Validate the inputs, storing validation error messages in an array.
  4. If there are no validation errors (the above array is empty), use the submitted data.
  5. Use a prepared query when supplying external/unknown data to an sql query statement.
  6. Use the simpler PDO extension. Your code at the top of this thread is using the mysqli extension. The mysqli extension is overly complicated and inconsistent when dealing with prepared queries.
  7. Use exceptions for database statement errors (connection, query, prepare, and execute) and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database errors will ā€˜automaticallyā€™ get displayed/logged the same as php errors.) The exception to this rule is when inserting/updating user submitted data and thereā€™s a duplicate or out of range value. In this case, your code would catch the exception, detect if the error number is one you code will handle, then setup a message telling the user exactly what was wrong with the submitted data. If the error number is not one that your code is handling, re-throw the exception and let php handle it.
  8. Do NOT compare boolean values with TRUE or FALSE values. This is missing the point of using boolean values at all. Keep It Simple (KISS.)

Once you do item #7 on this list, you will be getting an error that will help find whatā€™s causing the query to fail.

#21

Hereā€™s another item for the above list -
9. Put the post method form processing code above the start of the html document, not inside the html document.

#22

The OP was given a legit PDO approach aboveā€¦ (not sure why people come hereā€¦ ask for help , get itā€¦ then abandon it?)

Sponsor our Newsletter | Privacy Policy | Terms of Service