php - Foreach insert query the best way

Hi i've got this code which works fine but I think it uses a lot of server resources because execute a query to the database for each user id stored in a group, is there any way to solve this problem? Also how can I use prepared statement?

$event_start_date = date('d-m-Y', strtotime($new_start_date));
    $notification = "Nuovo evento <span class='text-warning'><strong>$event_title</strong></span> inizia il $event_start_date";
    $notification_status = "0";
    $notification_category= "events";


    $sql = "SELECT user_join_id FROM user_group_join WHERE group_join_id='$event_group'";

    $result= mysqli_query($conn,$sql);

    $datas= array();

    if(mysqli_num_rows($result) > 0){

        while($row=mysqli_fetch_array($result, MYSQLI_ASSOC)){

        $datas[]= $row;

        }

    }

    foreach($datas as $data) {

    $id_cliente = $data['user_join_id'];

    $event_notification = mysqli_prepare($conn, "INSERT INTO user_notifications (notification_sent_by, notification_sent_to, notification_message, notification_time, notification_status, notification_category, notification_category_id) VALUES(?,?,?,now(),?,?,?)");
    mysqli_stmt_bind_param($event_notification, 'iisisi', $userid, $id_cliente, $notification, $notification_status, $notification_category, $event_id);
    mysqli_stmt_execute($event_notification);
    mysqli_stmt_close($event_notification);

    }

Many thanks for your help

Hi i've got this code which works fine but I think it uses a lot of server resources because execute a query to the database for each user id stored in a group, is there any way to solve this problem? Also how can I use prepared statement?

$event_start_date = date('d-m-Y', strtotime($new_start_date));
    $notification = "Nuovo evento <span class='text-warning'><strong>$event_title</strong></span> inizia il $event_start_date";
    $notification_status = "0";
    $notification_category= "events";


    $sql = "SELECT user_join_id FROM user_group_join WHERE group_join_id='$event_group'";

    $result= mysqli_query($conn,$sql);

    $datas= array();

    if(mysqli_num_rows($result) > 0){

        while($row=mysqli_fetch_array($result, MYSQLI_ASSOC)){

        $datas[]= $row;

        }

    }

    foreach($datas as $data) {

    $id_cliente = $data['user_join_id'];

    $event_notification = mysqli_prepare($conn, "INSERT INTO user_notifications (notification_sent_by, notification_sent_to, notification_message, notification_time, notification_status, notification_category, notification_category_id) VALUES(?,?,?,now(),?,?,?)");
    mysqli_stmt_bind_param($event_notification, 'iisisi', $userid, $id_cliente, $notification, $notification_status, $notification_category, $event_id);
    mysqli_stmt_execute($event_notification);
    mysqli_stmt_close($event_notification);

    }

Many thanks for your help

• php

Share Improve this question asked Feb 19, 2017 at 9:27 pippopippo 4355 bronze badges 2

• I don't know where you are getting the value for $event_group from but it bothers me that you go through the trouble of binding prepared statements for the INSERT but not the SELECT. – Matthew Brown aka Lord Matt Commented Oct 4, 2017 at 15:35
• Also, more on topic, do you know that this code is running sub-optimally or are you guessing it is? – Matthew Brown aka Lord Matt Commented Oct 4, 2017 at 15:36

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Additionally, you prepare the statement, use it, and then close it every step of the loop. That's a lot of wasted effort.

Prepare once, use for the loop, then close at the end.

$event_notification = mysqli_prepare($conn, "INSERT INTO user_notifications (notification_sent_by, notification_sent_to, notification_message, notification_time, notification_status, notification_category, notification_category_id) VALUES(?,?,?,now(),?,?,?)");
foreach($datas as $data) {
    $id_cliente = $data['user_join_id'];
    mysqli_stmt_bind_param($event_notification, 'iisisi', $userid, $id_cliente, $notification, $notification_status, $notification_category, $event_id);
    mysqli_stmt_execute($event_notification);
}
mysqli_stmt_close($event_notification);

I would expect that to be a heck of a lot faster.

I am no expert with mysqli and prepared statements but I am pretty sure you can bind all the data into a huge array and do the interest in one hit.

This SO answer suggests that to me, anyway. Given you already built the big array, you should be able to use it without foreach to break it back up. That might be even faster still.