Updating 2 tables help

Hi
could someone have a look at this query, im trying to update 2 sql tables

mysql_query("UPDATE lobby,rooms SET lobby.line1=lobby.gametotal-lobby.product1,rooms.line1=rooms.gametotal-rooms.product1 where rooms.roomID=lobby.room.ID ");

its not updating the database,
i can use this

mysql_query("UPDATE rooms SET line1='$gametotal'-'$product1' ");
mysql_query("UPDATE lobby SET line1='$gametotal'-'$product1' ");

but i need the where room.ID

thanks paul

This works with me. “update test8 t8,test9 t9 set t9.email=t8.email where t9.uId=t8.uId”;Please see that the in both the table uId must have values to match.

try this –

UPDATE rooms 
INNER
  JOIN lobby
    ON lobby.roomID = rooms.roomID
   SET rooms.line1 = rooms.gametotal - rooms.product1 
;
UPDATE lobby 
INNER
  JOIN rooms
    ON rooms.roomID = lobby.roomID
   SET lobby.line1 = lobby.gametotal - lobby.product1
;

Hi
yes both roomID match,
i have also tried your code r937 but no joy,i have looked at my code and can’t for the life of me understand why it wont work.
iv also tried this.

mysql_query("UPDATE rooms,lobby SET rooms.line1='$gametotal'-'$product1',lobby.line1='$gametotal'-'$product1' where rooms.roomID=lobby.roomID");

“no joy” is not a valid mysql error message

please tell me what happened after each of the two statements i gave you

HI
first sql message
;# MySQL returned an empty result set (i.e. zero rows).

2nd # Affected rows: 1

this is the query i use because product1 is a variable

UPDATE rooms 
INNER
 

  JOIN lobby
    ON lobby.roomID = rooms.roomID
   SET rooms.line1 = rooms.gametotal - '$product1' ;UPDATE lobby INNER JOIN rooms ON rooms.roomID = lobby.roomID   SET lobby.line1 = lobby.gametotal - '$product1

I thought it would be easy two update 2 tables with one query. wrong
if you can’t update two tables together with 1 simple query why bother with a database.

for other reasons

sorry you’re disappointed

next time you’re doing online banking and transfer some money from one account to the other, just stop and remember that it ~isn’t~ done with a single UPDATE statement to both accounts simultaneously, and then ask yourself if the bank bothered to use a database

:wink:

well course im disapointed iv looked at my code can’t understand why it wont work, iv looked at your code and with your experience your code should work, so yep a little p— --f.
becuse i can’t go forward until i figure how to do this two table update.

want some help debugging the update statements i gave you?

HI
yes i could do with some help.
what do you need from me

what do you get for this –

SELECT rooms.line1, rooms.gametotal, rooms.product1 
  FROM rooms 
INNER
  JOIN lobby
    ON lobby.roomID = rooms.roomID

p.s. please run this outside of php

hi
i ran the query in php admin is this correct,
this is what i got
#1054 - Unknown column ‘rooms.product1’ in ‘field list’
because product1 is a variable

here is the php code before i tried to do the join between lobby, and rooms
i need to update rooms and lobby with the where roomID clause because they may be more than one room.

$result = mysql_query($query11);
$returnVars = array();
$returnVars['total'] = "$total";
$returnVars['profit'] = "$profit";
$returnVars['gametotal'] = "$gametotal";
$product = $total * (1 - $profit);
$product1 = $gametotal * (1 - 0.20);
$product2 = $gametotal * (1 - 0.30);
$product3 = $gametotal * (1 - 0.50);

$returnString = http_build_query($returnVars); 

mysql_query("UPDATE rooms SET gametotal='$product' ");

mysql_query('UPDATE rooms SET line1="$gametotal"-"$product1');
mysql_query("UPDATE rooms SET line2='$gametotal'-'$product2' ");
mysql_query("UPDATE rooms SET fullhouse='$gametotal'-'$product3' ");

mysql_query("UPDATE lobby SET gametotal='$product' ");
mysql_query("UPDATE lobby SET line1='$gametotal'-'$product1' ");
mysql_query("UPDATE lobby SET line2='$gametotal'-'$product2' ");
mysql_query("UPDATE lobby SET fullhouse='$gametotal'-'$product3' ");

whoa

i don’t actually do php, but i can sort of read it

could you do a SHOW CREATE TABLE for both tables please

what does “may be more than one room” mean? what’s a lobby?

Hi
lobby is a duplicate table of rooms. In the script there is a main admin section in the admin section the admin can create rooms, roomname,profit,ticket, etc.here is the lobby table.

CREATE TABLE IF NOT EXISTS `lobby` (
  `roomID` int(10) NOT NULL default '0',
  `time1` varchar(200) NOT NULL default '0',
  `ticket` varchar(100) NOT NULL default '10',
  `profit` decimal(10,2) NOT NULL default '0.00',
  `line1` decimal(10,2) NOT NULL default '0.00',
  `line2` decimal(10,2) NOT NULL default '0.00',
  `fullhouse` decimal(10,2) NOT NULL default '0.00',
  `total` decimal(10,2) NOT NULL default '0.00',
  `myprofit` decimal(10,2) NOT NULL default '0.00',
  `gametotal` decimal(10,2) NOT NULL default '0.00',
  `roomname` varchar(200) NOT NULL default '',
  `username` varchar(200) NOT NULL default '',
  `players` varchar(250) NOT NULL default '0'

rooms table

CREATE TABLE IF NOT EXISTS `rooms` (
  `roomID` int(10) NOT NULL auto_increment,
  `time1` varchar(200) NOT NULL default '0',
  `ticket` varchar(100) NOT NULL default '10',
  `profit` decimal(10,2) NOT NULL default '0.00',
  `line1` decimal(10,2) NOT NULL default '0.00',
  `line2` decimal(10,2) NOT NULL default '0.00',
  `fullhouse` decimal(10,2) NOT NULL default '0.00',
  `total` decimal(10,2) NOT NULL default '0.00',
  `myprofit` decimal(10,2) NOT NULL default '0.00',
  `gametotal` decimal(10,2) NOT NULL default '0.00',
  `roomname` varchar(200) NOT NULL default '',
 
  `players` varchar(250) NOT NULL default '0',
  PRIMARY KEY  (`roomID`),
  UNIQUE KEY `roomID` (`roomID`)

so there will be rows in each of those tables with matching roomIDs?

okay, let’s say that the roomID that you want to update is number 42

so you’re updating one row in each table, right?

UPDATE rooms 
   SET gametotal = $product
     , line1 = $gametotal - $product1
     , line2 = $gametotal - $product2
     , fullhouse = $gametotal - $product3
 WHERE roomID = 42
;
UPDATE lobby 
   SET gametotal = $product
     , line1 = $gametotal - $product1
     , line2 = $gametotal - $product2
     , fullhouse = $gametotal - $product3 
 WHERE roomID = 42
;

now, before you plug this into your php code, i’d like you to run them, one at a time, outside of php, which means you’ll have to hardcode the amounts (and note, single quotes are not required)

hi
the first query rooms
Affected rows: 0 (Query took 0.0002 sec)
no errors
2nd query lobby
Affected rows: 0 (Query took 0.0003 sec)

ah, did you pick an actual valid roomID instead of 42?

I get this error

SQL query:

UPDATE lobby SET gametotal = 10.00line1 = 10.00line2 = 12.00fullhouse = 15.00 WHERE roomID =3

MySQL said:

#1064 - 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 ‘line1 = 10.00
line2 = 12.00
fullhouse = 15.00
WHERE roomID = 3’ at line 3

why did you remove the commas?