Problem to update specific column

[syafia]

hye!

 

Im a newbie in MySQL and Php, i got a problem to update column in db. Here is the issue:

 

i have 2 tables named 'apply' and 'balance'.

 

Table: apply

 

 

 

userId

 

  leaveType

 

  daysApply

 

 

 

1

 

  Annual

 

  3

 

 

 

2

 

  Medical

 

  1

 

 

 

Table: balance

 

 

 

userId

 

  Annual

 

  Medical

 

 

 

1

 

  14

 

  3

 

 

 

2

 

  14

 

  3

 

 

 

 

When user apply for leave, the system will deduct the value from table:balance based on apply.leaveType = balance.@column name. My problem is i dont know how to update the column value based on the apply.leaveType value from other table.

 

i try to do this but it doesnt make sense:

 

$result=mysql_query(UPDATE `balance`, `apply` SET `balance.Annual`=`balance.Annual`-`apply.daysApply`, `balance.Medical`=`balance.Medical`-`apply.daysApply` WHERE `balance.Annual`=`apply.leaveType` AND `balance.Medical`=`apply.leaveType` AND `balance.userId`=`apply.userId`);

 

Hope someone can help me. Any helps are appreciated.

 

 

 

 

[attachment deleted by admin]

[RussellReal]

Hello there buddy

 

firstly, you should think about what information you will have ALREADY before php does any work.. if you're going to set up a 'leave' for some user, you're going to NEED 3 values anyway, userId, leaveType, daysApply

 

we also know leaveType will always be Annual or Medical, right? so we can simply use that data to proceed..

 

INSERT INTO apply (userId,leaveType,daysApply) VALUES ($userId,$leaveType,$daysApply); UPDATE balance SET $leaveType = $leaveType - $daysApply WHERE userId = $userId;

 

voila!

[akitchin]

if you've got dynamic column name adjustments to make, it's usually a sign that your data isn't optimally structured. a better table structure for this sort of data is to have a balance table that echoes the apply table:

 

userId
leaveType
remainingBalance

 

this will give each user (at the moment) two rows, but that's okay. the query to update it would be as simple as:

 

UPDATE balance SET remainingBalance = remainingBalance - $daysApply WHERE userId='$userId' AND leaveType='$leaveType'

 

this also leaves room for future leaveTypes to be added without interfering with the current data or adjusting the table structure - you merely need to add a row for each user for the given leaveType.

[syafia]

ok..i got the solution at last. Thanks  for realizing me that something going wrong with my db table. I've alter back my db table and got the solution to update the balance table.

 

Tq, all helps are appreaciated