3

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

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. Oct 4 '17 at 15:35
  • Also, more on topic, do you know that this code is running sub-optimally or are you guessing it is? Oct 4 '17 at 15:36
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.