MySQL INSERT and UPDATE at once

what is the code that you are working on ?

<form name= 'search' method='post'>
<input type='number'  max='<?






echo ($sum_total);




?>' name='duma' onKeyPress="return numbersonly(event, false)"/>
<br />
<input name="userl" type="hidden" class="textfield" id="userl" value='<?php echo $_SESSION['SESS_NAME'];?> '/>

<input type='submit'name='submit' value='Подай оферта'/>
<form>

<?
if ($_POST['submit']) {
$duma = $_POST['duma'];
$userl = $_POST['userl'];
$sql = "UPDATE orders SET bid='$duma', user_last='$userl'  WHERE id='$id'";


$results = mysql_query($sql)  or die (mysql_error());


}

?>

you need to add both the insert and update queries into a procedure and call the same from front end application.

if ($_POST['submit']) {
$duma = $_POST['duma'];
$userl = $_POST['userl'];
$sql = "UPDATE orders SET bid='$duma', user_last='$userl'  WHERE id='$id'";
$insert_query="INSERT INTO bids order='$id' user='$userl' bid='$duma'";

$results = mysql_query($sql)  or die (mysql_error());
$results = mysql_query($insert_query)  or die (mysql_error());

}

This is what I've done till now, but the query returns the following result:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order, user, bid) VALUES ('1','user1','358')' at line 1

check your INSERT syntax.

I have no idea why it doesn't work :( I have the fields order, user and bid in the mysql, and it reads the $ values, it should work :(

it should be something like

INSERT INTO bids (order,user,bid) values ($id,$userl,$duma);

I found where the mistake comes from. It reads order like ORDER, not like field name. Now it reports other mistake, like: Unknown column 'user1' in 'field list'.

post the exact SQL that you are using.

The MySQL table BIDS structure is the following:

id	int(11)					auto_increment	 	 	 	 	 	 	
	orders	int(11)						 	 	 	 	 	 	
	usero	varchar(32)						 	 	 	 	 	 	 
	bid	varchar(32)						 	 	 	 	 	 	 
	date	date

The php script is:

if ($_POST['submit']) {
$duma = $_POST['duma'];
$userl = $_POST['userl'];
$sql = "UPDATE orders SET bid='$duma', user_last='$userl'  WHERE id='$id'";
$sql="INSERT INTO bids (orders,usero,bid) values ($id,$userl,$duma)";

$results = mysql_query($sql)  or die (mysql_error());
}

$userl reads the name of the user, that's currently logged in, and the script should write it in the field "usero", but it reports the mistake:

Unknown column 'aaaa' in 'field list'

"aaaa" is my name, so it reads it but doesn't insert it into the usero field :(

just run the SQL part in back end with values and check ,if it works.

what is $id in your code ?

I managed to select results from two tables in the database - users and orders. $id is the order id, so whn i have 10 orders, and i want to bid on the 4-th, it reads the $id and updates only that order. I wrote script where $mid = member_id, which is the ID of the user, so it inserts in the BIDS table the id of the bidder. The code is:

$query="SELECT * FROM orders AS o, users AS u WHERE o.id='$id'";
$result=mysql_query($query);
$id=mysql_result($result,$i,"o.id");
$bid=mysql_result($result,$i,"o.bid");
$mid=mysql_result($result,$i,"u.member_id");
$biduser = $_SESSION['SESS_NAME'];


?>

The script is:

if ($_POST['submit']) {
$duma = $_POST['duma'];
$userl = $_POST['userl'];
$biduser = $_SESSION['SESS_NAME'];
$sql = "UPDATE orders SET bid='$duma', user_last='$userl'  WHERE id='$id'";
$sql = "INSERT INTO bids values ('','$id', '$mid', '$duma','1')";

$results = mysql_query($sql)  or die (mysql_error());


}

Now it INSERTs into BIDS table, but it doesn't UPDATEs the orders table, which is new problem to handle :( Any ideas how to write the script so it will run both the update and the insert query ?

I made it! Great! It does both UPDATE and INSERT so now I can review everyone's activity on the bidding! This is the code, so anyone, who wants to do the same, can use it :)

$query="SELECT * FROM orders AS o, users AS u WHERE o.id='$id'";
$result=mysql_query($query);
$id=mysql_result($result,$i,"o.id");
$bid=mysql_result($result,$i,"o.bid");
$mid=mysql_result($result,$i,"u.member_id");


if ($_POST['submit']) {
$duma = $_POST['duma'];
$userl = $_POST['userl'];
$biduser = $_SESSION['SESS_NAME'];
$sql = "UPDATE orders SET bid='$duma', user_last='$userl'  WHERE id='$id'";
$results = mysql_query($sql)  or die (mysql_error());
$qry = "INSERT INTO bids values ('','$id', '$mid', '$duma','1')";
$results = mysql_query($qry)  or die (mysql_error());


}

that is because of the variable names you are using.

since you are using the same variable name for both UPDATE and INSERT, INSERT i s over writing the UPDATE command before it gets executed

try using

$sql_update = "UPDATE orders SET bid='$duma', user_last='$userl' WHERE id='$id'";
$sql_insert = "INSERT INTO bids values ('','$id', '$mid', '$duma','1')";