SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: UPDATE issue

  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE issue

    Hi all

    I receive variables in the url from a table page to a form for any specific table row. I then catch the passed ID variable in...

    Code PHP:
    $stock_display_id

    I need to use this variable in an UPDATE query...

    Code PHP:
    if(isset($_POST['update'])
        {
          $updatesupplier                = htmlentities(strip_tags(mysql_real_escape_string($_POST['suppliername'])));
          $updatecategory              = htmlentities(strip_tags(mysql_real_escape_string($_POST['stockcategory'])));
          $updatedescription           = htmlentities(strip_tags(mysql_real_escape_string($_POST['description'])));
          $updateminreorderquantity = htmlentities(strip_tags(mysql_real_escape_string($_POST['min_reorder_quantity'])));
          $updateaddquantity          = htmlentities(strip_tags(mysql_real_escape_string($_POST['add_quantity'])));
     
          /* validate field lengths */
     
          if(strlen($updatedescription)>100 || strlen($updateminreorderquantity)>2 || strlen($updateaddquantity)>2)
          {
            echo 'Please adhere to maximum length of fields';
          }
          else
          {
             /* create stock update query  */
            $update_query = "UPDATE `stock`
                                    SET       `supplier_id`                = '$updatesupplier',
                                                `stock_category_no`    = '$updatecategory',
                                                `description`               = '$updatedescription',
                                                `unit_price`                = '$updateprice',
                                                `min_reorder_quantity` = '$updateminreorderquantity',
                                                `add_quantity`            = '$updateaddquantity'
                                    WHERE   `stock_inventory_no`   = '[B]$stock_display_id[/B]'";
     
            $update_result = mysql_query($update_query);
          }
        }
       }

    With the above set up i receive undefined variable ($stock_display_id) and i therefore cannot update the specific row.

    How do i incorporate
    Code PHP:
    if(isset($_GET['id']){ $stock_display_id = $_GET['id'];}
    in the above isset $_POST update query so i can define the variable and complete and update.

    Many thanks in advance

  2. #2
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Looks like you're incorporated bbcode my mistake:

    [ B ]$stock_display_id[ /B ]

    Remove [ B ][ /B ] and try again.

  3. #3
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    If you are using $_GET to get the right details from the database to populate the form, create a hidden field with the ID in it.
    Then it gets sent through with all the other data.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  4. #4
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tom8 View Post
    Looks like you're incorporated bbcode my mistake:

    [ B ]$stock_display_id[ /B ]

    Remove [ B ][ /B ] and try again.
    Please disregard my comment. I think you were just trying to highlight that.
    PHP Code:
    `stock_inventory_no`   = '[B]$stock_display_id[/B]'

  5. #5
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tom8 View Post
    Looks like you're incorporated bbcode my mistake:

    [ B ]$stock_display_id[ /B ]

    Remove [ B ][ /B ] and try again.
    Hi tom8

    I put those in to try and emphasis where i am using the variable in the UPDATE query, so still the same issue. Any ideas?
    Thanks

  6. #6
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you add the id field into the form as spikeZ suggested then you could just add that into your script:

    PHP Code:
    <?php
     
    if(isset($_POST['update'])
    {
        
    $stock_display_id $_POST['id'];
        
    $updatesupplier           htmlentities(strip_tags(mysql_real_escape_string($_POST['suppliername'])));
        
    $updatecategory           htmlentities(strip_tags(mysql_real_escape_string($_POST['stockcategory'])));
        
    $updatedescription        htmlentities(strip_tags(mysql_real_escape_string($_POST['description'])));
        
    $updateminreorderquantity htmlentities(strip_tags(mysql_real_escape_string($_POST['min_reorder_quantity'])));
        
    $updateaddquantity        htmlentities(strip_tags(mysql_real_escape_string($_POST['add_quantity'])));
     
          
    /* validate field lengths */
     
        
    if(strlen($updatedescription)>100 || strlen($updateminreorderquantity)>|| strlen($updateaddquantity)>2)
        {
            echo 
    'Please adhere to maximum length of fields';
        }
            else
        {
             
    /* create stock update query  */
            
    $update_query "UPDATE `stock`
                    SET `supplier_id`          = '
    $updatesupplier',
                                        `stock_category_no`    = '
    $updatecategory',
                                        `description`          = '
    $updatedescription',
                                        `unit_price`           = '
    $updateprice',
                                        `min_reorder_quantity` = '
    $updateminreorderquantity',
                                        `add_quantity`         = '
    $updateaddquantity'
                                    WHERE   `stock_inventory_no`   = '
    $stock_display_id'";
     
            
    $update_result mysql_query($update_query);
        }
    }
     
    ?>

  7. #7
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    If you are using $_GET to get the right details from the database to populate the form, create a hidden field with the ID in it.
    Then it gets sent through with all the other data.
    Sorry to ask this. Not done that before. What would that look like in the below page?

    Code PHP:
    <?php
      /* connect to database and table */
      require 'connect.inc.php';
     
      /***
       ** MATCH CARRIED VARIABLE TO REMAINING STOCK DATA FOR CURRENT FORM FIELDS
       ***/
     
      /* check if 'id' carried from previous page sucessfully  */
      if(isset($_GET['id']))
      {
        /* store 'id' in a variable */
        $stock_display_id = $_GET['id'];
     
        /* create a query to extract data from stock data using stock display row supplied value */
        $current_stockdisplayrow_query = "SELECT `supplier_name` ,
                                                 `category` ,
                                                 `description` ,
                                                 `curr_quantity` ,
                                                 `min_reorder_quantity`
                                          FROM   `stock`
                                          WHERE  `stock_inventory_no` = '$stock_display_id'";
     
        /* check query is valid, if result, loop to obtain data and store in variables */
        if($current_stockdisplayrow_result = mysql_query($current_stockdisplayrow_query))
        {
          $current_stockdisplayrow_num_rows = mysql_num_rows($current_stockdisplayrow_result);
     
          /* run loop to collect remaining data from stock table */
          if($current_stockdisplayrow_num_rows !==0)
          {
            while($current_stockdisplayrow_rows = mysql_fetch_assoc($current_stockdisplayrow_result))
            {
              /* store collected data in variables for form values */
              $currentsupplier           = $current_stockdisplayrow_rows['supplier_name'];
              $currentcategory           = $current_stockdisplayrow_rows['category'];
              $currentdescription        = $current_stockdisplayrow_rows['description'];
              $currentcurrquantity       = $current_stockdisplayrow_rows['curr_quantity'];
              $currentminreorderquantity = $current_stockdisplayrow_rows['min_reorder_quantity'];
            }
          }
          else
          {
            echo mysql_error();
          }
        }
        else
        {
          echo mysql_error();
        }
      }
      else
      {
        echo mysql_error();
      }
      ?>
     
      <?php
      /***
       ** CREATE QUERY TO EXTRACT AVAILABLE SUPPLIERS
       ** STORE IN VARIABLES FOR USE IN SELECT FIELDS FIELDS
       ***/
     
      /* drop down query for suppliers */
      $supplier_query            = "SELECT `supplier_id`,
                                           `supplier_name`
                                    FROM   `suppliers`";
      $supplier_result           = mysql_query($supplier_query);
      $supplier_num_rows         = mysql_num_rows($supplier_result);
      ?>
     
      <?php
      /***
       ** CREATE QUERY TO EXTRACT AVAILABLE SUPPLIERS
       ** STORE IN VARIABLES FOR USE IN SELECT FIELDS
       ***/
     
      /* drop down query for stock category */
      $stockcategory_query       = "SELECT `stock_category_no`,
                                           `category`
                                    FROM   `stock_category`";
      $stockcategory_result      = mysql_query($stockcategory_query);
      $category_num_rows         = mysql_num_rows($stockcategory_result);
      ?>
     
      <?php
      /***
       ** UPDATE RECORD FROM STOCKDISPLAY.PHP
       ***/
     
      if(isset($_GET['id']))
      {
        $stock_display_id = $_GET['id'];
        //if(isset($_POST['suppliername']) && isset($_POST['stockcategory']) && isset($_POST['description']) && isset($_POST['unit_price']) && isset($_POST['min_reorder_quantity']) && isset($_POST['add_quantity']))
        if(isset($_POST['update'])/*  && isset($_POST['id']) */)
        {
          $updatesupplier           = htmlentities(strip_tags(mysql_real_escape_string($_POST['suppliername'])));
          $updatecategory           = htmlentities(strip_tags(mysql_real_escape_string($_POST['stockcategory'])));
          $updatedescription        = htmlentities(strip_tags(mysql_real_escape_string($_POST['description'])));
          $updateminreorderquantity = htmlentities(strip_tags(mysql_real_escape_string($_POST['min_reorder_quantity'])));
          $updateaddquantity        = htmlentities(strip_tags(mysql_real_escape_string($_POST['add_quantity'])));
     
          /* validate field lengths */
     
          if(strlen($updatedescription)>100 || strlen($updateminreorderquantity)>2 || strlen($updateaddquantity)>2)
          {
            echo 'Please adhere to maximum length of fields';
          }
          else
          {
            /* calculate new quantity */
            /* $new_quantity = $currquantity + $addquantity; */
     
            /* create stock update query  */
            /* $update_query = "UPDATE `stock`
                             SET    `supplier_id`          = '$updatesupplier',
                                    `stock_category_no`    = '$updatecategory',
                                    `description`          = '$updatedescription',
                                    `unit_price`           = '$updateprice',
                                    `min_reorder_quantity` = '$updateminreorderquantity',
                                    `add_quantity`         = '$updateaddquantity'
                             WHERE  `stock_inventory_no`   = '$stock_display_id'"; */
     
            $update_query = "UPDATE stock SET description='$updatedescription', min_reorder_quantity='$updateminreorderquantity' WHERE stock_inventory_no='6'";
     
            $update_result = mysql_query($update_query);
            if (!$update_result) die(mysql_error());
          }
        }
      }
      ?>
     
      <form method="POST" action="stockupdate.php">
      <fieldset>
      <legend>Stock Update Form</legend>
      <ol>
      <li>
      <label class="mandatory" title="If required">Only update required fields</label>
      </li>
      <li>
      <label for="current_supplier" title="Current_supplier">Current supplier: </label>
      <input id="current_supplier" name="current_supplier" type="text" size="40" disabled="disabled" value="<?php echo $currentsupplier; ?>" />
      </li>
      <li>
      <label for="suppliername" title="Please change supplier if required">Supplier: </label>
      <select name="suppliername">
      <?php
           /* PHP SELECT MENU
                 1 - create "Please select" default category
                 2 - create for loop to cycle through suppliers
                 3 - assign variables for suppliers...
                 4 - ...display suppliers
           */
           echo '<option value="">Please Select...</option>';
           for($i=0; $i < $supplier_num_rows; $i++)
           {
           $supplier_id   = mysql_result($supplier_result,$i,0);
           $supplier_name = mysql_result($supplier_result,$i,1);
           echo'<option value="'.$supplier_id.'">'.$supplier_name.'</option>';
           }
      ?>
      </select>
      </li>
      <li>
      <label for="current_category" title="Current_category">Current category: </label>
      <input id="current_category" name="current_category" type="text" size="40" disabled="disabled" value="<?php echo $currentcategory; ?>" />
      </li>
      <li>
      <label for="stockcategory" title="Please change category if required">Category: </label>
      <select name="stockcategory">
      <?php
           /* PHP SELECT MENU
                  1 - create "Please select" default category
                  2 - create for loop to cycle through categories
                  3 - assign variables for categories...
                  4 - ...display categories
           */
           echo '<option value="">Please Select...</option>';
           for($i=0; $i < $category_num_rows; $i++)
           {
           $stock_category_no = mysql_result($stockcategory_result,$i,0);
           $category          = mysql_result($stockcategory_result,$i,1);
           echo'<option value="'.$stock_category_no.'">'.$category.'</option>';
           }
      ?>
      </select>
      </li>
      <li>
      <label for="current_description" title="Current description">Current Description: </label>
      <input id="current_description" name="current_description" type="text" size="40" disabled="disabled" value="<?php echo $currentdescription; ?>" maxlength="100" />
      </li>
      <li>
      <label for="description" title="Please change the product description if required">Description: </label>
      <textarea id="description" name="description" maxlength="100" rows="5" cols="29" /></textarea>
      </li>
      <li>
      <label for="curr_quantity" title="Current Quantity">Current Qty: </label>
      <input id="curr_quantity" name="curr_quantity" type="text" size="40" disabled="disabled" value="<?php echo $currentcurrquantity; ?>" maxlength="2" />
      </li>
      <li>
      <label for="current_min_reorder_quantity" title="Current min reorder quantity"> Current Min Reorder Qty: </label>
      <input id="current_min_reorder_quantity" name="current_min_reorder_quantity" type="text" size="40" disabled="disabled" value="<?php echo $currentminreorderquantity; ?>" maxlength="2" />
      </li>
      <li>
      <label for="min_reorder_quantity" title="For use by the administration office (Please enter or change the minimum reorder number)">Min Reorder Quantity: </label>
      <input id="min_reorder_quantity" name="min_reorder_quantity" type="text" maxlength="2" size="40" />
      </li>
      <li>
      <label for="add_quantity" title="Please add additional stock number here">Additional Qty: </label>
      <input id="add_quantity" name="add_quantity" type="text" maxlength="2" size="40" />
      </li>
      </ol>
      </fieldset>
      <fieldset>
      <input
      </fieldset>
      <fieldset class="submit">
      <input class="submit" type="submit" name="update" value="Update" title="When you have finished altering stock data...Click me!" />
      </fieldset>
      </form>

    Many thanks

  8. #8
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Please test the code below and let us know what you got:

    PHP Code:
    <?php
       
    /* connect to database and table */
    require 'connect.inc.php';
     
      
    /***
       ** MATCH CARRIED VARIABLE TO REMAINING STOCK DATA FOR CURRENT FORM FIELDS
       ***/
     
      /* check if 'id' carried from previous page sucessfully  */
       
    if(isset($_GET['id']))
       {
        
    /* store 'id' in a variable */
           
    $stock_display_id $_GET['id'];
     
        
    /* create a query to extract data from stock data using stock display row supplied value */
           
    $current_stockdisplayrow_query "SELECT `supplier_name` ,".
                            
    "`category` ,".
                            
    "`description` ,".
                            
    "`curr_quantity` ,".
                            
    " `min_reorder_quantity`".
                            
    " FROM   `stock`".
                            
    " WHERE  `stock_inventory_no` = '$stock_display_id'";
     
        
    /* check query is valid, if result, loop to obtain data and store in variables */
           
    if($current_stockdisplayrow_result mysql_query($current_stockdisplayrow_query))
           {
               
    $current_stockdisplayrow_num_rows mysql_num_rows($current_stockdisplayrow_result);
     
          
    /* run loop to collect remaining data from stock table */
               
    if($current_stockdisplayrow_num_rows !==0)
               {
                   while(
    $current_stockdisplayrow_rows mysql_fetch_assoc($current_stockdisplayrow_result))
                   {
              
    /* store collected data in variables for form values */
                       
    $currentsupplier           $current_stockdisplayrow_rows['supplier_name'];
                       
    $currentcategory           $current_stockdisplayrow_rows['category'];
                       
    $currentdescription        $current_stockdisplayrow_rows['description'];
                       
    $currentcurrquantity       $current_stockdisplayrow_rows['curr_quantity'];
                       
    $currentminreorderquantity $current_stockdisplayrow_rows['min_reorder_quantity'];
                   }
               }
               else
               {
                   echo 
    mysql_error();
               }
           }
           else
           {
               echo 
    mysql_error();
           }
       }
       else
       {

      
    /***
       ** CREATE QUERY TO EXTRACT AVAILABLE SUPPLIERS
       ** STORE IN VARIABLES FOR USE IN SELECT FIELDS FIELDS
       ***/
     
      /* drop down query for suppliers */
           
    $supplier_query "SELECT `supplier_id`,".
                     
    "`supplier_name`".
                     
    "FROM   `suppliers`";
           
    $supplier_result   mysql_query($supplier_query);
           
    $supplier_num_rows mysql_num_rows($supplier_result);

      
    /***
       ** CREATE QUERY TO EXTRACT AVAILABLE SUPPLIERS
       ** STORE IN VARIABLES FOR USE IN SELECT FIELDS
       ***/
     
      /* drop down query for stock category */
           
    $stockcategory_query "SELECT `stock_category_no`,".
                      
    "`category`".
                      
    "FROM   `stock_category`";
           
    $stockcategory_result mysql_query($stockcategory_query);
           
    $category_num_rows    mysql_num_rows($stockcategory_result);

      
    /***
       ** UPDATE RECORD FROM STOCKDISPLAY.PHP
       ***/
       
    }
      if(isset(
    $_POST['id']))
      {
          if(isset(
    $_POST['update'])/*  && isset($_POST['id']) */)
          {
              
    $stock_inventory_no $_POST['id'];
              
    $updatesupplier           htmlentities(strip_tags(mysql_real_escape_string($_POST['suppliername'])));
              
    $updatecategory           htmlentities(strip_tags(mysql_real_escape_string($_POST['stockcategory'])));
              
    $updatedescription        htmlentities(strip_tags(mysql_real_escape_string($_POST['description'])));
              
    $updateminreorderquantity htmlentities(strip_tags(mysql_real_escape_string($_POST['min_reorder_quantity'])));
              
    $updateaddquantity        htmlentities(strip_tags(mysql_real_escape_string($_POST['add_quantity'])));
     
          
    /* validate field lengths */
     
              
    if(strlen($updatedescription)>100 || strlen($updateminreorderquantity)>|| strlen($updateaddquantity)>2)
              {
                  echo 
    'Please adhere to maximum length of fields';
              }
              else
              {
            
    /* calculate new quantity */
            /* $new_quantity = $currquantity + $addquantity; */
     
            /* create stock update query  */
            /* $update_query = "UPDATE `stock`
                             SET    `supplier_id`          = '$updatesupplier',
                                    `stock_category_no`    = '$updatecategory',
                                    `description`          = '$updatedescription',
                                    `unit_price`           = '$updateprice',
                                    `min_reorder_quantity` = '$updateminreorderquantity',
                                    `add_quantity`         = '$updateaddquantity'
                             WHERE  `stock_inventory_no`   = '$stock_display_id'"; */
     
                  
    $update_query "UPDATE stock SET description='$updatedescription', min_reorder_quantity='$updateminreorderquantity' WHERE stock_inventory_no='$stock_display_id'";
     
                  
    $update_result mysql_query($update_query);
                  if (!
    $update_result) die(mysql_error());
              }
          }
      }
    ?>
     
      <form method="POST" action="stockupdate.php">
         <input type="hidden" name="id" value="<?php echo $stock_display_id?>" />
      <fieldset>
      <legend>Stock Update Form</legend>
      <ol>
      <li>
      <label class="mandatory" title="If required">Only update required fields</label>
      </li>
      <li>
      <label for="current_supplier" title="Current_supplier">Current supplier: </label>
      <input id="current_supplier" name="current_supplier" type="text" size="40" disabled="disabled" value="<?php echo $currentsupplier?>" />
      </li>
      <li>
      <label for="suppliername" title="Please change supplier if required">Supplier: </label>
      <select name="suppliername">
      <?php
         
    /* PHP SELECT MENU
                 1 - create "Please select" default category
                 2 - create for loop to cycle through suppliers
                 3 - assign variables for suppliers...
                 4 - ...display suppliers
           */
       
    echo '<option value="">Please Select...</option>';
       for(
    $i=0$i $supplier_num_rows$i++)
       {
           
    $supplier_id   mysql_result($supplier_result,$i,0);
           
    $supplier_name mysql_result($supplier_result,$i,1);
           echo
    '<option value="'.$supplier_id.'">'.$supplier_name.'</option>';
       }
      
    ?>
      </select>
      </li>
      <li>
      <label for="current_category" title="Current_category">Current category: </label>
      <input id="current_category" name="current_category" type="text" size="40" disabled="disabled" value="<?php echo $currentcategory?>" />
      </li>
      <li>
      <label for="stockcategory" title="Please change category if required">Category: </label>
      <select name="stockcategory">
      <?php
           
    /* PHP SELECT MENU
                  1 - create "Please select" default category
                  2 - create for loop to cycle through categories
                  3 - assign variables for categories...
                  4 - ...display categories
           */
       
    echo '<option value="">Please Select...</option>';
       for(
    $i=0$i $category_num_rows$i++)
       {
           
    $stock_category_no mysql_result($stockcategory_result,$i,0);
           
    $category          mysql_result($stockcategory_result,$i,1);
           echo
    '<option value="'.$stock_category_no.'">'.$category.'</option>';
       }
      
    ?>
      </select>
      </li>
      <li>
      <label for="current_description" title="Current description">Current Description: </label>
      <input id="current_description" name="current_description" type="text" size="40" disabled="disabled" value="<?php echo $currentdescription?>" maxlength="100" />
      </li>
      <li>
      <label for="description" title="Please change the product description if required">Description: </label>
      <textarea id="description" name="description" maxlength="100" rows="5" cols="29" /></textarea>
      </li>
      <li>
      <label for="curr_quantity" title="Current Quantity">Current Qty: </label>
      <input id="curr_quantity" name="curr_quantity" type="text" size="40" disabled="disabled" value="<?php echo $currentcurrquantity?>" maxlength="2" />
      </li>
      <li>
      <label for="current_min_reorder_quantity" title="Current min reorder quantity"> Current Min Reorder Qty: </label>
      <input id="current_min_reorder_quantity" name="current_min_reorder_quantity" type="text" size="40" disabled="disabled" value="<?php echo $currentminreorderquantity?>" maxlength="2" />
      </li>
      <li>
      <label for="min_reorder_quantity" title="For use by the administration office (Please enter or change the minimum reorder number)">Min Reorder Quantity: </label>
      <input id="min_reorder_quantity" name="min_reorder_quantity" type="text" maxlength="2" size="40" />
      </li>
      <li>
      <label for="add_quantity" title="Please add additional stock number here">Additional Qty: </label>
      <input id="add_quantity" name="add_quantity" type="text" maxlength="2" size="40" />
      </li>
      </ol>
      </fieldset>
      <fieldset>
      <input
      </fieldset>
      <fieldset class="submit">
      <input class="submit" type="submit" name="update" value="Update" title="When you have finished altering stock data...Click me!" />
      </fieldset>
      </form>


Tags for this Thread

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
  •