how to insert and update mysql table in php

Question

i have two tables one is sale 2nd is stock

in stock table i have all item for sale

then i use this code to sale items and update stock table quantity also but its not update record and insert data in mysql i want to to update and insert data in mysql how can i do this

please help me to fix this issue thanks

Form

       <div align="center">
         <legend align="center" >Stock!</legend>
       </div>
       <div class="fieldset">
         <p>
       <label class="field" for="date">Date: </label>

           <input name="date" type="text" class="tcal" value="<?php echo date("Y-m-d");; ?>" size="30"/>
             </p>
       <p>
         <label class="field" for="username">User Name : </label>

         <input name="username" type="text"  id="username"  value="<?php echo $username; ?>" size="30"/>
       </p>
       <p>
         <label class="field" for="item">Item: </label>

         <input name="item" type="text"  value="<?php echo $item; ?>" size="30"/>
       </p>


           <p>
           <label class="field" >Quantity :</label>
           <input  name="quantity" type="text" value="<?php echo $quantity; ?>"  size="30"/>
         </p> 
           <p>
           <label class="field" >Amount :</label>
           <input  name="amount" type="text" value="<?php echo $amount; ?>"  size="30"/>
         </p> 
      </div>
     </fieldset>
       <p align="center" class="required style3">Please Fill The Complete Form </p>
       <div align="center">
         <input name="submit" type="submit" class="style1" value="Submit">

       </div>
     </form> 

 <?php 
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?>

Php Code

  <?php
/* 
 NEW.PHP
 Allows user to create a new entry in the database
*/

 // creates the new record form
 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($id ,$date ,$username,$item,$quantity,$amount, $error)

 {
 ?>

 <?php 
     }


     $item = $_GET['item']

     // connect to the database
     include('connect-db.php');

     // check if the form has been submitted. If it has, start to process the form and save it to the database
     if (isset($_POST['submit']))
     { 
     // get form data, making sure it is valid
     $id = mysql_real_escape_string(htmlspecialchars($_POST['id']));
     $date = mysql_real_escape_string(htmlspecialchars($_POST['date']));
     $username = mysql_real_escape_string(htmlspecialchars($_POST['username']));
     $item = mysql_real_escape_string(htmlspecialchars($_POST['item']));
     $quantity = mysql_real_escape_string(htmlspecialchars($_POST['quantity']));
     $amount = mysql_real_escape_string(htmlspecialchars($_POST['amount']));



     // check to make sure both fields are entered
     if ($date == '' || $quantity == '')
     {
     // generate error message
     $error = 'ERROR: Please fill in all required fields!';

     // if either field is blank, display the form again
     renderForm($id ,$date ,$username,$item,$quantity,$amount,  $error);

     }
     else
     {
     // save the data to the database
      mysql_query("INSERT INTO  sale SET date='$date', username='$username',item='$item',quantity='$quantity',amount='$amount'")


    $result = mysql_query("UPDATE stock SET quantity='-$quantity' WHERE item='$item'") 




     or die(mysql_error()); 
     echo "<center>Stock Enter Complete!</center>";
     // once saved, redirect back to the view page

     }
     }
     }
     else
     // if the form hasn't been submitted, display the form
     {
     renderForm('','','','','','','','');
     }

             ?>

What issue would that be? Could you explain what the problem actually is? — andrewsi, Oct 23 '13 at 13:48
also, [avoid using mysql_ functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — aleation, Oct 23 '13 at 13:50
my code is not working properly its not update and insert record this is problem — user2903400, Oct 23 '13 at 13:57
@user2903400 I have broken down your query, and put it in a readable format at the bottom of my answer, have a look. Try it out and let me know — Daryl Gill, Oct 23 '13 at 14:03

score 1 · Answer 1 · edited May 23 '17 at 12:21

Correct syntax for inserting:

INSERT INTO table (col1,col2,col3..et) VALUES ('ValueFor1', 'valuefor2','valuefor3')

Correct syntax for update:

UPDATE table SET col='value' WHERE col='value'

Though, you should not be using mysql_* functions for new code -- switch to an alternative such as PDO/MySQLi.

Reasons why, listed here

Furthermore, you have a lot of errors generated, one of which @idmosel has mentioned, but one I've spotted is:

<input name="item" type="text"  value="<?php echo $item; ?>" size="30"/>

You should wrap your echo within an isset() statement to stop warnings being generated for undefined variables:

value="<?php if(isset($item)){ echo $item; }?>"

Let's have a look at your insert query:

mysql_query("INSERT INTO  sale SET date='$date', username='$username',item='$item',quantity='$quantity',amount='$amount'")

Questions that spring to mind:

Have you correctly called the depreciated mysql_connnect()
Do you realize your missing a closing semi-colon (;) at the end of this statement

Let's restructure it:

$Insert = mysql_query("INSERT INTO sale (`date`,`username`,`item`,`quantity`,`amount`)
values ($date,$username,$item,$quantity,$amount)");

if (!$Insert) {
    echo "Problem With MySQL_Query, there was a false return. SQL didn't finish";
    exit;
}

@user2903400 Follow my "correct" syntax for update, and use the "restructured" insert query as an example, If I give everything to you on a plate, you will not learn — Daryl Gill, Oct 23 '13 at 14:07

score 0 · Answer 2 · answered Oct 23 '13 at 13:53

0

You seem to have a lot wrong with your syntax i.e.

$item = $_GET['item']

Should have semicolon after. Please check and correct your syntax and get back to us with specific questions.

answered Oct 23 '13 at 13:53

ldmo

369
1
2
9

i change but again same problm – user2903400 Oct 23 '13 at 14:04

iskandarm · Accepted Answer · 2013-10-23T14:32:11.007

<?php
/* 
 NEW.PHP
 Allows user to create a new entry in the database
*/

 // creates the new record form
 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($id ,$date ,$username,$item,$quantity,$amount, $error)
 {

 }

     $item = $_GET['item'];

     // connect to the database
     include 'connect-db.php';

     // check if the form has been submitted. If it has, start to process the form and save it to the database
     if (isset($_POST['submit']))
     {
     // get form data, making sure it is valid
     $id = mysql_real_escape_string(htmlspecialchars($_POST['id']));
     $date = mysql_real_escape_string(htmlspecialchars($_POST['date']));
     $username = mysql_real_escape_string(htmlspecialchars($_POST['username']));
     $item = mysql_real_escape_string(htmlspecialchars($_POST['item']));
     $quantity = mysql_real_escape_string(htmlspecialchars($_POST['quantity']));
     $amount = mysql_real_escape_string(htmlspecialchars($_POST['amount']));



         // check to make sure both fields are entered
         if ($date == '' || $quantity == '')
         {
         // generate error message
         $error = 'ERROR: Please fill in all required fields!';

         // if either field is blank, display the form again
         renderForm($id ,$date ,$username,$item,$quantity,$amount,  $error);

         }
         else
         {
         // save the data to the database
          mysql_query("INSERT INTO  sale SET date='$date', username='$username',item='$item',quantity='$quantity',amount='$amount'");


        $result = mysql_query("UPDATE stock SET SET quantity=quantity - $quantity WHERE item='$item'")
            or die(mysql_error()); 
         echo "<center>Stock Enter Complete!</center>";
         // once saved, redirect back to the view page

         }
     }else
     // if the form hasn't been submitted, display the form
     {
     renderForm('','','','','','','','');
     }
?>

syntax error, unexpected $end it is showing this error again in last line and last line is emty line — user2903400, Oct 23 '13 at 14:00
now its working thanks daryl girl and iskandarm but there is a problem — user2903400, Oct 23 '13 at 14:23
before i check my stock value its have 22 quantity when i enter a quantity 2 using this code after that i check to my quantity then it is showing -2 can u tell me how can i minus quantity to actual quanity — user2903400, Oct 23 '13 at 14:25

score 0 · Answer 4 · answered Oct 23 '13 at 14:05

0

Update code:

Remove:

mysql_query("INSERT INTO  sale SET date='$date', username='$username',item='$item',quantity='$quantity',amount='$amount'")
$result = mysql_query("UPDATE stock SET quantity='-$quantity' WHERE item='$item'")

Add:

mysql_query("INSERT INTO  sale (date,username,item,quantity,amount) values ('".$date."', '".$username."', '".$item."', '".$quantity."', '".$amount."'");
$result = mysql_query("UPDATE stock SET quantity= quantity - ".$quantity." WHERE item='".$item."'");

answered Oct 23 '13 at 14:05

Rajiv Ranjan

1,869
1
11
20

Parse error: syntax error, unexpected T_LOGICAL_OR in this line or die(mysql_error()); – user2903400 Oct 23 '13 at 14:08
can you share complete error? – Rajiv Ranjan Oct 23 '13 at 14:10
@user2903400 this has happened because by assumption, you have copied and pasted this entire code in replacement to your own.. Hence you have closed off the query `; or die(""); << – Daryl Gill Oct 23 '13 at 14:14
Parse error: syntax error, unexpected T_LOGICAL_OR in stocksale.php on line 271 – user2903400 Oct 23 '13 at 14:14
can you share some line of code from line number 260 to line number 280 of stocksale.php – Rajiv Ranjan Oct 23 '13 at 14:16

how to insert and update mysql table in php

4 Answers4