How to execute mysqli queries while iterating through rows returned using mysqli_use_result? (PHP)

Question

I am trying to iterate through all users and call a function on each username from an accounts table I have on a MySQL database.

At first, I tried:

$result = mysqli_query($mysqli, "SELECT username FROM accounts");
while ($row = $result->fetch_assoc()) {
    try {
        getFollowerData($row["username"], $mysqli, $ig);    
    } catch (Exception $e) {
        echo "$e \n";
    }
}

But that used too much memory as I have over 4 million records in the table.

So I then tried:

$result = mysqli_query($mysqli, "SELECT username FROM accounts", MYSQLI_USE_RESULT);
while ($row = $result->fetch_assoc()) {
    try {
        getFollowerData($row["username"], $mysqli, $ig);    
    } catch (Exception $e) {
        echo "$e \n";
    }
}

However, when I do this I get:

error: Commands out of sync; you can't run this command now

because I have the line:

$result = mysqli_query($mysqli, $insertFollowerData);

in my getFollowerData() function, meaning I'm trying to make an SQL query while I am still making the first query to get all the usernames from accounts.

Is there any way to make these queries simultaneously?

Maybe You open two separate connections for the two queries? — Roman Hocke
– Roman Hocke, Commented Feb 8, 2019 at 12:07

Dharmesh Vasani · Accepted Answer · 2019-02-08 12:37:25Z

1

Instead of calling this function ( getFollowerData($row["username"], $mysqli, $ig)) in loop You can user inner left join to get the follower data from the database. and you can use limiting the bunch of data and use lazy loading concept via pagination to display those data

$result = mysqli_query($mysqli, "SELECT username, followerdatacolumns FROM accounts LEFT JOIN follower_data_table_name ", MYSQLI_USE_RESULT);
while ($row = $result->fetch_assoc()) {
    try {
         // Your result  
    } catch (Exception $e) {
        echo "$e \n";
    }
}

answered Feb 8, 2019 at 12:37

Dharmesh Vasani

4752 gold badges5 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute mysqli queries while iterating through rows returned using mysqli_use_result? (PHP)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related