Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder DjDanHayden's Avatar
    Join Date
    Nov 2008
    Location
    Ireland
    Posts
    55
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Help update databse after form while loop

    I have this piece of code that i cannot seem to get to work. It will only update the last row in the database. I need it to update all the rows when the form is submitted. Do i need to loop the update ?


    PHP Code:
       <fieldset>
       <legend>Ticket Categorys</legend>
       <form method='post' action='admin.php?do=ticketCats'>
       <table width='100%' style='background: #dddddd; padding:2px;'>
        <tr style='background: #C4C4C4;'>
            <td style='font-weight: bold;'><center>ID</center></td>
            <td style='font-weight: bold;'>Category</td>
            <td style='font-weight: bold;'><center>Action</center></td>
        </tr>    
        <?

        $color
    ="1";
            
        
    $listcats=mysql_query("SELECT id,name FROM ticket_cats ORDER BY name ASC");
        while(list(
    $id,$name)=mysql_fetch_row($listcats))
        {
                
            if(
    $color==1)
            {
            
    ?>
                <tr style='background: #fff;'>
                <?
                $color
    ="2";
            }
            else
            {    
            
    // Set $color back to 1
            
    $color="1";
            }
            
    ?>
                        
                        
                <td style='width:15px;'><input type='hidden' value='<?echo $id;?>' name='id[]'><center><?echo $id;?></center></td>
                <td><input type='text' name='catname' style='width:400px;' value='<?echo $name;?>'/></td>
                <td><center><input type='submit' name='deleteCat' value='Delete ID:(<?echo $id;?>)' /></center></td>
                </tr>
            <?
        
    }
        
    ?>
        <tr>
            <td>
                <input type='submit' name='updateAllCats' value='Update Categorys' />
            </td>
        </tr>
        </table>
        </form>
        </fieldset>
        
    <?
            
    if(!empty($_POST['updateAllCats']))                                                                        
            {
            
    $catname $_POST['catname'];
            
    $ida $_POST['id'];
            
            
    mysql_query("UPDATE ticket_cats SET name='$catname' WHERE id='$ida'");
                
            }
    ?>

  • #2
    New Coder
    Join Date
    Dec 2011
    Posts
    62
    Thanks
    4
    Thanked 10 Times in 10 Posts
    First I would suggest using mysqli or PDO for mysql has been depreciated.

    The following is how you would do it in mysqli (The code hasn't been tested....if you need more help you can easily get the help at php.net).

    PHP Code:
    <?php
    $link 
    mysqli_connect('localhost''my_user''my_password''your_database');

    /* check connection */
    if (!$link) {
        
    printf("Connect failed: %s\n"mysqli_connect_error());
        exit();
    }

    $stmt mysqli_prepare($link"UPDATE
                   ticket_cats
                SET
                   name =?
                WHERE
                   id = ?
            "
    );
            
    mysqli_stmt_bind_param($stmt'ss'$catname$ida);

            
    $catname $_POST['catname'];
            
    $ida $_POST['id']; 

    /* execute prepared statement */
    mysqli_stmt_execute($stmt);


    /* close statement and connection */
    mysqli_stmt_close($stmt);

    /* close connection */
    mysqli_close($link);
    ?>
    Sorry, I'm half a wake I didn't see you wanted to update more than one row...
    Last edited by Strider64; 04-02-2013 at 12:29 PM.

  • #3
    New Coder
    Join Date
    Feb 2012
    Posts
    29
    Thanks
    1
    Thanked 1 Time in 1 Post
    Your problem is here
    PHP Code:
    list($id,$name)=mysql_fetch_row($listcats
    it doesnt create list of all ids in db, mysql_fetch_row is getting only one row.

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    3,763
    Thanks
    23
    Thanked 548 Times in 547 Posts
    @DjDanHayden: Worked(well I saw the form) BUT I first had to change all the "<?" to "<?php".

    But your code is in trouble. the ACTION column has delete buttons that just submits the form. That's all.
    Because of the last php section, I think it just calls itself. Is the name of this code "admin.php"?
    And that section has these lines:
    PHP Code:
    $ida $_POST['id'];
    mysql_query("UPDATE ticket_cats SET name='$catname' WHERE id='$ida'"); 
    You have made $ida an array so
    PHP Code:
    WHERE id='$ida' 
    makes no sense, it can't be done.
    Evolution - The non-random survival of random variants.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    @sun and @strider: +1. Sun you are correct; the update shouldn't actually perform any task as it is using an id='Array' as its value (the string representation of the array). Strider, yep mysqli or PDO should be promoted at this time. I have a link in my sig as well for considering which to use and why.

    To expand on strider's, I would rewrite your HTML. You can choose to use the buttons if you like, but personally I prefer the use of checkboxes for deletions to allow many at a time. Something like so (I will write in OO instead):
    PHP Code:
    <?php
    $link 
    = new MySQLi('localhost''my_user''my_password''your_database');
    if (isset(
    $_POST['catname']))
    {
        if (
    $stmt $link->prepare("UPDATE ticket_cats SET name=? WHERE id=?"))
        {
            
    // note I took this off of your original query.  If id is not a string, than instead use 'i' for the second 's'.
            
    $stmt->bind_param('ss'$name$id);
            foreach (
    $_POST['catname'] AS $id => $name)
            {
                
    $stmt->execute();
            }
            
    $stmt->close();
        }
    }
    ?>
       <fieldset>
       <legend>Ticket Categorys</legend>
       <form method='post' action='admin.php?do=ticketCats'>
       <table width='100%' style='background: #dddddd; padding:2px;'>
        <tr style='background: #C4C4C4;'>
            <td style='font-weight: bold;'><center>ID</center></td>
            <td style='font-weight: bold;'>Category</td>
            <td style='font-weight: bold;'><center>Action</center></td>
        </tr>    
        <?php

        $color 
    1;
            
        
    $listcats $link->query("SELECT id,name FROM ticket_cats ORDER BY name ASC");
        while(list(
    $id,$name) = $listcats->fetch_assoc())
        {
            
    // these would be better off using a class type instead.  That said you really shouldn't use tables for this either.
            
    if (($color++ & 1) == 0)
            {
                
    $styleColour '#fff';
            }
            else
            {
                
    $styleColour '#ccc'// ?
            
    }
            
    printf('<tr style="background-color: %s">'$styleColour);
            
    printf('<td>%s</td>'$id);
            
    printf('<td><input type="text" name="catname[%s]" style="width:400px;" value="%s"/></td>'$id$name);
            
    printf('<td><label><input type="checkbox" name="deleteCat[]" value="%s"/>Delete ID: %s</label></td>'$id$id);
            print(
    '</tr>');
        }
        
    $listcats->free();
        
    ?>
        <tr>
            <td>
                <input type='submit' name='updateAllCats' value='Update Categorys' />
            </td>
        </tr>
        </table>
        </form>
        </fieldset>
    And you can chain the deleteCat into the rest of the form code. Simply check it off and submit. You could add a second button for just the deletions which requires clicking on as well.

    I didn't write the deletion code in this, but once you check than the isset for deleteCat should be populated with an array of each item selected. You can bind or use an IN clause for that.
    It's untested as well, but works okay in my head :P
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    DjDanHayden (04-03-2013)

  • #6
    New Coder DjDanHayden's Avatar
    Join Date
    Nov 2008
    Location
    Ireland
    Posts
    55
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    @sun and @strider: +1. Sun you are correct; the update shouldn't actually perform any task as it is using an id='Array' as its value (the string representation of the array). Strider, yep mysqli or PDO should be promoted at this time. I have a link in my sig as well for considering which to use and why.

    To expand on strider's, I would rewrite your HTML. You can choose to use the buttons if you like, but personally I prefer the use of checkboxes for deletions to allow many at a time. Something like so (I will write in OO instead):

    And you can chain the deleteCat into the rest of the form code. Simply check it off and submit. You could add a second button for just the deletions which requires clicking on as well.

    I didn't write the deletion code in this, but once you check than the isset for deleteCat should be populated with an array of each item selected. You can bind or use an IN clause for that.
    It's untested as well, but works okay in my head :P

    @Fou-lu

    Thanks that seems to work and be much better code. The only part that does not seem to work it the output of the value in each textbox which needs to be displayed. In this case its the row "name".

    Also that does the %s do ?

    Thanks for your time.

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Sorry, use fetch_row() not fetch_assoc(). That should fix that up.
    %s in [s]printf means to interpret the argument provided in that location as a string. There are many different format specifiers for different datatypes; you can check the api for it at http://php.net/function.sprintf.php
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Tags for this Thread

    Posting Permissions

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