mysql - 从其他表中插入选择与乘法

Question

我想将数据从 table2 插入到 table1，其中 table2 中的数据乘以 table3 中的值。

table1 和 table2 具有相同的列(id、rankking、status、access、facility、popularity)(id 自动递增)表2有数据:

id |排名 |状态 |访问 |设施 |人气

1 | 9 | 4 | 2 | 4 | 6

2 | 4 | 5 | 1 | 2 | 7

3 | 5 | 8 | 5 | 3 | 4

表3有数据:

id |值
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

从该查询中我发现错误，有帮助吗？

Answer 1

您选择一个名为value的列，然后您应该访问$c2中的值(并且不需要在select中使用别名(对于插入/选择，值是按位置分配的)

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));