SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Implement multiple row edits with one submit phpmyadmin-style?

    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...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chelse View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chelse View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm....

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

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,029
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chelse View Post
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 .

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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.

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    The Netherlands
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    The Netherlands
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    HTML Code:
    <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
    PHP Code:

    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.
    HTML Code:
    <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.

    HTML Code:
    <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:

    PHP Code:

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

    Last edited by RvanD85; Apr 16, 2012 at 05:27. Reason: came up with another way of building the form

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RvanD85 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    The Netherlands
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's pretty much what i said in post #2
    Yes indeed. But topic starter asked it again

    Quote Originally Posted by chelse View Post
    (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

  15. #15
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  16. #16
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  17. #17
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  18. #18
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Version 1 worked just fine. Print_r $_POST['myarray']
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  19. #19
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi StarLion

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

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •