HTML Form is not adding data to MySQL database?

I don’t have any relationships in my database structure. Would this be necessary to reference the data later in my report so I could use the JOIN query or is that not necessary?

Also, how would I automatically grab the member ID from the members table in the form based on the lookup of the members name?

Zip code is not being stored in the attendance table at all - only in the report. The zip code is merged because the data will not be sorted that way. Only by precinct.

If someone moves, they would have to notify the admin and that admin would go into the members table and replace the old address with their new one.

So, basically the members table is the heart of the application and the other tables reference it (except for users and user roles - those are two separate tables).

When you do the search, I thought you’d be putting the results into a html <select>, in which case you would set the value parameter for each <option> to be your member id. I can’t see whether you’re actually doing that, because your search routine seems to return all the results in <p> tags. But it might be worth changing. In any case, return the id along with the name, and when the user selects the member, pass that into the form that asks for the rest of the information. The form display code can retrieve the member name and other information to display as confirmation.

I don’t think it’s necessary - I use JOINs in my own projects without putting constraints on the database. It may well be better to have them, though. Typically you’d have a constraint to make sure that the member id exists before you write a row into the attendance table, but if you’re selecting the member from a list then it’s a little bit of a waste. It may be worth adding later once you have it working, though.

This is what the form currently looks like:

And this is what the search bar does:

The only thing the search is doing is a name lookup, not an ID lookup. Here is the form’s code:

<?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: 100%;
        position: relative;
        display: inline-block;
        font-size: 16px;
    }
    .search-box input[type="text"]{
        height: 32px;
        padding: 5px 10px;
        border: 1px solid #CCCCCC;
        font-size: 16px;
    }
    .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 | Record Attendance</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 Search</label>
			<input type="text" class="form-control" id="member_name" name="member_name" placeholder="Enter Member's Last Name" maxlength="" autocomplete="off" required />
			<div class="result"></div>
		</div>
		<!--<div class="form-group">
			<label for="member_id">Member ID</label>
			<input type="text" class="form-control" id="member_id" name="member_id" placeholder="" maxlength="255" autocomplete="off" readonly/>
		</div>-->		
		<div class="form-group">
			<label for="member_email">Member's Email Address</label>
			<input type="text" class="form-control" id="member_email" name="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" name="member_phone" placeholder="" maxlength="14" autocomplete="off" />
		</div>
		<div class="form-group">
			<label for="present">Attended &nbsp</label>
			<input type="checkbox" id="present" name="present" placeholder="" value="1" autocomplete="off" required />
		</div>
		<div class="form-group">
			<label for="alternate">Plan to Attend State Convention? &nbsp</label>
			<input type="checkbox" id="alternate" name="alternate" placeholder="" value="2" autocomplete="off" />
		</div>				
			<input type="submit" name="btn_save" class="btn btn-success" value="Save and Add New">
			<input type="submit" name="btn_close" 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 backend-search.php file where the data is retrieved from the user input:

<?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);

?>

This is the authentication that PHP does in the background (the attn_record.php file):

<?php

include ('dbconnect.php');

$stmt = $pdo->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 and add new record
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();
  header('Location: ../record.php');
}

// insert a row and exit attendance screen
if(isset($_POST['btn_close'])) {
  $member_name = $_POST["member_name"];
  $member_email = $_POST["member_email"];
  $member_phone = $_POST["member_phone"];
  $present = $_POST["present"];	
  $stmt->execute();
  header('Location: ../index.php');
}

?>

And (just in case) here is the structure of the attendance table:

So, someone comes up to me and gives me their last name. I put in the person’s last name in the search bar. That search bar adds the person’s name in the attendance table and I add their email and updated phone number and mark them as present.

What I want to do with this is run a report on a different page that’s basically saying “I need a report showing me who all was here with their name and address sorted by what precinct they reside in.” I would like to generate this report in both XLSX and PDF files that the admin can download to look at. The problem in generating the report is that I can’t get the information I need because I’m not calling in the information in the table. I’m not sure what the best way to do that is…

At the risk of sounding like a stuck record (if you remember what a record is), I would say again that you need to drive the connection by member id, not by member name. What happens if you have two delegates with the same name? How do you know which one has attended? Your search routine can’t help as it will only be showing the two identical names, and it will be confusing as to whether you have a data duplication error or two actual individuals.

There’s no reason the search can’t work in a similar way, but return the members id (and perhaps the first line of the address, for separation purposes) as well. Your code then splits the ID out of the return and uses that for the next part of the process. Exactly how that happens can vary - you can return a JSON-encoded array of results and parse it on return, or you could just add it on to the strings that are already being shown, and use a separator string that won’t appear in the real data. It probably doesn’t matter too much that your user might see it, using the last approach.

Slightly O/T, but is there any need for the “attended” tick-box in your form? Presumably you’ll only be bringing that form up if someone has attended, so that could at least default to “ticked”.

I’d also store member_email and member_phone in the members table, not the attendance table, unless there’s a really good reason to store it there. It can be gathered in the way that you want, when the member attends, but updated into the members table.

2 Likes

So the search needs to be run by Member ID instead of Member Name? How then would the member’s name be placed in the attendance table for recording their showing up? I can change the search to use the Member’s ID, but I want to keep the member’s name and only use the ID for reference in the report, not to show it in the report. Maybe I’m not following you on something… And yes, I know what a record is and I know what you mean by that.

Yes there is. This is what present is for. This is a required field in case an operator goes to mark someone as attended but forgets to check the box.

The members table has nearly 50,000 records. It would be very tough and a waste of time to get 50,000 emails when only 250 people (at best) show up.

It wouldn’t. The attendance table would store the members id only, and when you want to create the report, you use a JOIN in your query to retrieve the stuff that’s only in the members table, such as the name, address and precinct. The search itself needs to show the members name (and perhaps first line of address) so that the operator can be sure they’ve picked the right user, but internally your code uses the id.

But if you didn’t have the check-box, you can still set the column to “1” when you process the attendance record. My point was that if the only reason your user has brought that form up is to mark someone as “attended”, then there’s no need to have another confirmation (especially if the user can forget to tick it) - when the user presses “confirmed attendance” to store the row, your query sets that column to ‘1’ automatically. If the form does other things, then of course this doesn’t apply.

My point was about where you store the information, not how much of it you gather. It’s a piece of information that belongs to the member, so it should be in the members table, unless there’s a very good reason for it being somewhere else. Don’t forget, with a JOIN you’ll still be able to retrieve it from the members table for your reports or future mailshots.

I’ve got to go out, when I’ve got a bit more time I’ll go into more detail about what I mean, if you want me to. I’m not trying to be negative about what you’re doing.

This makes a bit more sense now. So, (just so I can make sure I understand this right) the search can still be the member’s name and show the member’s name, but based on the search results, it has to call the ID of that particular member and store that instead. That way, when I run the report later, I can used the JOIN query that you mentioned to combine the relationship between the two tables and create the report I am looking for.

I still have a question on this though. The code I posted in a previous response shows that it selects the member’s name and records it in the database. Let’s say I don’t want to record the member’s name, but call the member’s ID instead (like you said). What’s the best way to do that based on the code above? Is it simply a matter of changing the search code around or am I adding another clause somewhere?

Doesn’t that depend on the type of JOIN query you use? I know there’s various types, but I’m not sure which join works best for this situation (although I want to say INNER since that appears to be the most commonly used).

Going into more detail would help. I understand you are not trying to be negative. I really do appreciate the feedback. If there is a way of how I can improve the code without changing the way the code works, then I’ll work on that.

Yes, the name will (should) only be recorded in one table. Other table rows that reference a member should only do so by their ID.
The general rule is that data should not be duplicated in more than one table, any join or cross reference should be made by an ID, which as a unique and unambiguous way of identifying something that remains a constant.
The data you select does not have to be the same data you search for, or from the same table, it can be whatever you need, so long as there is a join between the tables.

Yes, there are different types of JOIN, I tend to only use an INNER JOIN because that suits what I’m doing (which is not much). Your query would look something like

select * from attendance inner join members on members.id = attendance.memberid 
   where present = 1 order by members.precinct

That presumes that you use a column called memberid in your attendance table. Of course, in real life you’d specify which columns you need rather than use *.

As for the search, I’m not really up to date on Ajax - it’s something I keep experimenting with, but for various reasons I haven’t done much for a while. One simple way to make a quick change would be in your search php code:

$sql = "SELECT id, name FROM members WHERE name LIKE :term";
... and ...
echo "<p>" . $row["id"] . " -+- " . $row["name"] . "</p>";

This would quickly and easily put the member id into the start of the search results on each line. Once you have it there, it would appear in your “name” box, and the “-±” string is just so you can easily strip it out later.

That’s not a good way to do it, by the way. You should really return the results as an array rather than building the html, and use JavaScript to parse the array into a more appropriate way to use the data. For now, though, it might do the job. And I don’t know enough JS to show how to change it, off the top of my head.

Using this method, when you submit the form, your PHP code will get the “name” field as always, then it’ll use strpos or even explode to split the name into two separate variables. You can then validate the member id (for now it’s just going to be in a text box, so anyone can type anything in) and store it in the attendance table.

Note: the “- + -” string is just a delimiter that’s unlikely to be in the name string, it has no significance and you could change it for anything you want, as long as that too won’t be in the name string.

I see. So given what you mentioned, I changed the code in the php search file to this:

$sql = "SELECT id, 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){
            $stmt->fetchAll(PDO::FETCH_ASSOC);
			echo $stmt;
			//while($row = $stmt->fetch()){
                //echo "<p>" . $row["id"] . " -+- " . $row["name"] . "</p>";
            //}
        } else{
            echo "<p>No matches found</p>";
        }
    }

But I get an error that says this:

Recoverable fatal error: Object of class PDOStatement could not be converted to string in C:\xampp\htdocs\ccrp\api\search\backend-search.php on line 18

Maybe I misunderstood what I read earlier?

TLDR thread…

From the manual…

PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.
https://www.php.net/manual/en/pdostatement.rowcount.php

Notice SELECT is not mentioned? This tutorial should get you on track to properly using PDO.
https://phpdelusions.net/pdo

Simple example:

<?php
$search = "%$search%";
$stmt = $pdo->prepare("SELECT name FROM table WHERE name LIKE ?");
$stmt->execute([$search]);
$result = $stmt->fetchAll();

if ($result) {
    foreach ($result as $row) {
        echo $row['name'];
    }
} else {
    echo 'No Records';
}

It does also say "If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications. " and as the search routine was working before, we could presume that it works on the OPs database.

Which is line 18 of your code? There aren’t 18 lines in the part you show.

In any case, you seem to have changed from using fetch() to using fetchAll(), and I think that’s your problem. You’re trying to echo the $stmt object, when you really need to echo the rows from it, as you did before. Remove the fetchAll() and the echo after it, and reinstate the two lines you commented out.

I figured it out (with some help from @benanamen) and you were right. I was trying to echo the $stmt object when I need to display the rows from it. However, rather than commenting out the lines I had before, I changed the code around to make it go through a foreach statement rather than a while statement and building HTML around it. See code below:

    if(isset($_REQUEST["term"])){
        // create prepared statement
        $sql = "SELECT id, 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){
            $result = $stmt->fetchAll();
			if($result) {
				foreach ($result as $row) {
					echo "<p>" . $row['id'] . " -+- " . $row['name'] . "</p>";
				}
			}
        } else{
            echo "<p>No members found</p>";
        }
    }

To answer your previous question as to which line of code was causing the error, line 18 was the

echo $stmt;

object which you pointed out was incorrect. I failed to include the full code in my previous response. I changed the code to a foreach because I remember you said:

Can you show me an example of how this would work so I can figure out how to implement this in the code? I tested my code by running the form and it records the following in the attendance table:

It looks like I’m on the right track, but how do I split the two values into different columns and only record the ID to reference later? I think you already mentioned how to do this, but I still don’t quite understand it.

You need to get rid of the row count. The if($result) is already taking care of it.

Ah! I didn’t see that. Thanks!

I just noticed how you are storing data in your member_name column. You need to split that data up into separate columns. First name, Last name, Middle name, Member id. Depending on what you are doing, the member id is likely to actually go in a separate table with membership specific data.

Why split them up? Would that make it easier in the long run?

Yes. Learn about Database Normalization.

Something like

$name = $_POST['name']; // or whatever your form field is called
$ary = explode("-+-", $name);
var_dump($ary);

This will split the string up into an array, with the -+- string as the separator. So you then need to use whichever element of the array as your member ID.

As I said, anyone who knows about Ajax and JS and so on is probably spitting at the screen now, but the above would allow you to use what you have without major changes.