Edit multiple values from same mysql columns 1

[buzzt]

I need to edit multiple entries in my mysql table at once. What I have so far, is a query that returns all the result of a given date. I then create a loop which writes form fields with each result in its corresponding field.

The problem is I do not want to submit the form for each entry I modify. I want to change the whole day on the same page, and submit the changes all at once. So what I have is:

id1 - time1 - event1
id2 - time2 - event2
id3 - time3 - event3
etc...

I want to edit (for example) time1 - event1 and time2 and event3 all from the same page at the same time. The problem is that my form only passes on the last set of results to the page that updates the mysql table (id3 - time3 - event3). How can I make all the changes submit at once? I thought of using an array, but have been unsuccessful so far...

 

[sleipnir214]

What kind of form fields are you using, and how are you naming them?

If you simulate array elements in the names of your form fields, in the PHP script to which that form is submitted, the values from the fields will be in an array.

For example, given the form:

<form method="post" action="foo.php>
<input type="text" name="input[0]">
<input type="text" name="input[1]">
<input type="text" name="input[2]">
<input type="text" name="input[3]">
<input type="submit">
</form>

Then in foo.php $_POST['input'] will be an array containing all the values.

You can also do multidimensional arrays:

<input type="text" name="input[0][a][3]">




Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[kenrbnsn]

What does your form look like now?

What have you tried?

If you don't tell us, we can't help you.

Ken

 

[buzzt]

Here's an abreviated version of my attempt:

<table width=600 cellpadding=0 cellspacing=0 border=0>
<tr>
<td>

<?
echo "<form name=\"update\" method=\"post\" action=\"edited.php" enctype=\"multipart/form-data\">";
for ($i=0; $i<$num_results; $i++)
{
$row = mysql_fetch_array($query);
$id = $row['id'];
$time = $row['time'];
$event = $row['event'];

?>
<table width=600 cellpadding=0 cellspacing=5 border=0>
<tr height=25>
<td class="formtext"> <strong>Time</strong> </td>
<td>
<input type=text name="time<? echo $i; ?>" size=45 value="<? echo $time ?>">
</td>
</tr>
<tr height=25>
<td class="formtext"> <strong>Event</strong> </td>
<td>
<textarea name="event<? echo $i; ?>" cols=60 rows=2 wrap=virtual><? echo $event ?></textarea>
</td>
</tr>
</table>
<br>
<?
}

?>
<center><input type=submit name="Submit" value="Submit Changes"></center>
<br>
<br>
</td>
</form>
</tr>
</table>

 

[sleipnir214]

I would produce a form using a script like:

<?php
print '
<table width=600 cellpadding=0 cellspacing=0 border=0>
	<tr>
		<td>';
print '<form name="update" method="post" action="edited.php" enctype="multipart/form-data">';
while ($row = mysql_fetch_array($query))
{
	$id       = $row['id'];
	$time     = $row['time'];
	$event    = $row['event'];
     
	print '
			<table width=600 cellpadding=0 cellspacing=5 border=0>
				<tr height=25>
					<td class="formtext"> <strong>Time</strong> </td>
					<td> 
						<input type=text name="event_record[' . $id . '][time]" size=45 value="<? echo $time ?>">
					</td>
				</tr>
				<tr height=25>
					<td class="formtext"> <strong>Event</strong> </td>
					<td> 
						<textarea name="event_record[' . $id . . '][event]" cols=60 rows=2 wrap=virtual><? echo $event ?></textarea>
					</td>
				</tr>
			</table>
			<br>';
}

print '
			<center><input type=submit name="Submit" value="Submit Changes"></center>
			<br>
			<br>
		</td>
	</form>
	</tr>
</table>';
?>

When submitted, $_POST['event_record'] would itself be an array with the keys of the array would be the record IDs from the table. Each element of that array would also be an array, with two elements named 'time' and 'event'.

I would also place in the form hidden fields which contain the original information. That way, in the receiving script you can detect which fields were changes and only update those records.

<aside>
You may have noticed that instead of switching back and forth between "HTML output" mode and "interpreted PHP" mode, my version of your script stays in "interpreted PHP" mode and outputs everything using print statements. My testing has shown this gives better script performance.
</aside>


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sleipnir214]

CORRECTION:

This code is better:

print '
<table width=600 cellpadding=0 cellspacing=0 border=0>
	<tr>
		<td>';
print '<form name="update" method="post" action="edited.php" enctype="multipart/form-data">';
while ($row = mysql_fetch_array($query))
{
	$id       = $row['id'];
	$time     = $row['time'];
	$event    = $row['event'];
     
	print '
			<table width=600 cellpadding=0 cellspacing=5 border=0>
				<tr height=25>
					<td class="formtext"> <strong>Time</strong> </td>
					<td> 
						<input type=text name="event_record[' . $id . '][time]" size=45 value="' . $time . '">
					</td>
				</tr>
				<tr height=25>
					<td class="formtext"> <strong>Event</strong> </td>
					<td> 
						<textarea name="event_record[' . $id . . '][event]" cols=60 rows=2 wrap=virtual>' . $event . '</textarea>
					</td>
				</tr>
			</table>
			<br>';
}

print '
			<center><input type=submit name="Submit" value="Submit Changes"></center>
			<br>
			<br>
		</td>
	</form>
	</tr>
</table>';
?>

The previous had a couple of problems with print statements.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[buzzt]

So how do I see all the (new - modified) results on the next page now (which will also submit them to the db at the same time)?

 

[kenrbnsn]

In the code that recieves the data, do the following:

$event_record = $_POST['event_record'];

Then cycle through the array to get your values. If you're not sure of what the above code snippet does, put a

echo '<pre>';print_r($event_record);echo '<pre>';

just after the line.

Ken

 

[buzzt]

Here's what it looks like:

Array
(
[6897] => Array
(
[time] => Time 1
[event] => Event 1
)

[6898] => Array
(
[time] => Time 2
[event] => Event 2
)
)

This is great so far, but I'm still unsure about 2 things:

1. Make this look a little easier to read to non-technical people.

2. Submit ALL this information at once to the mysql update query.

 

[sleipnir214]

Let's deal with number 2 first.

You can't send this to MySQL all at once. You're changing existing records, which means you'll have to do a set of individual UPDATE queries, one for each record. Your script will have to loop through the submitted data, creating UPDATE queries and submitting them to MySQL.


In terms of providing pretty output, it's a matter of looping through the array and issuing a set of formatted print statements. But is it necessary to show the data to the user? Your user provided the data.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[buzzt]

I'm not very experienced with arrays, so I'm not sure how to extract this information without specifying the actual record.

echo $record[6897][event];

I'm not sure how I would write this loop for the update query... Sorry to bother you so much.

 

[sleipnir214]

Use a foreach() loop. Something like:

foreach ($_POST['event_record'] as $event_id => $event_data)
{
$query =
"UPDATE tablename set time = '" .
$event_data['time'] .
"', event = '" .
$event_data['event'] .
"' WHERE id = " .
$event_id;
}



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[buzzt]

I did this to view the results:

echo $event_data['time'] . "<br>";
echo $event_data['event'] . "<br>";

I would like to understand what I'm doing (rather than just copy this) so, I'm sorry if I'm going on and on here. Why does the echo statement work the way I did it above and yet you gave me the code for the update as

$query = "UPDATE tablename set time = '" . $event_data['time'] . "', event = '" . $event_data['event'] . "' WHERE id = " . $event_id;

What are the '" . for before and after each item for?

 

[sleipnir214]

I provided the code I did because you said you wanted to update records in the table, based on user input. Do you no longer need to programmatically create UPDATE queries to update the table?


They're not before and after each item. They're only before and after each item which has a string value -- look at the use of $event_id for contrast.

The singlequotes are there to delineate string values inside the query. Given the data you posted above, to update that record, your code should produce a query like:

UPDATE tablename SET time = [red]'[/red]Time 1[red]'[/red], event = [red]'[/red]Event 1[red]'[/red] WHERE id = 6897

The quotes in my code correspond to the above query string.




Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[buzzt]

Sorry... my bad. It all works, and makes sense to me now! Thank you very much for all your help.

 

[hos2]

wow this code is great , just what I was looking for. was just trying all combinations of arrays

I already modified the code a bit to make it more flexible when you have al lot of fields and checkboxes
it's even possible to make it more flexible if you pass the tablename as a get variable

foreach ($_POST['event_record'] as $event_id => $event_data)
{
   $query = $query . "UPDATE test set ";

	foreach ($event_data as $fieldname => $value)
		{
			if (is_array($value))
				{
				$arrayLength = count($value);
				$temparray=array_values($value);	
				unset($value);
				for ($i = 0; $i < $arrayLength; $i++){ $value=$value .$temparray[$i] ."#";}
//I store checkbox values in a string with # as seperator				}
		$query = $query . "$fieldname = '" . $value . "'," ;
		}
	$query=substr($query,0,strlen($query)-1); // get rid of the last ,
	$query = $query. " WHERE testid = '" . $event_id ."'";
	$rs=mysql_query($query,$conn);
	unset($query);
}

and here the form that works on this

<?
include "globalvars.php3";
include $rootpath."cgi-bin/connopen_half.php3";

$query="SELECT * from test";


print("
<html>
<body>
<table>
<form action=sqlaction.php3?testid=$testid target=_self method=post>  
<tr><td><table>
<tr><td>name</td><td>town</td><td>zipcode</td><td>press</td><td>guest</td><td>vip</td></tr>");

$q_result = mysql_query($query);
while ($ary = mysql_fetch_assoc($q_result)) { //one
  while (list($key,$val) = each($ary)) { //two
   $$key = $val;
  }  // end two



print("
<tr><td><INPUT TYPE=\”text\” name=event_record[$testid][testname]  size=30 value=\"$testname\"></td>
<td><INPUT TYPE=\”text\” name=event_record[$testid][testtown]  size=30 value=\"$testtown\"></td>
<td><INPUT TYPE=\”text\” name=event_record[$testid][testzipcode]  size=10 value=\"$testzipcode\"></td>");

$i=0;
	echo "<td><INPUT TYPE=checkbox name=event_record[$testid][testcategorie][$i] value=\"press\"";
		if (stristr($testcategorie,"press" ."#")) {echo " CHECKED ";}
	echo "></td>";
$i=1;
	echo "<td><INPUT TYPE=checkbox name=event_record[$testid][testcategorie][$i] value=\"guest\"";
		if (stristr($testcategorie,"guest" ."#")) {echo " CHECKED ";}
	echo "></td>";

$i=2;
	echo "<td><INPUT TYPE=checkbox name=event_record[$testid][testcategorie][$i] value=\"vip\"";
		if (stristr($testcategorie,"vip" ."#")) {echo " CHECKED ";}
	echo "></td>";

}

print("
</tr><tr><td colspan=3><input type=Submit value=Submit name=Submit></td></tr>
</table>
</BODY>
</HTML>

");

you can see it working on

http://thehospages.com/multipleupdate/index.php3

ps still a question left. if I uncheck all the checkboxes in a row how can I pass on that there must be an empty string ????

kind regards and a star for sleipnir