有什么办法可以最小化这段代码的Mysql数据库之旅吗？

i have a table called propAmenities which holds two column amenity_id and property_id basically the table holds the foreign keys.

我有一个名为propAmenities的表,其中包含两列amenity_id和property_id,基本上该表包含外键。

now i have two insert data into this table, and while inserting data property_id column will have the same value for all rows that are to be inserted while amentiy_id value will vary, now for example the values may look like

现在我有两个插入数据到这个表中,并且插入数据时,property_id列对于要插入的所有行具有相同的值,而amentiy_id值将变化,现在例如值可能看起来像

INSERT INTO propAmenities(amenity_id, property_id) VALUES(1,1);
INSERT INTO propAmenities(amenity_id, property_id) VALUES(2,1);
INSERT INTO propAmenities(amenity_id, property_id) VALUES(3,1);
INSERT INTO propAmenities(amenity_id, property_id) VALUES(4,1);
INSERT INTO propAmenities(amenity_id, property_id) VALUES(5,1);

and the current code to insert the data i am using is:

并且插入我正在使用的数据的当前代码是:

public function savePropAmenities($amenityIds = array()) {
    if($this->validateRequired(array('propertyId'))) {
        foreach($amenityIds as $amenityId) {
            $sth = $this->dbh->prepare('INSERT INTO
                                        propAmenities
                                        (amenity_id, property_id)
                                        VALUES
                                        (:amenityId, :propertyId)');
            $sth->bindParam(':amenityId', $amenityId);
            $sth->bindParam(':propertyId', $this->data['propertyId']);
            $sth->execute();    
        }
    }
}

the above code will run a loop and will make a frequent trip to database to insert the records. is there anyway i could cut of the trip and minimize it to one?

上面的代码将运行一个循环,并将经常访问数据库以插入记录。无论如何我可以减少旅行并将其最小化到一个?

2 个解决方案

#1

You can do a multi value insert (for MySQL atleast)

你可以做一个多值插入(至少为MySQL)

INSERT INTO propAmenities (amenity_id, property_id) VALUES (1, 1), (2, 1), (3, 1)

Also you can set a default value for the field on the database.

您还可以为数据库上的字段设置默认值。

ALTER propAmenities MODIFY COLUMN property_id INT DEFAULT 1;

Then you could do this

然后你可以做到这一点

INSERT INTO propAmenities (amenity_id) VALUES (1), (2), (3)

#2

Not a reduce-to-one answer but heres how to make it more efficient:

这不是一个简单的答案,而是如何提高效率:

Move

 $sth = $this->dbh->prepare('INSERT INTO
                                        propAmenities
                                        (amenity_id, property_id)
                                        VALUES
                                        (:amenityId, :propertyId)');
$sth->bindParam(':propertyId', $this->data['propertyId']);

out of the for loop. There is no need to call it multiple times. This should reduce some traffic.

走出for循环。无需多次调用它。这应该减少一些流量。

EDIT

so your code becomes

所以你的代码变成了

public function savePropAmenities($amenityIds = array()) {
    if($this->validateRequired(array('propertyId'))) {
        $sth = $this->dbh->prepare('INSERT INTO
                                        propAmenities
                                        (amenity_id, property_id)
                                        VALUES
                                        (:amenityId, :propertyId)');
        $sth->bindParam(':propertyId', $this->data['propertyId']);
        foreach($amenityIds as $amenityId) {
            $sth->bindParam(':amenityId', $amenityId);

            $sth->execute();    
        }
    }
}

#1