Hi all. I am new user here and I found a lot of usefull info in this board. What I am curious to learn is how can I update a record in MySQL and in the same time to Insert a new record in another table. For example, I have a table called ORDERS and users choose an order and offer price for it. When he offers a price, the query updates the BID and USER_LAST fields in the ORDERS table. What I want is with the same query, also to INSERT the Order ID, name of the user and his bid into other table, where will be storet the full history of the bidding. So for example User1 offers 20, the Orders table updates to bid=20 user_last=user1. When user2 bids 22 the orders table updates to bid=22 user_last=user2. I want to have both the user1's and user2's bid written in another table called BIDS, so I can review who has bided and how much. Thanks a lot for you attention and hopefully I will get the answer :) Thanks.

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')";
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.