Page 1 of 1

two table update

Posted: Mon Jan 16, 2006 7:47 pm
by joecrack
i have a pretty good working script that is updating datasets.
but now i changed it a little bit and it has to update TWO tables now.
but i just cant make it. the update part of the script looks like this:

Code: Select all

if (($_SESSION['projnr'] && $_SESSION['customernr']!= '') && (count($params))) {
   $sql = "UPDATE sam_date_val SET ";
   foreach ($params as $key=>$val) {
      $sql .= "$key = '$val', ";
   }
   $sql = substr($sql, 0, -2);
   $sql .= "WHERE projnr ='" .$_SESSION['projnr']."' AND customernr ='".$_SESSION['customernr']."'";
   mysql_query($sql);
}
I tried to just copy this update IF and make it a different varaible than $params and change the table name - but that didnt work out. I hope anyone has an idea.
thx
joe

Posted: Tue Jan 17, 2006 2:18 pm
by jmut
Starting with MySQL 4.0.4, you can also perform UPDATE operations covering multiple tables.
So as a start you would need that much MySQL
Then you could see an example and do the trick easily.


http://dev.mysql.com/doc/refman/4.1/en/update.html

Code: Select all

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Posted: Tue Jan 17, 2006 9:52 pm
by joecrack
ok i tried to do the double table qquery like this:

Code: Select all

UPDATE sam_bestellung,sam_date_val SET sam_bestellung.area='A9' AND sam_date_val.contrdate='2009-09-09' WHERE sam_bestellung.projnr='9999' AND sam_date_val.projnr='9999' AND sam_bestellung.customernr='99.99.' AND sam_date_val.customernr='99.99.'
But instead of putting A9 into the field, it blanks it. What is wrong with this statement???
I dont get an sql error !!!

Posted: Wed Jan 18, 2006 12:11 am
by josh
Try forcing a table join

Code: Select all

WHERE items.id=month.id;
notice how they are joining on the primary key there


Do you have a primary key or a foreign key you can join on? From your example it looks like both fields are the same in both tables?

Posted: Wed Jan 18, 2006 12:41 am
by joecrack
i havent set a foreign or a primary key cause i set these fields in both tables as "unique" and my phpadmin tells me i shouldnt set unique and primary on same fields.

if i would do

Code: Select all

WHERE items.id=month.id;
how do i tell sql then that it has to be id 9999 in both tables?
and yes the fields are the same in both tables - same name and in this case same values!
do you mean just add

Code: Select all

WHERE sam.bstellung.projnr=sam_date_val.projnr
and the same with customernr???

Posted: Wed Jan 18, 2006 1:20 am
by jmut
joecrack wrote:ok i tried to do the double table qquery like this:

Code: Select all

UPDATE sam_bestellung,sam_date_val SET sam_bestellung.area='A9' AND sam_date_val.contrdate='2009-09-09' WHERE sam_bestellung.projnr='9999' AND sam_date_val.projnr='9999' AND sam_bestellung.customernr='99.99.' AND sam_date_val.customernr='99.99.'
But instead of putting A9 into the field, it blanks it. What is wrong with this statement???
I dont get an sql error !!!

Code: Select all

UPDATE sam_bestellung,sam_date_val SET sam_bestellung.area='A9' , sam_date_val.contrdate='2009-09-09' WHERE 
sam_bestellung.projnr = sam_date_val.projnr AND sam_bestellung.projnr='9999' AND sam_bestellung.customernr='99.99.' AND sam_date_val.customernr='99.99.'
NOTE the comma after SET ...not using AND ..althought not 100% sure if it could work with AND also
whats with the customernr equals?!? are they unique also. Something is wrong with this table
could you post
SHOW CREATE TABLE table_name;
for both tables.

Posted: Wed Jan 18, 2006 1:47 am
by joecrack
ok that worked NICE - but now i put the code with all the variables in my update script and it doesnt work

Code: Select all

$sql = "UPDATE sam_bestellung,sam_date_val SET sam_bestellung.Groupp='$groupp',sam_bestellung.EqDes='$eqdes',sam_bestellung.Area='$area',sam_bestellung.Yard='$yard',sam_bestellung.Hull='$hull',sam_bestellung.Classification='$classification',sam_bestellung.Custrefnr='$custrefnr',sam_bestellung.Custrefname='$custrefname',sam_bestellung.ShipNewbuild='$shipnewbuild',sam_bestellung.ShipOwner='$shipowner',sam_bestellung.Designer='$designer',sam_bestellung.Shiptype='$shiptype',sam_bestellung.Shipclass='$shipclass',
sam_date_val.ContrDate='$contrdate',sam_date_val.Plannedmonth='$plannedmonth',sam_date_val.actdeldate='$actdeldate',sam_date_val.incdate='$incdate',sam_date_val.exrate='$exrate',sam_date_val.currency='$currency',sam_date_val.contramount='$contramount',sam_date_val.deldate1='$deldate1',sam_date_val.deldate2='$deldate2',sam_date_val.deldate3='$deldate3',sam_date_val.amendment1='$amendment1',sam_date_val.amendment2='$amendment2',sam_date_val.amendment3='$amendment3'
WHERE sam_bestellung.projnr='$projnr' AND sam_date_val.projnr='$projnr' AND sam_bestellung.customernr='$customernr' AND sam_date_val.customernr='$customernr'";
        mysql_query ($sql) or die ( 'MySQL-Fehler: ' . mysql_error ());

if (mysql_errno() != 0){
  echo mysql_error();
}

if (mysql_query($sql,$db)) {
	echo "<center>WORXs<c/enter>";
}
	else{
		echo "Didnt work </br></br>";
	}
I get the echo WORXs - but the tables dont get updated. I should tell me if the sql UPDATE statement is wrong.
What could it be. I mean it worked with the two smal tables. Also i tried it in phpmyadmin and put in data for all the $xyzvariables and it worked. I also tested the variables like $projnr, $area and they work.
Some of them are empty, but i think that shouldnt be the prob. I also left some blank when i tried it in phpmyadmin. anyone got an idea? thanks again to all you guys helping me.

Posted: Wed Jan 18, 2006 9:21 am
by jmut
maybe some of them have
' or somthing.
and you are doing kind of SQL injection...hence the query is not working. Escape all your data.

see what the mysql log says when executing the query.

And the worst case scenario: add one after another until you find out the problem.