asked on

How can I update information to the database when there information is dynamic not static?

I have this code:

							<form action ="edit.php" method = "post">
<table class="table table-striped">
  <thead>
    <tr>
							<?php
$query = mysqli_query($conn,"SHOW columns FROM `call`");
// Loop over all result rows
while($row = mysqli_fetch_array($query))
{
    echo '<th>';
    echo $row["Field"];
    echo '</th>';
}
 ?>
	</thead>
	<tbody>
	<tr>
	<?php 
	$query2= "SELECT * from `call` WHERE `Generated By` = '$username'";
	$result= mysqli_query($conn, $query2);
	while($row = mysqli_fetch_assoc($result))
	{
	echo '<tr>';
	foreach($row as $values){
	
	echo '<td>';
	echo '<input class = "change readonly-input" value= "' .$values . '" readonly name="'. $values . '"/>';

	echo '</td>';
			}
	echo '<td><input data-original-title="Save this user" data-toggle="tooltip" type="submit" class="btn btn-sm btn-success" id="edit" Value= "Save">';
	echo '</td>';
	
	}
	echo '</tr>';
	?>
	</tbody>	
	</table>	
	</form>

Open in new window

Jquery:

<script>
$(function() {
  $('.change').focus(function() {
    $(this).removeClass('readonly-input').prop('readonly', false);
  });
  $('.change').blur(function() {
    $(this).addClass('readonly-input').prop('readonly', true);
  });
});
</script>

Open in new window

When I click on a field it can be edittable, but how can I save it into the database after I click the save button. I mean I usually do it by this way:

		$query = "UPDATE users SET firstname='$name', lastname = '$lastname',   email= '$mail' WHERE username='$username'"

Open in new window

;
But since there is no static field, how am I suppose to update it to the database?

Ray Paseur

This is called "table maintenance," and it's a common question. This article shows the moving parts on the server side (what gets run after you submit the HTML form).
https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html

Jazzy 1012

ASKER

Is there a way you can show me an example, because I didn't understand fully.

Ray Paseur

Have you read the article? It's full of examples! If there are any examples in the article that you do not understand, please refer to them, and I'll clarify the article, as well as explain the examples here.

Jazzy 1012

ASKER

Yes but my question is that how the arrays show and is there a simper way of writing the code?

Ray Paseur

how the arrays show

Please explain a little more, thanks.

Jazzy 1012

ASKER

the ones that are
$a=> (
a=>g
...
)

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

@Julian: You might want to have a look at this one:
https://www.experts-exchange.com/questions/28996004/I-have-many-databases-and-one-main-one-how-can-I-take-there-column-names-from-the-main-one.html

I think our author may require a mapping table of some sort to match the DB column names to external data elements. Jasmine does not want to have column names that match the HTML input control names.

Julian Hansen

 Jasmine does not want to have column names that match the HTML input control names.

Open in new window

I don't understand this - the input control names are invisible - and in the solution above wlll look something like

data[21][firstname]
data[21][surname]

Can't see how that has any bearing on the form or the table.

Otherwise it is like asking - how can I uniquely id my fieldnames without giving them a unique name ....

Jazzy 1012

ASKER

Everything worked except my query won't run probably because it has a "comma" at the end before the WHERE statement
How can I remove this?

require "connection.php";

// GET THE DATA FROM POST IF IT EXISTS
$data = isset($_POST['data']) ? $_POST['data'] : false;

// IF IT IS A VALID ARRAY THEN PROCESS IT
if (is_array($data)) {
	// LOOP THOUGH EACH SUBMITTED RECORD
	foreach($data as $id => $rec) {

		// START AN UPDATE STRING
		$updatestr = '';
		
		// ADD FIELD / VALUES TO UPDATE STRING
		foreach($rec as $fieldname => $value) {
			if($fieldname == 'id'){
				continue;
			}
			else{
			$updatestr .= "`{$fieldname}` = '{$value}',";
			}
		}

		// REMOVE THE TRAILING ,
		trim($updatestr, ',');
		rtrim($updatestr, ',');
	
		// CREATE THE UPDATE QUERY USING THE ID OBTAINED FROM
		//  THE KEY OF THIS data ELEMENT
		$query = "UPDATE `call` SET {$updatestr} WHERE id= '$id'";

		// SEND IT TO THE DB
		$result= mysqli_query($conn, $query);
	}
	echo "working";
}
else {
	echo "not working";
}

Open in new window

I tried rtirm to the $updatestr, but nothing seems to work, I want to remove the last comma in it

Jazzy 1012

ASKER

Also how can I real_escape each field incase any user inputs special characters?

Jazzy 1012

ASKER

I got the comma to work by doing:
$updatestr = rtrim($updatestr, ',');
Just now how can I real_escape?

Jazzy 1012

ASKER

Also I realized that the id is always 1, whats the problem with that?

Jazzy 1012