Insert into tables linked by primary key

I have two tables with the relationship

+----------------------+      +-----------------+
|        Shape         |      |  Point_Values   |
+----------------------+      +-----------------+
| shape_id (KEY)       |      | id (KEY)        |
| shape_name (VARCHAR) |      | point_x (INT)   |
+----------------------+      | point_y (INT)   |
                              | shape_id (INT)  |
                              +-----------------+

开发者_运维知识库

How do you go about structuring an insert in general or for CodeIgniter at the same time where you get the shape and points in the same request, meaning that when a shape finishes there is an array of points and the shape name/id. I have to do the inserts into the table but have the shape_id in both match.

---

By request

Array
(
    [name] => "circle"
    [points] => Array
        (
            [x] => Array
                (
                    [0] => 182
                    [1] => 190
                    [2] => 215
                    [3] => 240
                    [4] => 291
                    [5] => 328
                    [6] => 364
                    [7] => 391
                    [8] => 425
                    [9] => 459
                    [10] => 487
                    [11] => 512
                    [12] => 529
                    [13] => 540
                    [14] => 551
                    [15] => 560
                    [16] => 570
                    [17] => 581
                    [18] => 592
                    [19] => 604
                    [20] => 617
                    [21] => 628
                    [22] => 635
                    [23] => 639
                    [24] => 642
                    [25] => 642
                    [26] => 640
                    [27] => 630
                    [28] => 619
                    [29] => 606
                    [30] => 591
                )

            [y] => Array
                (
                    [0] => 165
                    [1] => 159
                    [2] => 150
                    [3] => 147
                    [4] => 144
                    [5] => 144
                    [6] => 147
                    [7] => 152
                    [8] => 162
                    [9] => 172
                    [10] => 184
                    [11] => 199
                    [12] => 211
                    [13] => 219
                    [14] => 225
                    [15] => 228
                    [16] => 231
                    [17] => 232
                    [18] => 233
                    [19] => 233
                    [20] => 231
                    [21] => 222
                    [22] => 204
                    [23] => 189
                    [24] => 156
                    [25] => 133
                    [26] => 120
                    [27] => 100
                    [28] => 85
                    [29] => 69
                    [30] => 54
                )

        )

)

---

Well considering that array, this code should work nicely. It does one initial insert to get the id from table shape and all points are batch inserted.

function insertShape($s){
    mysql_query('INSERT INTO shape (name) VALUES ("'.$s['name'].'");');
    $id=mysql_insert_id();
    $i=0;
    for(;;){
        if(!isset($s['points']['x'][$i], $s['points']['y'][$i]))break;
        $inserts[]='('.$id.','.$s['points']['x'][$i].','.$s['points']['y'][$i].')';
        $i++;
    }
    mysql_query('INSERT INTO point_values (shape_id,x,y) VALUES '.implode(',',$inserts));
}

I'd probably make that for into a foreach if your situation doesn't need rigorous testing on the keys.

---

I suggest u basic Algorithm, first u make foreign key constraint between shape_id(point_value) and shape_id(shape)

first insert into shape table

mysql_query("INSERT INTO shape SET shape_name='".$array['name']."'");

and then get the last_insert_id

$shape_id=mysql_insert_id();

then go to point_value table

foreach( $array['points] as $k)
{

   $qry= "INSERT INTO point_value SET
                               point_x=(int)$array['points']['x'][$i],
                               point_y=(int)$array['points']['y'][$i],
                               shape_id=(int)$shape_id ";
   mysql_query($qry);
} 

i hope this will help u..ask for help Thanks