HTML Form is not adding data to MySQL database?

jmyrtle · January 13, 2020, 2:09pm

I am writing PHP code where I am trying to insert user data from an HTML form into a MySQL database for recording attendance. The problem is, I am not getting an error when I run my code, but there is no data being inserted and I can’t figure out why?

Here is the code I have:

<?php

include('nav/head.php');

?>

<!DOCTYPE html>
<html lang="en">

<head>

  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">
  
<style type="text/css">
    /* Formatting search box */
    .search-box{
        width: 1648px;
        position: relative;
        display: inline-block;
        font-size: 16px;
    }
    .search-box input[type="text"]{
        height: 32px;
        padding: 5px 10px;
        border: 1px solid #CCCCCC;
        font-size: 14px;
    }
    .result{
        position: absolute;        
        z-index: 999;
        top: 100%;
        left: 0;
    }
    .search-box input[type="text"], .result{
        width: 100%;
        box-sizing: border-box;
    }
    /* Formatting result items */
    .result p{
        margin: 0;
        padding: 7px 10px;
        border: 1px solid #CCCCCC;
        border-top: none;
        cursor: pointer;
		background-color: #FFFFFF;
    }
    .result p:hover{
        background: #f2f2f2;
    }
</style>
<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
    $('.search-box input[type="text"]').on("keyup input", function(){
        /* Get input value on change */
        var inputVal = $(this).val();
        var resultDropdown = $(this).siblings(".result");
        if(inputVal.length){
            $.get("api/search/backend-search.php", {term: inputVal}).done(function(data){
                // Display the returned data in browser
                resultDropdown.html(data);
            });
        } else{
            resultDropdown.empty();
        }
    });
    
    // Set search input value on click of result item
    $(document).on("click", ".result p", function(){
        $(this).parents(".search-box").find('input[type="text"]').val($(this).text());
        $(this).parent(".result").empty();
    });
});
</script>

  <title>CCRP | Home</title>
  
  <?php include('nav/header.php'); ?>
	
	<h1 class="h3 mb-2 text-gray-800">Record Attendance</h1>
	<br>
	<form action="api/attn_record.php" method="post">
		<div class="form-group search-box">
			<label for="member_name">Member Name</label>
			<input type="text" class="form-control" id="member_name" placeholder="Search for Member..." maxlength="" autocomplete="off" />
			<div class="result"></div>
		</div>
		<div class="form-group">
			<label for="member_email">Member's Email Address</label>
			<input type="text" class="form-control" id="member_email" placeholder="" maxlength="255" autocomplete="off" />
		</div>
		<div class="form-group">
			<label for="member_phone">Member's Phone Number</label>
			<input type="text" class="form-control" id="member_phone" placeholder="" maxlength="14" autocomplete="off" />
		</div>
		<div class="form-group">
			<label for="present">Attended &nbsp</label>
			<input type="checkbox" id="present" placeholder="" value="Attended" autocomplete="off" />
		</div>
			<input type="submit" id="btn_save" class="btn btn-primary" value="Save and Add New">
			<input type="submit" id="btn_home" class="btn btn-danger" value="Save and Close">										
	</form>
	
        </div>
        <!-- /.container-fluid -->

      </div>
      <!-- End of Main Content -->

	<?php include('nav/footer.php'); ?>

</html>

This is the PHP code where the insertion is supposed to be happening:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ccrp_db";

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 attendance (member_name, member_email, member_phone, present) VALUES (:member_name, :member_email, :member_phone, :present)");
    $stmt->bindParam(':member_name', $member_name);
    $stmt->bindParam(':member_email', $member_email);
    $stmt->bindParam(':member_phone', $member_phone);
	$stmt->bindParam(':present', $present);

// insert a row
if(isset($_POST['btn_save'])) {
    $member_name = $_POST["member_name"];
    $member_email = $_POST["member_email"];
    $member_phone = $_POST["member_phone"];
	$present = $_POST["present"];
	}
	
    $stmt->execute();

    echo "New records created successfully";
}
catch(PDOException $e)
{
    echo "Error: " . $e->getMessage();
}
$conn = null;

?>

And this is the result from the MySQL Database:

martbean · January 13, 2020, 2:18pm

Add the following to the top of your insertion script and run it again:

error_reporting(E_ALL);
ini_set('display_errors', 1);

I think you’ll find $member_name, etc. are undefined.

I always find it worth having errors displayed during development as it makes it a lot easier to highlight issues, but always ensure you are not displaying errors in production.

jmyrtle · January 13, 2020, 2:25pm

It doesn’t produce any errors for me. Just empty records like before…

martbean · January 13, 2020, 5:12pm

Try:

var_dump($member_name);

just after binding the parameters and see what you get.

jmyrtle · January 13, 2020, 5:34pm

After I run the submission, it simply says NULL

mabismad · January 13, 2020, 5:48pm

None of your form fields have name attributes, so, none of the php code referencing those form fields will be TRUE, i.e. the if(isset(…)) logic will be skipped over.

Your post method form processing code should -

Detect that a post method form has been submitted. If there’s more than one form that submits to the same page, add further logic to distinguish between the different forms.
Trim and validate all inputs, storing validation error messages in an array.
If there are no validation errors (the errors array is empty), use the submitted data. All the operational code using the input data should be inside of this conditional statement. You are currently preparing the query and executing the query outside of any logic testing if any form data has been submitted.
Use positional place-holders (question-marks ? ), and implicit binding (supply an array of the input data to the ->execute([…]) call) to avoid repetitive code and syntax for each each input.
Don’t output raw database statement errors onto a live web page. The try/catch logic for an INSERT query should only test for and tell the use when a duplicate data error has occurred. It should not output the raw error message as part of this process.

@martbean, because the OP is explicitly binding by reference, there won’t be any php errors if the variables don’t exist at the time the bindParam() statements are executed and null values will get used for these at the time the reference is evaluated when the execute() statement is executed.
.

jmyrtle · January 13, 2020, 5:57pm

So what is the difference then in the id attribute and the name attribute in HTML? I replaced id with name in my code and it’s working, but the phone number is not the characters I am entering…

For example, if I type 1234567890 in the HTML form, I get back 2147483647 in my database…

mabismad · January 13, 2020, 6:04pm

The id in the html is used in the browser. The name attribute gives the form field a name, which is required to reference the value.

For the phone number problem. Phone ‘numbers’ are not integers. They are formatted strings consisting of numerical characters and possibly ‘()’ and ‘-’ characters. They should be stored using a character data type in a database.

jmyrtle · January 13, 2020, 6:16pm

Got it. Everything appears to be working now. Thanks!

So, I have another question, but this is more advanced: The first user input in my form is actually a search bar that performs a lookup of a person’s name from another table in my database called members. Once they select the correct name that appears from the dropdown, is there a way of how I can tell the code to automatically fill in the rest of the data from the members table into the form so that I can run a report with this information later?

droopsnoot · January 13, 2020, 6:30pm

Yes, in the same way that you populate the dropdown from your search, you can call another PHP script with the selected member unique ID and retrieve all the other fields and populate them in your success() function.

If the form just contains all the stuff from the members table without any alteration, can’t you just use the unique ID in your later report?

martbean · January 13, 2020, 7:13pm

Well I was trying to guide the OP towards figuring out what was wrong for themselves but as you point out there are a number of other issues.

jmyrtle · January 13, 2020, 7:42pm

@droopsnoot Yes, but this is for an attendance report that will be created later on by administration only and it has to include the person’s name, residential address and whether they were marked present or not. This information comes from the members table because it already includes this information. There is some information that the members table does not contain that the report has to have, so there will be some modification, but the members table is mostly used for reference. (Sorry if this isn’t the exact answer you were looking for, I didn’t really know how to respond to it.)

Also, in this quote:

I’m not quite sure what you mean. This is what my search code looks like (in PHP):

<?php

require('../dbconnect.php');
 
// Attempt search query execution
try{
    if(isset($_REQUEST["term"])){
        // create prepared statement
        $sql = "SELECT name FROM members WHERE name LIKE :term";
        $stmt = $pdo->prepare($sql);
        $term = $_REQUEST["term"] . '%';
        // bind parameters to statement
        $stmt->bindParam(":term", $term);
        // execute the prepared statement
        $stmt->execute();
        if($stmt->rowCount() > 0){
            while($row = $stmt->fetch()){
                echo "<p>" . $row["name"] . "</p>";
            }
        } else{
            echo "<p>No matches found</p>";
        }
    }  
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
 
// Close statement
unset($stmt);
 
// Close connection
unset($pdo);

?>

SamA74 · January 13, 2020, 7:54pm

Just to return to this point regarding HTML. Although it may seem redundant, you should have both name and id attributes.
The id is required to link the input with its label. The label’s for attribute references the input’s id. This is required for accessibility. The other way is to enclose the input within the label.
The name attribute is required to give the the input its key string the $_POST array.

droopsnoot · January 13, 2020, 8:08pm

If it’s just a case of adding extra fields, then you should just point to the members table and store the extra fields. There’s no point duplicating data without good reason. One good reason might be that you need to record their address at the time of the attendance report which might change later - referring only to the members table in that situation would not reflect the older address.

jmyrtle:

I’m not quite sure what you mean. This is what my search code looks like (in PHP):
<?php

require('../dbconnect.php');
 
// Attempt search query execution
try{
    if(isset($_REQUEST["term"])){
        // create prepared statement

Yes, you’d need different code to retrieve the information from the members table based on the selected member id. But the principle is the same - you know how to call the search and do something with the information returned, so you do a similar thing here - call a new routine, pass in the members id, retrieve the information and populate your form fields with it.

jmyrtle · January 13, 2020, 8:17pm

What then would be the best way? My original idea was to automatically fill in extra fields based on the lookup of the member’s name that the user types in. These fields would be read-only fields and cannot be modified by the user. Only admins can do this when it is necessary to do so.

If that’s not the best way, how do you simply point to the members table? Would you do that in a form like this or in the reports later?

SamA74 · January 13, 2020, 8:21pm

I think we need to know what other data you need, then decide if it should all go in the same table or not.

jmyrtle · January 13, 2020, 8:26pm

I have to include the member’s name, residential address, what precinct they reside in (since the report generated later will be sorted by precinct), and whether they were marked present or not. In the form from the code in my op, I have included the member’s name, email, phone number and attendance. I need the other information (residential address, precinct, etc.) in the report when I generate it, but I cannot get to it unless I call it from the members table somehow which I need to figure out how to do. This form I am working on is going in a separate table called attendance.

droopsnoot · January 14, 2020, 10:05am

Well, that’s two different things. If the fields are to be read-only and contain only the same information that is in the members table, then there’s no need to store them in the attendance table - just store the member unique-id, and when you need the address etc, use a JOIN in your query to retrieve it from the members table. If the data can be different, though, then you need to store it.

The term you want to research is “database normalisation”.

Your reports would gather the information from the relevant sources. Your attendance table would contain the member id, and that would allow your query to retrieve the members data.

It’s no different that the flag you might use to indicate the reason for non-attendance. You wouldn’t store “Sick”, “Holiday”, “Training” or whatever the appropriate reasons for non-attendance are as text in every single row, you’d have a table to store those in:

reason_id   reason_description
------------------------------
0           Sick
1           Holiday
2           Training

and then your attendance table would just store ‘0’, ‘1’ or ‘2’ as required. When you’re marking the member as absent, your drop-down for the reason would show the description from the table, and the value would be the id. When you want to search for all the holiday days, you just search for ‘1’ in the id, instead of allowing text and wondering whether people typed in “Holiday”, “vacation”, “holyday” or anything else. If you need to add a reason, just create a new row in the table. If you need the text in your form / report, just JOIN the table and retrieve the description.

All of this is in the members table, presumably, so all you need to store in the attendance table is the unique id for the member. The only caveat, as I said earlier, is if the information might change over time and you need to have the original information in your report. In that case, the address / precinct would need to be stored each time.

The point is to not duplicate data, originally from a storage space point of view but also from the point of view of how to deal with changes. Say your member is marked absent a few times, and so generates a row in this new table. Six months later, they move house. In your report, which address and precinct do you want to see? The one they were at when they were marked absent, or their current address?

jmyrtle · January 14, 2020, 12:59pm

So, here is the structure of the members table:

The data I need to save is the precinct (which the report would be sorted by), the member’s name, the residential address, and whether the delegate is marked present. The name and attendance are already being recorded in the attendance table, so those are already being taken care of.

I don’t necessarily want to do that because the final report shouldn’t contain that.

Here is an example of what one of the report records may look like:

Precinct and Residential Address is what I need to bring in from the members table. Everything else is being handled by the attendance form and is being sent to the attendance table.

I will always be looking at their current address as admins have the ability to change the members table data. I’ve already set up DataTables to accomplish this.

droopsnoot · January 14, 2020, 1:07pm

That’s not a reason. Just because it’s in the table doesn’t mean you have to show it in the report.

If you only want to see the members current details exactly as they are stored in the members table, then there is no need to store anything in the attendance table other than the member-id and anything else specific to the attendance table.

attendance table
attid      int   auto-increment unique id for attendance row
memberid   int   reference to members table id column
present    bool  member was present
ea         bool  whatever your right-hand column was
date       date  presumably you store a date for the record

As long as the precinct, name and residential address can be referenced using the member id, there’s no need to duplicate it.

Incidentally I wouldn’t store “age” in the members table - what do you do to keep that up to date? Store date of birth instead.

I’m also not sure about the way you store your address, especially as you’ve merged the zip in there as well, making it very difficult to search or order by zip code. That seems to be in one column in ‘members’, but in two separate columns in ‘attendance’.