Implement multiple row edits with one submit phpmyadmin-style?

[chelse]

Hi: can anyone help?

It is straightforward to edit and delete rows in a table one at a time using forms to specify the update details, click the submit button and wait for the action to be completed. But this "client / server" action is usually tedious.

In modern versions of phpmyadmin it is possible to initiate edits to multiple rows. For example when using the application's browsing facility to display a table, the data in each row is displayed preceded by a check box. Clicking the checkboxes on multiple rows and then clicking the submit button brings up edit forms for each of the checked rows. These can then be filled in , one after another: pressing a single submit buton comletes all the edits. Multiple row deletes can be done in a similar manner.

Its very ingenious. Can anyone provide coding instructions to accomplish actions like phpmyadmin's ?

It would seem that arrays of data have to be sent from a "client" form to a "server" application which does the work.

I've searched Sitepoint's forums but can't find details...

[r937]

Can anyone provide coding instructions to accomplish actions like phpmyadmin's ?

if you're making the same change to multiple rows (e.g. updating customer status to 'deadbeat' where amount_owing > 100 and last_payment_date < current_date - interval 1 year) then you can write a simple SQL statement for it

if it's a different change on every row, then you would actually want to do them one at a time, right?

[chelse]

Hi SQL Consultant

No, its not like this - much more clever.

In the current version of phpmyadmin - version 3.4 I think - you can Browse a table. Selecting the BROWSE menu item brings up a table of all the table's rows. Each row starts off with an unticked checkbox followed by the links: EDIT, INLINE EDIT COPY and DELETE. The row's contents come next. If you tick the checkboxes on (say) rows 2, 3, 6 and 10 and then select the submit button labelled 'change', a new page comes up containing data entry forms for all the 4 rows. These can then be edited using the *same* phpmyadmin page and clicking the 'GO' button. This returns to the previous page showing the amended details for the 4 rows.

If the button 'delete' had been clicked, then the 4 rows would be deleted - after a warning.....

Sorry to be verbose. Question is How are these actions coded?

I guess that clicking the 'change' button submits (at least) the check state, and row-number of all 4 checked records. How is this done? by sending a 3 x 4 array of data back to the 2nd page where the 4 idata nput forms are displayed.

Can you help?

[r937]

Question is How are these actions coded?

sorry, NFI (no idea)

you would have to contact the developers of phpmyadmin

not really a mysql question

[chelse]

Hmmm....

Thanks for trying, I'll try and contact phpmyadmin's developers

[SpacePhoenix]

Hi SQL Consultant

No, its not like this - much more clever.

In the current version of phpmyadmin - version 3.4 I think - you can Browse a table. Selecting the BROWSE menu item brings up a table of all the table's rows. Each row starts off with an unticked checkbox followed by the links: EDIT, INLINE EDIT COPY and DELETE. The row's contents come next. If you tick the checkboxes on (say) rows 2, 3, 6 and 10 and then select the submit button labelled 'change', a new page comes up containing data entry forms for all the 4 rows. These can then be edited using the *same* phpmyadmin page and clicking the 'GO' button. This returns to the previous page showing the amended details for the 4 rows.

If the button 'delete' had been clicked, then the 4 rows would be deleted - after a warning.....

Sorry to be verbose. Question is How are these actions coded?

I guess that clicking the 'change' button submits (at least) the check state, and row-number of all 4 checked records. How is this done? by sending a 3 x 4 array of data back to the 2nd page where the 4 idata nput forms are displayed.

Can you help?

Possibly PHPMyAdmin is sending 4 queries in one hit to the MySQL or sending one query at a time and then displaying the outcome of the queries

[guelphdad]

Sounds like you are talking of tables that are trivial in size. PHPMYADMIN also only shows you 30 rows at a time unless you specify a larger number. But tell me how efficient this is if you have 100,000 rows in your table? How do you intend to scroll through looking for that many rows by hand?

Don't get caught up on this and figure out how to actually use the sql query/delete/update itself to look after the data.

[chelse]

Hi guelfdad and SpacePhoenix

Point about increasing feasibility with table size. In fact the tables I am concerned with are not likely to exceed 400 records. This should be do-able. The edits to rows are diverse - eg updating a club members address list. BUT as mentioned previously its very tedious to do such edits one by one, each taking 2 or 3 client-server steps, possibly slowed down by heavy internet traffic at each step. With phpmyadmin's current application, you can select a 12 rows needing attention and in just one submit you will get 12 data entry forms which can be filled up and then again, in one submit operation the 12 rows come back updated correctly - apart from typos of course!

My problem is that I can't figure out how to send a variety of arrays of data in order to send / receive the 12 sets of data in one go. The html form's input statement can, after all, only send one name:value pair.

As SpacePhoenix says, yes phpmyadmin composes some magic SQL to do 12 updates at once. This looks possible, but the main problem seems to me to get the 12 sets of data sent in one Form submit .

[guelphdad]

why do you have to do them by hand and why do you need to use phpmyadmin to do deletes from the table by hand at all?
Suppose you have the following userids you need to delete:
2, 34, 111, 119, 201, 334, 345, 399

how would you delete them right now? are you suggesting you would query your table, bring back all records and then tediously scroll through them to delete the records by clicking a checkbox and then a delete button?

Isn't it simply easier to write the following in the sql tab:

DELETE FROM 
  yourtablename
WHERE 
  userid IN (2, 34, 111, 119, 201, 334, 345, 399)

one single delete statement. and you can see how trivial it would be to delete the users if that list grew to say 50 you would have to delete, and even more so, in comparison to the way you want to do it if those 50 were spread over 10,000 rows.

[RvanD85]

I would guess PHPmyadmin submits the form data as array. They name them probably something like this in html.

Name[]
Number[]
Date[]

If you submit you receive arrays. $_POST[name][0] $_POST[name][1] etc... And if you are editing multiple rows you can give the row id to the html input element. Name[324]

<Input type:text name:name[324] value:test />

Ps my phone has no equal sign, so used : instead.

[chelse]

Many thanks to guelfdad and RvanD85 for your help.

Yes I had appreciated that the SQL would be as guelfdad has laid out and the SQL is easier for multiple deleting than for multiple updating. (I imaging that separate SQL is needed for updating each identified record {?}) But the problem is in constructing the php code to specify the set of userids and the arrays of data to implement the updating - all involving the complicated client-server handshake.

As stated, my tables have less than 200 records, so employing check boxes at the start of each row in a HTML form display of the table enables the userids to be captured and sent to the server which then has all that's necessary to implement guelfdads SQL

RvanD85's code snippits are tantalising. Sending arrays like this is new to me and I'll conduct some experiments. If you can amplify your suggestion I shall be grateful.

chelse

[RvanD85]

Your welcome, I hope it is to use for you.

Today I looked at phpmyadmin and they indeed send post arrays.

In your form it would look like this.

<form action="" method="post">
<!-- row 1 -->
<input type="text" name="name[1]" value="row1name" />
<input type="text" name="date[1]" value="row1date" />
<input type="text" name="address[1]" value="row1address" />
<!-- row 2 -->
<input type="text" name="name[2]" value="row2name" />
<input type="text" name="date[2]" value="row2date" />
<input type="text" name="address[2]" value="row2address" />
<!-- row 3 -->
<input type="text" name="name[3]" value="row3name" />
<input type="text" name="date[3]" value="row3date" />
<input type="text" name="address[3]" value="row3address" />
<!-- row 4 -->
<input type="text" name="name[4]" value="row4name" />
<input type="text" name="date[4]" value="row4date" />
<input type="text" name="address[4]" value="row4address" />
</form>

In your PHP you can do something like

foreach($_POST['name'] as $key => $value)
{
   //Here you can execute functions or query on every post
   $db->query('UPDATE yourtable SET name = "'.$_POST['name'][$key].'", date = "'.$_POST['date'][$key].'", address = "'.$_POST['address'][$key].'" WHERE id = '.$key);
} 

If it are new rows you are adding then you can leave the id out of your post arrays.

<form action="" method="post">
<!-- row 1 -->
<input type="text" name="name[]" value="row1name" />
<input type="text" name="date[]" value="row1date" />
<input type="text" name="address[]" value="row1address" />
<!-- row 2 -->
<input type="text" name="name[]" value="row2name" />
<input type="text" name="date[]" value="row2date" />
<input type="text" name="address[]" value="row2address" />
<!-- row 3 -->
<input type="text" name="name[]" value="row3name" />
<input type="text" name="date[]" value="row3date" />
<input type="text" name="address[]" value="row3address" />
<!-- row 4 -->
<input type="text" name="name[]" value="row4name" />
<input type="text" name="date[]" value="row4date" />
<input type="text" name="address[]" value="row4address" />
</form>

If you want to delete all the selected rows, you can use 1 query and do the where id IN(1,2,3,4,etc...). This is also valid if you i.e. update the name of multiple rows with the same value.
But whenever you update multiple rows where each has his own values, you are forced to create multiple querys I believe.

I think maybe you can build your form also this way. But don't know for sure. You'll have to try it out.

<form action="" method="post">
<!-- row 1 -->
<input type="text" name="row[1][name]" value="row1name" />
<input type="text" name="row[1][date]" value="row1date" />
<input type="text" name="row[1][address]" value="row1address" />
<!-- row 2 -->
<input type="text" name="row[2][name]" value="row2name" />
<input type="text" name="row[2][date]" value="row2date" />
<input type="text" name="row[2][address]" value="row2address" />
<!-- row 3 -->
<input type="text" name="row[3][name]" value="row3name" />
<input type="text" name="row[3][date]" value="row3date" />
<input type="text" name="row[3][address]" value="row3address" />
<!-- row 4 -->
<input type="text" name="row[4][name]" value="row4name" />
<input type="text" name="row[4][date]" value="row4date" />
<input type="text" name="row[4][address]" value="row4address" />
</form>

Php would be like this I guess:

foreach($_POST['row'] as $key => $values)
{
   //Here you can execute functions or query on every post
   $db->query('UPDATE yourtable SET name = "'.$values['name'].'", date = "'.$values['date'].'", address = "'.$values['address'].'" WHERE id = '.$key);
} 

[r937]

This is also valid if you i.e. update the name of multiple rows with the same value.
But whenever you update multiple rows where each has his own values, you are forced to create multiple querys I believe.

that's pretty much what i said in post #2

[RvanD85]

that's pretty much what i said in post #2

Yes indeed. But topic starter asked it again

(I imaging that separate SQL is needed for updating each identified record {?})

And I didn't read the full topic again, so I guessed It wasn't mentioned here before

[chelse]

Hi

Well, I've cracked the problem. HTML Forms can't deal directly with arrays of data. So if
arrays have to be sent, they have to be sent element by element as NAMEATA pairs. All NAMES
must be unique.

To explain it, Suppose we have a 4-column name & address table shown here as a HTML form with a single submit button
with a value of 'updating':-

chkbox ID first last address
---------------------------------------------------------------
X 9 Michael Greveson 109, Glen Rd
..... other records.....
X 39 Bradwell Johnson 37 Edgar St
____________________________________________________
updating
____________________________________________________

The 'X' at the start of every row is a checkbox; if it is ticked the checkbox acquires an integer
number equal to the record's ID. One or more of the check boxes may be ticked thus generating a HTML
input statement for example:-

<input type='checkbox' name='chk.$id' value='$id'>

When the submit button is clicked, a name-value pair is sent to the server for each of the checked
rows of the table. If the 2 rows above were checked the name-value pairs are "chk9=>9, chk39=>39".
From the received $_POST array, an array $idary can be extracted containing, in this example,
the elements : (0=>9, 1=>39) (note the actual names of the checkboxes can be any *unique* strings,
we only need the elements of the function array_values($_POST)).

The next step is to initiate a update-data entry form to capture the data (2 sets of 4 data
items in the example) to generate SQL INPUT statements for each of the 2 records.
A 2 row x 4 column array ( e.g. $updata) is needed so that, for example the element $updata[0][4]
is the the NEW value of Mr Greveson's address (currently '109, Glen Rd'). Similarly, $updata[1][2] is
the value replacing 'Bradwell'.

Since a HTML form can only send name-pairs where the name is unique, it is necessary to add
a suffix to the names of the fields. Thus I've added a suffix :'_$idary[0]' (for the first record to be edited) and
'_$idary[1]' (for the 2nd) to the field names. Thus a typical input statement in the update-data entry form is:-

$ind : <input type="Text" name="'. $ind.'_'.$iuset.'" value="'. $row[$iuset][$ind]

(Where $ind = field-name[$i}, $i between 0 and field count;
$iuset = the index number of the set of update-data - [0 or 1 in the example]
$row[$iuset] = $row[$iuset] = mysql_fetch_array($result); [ i.e. the row of data items
in the $iuset'th row being updated].)

In a bit more detail the skeleton code for the modifyrecord form is:-

function modifyrecform2($idary){ // $idary array contains list of IDs for records to be edited
print('<form method="post" action="' . $SELF. '">');
for ($iuset = 0; $iuset < count($idary); $iuset++) { // iterate over all sets (0 and 1 in example)
$idx = $idary[$iuset];
if ($idx) { // $irec'th row in the update set
$sql = "SELECT * FROM $tblname WHERE id=$idx";
$result = mysql_query($sql);
$row[$iuset] = mysql_fetch_array($result);
}
print ('<input type=hidden name="' . $fldnames[0] . '_'.$iuset .'" value="' . $idx . '">');
print("<h3> Updating Record $idx</h3>");

for ($i=1; $i <= $colcount -1; $i++) {
$ind = $fldnames[$i];
print("$ind");
echo '<input type="Text" name="'. $ind.'_'.$iuset.'" value="'. $row[$iuset][$ind] .'">';
}
} // for $irec
print ('<input type="Submit" name="submit" value="updating">');
echo "/form>";

Note that update-data entry forms are provided for each update set (2 in this case), but only one
submit button

When the edits are made and the submit button clicked, the received $_POST array is:-
$_POST = array('id_0'=>'9','first_0'=>'Michael','last_0'=>'Greveson','address_0'=>'109,Glen Rd',
'id_1'=>'39','first_1'=>'Bradwell','last_1'=>'Johnson','address_1'=>'37 Edgar St',
'submit'=>'updating') [except that some of the data here will have been edited.]

This is parsed with the following code to generate a 2-row array ($data):-

$updata = array();
foreach( $_POST as $key => $value) {
if ( ereg('_[0-9]+', $key, $regs )){
$of = ltrim($regs[0], '_');
$b = ereg_replace("_[0-9]+", "", $key);
$updata[$of][$b] = $value;
}else{;

The code removes the suffices to the 'NAME' elements in the 2 row array:-

$updata = array(0 ,
array ('id'=>'9','first'=>'Michael','last'=>'Greveson','address'=>'109, Glen Rd')
1, array('id'=>'38','first'=>'Bradwell','last'=>'Johnson','address'=>'37 Edgar St' ))

SQL UPDATE statements can easily be constructed for the 2 records from the elements of
array_values($data[0]) and array_values($data[1]) e.g. :-

$sql = "UPDATE $tblname SET "first='Michael',last'"="'Greveson',address='109, Glen Rd'" WHERE id=9";
and $sql = "UPDATE $tblname SET "first='Bradwell',last'"="'Johnson',address='37 Edgar St'" WHERE id=39";

I've found this useful for updating 10 records in tables ( of members of a club) with 900 records and 16 fields. Its very much faster to make edits to records 5 at a time rather than singly.

chelse

[StarLion]

HTML forms can handle/send arrays just fine. PHP can receive those arrays just fine. (name="myarray[mykey]" )

to the original question, it is possible to send a multi_query with the mysqli library. (mysqli_multi_query). Said multi-query is just a string of queries tacked together with semicolons.

[chelse]

Hi

Following Rvan85's suggestion above, I have been trying to get the following html form to pass arrays :-

{Version 1}
Function addrec(){
print('<form method="post" action="' . $SELF. '">');

echo'<input type="text" name="myarray[name]" value="John">';
echo'<input type="text" name="myarray[job]" value="Director">';

echo'<input type="Submit" name="submit" value="Go">'; // output remaining lines
echo'</form></table>';
} // addrec
and the received POST array is:-

$_POST = array('myarray'=>'Array','submit'=>'Go')

but,

If the two input statements are replaced by:-

echo'<input type="text" name="name" value="John">';
echo'<input type="text" name="job" value="Director">';

Then, of course we get

$_POST = array('name'=>'John','job'=>'Director','submit'=>'Go')

Why does Version 1 not work. I am using PHP 5.2.3.3.

[StarLion]

Version 1 worked just fine. Print_r $_POST['myarray']

[chelse]

Hi StarLion

Yep, absolutely right many thanks - my brain clearly out of gear....

(guess you meant print_r ($_POST['myarray'] )