4 Replies - 349 Views - Last Post: 06 February 2013 - 06:54 PM Rate Topic: -----

#1 ygboxx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-February 13

how to update only one field in a table with a new value

Posted 06 February 2013 - 01:58 AM

hi. i have a problem with my coding. actually this coding works, the problem is i can't automatically update the new value to another table.
this is my coding:

<?php



session_start();

$user="root"; //user for server
$host="localhost"; //host for server
$username=""; //user for server
$password=""; //password for server
$database="inventory"; //database for server
$table="issue"; //table for server

mysql_connect($host, $user, $password) or die ("can't connect");
mysql_select_db($database) or die (mysql_error());


if(isset($_POST['submitted'])){

$rp=trim($_POST['NoRp']);

$pn=trim($_POST['product']);
$pk=trim($_POST['prod_taken']);
$ib=trim($_POST['issueby']);
$un=trim($_POST['unit']);
$t=trim($_POST['terminal']);

	
	
$errors=array(); 



if (empty($_POST['NoRp'])){
$errors[] ='Please enter the Rp No';
} else {
$rp=trim($_POST['NoRp']);
}



if (empty($errors)) {

	

$sql= "INSERT INTO issue ( NoRp, product, prod_taken, issueby,unit,terminal,date) VALUES
('$rp','$pn','$pk','$ib','$un','$t', NOW() )";

$result=@mysql_query($sql); 
if($result) {
$sql1= mysql_query("SELECT inStock from product where prodName='$pn'");
$sql2= mysql_query("SELECT prod_taken from issue where product = '$pn' and prod_taken='$pk'"); 
$result1=mysql_result($sql1,0); 
$result2=mysql_result($sql2,0,0); 

echo $result1;
echo "---";
echo $result2;

function calcStock ($a,$B)/>{

return($a - $B)/>;
}
//$calc1= mysql_result($sql1,$sql2);
echo "---";
echo calcStock($result1,$result2);
$sql3=mysql_query("UPDATE product SET inStock=calcStock($result1&&$result2) where prodName='$pn'");




echo'<center><h1 id="mainhead">Thank you!<h1>
</center>';



echo "<center><a href=homeAdmin.php>BACK</a></center>";


exit();
} else {
echo '<h1 id="mainhead">System Error</h1>
<p class="error">  System error. We apologize for any inconvenience.</p>';




exit();
}

mysql_close();
}else {
echo '<h1 id="mainhead"> Error!</h1>
<p class="error"> The following error(s) occurred:<br/>';
foreach($errors as $msg) {
echo "- $msg<br/>\n";
}
echo '</p><p> Please try again.</p><p><br/></p>';
}
}

?>




<form action="stockform.php" method="post">

<table cellspacing=1 border=0 width=50%  align="center"> 
<tr>
<td colspan="5" style="background: #935700; color:#FFFFFF; font-size:20px"><center>ISSUE FORM</center></td></tr>


<tr><td>&nbsp;</td><td>&nbsp;</td></tr>
<tr><td>&nbsp;</td><td>&nbsp;</td></tr>
<tr>
<td align="right" valign="middle" width="170"><font color="#000000">Rp No : </font></td>
<td valign="middle"><font color="#000000"><input type="text" name="NoRp" size="25" maxlength="200" value="<?php if (isset($_POST['NoRp'])) echo $_POST['NoRp']; ?>"></font></td> 
</tr>

<tr>
<?php
$user="root"; //user for server
$host="localhost"; //host for server
$username=""; //user for server
$password=""; //password for server
$database="inventory"; //database for server
$table="product"; //table for server

mysql_connect($host, $user, $password) or die ("can't connect");
mysql_select_db($database) or die (mysql_error());

$query = "SELECT prodName FROM product" ;
$result = mysql_query($query);
echo' <td align="right" valign="middle" width="140">Product: </td><td><select name="product">';
while($row = mysql_fetch_assoc( $result )) { 
        echo '<option value="'.$row['prodName'].'">' . $row['prodName'] . '</option>';   
}
echo '</select>';
?></td> 

</tr>
<tr>
		<td align="right" valign="middle" width="170"><font color="#000000">Total taken : </font></td>
<td valign="middle"><font color="#000000"><input type="text" name="prod_taken" size="25" maxlength="200" value="<?php if (isset($_POST['prod_taken'])) echo $_POST['prod_taken']; ?>"></font></td> 
</tr>

<td align="right" valign="middle" width="170"><font color="#000000">Issue By : </font></td>
<td valign="middle"><font color="#000000"><input type="text" name="issueby" size="25" maxlength="200" value="<?php if (isset($_POST['issueby'])) echo $_POST['issueby']; ?>"></font></td> 
</tr>

		
       <?php
$user="root"; //user for server
$host="localhost"; //host for server
$username=""; //user for server
$password=""; //password for server
$database="inventory"; //database for server
$table="unit"; //table for server

mysql_connect($host, $user, $password) or die ("can't connect");
mysql_select_db($database) or die (mysql_error());

$query = "SELECT unitName FROM unit" ;
$result = mysql_query($query);
echo' <td align="right" valign="middle" width="140">Unit: </td><td><select name="unit">';
while($row = mysql_fetch_assoc( $result )) { 
        echo '<option value="'.$row['unitName'].'">' . $row['unitName'] . '</option>';   
}
echo '</select>';
?>

</td> 

 
<tr>
<?php
$user="root"; //user for server
$host="localhost"; //host for server
$username=""; //user for server
$password=""; //password for server
$database="inventory"; //database for server
$table="terminal"; //table for server

mysql_connect($host, $user, $password) or die ("can't connect");
mysql_select_db($database) or die (mysql_error());

$query = "SELECT terName FROM terminal" ;
$result = mysql_query($query);
echo' <td align="right" valign="middle" width="140">Terminal: </td><td><select name="terminal">';
while($row = mysql_fetch_assoc( $result )) { 
        echo '<option value="'.$row['terName'].'">' . $row['terName'] . '</option>';   
}
echo '</select>';
?></td> 

</tr>
	
	
<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr>
</table>
<br>
<p align="center"><input type="submit" name="submitted" value="SAVE"/>
<input type="reset" name="reset" value="CLEAR"/></p>
<input type="hidden" name="submitted" value="TRUE" />

</form>



			</table>
		</td>
      </tr>
    </table>
	</td>
  </tr>

	
  </tr>


</body>
</html>


hope it can be solved. tq.

Is This A Good Question/Topic? 0
  • +

Replies To: how to update only one field in a table with a new value

#2 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3594
  • View blog
  • Posts: 12,365
  • Joined: 12-December 12

Re: how to update only one field in a table with a new value

Posted 06 February 2013 - 04:17 AM

Whilst date is not, technically, a reserved word in MySql I would not, personally, use it as a field-name. Even so, I would still quote it with back-ticks:

$sql= "INSERT INTO issue ( NoRp, product, prod_taken, issueby,unit,terminal,`date`) VALUES


There are a number of other things I could comment about your code, but you haven't asked a specific question. You say it's working so what is your question?
Was This Post Helpful? 0
  • +
  • -

#3 laytonsdad  Icon User is offline

  • Cheese and Sprinkles
  • member icon

Reputation: 442
  • View blog
  • Posts: 1,883
  • Joined: 30-April 10

Re: how to update only one field in a table with a new value

Posted 06 February 2013 - 10:39 AM

Move away from mysql_* as it is depreciated, look into mysqli or PDO.
Was This Post Helpful? 0
  • +
  • -

#4 ygboxx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-February 13

Re: how to update only one field in a table with a new value

Posted 06 February 2013 - 05:44 PM

View Postandrewsw, on 06 February 2013 - 04:17 AM, said:

Whilst date is not, technically, a reserved word in MySql I would not, personally, use it as a field-name. Even so, I would still quote it with back-ticks:

$sql= "INSERT INTO issue ( NoRp, product, prod_taken, issueby,unit,terminal,`date`) VALUES


There are a number of other things I could comment about your code, but you haven't asked a specific question. You say it's working so what is your question?


thanks for reply.
 

$result=@mysql_query($sql); 
if($result) {
$sql1= mysql_query("SELECT inStock from product where prodName='$pn'");
$sql2= mysql_query("SELECT prod_taken from issue where product = '$pn' and prod_taken='$pk'"); 
$result1=mysql_result($sql1,0); 
$result2=mysql_result($sql2,0,0); 

echo $result1;
echo "---";
echo $result2;

function calcStock ($a,$B)/>{

return($a - $B)/>;
}
//$calc1= mysql_result($sql1,$sql2);
echo "---";
echo calcStock($result1,$result2);
$sql3=mysql_query("UPDATE product SET inStock=calcStock($result1&&$result2) where prodName='$pn'");



actually,after this calculation, I want to auto update the new value of inStock in product table. But, the problem is, I dont know the sql to update the new value into the inStock.
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: how to update only one field in a table with a new value

Posted 06 February 2013 - 06:54 PM

Forgive me if I'm wrong, but on line 67 are you trying to call a PHP function within an SQL query? SQL will have no visibility of this, I think you might need to change your line 67 to:
$sql3 = mysql_query("UPDATE product SET inStock=" . calcStock($result1,$result2) . " WHERE prodName='$pn'");


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1