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
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?