动态插入数据到mysql表中 [英] inserting data into a mysql table dynamically

问题描述

请原谅我没有正确的问题标题，但我的问题如下

Forgive me for lack of a proper question title but my question is as below

我有一个包含如下数据的表格:

I have a table with data that look like this:

    mysql> select * from tablex;

+-------+---------+-----+
| id  | post_id   | pid |
+-------+---------+-----+
| 14549 |    7195 |  27 |
| 14551 |    7195 |  34 |
| 14556 |    7195 |   1 |
| 14564 |    7196 |  51 |
| 14566 |    7196 |  11 |
| 14571 |    7196 |  37 |
| 14576 |    7197 |  36 |
| 14578 |    7198 |  11 |
| 14586 |    7199 |  15 |
| 14612 |    7201 |  42 |
+-------+---------+-----+

当我对重复进行计数时，我得到这样的数据结构:

When i do a count for duplicates, i get a data structure like this:

mysql> select count(*), post_id from tablex group by post_id;
+----------+---------+
| count(*) | post_id |
+----------+---------+
|        3 |    7195 |
|        3 |    7196 |
|        1 |    7197 |
|        1 |    7198 |
|        1 |    7199 |
|        1 |    7201 |
+----------+---------+

我正在寻找有关如何最好地使用 php/mysql 操作上述数据以将 tabley 更新为如下所示的想法

I am seeking ideas of how best to manipulate the data above using php/mysql to update tabley to look like this

mysql> select * from tabley order by meta_id desc;
+---------+---------+------------------+---------------+
| meta_id | post_id | meta_key         | meta_value    |
+---------+---------+------------------+---------------+
|    7575 |     7195| multiple         |       3       |
|    7574 |     7195| multiple_0       |      27       |
|    7573 |     7195| multiple_1       |      34       |
|    7572 |     7195| multiple_2       |      1        |
|         |         |                  |               |
+---------+---------+------------------+---------------+

你会注意到 post_id 7195 出现了 3 次，所以第一步是将 meta_key 的倍数设置为 3.

You will notice that post_id 7195 occurs 3 times,so the first step is to set meta_key multiple to 3.

下一步是运行一个循环并创建键 0-2 并创建元键 mutiple_0 - multiple_2 并插入对应于 post_id 7195 的值 27,34,1，如 tablex 所示......这是最简单的实现这一目标的方法?

The next step is to run a loop and create key 0-2 and create meta keys mutiple_0 - multiple_2 and insert values 27,34,1 that correspond to post_id 7195 as can be seen from tablex ...Which is the easiest way to achieve this?

推荐答案

这是代码.不要忘记更改服务器名、用户名、密码和数据库

Here is the code . Don't forget to change the servername,username,password and database

$conn = mysqli_connect('localhost', 'root', 'password','database');
  $sql=mysqli_query($conn,"select count(*) as count,post_id from tablex group by post_id");
    while($row=mysqli_fetch_array($sql)){   
        $count[]=$row["count"];
        $postid[]=$row["post_id"];
        }
        foreach (array_combine($postid,$count) as $pid=>$cnt){

            for($i=0;$i<=$cnt;$i++){
                $pstid = $pid;
                if($i==0){
                $multiple = "multiple";
                $meta= $cnt;
                }
                else{
                    $x=$i-1;
                    $multiple = "multiple_".$x;
                    $query=mysqli_query($conn,"select pid from tablex where post_id='$pid'");
                    while($row=mysqli_fetch_array($query)){ 
                    $id[]=$row["pid"];
                    }
                    $meta = $id[$i-1];
                }
$query2=mysqli_query($conn,"INSERT INTO tabley(post_id,meta_key,meta_value) VALUES('$pid','$multiple','$meta')");
            }

        }

这篇关于动态插入数据到mysql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！