Home › Forums › General Issues › Update_field for multiple Posts using one sql query
I update around 300 posts a few times a week with a process similar to this:
$schools = get_schools_object_from_csv($file_path);
foreach ($schools as $id => $school_data) {
$post_id = get_post_id_by_school_id($id);
if (empty($post_id)) {
$post_id = wp_insert_post( [...$school_data[stuffToCreatePost]] );
update_field('programs', $data['programs'], $post_id);
} else {
update_field('programs', $data['programs'], $post_id);
}
}
These schools are sometimes existing schools (a post already exists) where we need to update the school’s programs: an ACF repeater, or it’s a new school where I need to wp_insert_post and then I add the programs to that post.
This process takes around 1 minute for around 300 schools. I’m wondering if it could be faster.
I think I could improve the time significantly if I use php to iterate through the schools and create 1 SQL query, similar to what was described in this other topic where they recommend:
INSERT INTO wp_postmea (post_id, meta_key, meta_value) VALUES ("1", "field_1", "value_1"),("1", "_field_1", "field_0123456789"),..... etc.
update_field() allows me to dump a whole array/object of the “programs” repeater field into a:
update_field('programs', $data['programs'], $post_id);
I’m not sure how to handle this into a sql query.
But my thought is that I could run 1 query to fetch ALL post IDs for every school (instead of a db query inside the foreach to get just 1 postID), and then save that PostID to the school array.
Then do a foreach for $schools and build a sql query (psuedo code):
$sql=[]
foreach($schools as $id => $school) {
$sql[] = "INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
VALUES ($school[post_id], "meta_key for programs(?)", $school['programs'])
ON DUPLICATE KEY UPDATE meta_value = $school['programs']"
}
$sql_query = implode(' ; ', $sql);
$wpdb->query($sql_query)
This would allow PHP to run quickly to build one large sql query, with just 1 wpdb query to insert OR update all schools/programs.
At this point I’m just curious if I can update an entire repeater field w/ a sql query this way?
I should mention I cant simply delete all school posts because there’s a lot of other fields (other than the programs repeater) that need to be preserved
I forgot to mention that the update_field() is taking so long because the programs repeater field has about 60 sub-fields, including another repeater “locations” inside of it.
Maybe updating 300 (schools) * 60 (total fields) will just take this long? Was hoping for something sub 10 seconds, since 18000 updates seems like it shouldn’t take 60 seconds
I’ve also tried several “wp_importing” tricks, none of which have decreased the time to import
ex: set_time_limit(0);
ignore_user_abort();
wp_defer_term_counting( true );
wp_defer_comment_counting( true );
$wpdb->query(‘SET autocommit = 0;’);
if (!defined(‘WP_IMPORTING’)) {
define(‘WP_IMPORTING’, true);
}
$wpdb->query(‘ALTER TABLE wp_postmeta DISABLE KEYS;’);
with appropriate cleanup after
You must be logged in to reply to this topic.
Welcome to the Advanced Custom Fields community forum.
Browse through ideas, snippets of code, questions and answers between fellow ACF users
Helping others is a great way to earn karma, gain badges and help ACF development!
We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Privacy Policy. If you continue to use this site, you consent to our use of cookies.