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 3 of 3
  1. #1
    Regular Coder
    Join Date
    May 2008
    Posts
    446
    Thanks
    23
    Thanked 5 Times in 5 Posts

    Trying to update multiple rows mysqli

    I need to update all the rows in a table.

    Currently I am using a foreach to loop through the various input values and update the database in the appropriate places

    Here is the code I am using so far:
    PHP Code:
    function update_admin()
    {
        
    // create new record
        
    if(isset($_POST['submit'])) 
        {
        
        
    $Database = new mysqli('localhost''#####''#######''######');

        
    mysqli_report(MYSQLI_REPORT_ERROR);
        
            
            
    $ids_array get_user_ids();
            
    $num_row get_user_rows();
            
    $x 1;
            while(
    $x $num_row)
            foreach(
    $ids_array as $id)
            {
                
    $user_name htmlentities($_POST['user_'.$id.'_name'], ENT_QUOTES);
                
    $nick_name htmlentities($_POST['nick_'.$id.'_name'], ENT_QUOTES);
                
    $update_super $_POST['update_'.$id.'_super'];
                
    $set_super 0;
                if(isset(
    $update_super))
                {
                    
    $set_super 1;
                }
                
                if (
    $stmt $Database->query("UPDATE views SET view_name='".$user_name."', view_pass='".$nick_name."', view_priv='".$update_super."' WHERE view_id='".$id."'" ))
                {

                    
    $stmt->execute();
                    
                }
                else
                {
                    echo 
    'Error: could not prepare SQL statement.<br />';
                }
            }
            
    $stmt->close();
            
    header("Location: home.php");
            

        }

    }
    // end submit_form() 
    Right now I am not getting any results from this, site just takes a long time to reload the page.

    Side note: $ids_array = get_user_ids(); does work properly, so no worries there.

    I appreciate the help

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This isn't a statement:
    PHP Code:
                if ($stmt $Database->query("UPDATE views SET view_name='".$user_name."', view_pass='".$nick_name."', view_priv='".$update_super."' WHERE view_id='".$id."'" ))
                {

                    
    $stmt->execute();
                    
                } 
    That's a direct query. The execute will fail on that (fatally). $stmt is a resultset, not a prepared statement, choose one or the other that you'd like to use. If you choose the continue querying, you must escape that input data provided by the post.
    If you choose a prepared statement, you prepare the query BEFORE the foreach call, and you bind your parameters and execute from the loop.

  • #3
    Regular Coder
    Join Date
    May 2008
    Posts
    446
    Thanks
    23
    Thanked 5 Times in 5 Posts
    Thanks for the reply, I got the query going using the query not the statement.

    Although I am now having an issue with my checkbox 'view_priv' getting populated from the database correctly. I had it working before fine and have not changed the code that populates it on the page. My issue must be in the update_user() function with the redirect at the end.

    Here is what I have:
    PHP Code:
    function update_admin()
    {
        
    // create new record
        
    if(isset($_POST['submit'])) 
        {
        
        
    $Database = new mysqli('localhost''######''#########''########');

        
    mysqli_report(MYSQLI_REPORT_ERROR);
        
            
            
    $ids_array get_user_ids();
            
    $num_row get_user_rows();
            
    $x 1;
            
            
            

            foreach(
    $ids_array as $id)
            {
                
    $user_name htmlentities($_POST['user_'.$id.'_name'], ENT_QUOTES);
                
    $nick_name htmlentities($_POST['user_'.$id.'_pass'], ENT_QUOTES);
                if(
    $user_name == '' || $nick_name == '')
                {
                    echo 
    'Please fill in all the user names and passwords.';
                    
    sleep(4);
                    
    header("Location: home.php");
                }
        
                
    $update_super $_POST['update_'.$id.'_super'];
                
    $set_super 0;
                if(isset(
    $update_super))
                {
                    
    $set_super 1;
                }
                
                
    $user_name $Database->real_escape_string($user_name);
                
    $nick_name $Database->real_escape_string($nick_name);


                
                if (
    $Database->query("UPDATE views SET view_name='".$user_name."', view_pass='".$nick_name."', view_priv='".$update_super."' WHERE view_id='".$id."'" ))

                {

                    
                }
                else
                {
                    echo 
    'Error: could not prepare SQL statement.<br />';
                }
            } 
    // end foreach
            
            
            
    header("Location: home.php");
            
    $Database->close();
        } 
    // end if(isset($_POST['submit']))

    }// end submit_form() 

    Any ideas?

    Thanks for the help


  •  

    Posting Permissions

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