dcsimg
www.webdeveloper.com
Results 1 to 9 of 9

Thread: Update form in MySQLi

  1. #1
    Join Date
    Jan 2015
    Posts
    49

    Update form in MySQLi

    Hi all, wonder if anyone can help. I'm working with MySQLi. I've managed to create a form which inserts into a database and created a view which echos what's in a table. I have also created an edit link edit link that with:
    PHP Code:
    edit.php?id={$row->id
    In edit.php I have a form and that's where I have got to and I'm stuck. Fields i need to update are Title, Text, Link.

    1. How do I echo out in each text field of the form the current data
    2. How do I set it up to UPDATE. I'm trying this method, doesn't seem to be working yet.

    Update code
    PHP Code:
    <?php
    //error_reporting(0);
    include ('../db/db_connection.php');
    include (
    '../db/security.php');

    $stmt $mysqli->prepare("UPDATE snippets SET Title = ?,
       Text = ?,
       Link = ?, 
       Campaign = ?
       WHERE id = ?"
    );
    $stmt->bind_param('sssii',
       
    $_POST['Title'],
       
    $_POST['Text'],
       
    $_POST['Link'],
       
    $_POST['Campaign'],
       
    $_POST['id']);
    $stmt->execute();
    $stmt->close();
    ?>
    The form follows after this.

    I currently get an error Undefined variable: mysqli

    Help would be much appreciated.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    20,320
    Quote Originally Posted by TheTrooper View Post
    ...
    I currently get an error Undefined variable: mysqli
    ...
    Well, I'd start with solving that. Where do you define/instantiate $mysqli? You might want to change those "include" calls to "require", to make 100% sure they're being loaded.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

  3. #3
    Join Date
    Jan 2015
    Posts
    49
    Quote Originally Posted by NogDog View Post
    Well, I'd start with solving that. Where do you define/instantiate $mysqli? You might want to change those "include" calls to "require", to make 100% sure they're being loaded.
    hey. in my db_connection file I have a line with;

    PHP Code:
    $db = new mysqli('***''***''''***'); 
    I'm guessing that's where its supposed to be defined from, and this could be an issue?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    20,320
    Yep, if it's calling it $db, then you need to use $db instead of $mysqli in your code (or vice versa, of course, just so it's the same name). It's just a variable (that happens to hold an instance of your db connection object, but you can name it whatever you want.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

  5. #5
    Join Date
    Jan 2015
    Posts
    49
    No errors!

    not actually doing anything though. It's fair to say I'm a bit lost from this part. I suppose trying to echo the relavent data in the correct text fields to know what's being updated then the UPDATE itself. Any ideas?

  6. #6
    Join Date
    Jan 2015
    Posts
    49
    Here you go, I'm a step closer. I currently have an error though. I found a simple application which I followed however I have an error which I can't identify.

    Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement

    PHP Code:
    <?php }



            
    /*

               EDIT RECORD

            */
        // if the 'id' variable is set in the URL, we know that we need to edit a record
        
    if (isset($_GET['id']))
        {
            
    // if the form's submit button is clicked, we need to process the form
            
    if (isset($_POST['submit']))
            {
                
    // make sure the 'id' in the URL is valid
                
    if (is_numeric($_POST['id']))
                {
                    
    // get variables from the URL/form
                    
    $id $_POST['id'];
                    
    $Title htmlentities($_POST['Title'], ENT_QUOTES);
                    
    $Text htmlentities($_POST['Text'], ENT_QUOTES);
                    
    $Link htmlentities($_POST['Link'], ENT_QUOTES);
                    
    $Campaign htmlentities($_POST['Campaign'], ENT_QUOTES);
                    
                    
    // check that firstname and lastname are both not empty
                    
    if ($Title == '' || $Text == '' || $Link == '' || $Campaign == '')
                    {
                        
    // if they are empty, show an error message and display the form
                        
    $error 'ERROR: Please fill in all required fields!';
                        
    renderForm($Title$Text$Link$Campaign$error$id);
                    }
                    else
                    {
                        
    // if everything is fine, update the record in the database
                        
    if ($stmt $db->prepare("UPDATE snippets SET Title = ?, Text = ?, Link = ?,     Campaign = ?
                            WHERE id=?"
    ))
                        {
                            
    $stmt->bind_param("sssii"$Title$Text$Link$Campaign$id);
                            
    $stmt->execute();
                            
    $stmt->close();
                        }
                        
    // show an error message if the query has an error
                        
    else
                        {
                            echo 
    "ERROR: could not prepare SQL statement.";
                        }
                        
                        
    // redirect the user once the form is updated
                        
    header("Location: ribbon_list.php");
                    }
                }
                
    // if the 'id' variable is not valid, show an error message
                
    else
                {
                    echo 
    "Error!";
                }
            }
            
    // if the form hasn't been submitted yet, get the info from the database and show the form
            
    else
            {
                
    // make sure the 'id' value is valid
                
    if (is_numeric($_GET['id']) && $_GET['id'] > 0)
                {
                    
    // get 'id' from URL
                    
    $id $_GET['id'];
                    
                    
    // get the recod from the database
                    
    if($stmt $db->prepare("SELECT * FROM snippets WHERE id=?"))
                    {
                        
    $stmt->bind_param("i"$id);
                        
    $stmt->execute();
                        
                        
    $stmt->bind_result($id$Title$Text$Link$Campaign);
                        
    $stmt->fetch();
                        
                        
    // show the form
                        
    renderForm($Title$Text$Link$CampaignNULL$id);
                        
                        
    $stmt->close();
                    }
                    
    // show an error if the query has an error
                    
    else
                    {
                        echo 
    "Error: could not prepare SQL statement";
                    }
                }
                
    // if the 'id' value is not valid, redirect the user back to the view.php page
                
    else
                {
                    
    header("Location: home.php");
                }
            }
        }
        
    $db->close();
    ?>

  7. #7
    Join Date
    Jan 2015
    Posts
    49
    Moved on since then. Full code below. Issues:

    1. Link and Text are echoing in eachothers text fields
    2. Form will not update

    PHP Code:
    <?php
    //error_reporting(0);
    require('../db/db_connection.php');
    require(
    '../db/security.php');

    session_start();

    ?>
    <?php
    function renderForm($Title ''$Text =''$Link ''$Campaign ''$error ''$id '')
    {
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <link rel="stylesheet" href="/styles/style.css">
    <link rel="stylesheet" href="/styles/responsive.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
    </script>
    <title>Britain's Small Wars | Admin</title>
    </head>
    <body>
    <div class="container">
            <div class="admin_header">
            <?php require('includes/header.php'); ?>
            </div>
            <div class="main_nav">
            <?php require('includes/admin_nav.php'); ?>
            </div>
            <div class="content">
            <p>&nbsp;</p>
    <div class="news">
    <div class="heading">
    <?php
    if($_SESSION['username'])
    {
        echo 
    "
        
        Hello again, "
    .$_SESSION['username'
        ;
    }
        else
            
    header ("location: login.php");
    ?>
    </div>

    </div>
    <div class="article_wrap">
    <div class="articles"><div class="articles_heading_wrap"><div class="articles_heading">
      <h4>Edit Snippet &amp; Link</h4></div></div><div class="update"> (random)</div></div>
    <!--  THIS IS THE SNIPPET TEMPLATE -->

    <?php if ($error != '') {
                        echo 
    "<div style='padding:4px; border:1px solid red; color:red'>" $error
                            
    "</div>";
                    } 
    ?>
    <?php 
    if ($id != '') { echo "Edit Record"; } ?>

    <form action="" method="POST">
        <div class="field">
        <label for="Text">Title:</label>
            <input type="text" name="Title" id="Title" value="<?php echo $Title?>">
        </div>
            <div class="field">
            <label for="Text">Snippet Text:</label>
            <input type="text" name="Text" id="Text" value="<?php echo $Text?>">
        </div>
            <div class="field">
            <label for="Link">Link to Page:</label>
            <input type="text" name="Link" id="Link" value="<?php echo $Link?>">
        </div>
            <div class="camp_select">
              <label for="Campaign">Select:</label>
                <select name="Campaign" id="Campaign" accesskey="c" tabindex="v" value="<?php echo $Campaign?>">
                  <option value=""> -- select an option -- </option>
                  <option value="1">option1</option>
                  <option value="2">option2</option>
                  <option value="3">option3</option>
                  <option value="4">option4</option>
                  <option value="5">option5</option>
                  <option value="6">option6</option>
                  <option value="7">option7</option>
                  <option value="8">option8</option>
                  <option value="9">option9</option>
                  <option value="10">option10</option>
                  <option value="11">option11</option>
                  <option value="12">option12</option>
                  <option value="13">option13</option>
                  <option value="14">option14</option>
                  <option value="15">option15</option>
                  <option value="16">option16</option>
                  <option value="17">option17</option>
                  <option value="18">option18</option>
                  <option value="19">option19</option>
                  <option value="20">option20</option>
                  <option value="21">option21</option>
                  <option value="22">option22</option>
                  <option value="23">option23</option>
                  <option value="24">option24</option>
                  <option value="25">option25</option>
                </select>
        </div>
        <input type="submit" value="Submit">
    </form>
    </div>
    <p>&nbsp;</p>
            </div>
            <div class="footer">
            <?php require('/includes/footer.php'); ?>
            </div>
      </div>
    <script>
        $('.handle').on('click', function(){
            $('main_nav ul').toggleClass('showing');
        });
    </script>
    </body>
    </html>
    <?php }



            
    /*

               EDIT RECORD

            */
        // if the 'id' variable is set in the URL, we know that we need to edit a record
        
    if (isset($_GET['id']))
        {
            
    // if the form's submit button is clicked, we need to process the form
            
    if (isset($_POST['submit']))
            {
                
    // make sure the 'id' in the URL is valid
                
    if (is_numeric($_POST['id']))
                {
                    
    // get variables from the URL/form
                    
    $id $_POST['id'];
                    
    $Title htmlentities($_POST['Title'], ENT_QUOTES);
                    
    $Text htmlentities($_POST['Text'], ENT_QUOTES);
                    
    $Link htmlentities($_POST['Link'], ENT_QUOTES);
                    
    $Campaign htmlentities($_POST['Campaign'], ENT_QUOTES);
                    
                    
    // check that firstname and lastname are both not empty
                    
    if ($Title == '' || $Text == '' || $Link == '' || $Campaign == '')
                    {
                        
    // if they are empty, show an error message and display the form
                        
    $error 'ERROR: Please fill in all required fields!';
                        
    renderForm($Title$Text$Link$Campaign$error$id);
                    }
                    else
                    {
                        
    // if everything is fine, update the record in the database
                        
    if ($stmt $db->prepare("UPDATE snippets SET Title = ?, Text = ?, Link = ?, Campaign = ?
                            WHERE id=?"
    ))
                        {
                            
    $stmt->bind_param("sssiiii"$Title$Text$Link$Campaign$Created$Order$id);
                            
    $stmt->execute();
                            
    $stmt->close();
                        }
                        
    // show an error message if the query has an error
                        
    else
                        {
                            echo 
    "ERROR: could not prepare SQL statement.";
                        }
                        
                        
    // redirect the user once the form is updated
                        
    header("Location: ribbon_list.php");
                    }
                }
                
    // if the 'id' variable is not valid, show an error message
                
    else
                {
                    echo 
    "Error!";
                }
            }
            
    // if the form hasn't been submitted yet, get the info from the database and show the form
            
    else
            {
                
    // make sure the 'id' value is valid
                
    if (is_numeric($_GET['id']) && $_GET['id'] > 0)
                {
                    
    // get 'id' from URL
                    
    $id $_GET['id'];
                    
                    
    // get the record from the database
                    
    if($stmt $db->prepare("SELECT * FROM snippets WHERE id=?"))
                    {
                        
    $stmt->bind_param("i"$id);
                        
    $stmt->execute();
                        
                        
    $stmt->bind_result($id$Title$Text$Link$Campaign$Created$Order);
                        
    $stmt->fetch();
                        
                        
    // show the form
                        
    renderForm($Title$Text$Link$CampaignNULL$id);
                        
                        
    $stmt->close();
                    }
                    
    // show an error if the query has an error
                    
    else
                    {
                        echo 
    "Error: could not prepare SQL statement";
                    }
                }
                
    // if the 'id' value is not valid, redirect the user back to the view.php page
                
    else
                {
                    
    header("Location: ribbon_list.php");
                }
            }
        }
        
    $db->close();
    ?>

  8. #8
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,968
    <select> does not have a "value" tag

    <form> still has no name, no enctype and no action, you have to tell the form where to submit to.

    <input> for the submit button has no "name" tag, should be name="Submit" with a capital S. the PHP isset($_POST['submit']) would then be isset($_POST['Submit']) I also suggest that you also test the request method is an actual POST, just because $_POST['Submit'] is present, doesn't mean that the form was a form post, use if( $_SERVER['REQUEST_METHOD']=="POST" and isset($_POST['submit'] ){

    I have no idea what you mean by this "Link and Text are echoing in eachothers text fields", it does help to have an example of what is being output and what you expect it to be, these can be two different things altogether.

    The error reported "Number of bind variables doesn't match number of fields in prepared statement" means exactly what it says, you have too many fields for the query. Have you tried to echo output to see if it is as expected? NogDog is the best person to talk you through PDO as it is his preferred method of accessing a database. if you are still stuck then mysqli_* as either procedural (easier) or OO (harder but simpler).
    Last edited by \\.\; 03-07-2015 at 06:59 PM.
    STOP using $ prefix on JavaScript variable names...
    Please remember to wrap any code you have in forum tags:-

    [CODE]...[/CODE] [HTML]...[/HTML] [PHP]...[/PHP]

    If you can't think outside the box, you will be trapped forever with no escape...

  9. #9
    Join Date
    Jan 2015
    Posts
    49
    Quote Originally Posted by \\.\ View Post
    <select> does not have a "value" tag

    <form> still has no name, no enctype and no action, you have to tell the form where to submit to.

    <input> for the submit button has no "name" tag, should be name="Submit" with a capital S. the PHP isset($_POST['submit']) would then be isset($_POST['Submit']) I also suggest that you also test the request method is an actual POST, just because $_POST['Submit'] is present, doesn't mean that the form was a form post, use if( $_SERVER['REQUEST_METHOD']=="POST" and isset($_POST['submit'] ){

    I have no idea what you mean by this "Link and Text are echoing in eachothers text fields", it does help to have an example of what is being output and what you expect it to be, these can be two different things altogether.

    The error reported "Number of bind variables doesn't match number of fields in prepared statement" means exactly what it says, you have too many fields for the query. Have you tried to echo output to see if it is as expected? NogDog is the best person to talk you through PDO as it is his preferred method of accessing a database. if you are still stuck then mysqli_* as either procedural (easier) or OO (harder but simpler).
    Thanks. It's returning an error Undefined index: id
    PHP Code:
    if (is_numeric($_POST['id']))
                { 
    In regards to Link and Text are echoing in eachothers text fields:

    PHP Code:
    <input type="text" name="Text" id="Text" value="<?php echo $Text?>">
        </div>
            <div class="field">
            <label for="Link">Link to Page:</label>
            <input type="text" name="Link" id="Link" value="<?php echo $Link?>">
    The text Text is appearing in the Link field, and the link field is appearing in the Text field. Odd.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles