Select record from form and update

You might consider converting members to proper WP users or even a custom post type. Then you can use built in WP functionality to manage the data. A custom conversion script could read the existing data and create appropriate WP objects from it.

It’s certainly possible to work with the data as-is. Modify the list output to include an “Edit” link if the current user has adequate role or capability to edit members. The link should include the unique ID for that member record. The receiving edit page can use that ID to fetch the current data and present it in a HTML form. When the form is submitted, the handling code collects the form fields and assembles a proper SQL update query, which can be executed using the global $wpdb connection object.

You’ll also want want a “Delete” link and a way to add new members. All the necessary code can reside on a custom page template. What happens is dictated by what the sort of query string, POST data, and request type is received. The processing code could reside elsewhere if it makes more sense to you, just remember there are limited ways to invoke the WP environment. Your code cannot use WP resources without the environment being started. Your choices are page templates or going through admin-ajax.php or admin-post.php

Thank you for your reply. I followed your lead byI creating a custom template page with this code to create a test page, that successfully loads the table records.
…

 echo "<tbody>"; 
	echo "<table>";
    echo "<tr>"; 
	echo "<th>ID</th>";
		echo "<th>New</th>";	
		echo "<th>Title</th>";	
		echo "<th>FirstName</th>";	
		echo "<th>LastName</th>";	
		echo "<th>Address1</th>";	
		echo "</tr>";
		foreach($retrieve_data as $row){ 
		echo "<tr>"; 	
       echo  "<td>" . $row->ID. "</td>";      // Adding rows of table inside foreach loop
		echo  "<td>" . $row->New. "</td>";
		echo  "<td>" . $row->Title. "</td>";
		echo  "<td>" . $row->FirstName. "</td>";
		echo  "<td>" . $row->LastName. "</td>";
		echo  "<td>" . $row->Address1. "</td>";
			echo "<tr>"; 
	    }
    echo "</tbody>";
    echo "</table>"; 

}
?>

…
Q1 How do I sort the displayed records by Last Name and select one?
Q2 How do I update a specific field?

• This reply was modified 4 years, 1 month ago by bcworkz. Reason: missing backtick

You could sort the $retrieve_data array using PHP’s usort() where you define your own sorting logic. It’s not very efficient though compared to SQL sorting. To sort SQL results from a SELECT query, we include an ORDER BY clause. The exact query would depend upon your table structure, but roughly something like SELECT * FROM my_table WHERE col_1 = 'foo' ORDER BY last_name ASC
Ref: https://dev.mysql.com/doc/refman/5.7/en/select.html

What “one” do you want to select? Do you mean select a row to edit? You could build check boxes into the output much like how WP admin post list tables do. Except that implies you can edit more than one record at a time. Bulk processing of multiple selections can get rather complicated. I suggest you focus on editing single records at a time for now. I would output an edit link corresponding to each item in the table, again much like the posts list table does. The link might include a query string like ?edit-id=123 where 123 is that specific record’s ID value in the SQL table.

When your page template code finds a $_GET['edit-id'] value, it outputs an edit form instead of the usual table listing. The form’s fields are populated with values from the DB. When the form is submitted as a POST request to the same page, your template code detects the POST request instead of GET and processes the submitted data instead of doing the other things it can do. It gets the form values from $_POST, validates and sanitizes them, then does an UPDATE query to commit the proffered changes to the DB.

POSTed data should always include a nonce which your processing code verifies before doing anything. Your code should also verify the user has proper role/capability to edit these records. Do so before providing an edit link, before presenting an edit form, and once more before processing the form data.

Thank you. I sorted the SQL statement. The logic steps are:
I receive a request to update a changed email from member H….
I view the records of the members and locate member H…
I scroll over to the email column and update the email and click save.

$_Get[‘edit-id’] sounds promising, is there sample code out there? I haven’t been able to find a lot of advice on how to edit or update info from the front end. Clearly one can always go into phpMyAdmin, and I notice there are plugins that allow users to update their own records, but one assumes those are the only records they see anyway.

If the members were regular WP users, they could edit their own email ?? WP will even verify the email by sending members a validation link that is followed before the change is committed. OK, I’m done promoting that idea. You’re free to do as you wish.

It’s not exactly applicable, but maybe this would help with the current scheme:
https://www.tutsmake.com/php-code-insert-data-into-mysql-database-from-form/

In WP the $conn connection is already established, use the global $wpdb connection object methods to execute the query instead of using mysqli_query(). You needn’t create separate files as described. Place your PHP all on the page template file and use conditionals to determine what code executes to do and display various things. The example is inserting a new record, but updating an existing record is very similar, you only use a different query. Collecting data from a form is the same either way.

The example doesn’t implement any security measures which I’ve outlined in my last reply. You can omit these at first while you get something working, but you must implement the security before going live with your solution.

Code on page template works fine, submit button does nothing, never calls update2.php, no error, nothing.

    
    echo "<tr><form action='https://hsysgrp.com/AAUW/wp-content/themes/twentytwenty-child/update2.php' method='post'>"; 
	echo "<th>ID</th>";
		echo "<th>New</th>";	
		echo "<th>Title</th>";	
		echo "<th>FirstName</th>";	
		echo "<th>LastName</th>";	
		echo "<th>Address1</th>";	
		echo "</tr>";
		foreach($retrieve_data as $row){ 
		echo "<tr>"; 	
       echo  "<td>,input type=hidden name=ID value='" . $row->ID. "'</td>";      
	echo  "<td><input type=text name=New value='" . $row->New. "'</td>";
	echo  "<td><input type=text name=Title value='" . $row->Title. "</td>";
	echo  "<td><input type=text name=FirstName value='" . $row->FirstName. "</td>";
	echo  "<td><input type=text name=LastName value='" . $row->LastName. "</td>";
	echo  "<td><input type=text name=Address1 value='" . $row->Address1. "</td>";
	echo  "<td><input type=submit>";
	echo "<tr>"; 
	    }
    echo "</tbody>";
    echo "</table>"; 
    echo "<tr><form action=update2.php method=post>"; 
	echo "<th>ID</th>";
		echo "<th>New</th>";	
		echo "<th>Title</th>";	
		echo "<th>FirstName</th>";	
		echo "<th>LastName</th>";	
		echo "<th>Address1</th>";	
		echo "</tr>";
		foreach($retrieve_data as $row){ 
		echo "<tr>"; 	
       echo  "<td>,input type=hidden name=ID value='" . $row->ID. "'</td>";    
	echo  "<td><input type=text name=New value='" . $row->New. "'</td>";
	echo  "<td><input type=text name=Title value='" . $row->Title. "</td>";
	echo  "<td><input type=text name=FirstName value='" . $row->FirstName. "</td>";
	echo  "<td><input type=text name=LastName value='" . $row->LastName. "</td>";
	echo  "<td><input type=text name=Address1 value='" . $row->Address1. "</td>";
		echo  "<td><input type=submit>";
		echo "<tr>"; 
	    }
    echo "</tbody>";
    echo "</table>"; 
*****file update2.php*********************
<?php   
 $conn=mysqli_connect("localhost", "hsysgrpc_WPHZU", "***********", "hsysgrpc_WPHZU"); 
    if(!$conn)
        {
          die('Could not Connect MySql Server:' .mysqli_error());
 
$sql = "UPDATE AAUW_Members SET New = '$_POST[New]',Title = '$_POST[Title]',FirstName = '$_POST[FirstName]',LastName = '$_POST[LastName]',Address1 = '$_POST[Address1]' WHERE ID = '$_POST[ID]' ";
 
$query = mysqli_query($conn,$sql);
if(!$query)
{
    echo "Query does not work.".mysqli_error($conn);die;
}
else

    echo "Data successfully updated";
	header("refresh:2;url=custom-page_UpdateMembersTest.php");
}
?>

Your code is not generating valid HTML. There are tbody and table closing tags without opening tags. Maybe the first table has them in code prior to the snippet you posted, but the second table is missing them anyway.

Why two forms and tables? The form tags are missing matching closing </form> tags. The second form has a relative URL as action attribute. Don’t use relative URLs in WP, they are incompatible with WP permalink schemes. Some of the input fields are missing closing attribute value quotes and they also don’t have their closing > character. The first input tag has an opening , character instead of <. View the page’s source HTML in your browser and check the HTML of your form carefully.

Unless $retrieve_data only contains one row’s worth of data, you must have separate form tags for every row, e.g. every row is its own form.

A select query displays 10 records and displays them in a table. An input for FirstName is allowed to overwrite the previous value stored in $row-> FirstName.
When Submit is clicked the formhandler update2.php inserts the new value in the field.

But, The clicked submit does nothing.
There is only one form.
Here is the full code for AAUW_Insert_code.php:

<?php
/**
 * Template Name: Update Member Data Test
 *
 * Description: Twenty Twenty loves the no-sidebar look as much as
 * you do. Use this page template to remove the sidebar from any page.
 *
 * Tip: to remove the sidebar from all posts and pages simply remove
 * any active widgets from the Main Sidebar area, and the sidebar will
 * disappear everywhere.
 *
 * @package WordPress
 * @subpackage Twenty_Twenty
 * @since Twenty Twenty 1.0
 */

get_header(); ?>

	<div id="primary" class="site-content">
		<div id="content" role="main">
			<?php
			$link = mysqli_connect("localhost", "hsysgrpc_WPHZU", "***************", "hsysgrpc_WPHZU"); 
			
			if(!$link) {    
			die ("ERROR: Could not connect. " . mysqli_connect_error() );
			} 
			echo "Connected successfully";

global $wpdb;
			$wpdb->show_errors( true );
			
  $retrieve_data   = $wpdb->get_results( "SELECT ID,New,Title,FirstName,LastName,Address1 FROM AAUW_Members order by LastName limit 10"); // Query to fetch data from database table and storing in $results
if(!empty($retrieve_data))                        // Checking if $results have some values or not
{    
    echo "<table width='100%' border='0'>"; // Adding <table> and <tbody> tag outside foreach loop so that it wont create again and again
    echo "<tbody>"; 
	echo "<table>";
    echo  "<tr><form action='https://example.com/wp-content/themes/twentytwenty-child/update2.php' method='post'>"; 
	echo "<th>New</th>";	
		echo "<th>Title</th>";	
		echo "<th>FirstName</th>";	
		echo "<th>LastName</th>";	
		echo "<th>Address1</th>";	
		echo "</tr>";           
  		foreach($retrieve_data as $row){ 
		echo "<tr>"; 	        
		echo  "<td><input type=text name=New value='" . $row->New . "'></td>";
		echo  "<td><input type=text name=Title value='" . $row->Title . "'></td>";
		echo  "<td><input type=text name=FirstName value='" . $row->FirstName . "'></td>";
		echo  "<td><input type=text name=LastName value='" . $row->LastName . "'></td>";
		echo  "<td><input type=text name=Address1 value='" . $row->Address1 . "'></td>";
	    echo  "<input type=hidden name=ID value='" . $row->ID . "'>";  
		echo  "<td><input type=submit></td>";
		echo  "</form><tr>"; 
	    }
    echo "</tbody>";
    echo "</table>"; 

}
?>
		</div><!-- #content -->
	</div><!-- #primary -->

<?php get_footer(); ?>

Code for insert2.php:
 <?php   
 $conn=mysqli_connect("localhost", "hsysgrpc_WPHZU", "*************", "hsysgrpc_WPHZU"); 
    if(!$conn)
        {
          die('Could not Connect MySql Server:' .mysqli_error());
 
$sql = "UPDATE AAUW_Members SET New = '$_POST[New]',Title = '$_POST[Title]',FirstName = '$_POST[FirstName]',LastName = '$_POST[LastName]',Address1 = '$_POST[Address1]' WHERE ID = '$_POST[ID]' ";
 
$query = mysqli_query($conn,$sql);
if(!$query)
{
    echo "Query does not work.".mysqli_error($conn);die;
}
else

    echo "Data successfully updated";
	header("refresh:2;url=custom-page_UpdateMembersTest.php");
}
?>

• This reply was modified 4 years, 1 month ago by bcworkz. Reason: removed sensitive data

Please change your DB password. I’ve removed it from your posted code, but it could have leaked out to those with bad intent before I changed it. In testing your code, I accidentally hit your site’s insert2.php file with a request before I changed the code, so you may have an extra row of data if the request was successful. The values were just repeats of the field names.

Your form is only submitting the first row of data, if anything at all, regardless of which other submit button you click. There is only one <form> tag but multiple </form> tags. You need to move your <form> tag output to within the foreach loop so that each row is a separate form.

Update Member
The phptester says error on line 9 can’t see it.

 <?php   
 $conn=mysqli_connect("localhost", "8888888", "88888888, "88888888888"); 
    if(!$conn)
        {
          die('Could not Connect MySql Server:' .mysqli_error());
 
$sql = "UPDATE AAUW_Members SET FirstName = '$_POST[FirstName]',LastName = '$_POST[LastName]' WHERE ID = '$_POST[ID]'";
 echo "$sql";
if(mysqli_query($conn,$sql))   //line 9
{
     echo "Data successfully updated";
	
	
}
else
{
 echo "Not updated";  
	
}
?>

OK, very close now. The select statement selects and displays 10 rows and the submit button on each row.

The formhandler connects, but can update only the last record.

foreach($retrieve_data as $row){ 
		echo "<tr>"; 	
       echo  "<td>,input type=hidden name=ID value='" . $row->ID. "'</td>";     
		echo  "<td><input type=text name=New value='" . $row->New. "'</td>";
		echo  "<td><input type=text name=Title value='" . $row->Title. "</td>";
		echo  "<td><input type=text name=FirstName value='" . $row->FirstName. "</td>";
		echo  "<td><input type=text name=LastName value='" . $row->LastName. "</td>";
		echo  "<td><input type=text name=Address1 value='" . $row->Address1. "</td>";
		echo  "<td><input type=submit>";
		echo "<tr>"; 

update2.php

<?php   
 $conn=mysqli_connect("localhost", "888", "888", "888"); 
    if(!$conn)
        {
          die('Could not Connect MySql Server:' .mysqli_error());
        }
echo "connected sucessfully";
 
$sql = "UPDATE AAUW_Members SET New = '$_POST[New]',Title = '$_POST[Title]',FirstName = '$_POST[FirstName]',LastName = '$_POST[LastName]',Address1 = '$_POST[Address1]' WHERE ID = '$_POST[ID]'; ";
echo "$sql";
 
$query = mysqli_query($conn,$sql);
if(!$query)
{
    echo "Query does not work.".mysqli_error($conn);die;
}
else 
{ 
    echo "Data successfully updated";
	header("refresh:2;url=index.php");
}
?>

I think there’s still an issue with how the forms are constructed in HTML. Your rows all have the same names, but there’s only one form on the page, so only the last row gets submitted regardless of which submit button is clicked. As I’ve been saying, every row needs to be its own individual form.

No one else can see the page you linked to because the site has not been published through your host and requires admin login to see anything. So I’m only guessing at what might be wrong, but it does explain the symptom you describe of only updating the last record.

The supposed line 9 error is actually a syntax error in the assignment to $sql above. It sometimes takes the parser a few lines to figure out there’s a syntax error which occurred earlier. Always look at lines above if you cannot see the mentioned error where it occurs. There are no quotes in the array key references of the variables. When using array element or object property variables in double quoted strings, you have to enclose the variables in curly braces. For example: $sql = "UPDATE foo SET New = '{$_POST['New']}' WHERE ID = {$_POST['ID'};"

You were right! When each row is a form, it works like a charm. Thank you, I finally understood what you were telling me.
Now, How do I insert the prepare function into the UPDATE query. This is what works, I couldn’t get the curly braces version to work.

$sql = "UPDATE AAUW_Members SET FirstName = '$_POST[FirstName]',LastName = '$_POST[LastName]' WHERE ID = '$_POST[ID]'";
 echo "$sql";

• This reply was modified 4 years, 1 month ago by bcworkz. Reason: code fixed

Oh, Line 8 having quotes is another syntax error. Once I see one error I tend to stop looking for more.

$sql = "UPDATE AAUW_Members SET FirstName = '{$_POST['FirstName']}',LastName = '{$_POST['LastName']}' WHERE ID = {$_POST['ID']};";
echo $sql;

I’m assuming the ID field is numeric, so it does not need to be quoted in the resulting SQL.

Examine the prepare examples at https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
Apply stage 2’s bind operation for each parameter. Note that this is not the same prepare process as $wpdb->prepare() in WP. That is more akin to mysqli_real_escape_string(). Using mysqli* functions, unless you will make repeated queries, there’s little reason to prepare. You should be using mysqli_real_escape_string on any user supplied data being used in a query.
https://www.php.net/manual/en/mysqli.real-escape-string.php