CODEWITHSUNDEEP
phpmysqlsql
Vipin Kumar
Vipin Kumar

Reputation: 13

How to update sql table by using a variable which has dynamic value

I have a php code which sums up the value of the same column from two different tables of same database and stores it in a variable. The code is mentioned below:

$sql = 'SELECT
        (SELECT SUM( time_spent )
         FROM '.TICKET_RESPONSE_TABLE.'
         WHERE ticket_id='.db_input($id).')
        +(SELECT SUM( time_spent )
          FROM '.TICKET_NOTE_TABLE.'
          WHERE ticket_id='.db_input($id).')
    AS total_time';
$result = db_query($sql);
$cursor = mysql_fetch_row($result);
$total_time = $cursor[0];

Now,I want is to update a column in another table of the same database, with the value stored in the variable $total_time. Kindly help me with the same.

Upvotes: 0

Views: 579

Answers (3)

aF.
aF.

Reputation: 66697

You can do a direct update instead of having on a variable.

$sql = 'UPDATE *tablename*
        SET *columname* = 
        (SELECT SUM( time_spent )
         FROM '.TICKET_RESPONSE_TABLE.'
         WHERE ticket_id='.db_input($id).')
        +(SELECT SUM( time_spent )
          FROM '.TICKET_NOTE_TABLE.'
          WHERE ticket_id='.db_input($id).')';
$result = db_query($sql);

or afterwords like this:

$sql = 'SELECT
        (SELECT SUM( time_spent )
         FROM '.TICKET_RESPONSE_TABLE.'
         WHERE ticket_id='.db_input($id).')
        +(SELECT SUM( time_spent )
          FROM '.TICKET_NOTE_TABLE.'
          WHERE ticket_id='.db_input($id).')
    AS total_time';
$result = db_query($sql);
$cursor = mysql_fetch_row($result);
$total_time = $cursor[0];
$sql = 'SUPDATE *tablename*
            SET *columname* = ' . $total_time
$result = db_query($sql);

Upvotes: 2

Nir
Nir

Reputation: 25369

Why not $sql= "INSERT INTO other table SET field_name = $total_time "; $result = db_query($sql);

Upvotes: 0

PiTheNumber
PiTheNumber

Reputation: 23542

You can make a subselect:

UPDATE table1 t1
SET t1.val1 =
(SELECT val FROM table2 t2 WHERE t2.id = t1.t2_id )
WHERE t1.val1 = '';

Upvotes: 0

Related Questions