mtapez mtapez - 4 months ago 6
MySQL Question

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 |
+----------+---------+


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 |
| | | | |
+---------+---------+------------------+---------------+


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

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?

Answer

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')");
            }

        }
Comments