CODEWITHSUNDEEP
mysql
Hafidh
Hafidh

Reputation: 130

Insert Select with Multiplication from other table

I want insert data to table1 from table2 where data in table2 multiplicated with value at table3.

table1 and table2 has same column (id,rangking,status,access,facility,popularity) (id is auto increment) table2 has data :

id | rangking | status | access | facility | popularity

1 | 9 | 4 | 2 | 4 | 6

2 | 4 | 5 | 1 | 2 | 7

3 | 5 | 8 | 5 | 3 | 4

table3 has data :

id | value
c2 | 0.2
c3 | 0.5
c4 | 0.1
c5 | 0.3

$sql = mysqli_query ($koneksi, "SELECT value 
           FROM table3 WHERE id ='c2'") or die(mysqli_error($koneksi));
$c2 = mysqli_fetch_array($sql);

$sql = mysqli_query ($koneksi, "SELECT value 
               FROM table3 WHERE id ='c3'") or die(mysqli_error($koneksi));
    $c3 = mysqli_fetch_array($sql);

insert into table1 (rangking,status,access,facility,popularity) 
SELECT (rangking*$c2['value'] as Rank,status *$c3['value'] as Stat,access,facility,popularity) FROM table2

from that query I found error, any help?

Upvotes: 0

Views: 474

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

You select a column named value then you should accessing value in $c2 (and don't need alias in select (for insert/select the values are assigned by position)

insert into table1 (a2,a3,a4,a5) 
SELECT (a2*$c2['value'] as A2,a3,a4,a5) FROM table2

..

$sqlTest = mysqli_query ($koneksi, "insert into table1 (a2,a3,a4,a5) 
    SELECT (a2*$c2['value'] as A2,a3,a4,a5) FROM table2") or die(mysqli_error($koneksi));

Upvotes: 1

Rogue
Rogue

Reputation: 11483

You could save yourself the multiple queries by using a join:

INSERT INTO table1 (a2, a3, a4, a5)
SELECT table1.a2 * table3.value AS a2, table1.a3, table1.a4, table1.a5
FROM table1
INNER JOIN table3
  ON table3.id = :id

:id being a named parameter, which you can bind to the value of 'c2' (or insert manually, depending on whether or not it's user input).

I used the table3.id = :id in the ON because that will for sure bind the result to the row selected, but if you have a proper association/foreign key you would want to use that instead.

In the future, it helps to name your columns something meaningful, not just a1, a2, etc.

Upvotes: 0

Related Questions