UPDATE two tables and LIMIT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

UPDATE two tables and LIMIT

Post 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";
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

[mysql_man]Update[/mysql_man] wrote:Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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...
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Did you notice nothing strange with your printed query? ;)
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Did you mean that you sent empty form fields to indicate that they should not be changed?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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";
?>
Post Reply