Cannot get Form to gather data from a search

PHP
1

You folks were so helpful back in 2019 when I needed help with something similar that I’m trying again. The 2019 solution has worked for years but now I’m trying to use the search field to populate the output from a form. I have looked at the “similar topics” here but none are what I’m looking for.

I’m working on an online dog show entry form. Instead of the user typing all their dog’s information into the form with the various classes they can enter, I’ve set it up so all the dog’s info is in a table named “dognames”. Then the classes they want to enter will go into the “entries” table, along with the dog info pulled from the search results.

I’ve been struggling with it for 3 days now and have a niggling feeling that I have the search form in the wrong place but no matter what I try, the data is not transferred over to the “entries” table. It throws up an error that the field RegNo (dog’s registration #) cannot be empty, so obviously the search data doesn’t make it to the form.

I’ve removed a LOT of non-pertinent info (such as the 20-odd classes which can be entered), leaving just one class - so the code isn’t miles long. Note that the search result is named RegNoSearch:

<body>
	
<form action="" method="post">Enter registration number:
	<input type="text" name="RegNoSearch" title="Registration Number" size="12" maxlength="12" />
	<input type="submit" value="Search"/>
</form>	<br>

<?php

	// adding the next three lines should display the error
	// declare(strict_types=1);
		error_reporting(-1);
		ini_set('display_errors' ,'1');

	$servername = “ ”;
	$username = “ ”;
	$password = “ ”;
	$dbname = " ";

	// Create connection
		$conn = mysqli_connect($servername, $username, $password, $dbname);
	// Check connection
		if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	} 

	if ($_SERVER['REQUEST_METHOD'] == "POST") {

		$SearchRegNo = $_POST["RegNoSearch"]; // RegNoSearch = field name from above form

		$sql = "SELECT * FROM dognames WHERE RegNo LIKE '$SearchRegNo'";
		$result = $conn->query($sql);
	
	echo '<pre>'; print_r($result);
	
	if ($result->num_rows >0) {

		// output data of each row
		while($row = $result->fetch_assoc()) {
		echo "<p class='left'><br>
		Registration Number: ". $row["RegNo"]. 
		"<br>Dog Name: ". $row["ConfTitles"]. " " . $row["DogName"]. " ". $row["FldTitles"].
		"<br>Owner: ". $row["Owner"]. 
		"</p>";
	}
		} else {
		echo "<p class='center'><br>0 results";
		}
	}
?>

<p>Conformation Class:  
	<select name="ConfClass" title="Conformation Class">
	<option value="" selected>No Entry</option>
	<option value="6-9 Puppy">6-9 Puppy</option>
	<option value="9-12 Puppy">9-12 Puppy</option>
	</select></p>

<p>Owner-Handled Eligible? 
<select name="OwnerHandled">
<option value="No" selected>No</option>
<option value="Yes">Yes</option>
</select>
</p>

<p>Clicking the button below will send your entry.</p>

<p class="center">
<input name="knlSubmit" type="submit" id="knlSubmit" value="SUBMIT ENTRY" />
</p>

</form>

	<div id="footer">
			 <p class="small">&copy; <?php 
				  $thisYear = (int)date('Y'); 
				 echo $thisYear;?> Borzoi Club of America Inc. • Last updated <!-- #BeginDate format:Am1 -->February 10, 2024<!-- #EndDate --> • <a href="../sitemap.php" title="Sitemap for the BCOA website">SITEMAP</a> • <a href="../copyright.php">Copyright Policy</a>
			 </p>       
	</div> <!-- footer -->

</body>
2

You need to edit your post and add either bbcode [code][/code] tags or three mark-down back-ticks ```, on their own lines, before and after your code.

The code on a page should be laid out in this general order -

  1. initialization
  2. post method form processing
  3. get method business logic - get/produce data needed to display the page
  4. html document

The data in the dognames table should be related to the owner by an owner id. You should have an owner table that contains the owner data.

When an owner logs into your site, their user id should be stored in a session variable. In the get method business logic section of the code, you would query for and fetch the dog information matching the logged in user. In the html document, you would produce a select/option menu to allow selection of which of multiple dogs by the logged in owner to operate on. If there is only one choice, you can instead just display the single dog’s information. The value you submit should be the dog id (autoincrement primary index from the ‘dognames’ table.) You would store this id with any related data, such as the entries data.

You should either have a database table or an array that holds the classes data. This will establish a class id (autoincrement primary index.) You would submit this id and store it with any related data, such as in the entries table.

You should have a show/event table, that holds the unique/one-time information about each event - date, time, location, … This would establish a show id (autoincrement primary index.) The logged in owner would select the show being registered for. The show id would be submitted. This would be stored in with any related data, such as in the entries table.

The form that submits the data to be inserted into the entries should be a post method form, since it is performing an action on the server. Any search form should be a get method form.

You should not put external, unknown, dynamic values directly into sql query statements, where an sql special character in a value can break the sql query syntax, Use a prepared query instead. If it seems like using the mysqli extension is overly complicated, especially when dealing with prepared queries, it is. This would be a good time to switch to the much simpler PDO extension.

Since the insert query could produce duplicates, e.g. someone has already registered a dog id for a class id in a show id (these three columns need to be defined as a unique composite index), you should have exception error handling for the execution of the insert query, test if the error number is for a duplicate index error, and setup a message for the user letting them know what was wrong with the data that they submitted. For all other error numbers, just rethrow the exception and let php handle it.

A LIKE comparison in a query, without any wild-card characters, is the same as an = (equal) comparison. Just use an equal comparison in this case.

3

Oh shoot. The code was all separated out before I pasted it in here and I didn’t notice that it had gotten all squished together. I’m so sorry!

When I’ve built an entry system using a stand-alone database (FileMaker Pro) I did have all those different tables and intend to do the same again but haven’t broken everything apart yet because there’s no point in doing that if I can never figure out how to get the search data to pass to the form so that it can be entered into the table using the submit button. Your suggestion about relating the dognames to the owner by an owner id and having the classes in a separate table is EXACTLY what I did back in the day before I started fighting with mysql. :slight_smile:

I think I understand what you mean by adding the bbcode or back-ticks, so I’ll give that a try and re-post the code. I’m no spring chicken, so my brain is struggling somewhat but I’m determined, so hopefully the penny will drop at some point. :slight_smile:

Thank you so much, mabismad, for your detailed post!

5

Sigh. I’ve tried adding the word code in square brackets as you suggested to the beginning of all the code and the closing tag at the end and it does make the first part (the search box) display correctly but it’s still squishing together the chunk of php coding which is squished together in the original above.

How do I get that part to display correctly here?

6

The opening and closing [code] [/code] tags (there’s no spaces in them, despite appearances) need to be on their own separate lines, before your first line of code and after your last line of code.

7

That’s where I had them (with no spaces) in my 2nd example of the code - which I deleted because the middle section was still squished together.

I had the the opening one before the body tag and the closing one after the closing body tag when I first tried. Then when it was still squished I tried keeping the codes I originally had (top and bottom) and added and ending one after the search section and a beginning one before the following <?php but it was still squished after the search part and up to the part starting with
echo ’
'; print_r($result);

Obviously I need to add more of them but I can’t figure out where.

I live in Canada, so am heading to bed now. Didn’t want you to think I’m ignoring you if I don’t reply right away. :slight_smile:

8

There should be no need for multiple sets of bbcode tags. Perhaps a mod/admin will happen by and edit the first post. One thing you can do is edit the first post and make any change to it at all. This will make the edit history available, where the raw post can be seen/copied.

1 Like
9

I’ve formatted your code for you @Helen_W_Lee :slight_smile:

2 Likes
10

Oh! Thank you so much, Gandalf! I was just going to try to figure out how to do that before I head out to work. Much appreciated!

11

Gandalf did, which I greatly appreciate. Now I’m hoping that someone can figure out why the code isn’t working. There’s no point in me trying to build the full system (as per your suggestions, which have been my intent all along … eventually. :slight_smile: ) if I can’t figure out why the search info isn’t getting output. I’ve almost got the code memorized, I’ve been staring at it for so long but the penny just won’t drop.

1 Like
12

What output are you getting from the code?

13

I can’t see any code in the section you posted that would produce that error message. Is that one of the bits you snipped out? As that’s the bit that’s failing for you, it might help to be able to see the code that produces it.

I also don’t see where you start the second form, again that may be a bit you cut out for brevity.

1 Like
14

No, that’s not one of the bits I snipped out. There are 40 fields total, so if I included them all it would be WAY too long, so I’ve just left in 3 fields, to keep it short.

It throws that error because it’s not getting the result of the search field added to the rest of the information in the form which is precisely my problem.

That error, though, comes from the nationalentry-ENTRY-insert.php action on the ENTRY form, not on the search form. That’s why I think I might have the search form in the wrong place.

In case anyone needs to know, the nationalentry-ENTRY-insert.php coding is:

<?php require_once('../Connections/borzoiclubdbPDO.php'); ?>
<?php

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO entries (RegNo, DogName, Owner,)
	VALUES (:RegNo, :DogName, :Owner)");
	
    $stmt->bindParam(':RegNo', $RegNo);
     $stmt->bindParam(':DogName', $DogName);
     $stmt->bindParam(':Owner', $Owner);
 	
    // insert a row
    $RegNo = ($_POST['RegNo']);
    $DogName = ($_POST['DogName']);
    $Owner = ($_POST['Owner']);
     $stmt->execute();
    }

catch(PDOException $e)
   {
     echo "Connection failed: " . $e->getMessage();
    }

	echo '<meta http-equiv="refresh" content="1; URL=../nationalentries/national-add-dog.php" />';

if (version_compare(PHP_VERSION, '5.0.0', '<') ) exit("Sorry, this version of PHPMailer will only run on PHP version 5 or greater!\n");

$conn = null;
?>

I don’t know if it’s allowed but if it is, I can post the actual url, if that helps troubleshoot. I really do NOT want to have to stick with what we did last year because the users refused to proofread what they typed OR read the instructions about how to deal with the forms and it took us hours to fix everything before we could publish the catalogue.

ANY suggestions will be welcomed. I’m almost out of time getting this done. :smiling_face_with_tear:

15

This is the originally stated problem at the top of this thread. We cannot help with what is causing this, because you have not posted enough of the code. I recommend that you post the complete code, less any database credentials, at github, then post a link here. Best guess is that the form that submits the entry data doesn’t have a ‘RegNo’ field in it.

What you are showing us in this code, is repeating the same data values in multiple locations. This falls under a subject called ‘data normalization’. Also Don’t Repeat Yourself (DRY) programming. The dog’s RegNo tells you everything you need to know about the dog, e.g. its name, owner (id), … You don’t repeat this known data in the entries table. The entries table should only hold the unique/one-time information about each dog at each show, e.g. show_id, dog_id, and class_id (assuming that the class can be selected per show.)

Some points about the PDO based code -

  1. If you are using the PDO extension here, why did you bother with it for the code at the top of this thread? Just use the PDO extension everywhere.
  2. You should name the connection variable $pdo or similar, so that you can see/search which code is using which database extension.
  3. set the character set to match your database tables (you should be doing this with the mysqli extension, but are not), so that no character conversion occurs when sending data to/from the database. this is also required with emulated prepared queries to prevent any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished.
  4. set the emulated prepared query setting to false, you want to run real prepared queries whenever possible.
  5. set the default fetch mode to assoc, so that you don’t need to specify it in each fetch statement.
  6. Simply supply an array of input values to the execute([…]) call, eliminating all bindParam() statements.
  7. Because this query could produce an error for duplicate data, e.g. a dog has already been entered at a show, the exception try/catch logic should be testing for a duplicate index error number, and setup a message for the user letting them know what was wrong with the data that they submitted. For all other error numbers, simply rethrow the exception and let php handle it.

If you have more then 2-3 fields, you should be using a data-driven design, where you have a data structure (array, database table) that defines the expected fields, their validation, and processing. You would then loop over this defining information to dynamically validate the data, then if there are no validation errors, you would loop over it to dynamically build, then execute the sql query.

I’m wondering how many of these fields are unnecessary duplication of data that’s stored elsewhere in the database and will go away when you normalize the data?

As to the code for the post method form processing -

  1. you should detect if a post method form was submitted before referencing any of the form data.
  2. keep the form data as a set in an array variable. i.e. don’t copy variables to other variables for nothing. This is just a waste of typing.
  3. trim all the input data, mainly so that you can detect if all white-space characters were entered. After you do item #2 on this list, you can accomplish this with one single line of code.
  4. validate all now trimmed data separately, storing user/validation errors in an array, using the field name as the array index. If you were validating this data on the server and the RegNo is empty, you would never get to the point of executing the insert query.
  5. after the end of the validation logic, if there are no errors, use the submitted data.
  6. after using the data, if there are no errors, redirect to the exact same url of the current page to cause a get request. this will prevent the browser from trying to resubmit the form data.
  7. if you want to display a one-time success message, store it in a session variable, then test, display, and clear that session variable at the appropriate location in the html document.
  8. if there are errors at item #6, the code will continue on to display the html document, display any user/validation errors, and redisplay the form, populating the field values/selects with existing data so that the user doesn’t need to keep reentering data over and over.
  9. any value you output in a html context should have htmlentities() applied to it to help prevent cross site scripting.

Unless this is an application that you are distributing for others to install and use, there’s no need for any version testing in the code.

There’s also no need to close database connections in your code, because php destroys all the resources that were created when your script ends.

As to the bad User eXperience (UX) and data entry errors, if you are selecting from existing data choices, rather than requiring a lot of typing of data, it will simplify what the user must do and eliminate chances of typo mistakes.

As to your last stated problem, of why the search info isn’t getting output, you didn’t answer the question that I asked -

16

I’m pretty sure I did reply to someone about the error it threw up but maybe not you. :slight_smile:

In any event, I’ve finally figured it all out, thanks to a fellow club-member who happens to be a member of this forum and wrote to me privately. If I’d known he was familiar with this stuff I would have contacted him directly, so my apologies to the forum members.

I’ve ended up using 3 pages instead of just one and it works perfectly. I very much appreciate all the help I’ve received here.