Page 1 of 1

UPDATE two tables and LIMIT

Posted: Mon Oct 25, 2004 11:48 am
by cdickson
I have an update script that is working now to update 1 table, but I am trying to change it to update 2 tables, and I can't get it to work.

I also want to add "LIMIT 1" because sometimes, if you don't enter a date, it will delete all dates of all orders in the table.

The sequence of events is:
  • 1. User registers
    2. User places order
    3. Administrator retrieves order & user information which is displayed in input fields so that information can be updated if/when necessary. If information displays that does NOT need to be updated, then existing information should remain.
I must be doing something wrong with the variables. The syntax I am trying to use is:

Code: Select all

$query = "UPDATE Login_users a, orders b SET 
a.lender_name='{$_POST['lender_name']}', b.date='{$_POST['date']}',
b.ins_amount='{$_POST['ins_amount']}' 
WHERE b.order_no='{$_POST['order_no']}' AND b.userid = a.userid LIMIT 1";

Posted: Mon Oct 25, 2004 12:35 pm
by Weirdan
[mysql_man]Update[/mysql_man] wrote:Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.

Posted: Mon Oct 25, 2004 1:09 pm
by cdickson
I missed this, although I did read the manual before posting. :oops:

The query still doesn't work, so there is something else I am doing wrong. There is no error message, and as per my code I get the "success" message but:
  • - The date reverts to 0000-00-00 even though 2004-10-25 is in the field
    - The lender_name gets deleted
    - The ins_amount updates properly

Posted: Mon Oct 25, 2004 1:19 pm
by Weirdan
did you print out the query prior to call to mysql_query? And, btw, what is your db schema? That might be the problem with MySQL column type conversion...

Posted: Mon Oct 25, 2004 1:33 pm
by cdickson
Thanks for your help, Weirdan.

When I echo the query then update I get:
UPDATE Login_users a, orders b SET a.lender_name='', b.date='', b.ins_amount='300000' WHERE b.order_no='1000' AND b.userid = a.userid
The order has been successfully updated.
DB Schema:
Table: Login_users

Code: Select all

Field  Type  Null  Key  Default  Extra  
userid int(35)   PRI NULL auto_increment 
username varchar(40)         
password varchar(255)         
email varchar(255)         
f_name varchar(100)         
l_name varchar(100)         
activated enum('0','1')     0   
user_level enum('0','1','2','3')     0   
last_login datetime     0000-00-00 00:00:00   
decrypt_pass varchar(12)         
lender_name varchar(60)   MUL     
lender_address varchar(30)         
lender_city varchar(30)         
lender_state varchar(10)         
lender_zip varchar(10)     0   
lender_phone varchar(12)     0   
lender_fax varchar(12)         
lender_id int(10)     0
Table: Orders

Code: Select all

Field  Type  Null  Key  Default  Extra  
order_no int(11)   PRI NULL auto_increment 
userid int(35)     0   
date date     0000-00-00   
collateral_owner varchar(60)         
collateral_address varchar(60)         
collateral_city varchar(30)         
collateral_state varchar(20)         
collateral_zip int(10)     0   
collateral_county varchar(30)         
collateral_twp varchar(30) YES   NULL   
legal_descr longtext YES   NULL   
loan_no int(10)     0   
ins_amount int(10) YES   NULL   
community_name varchar(60)         
community_no int(10)     0   
map_no int(10) YES   NULL   
map_eff_date date YES   0000-00-00   
loma_lomr varchar(10) YES   NULL   
loma_lomr_date date YES   NULL   
flood_zone varchar(20) YES   NULL   
nfip_map varchar(10) YES   NULL   
ins_avail char(3)         
reg_program varchar(10)         
emergency varchar(10)         
ins_notavail varchar(10)         
bldg_CBRA char(3)         
CBRA_date date YES   0000-00-00   
flood_hazard char(3) YES   NULL   
comments longtext YES   NULL   
census_tract int(10) YES   NULL   
msa_smsa int(6) YES   NULL   
state_code int(3) YES   NULL   
county_code int(4) YES   NULL

Posted: Mon Oct 25, 2004 1:54 pm
by Weirdan
Did you notice nothing strange with your printed query? ;)

Posted: Mon Oct 25, 2004 2:10 pm
by cdickson
Yes...
I notice that the information that is already there - is not getting picked up by the UPDATE query, so it is clearing those fields instead of re-populating them or leaving them as they are.

So...
I figure it's because my syntax is wrong when I refer to the existing data. I have the fields with existing information updating with the syntax

Code: Select all

'{_$POST['lender_name']}'
, but I don't know what the proper way to show this is. Would I use

Code: Select all

'. $row['lender_name'] .'
instead? But then will this post to the field if the information is changed?

Posted: Mon Oct 25, 2004 2:29 pm
by Weirdan
Did you mean that you sent empty form fields to indicate that they should not be changed?

Posted: Tue Oct 26, 2004 8:02 am
by cdickson
No. The fields lender_name and date fields already have information assigned to them. The ins_amount field doesn't have anything assigned prior to arriving at this page.

When this page first appears, the lender_name and date show up in a text field so that the client has the ability to change these items if necessary. However whether I change the information or leave it the same, when I click on the Update button, it clears those fields no matter what is there. The lender_name becomes empty, and the date becomes 0000-00-00.

The ins_amount field updates properly, but I suspect it is because there isn't existing information in that field.

Posted: Tue Oct 26, 2004 8:16 am
by phpScott
try something like

Code: Select all

<?php
if(isset($_POST['date']))
  echo "date is <".$_POST['date']."> ";
else
  echo "nothing for date";

?>

at the top of your page to see if date is even being sent.

and your sure you are using the post method in your form?

Posted: Tue Oct 26, 2004 8:59 am
by cdickson
The date is being sent because it appears in the text field when the page first comes up, so I know it is there and is being read. The problem comes with the actual UPDATE process.

The form is definitely set to post:

Code: Select all

<form action="updateorder.php" method="post">
- and it was working until I added the update to the Login_users table

Initially I had the information from the Login_users table echoing pretty much as you show in your post, and it was working just fine updating only the orders table.

However the client found that they occasionally need to make updates to that information in addition to updating information in the orders table.

Once I added the second table into the UPDATE command is when things went awry, and that's why I think there is something wrong only in my syntax with the fields from the Login_users table.

Posted: Tue Oct 26, 2004 9:29 am
by phpScott
have you gone to the extreme yet of assining the POST variables out to another var like.

Code: Select all

<?php
$date=$_POST['date'];
$lender_name=$_POST['lender_name'];
$query="UPDATE Login_users a, orders b SET a.lender_name='$lender_name', b.date='$date', b.ins_amount='300000' WHERE b.order_no='1000' AND b.userid = a.userid ";
echo "query is $query";
?>