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.

Re: MySQL INSERT and UPDATE at once 80 80

what is the code that you are working on ?

Re: MySQL INSERT and UPDATE at once 80 80
<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());


}

?>
Re: MySQL INSERT and UPDATE at once 80 80

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

Re: MySQL INSERT and UPDATE at once 80 80
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

Re: MySQL INSERT and UPDATE at once 80 80

check your INSERT syntax.

Re: MySQL INSERT and UPDATE at once 80 80

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 :(

Re: MySQL INSERT and UPDATE at once 80 80

it should be something like

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

Re: MySQL INSERT and UPDATE at once 80 80

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'.

Re: MySQL INSERT and UPDATE at once 80 80

post the exact SQL that you are using.

Re: MySQL INSERT and UPDATE at once 80 80

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 :(

Re: MySQL INSERT and UPDATE at once 80 80

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

Re: MySQL INSERT and UPDATE at once 80 80

what is $id in your code ?

Re: MySQL INSERT and UPDATE at once 80 80

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 ?

Re: MySQL INSERT and UPDATE at once 80 80

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());


}
Re: MySQL INSERT and UPDATE at once 80 80

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 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.