1

In my code, I use this query to get all the rows where credited field is false:

$uncredited_incentives_query = $wpdb->prepare(
    "SELECT * FROM $incentives_table WHERE user_id = %s AND credited = FALSE ",
    $sponsor->ID
);

Then I work with the results. Then I have to set the credited field as true for the rows which were returned by the previous query. I am currently using this aproach.

$wpdb->update(
$incentives_table,
array(
    'credited' => true
),
array(
    'user_id'  => $sponsor->ID,
    'credited' => false
)
);

But I don't want to include any new rows which may have been added since this code was executed. So I want to know how is that possible? Am I using the correct approach?. If not, then how can I do so?

Thanks in advanced.

  • 1
    You need to have a unique identifier for each row and in your loop to update where this unique identifier.Or to save all the unique identifiers in a array and then update all in 1 time instead of updating each row in the loop. – Shibi Dec 25 '17 at 17:02
  • Do you mean something like this: [link]stackoverflow.com/a/8873956/3659689 @Shibi – ShahRukhMlk Dec 25 '17 at 17:07
  • No I meant like you did but instead of using the user_id use a column thats unique to the row. – Shibi Dec 25 '17 at 17:10
  • @Shibi I can do this using row ids. but the row ids array will be very large upto millions and more. I don;t think that will be wise to pass as an argument to [code]WHERE[/code]? Can you suggest any efficient method for doing that? – ShahRukhMlk Jan 2 '18 at 19:55
  • Maybe you can create some temp column that you update first with the user_id + timestamp for example and then select all the rows by this value. and update again by this value after you doing what you wanted. Or you can add some variable $x = 0; and increase it every row and every 100 rows to update. – Shibi Jan 2 '18 at 20:26
0

Method 1 - Check for the last row id.

SELECT * FROM $incentives_table WHERE user_id = %s AND credited = FALSE

You can get the last row in the array and get the column row_id.

And then you can update with the where

WHERE user_id = %s AND credited = FALSE AND row_id <= {last row id}

Method 2 - temp column.

First you need to create another column in the table for this lets call it for example 'temp_user_update`.

Now when you want to get the data and then update you need first to create variable to use in your code and save it in this column.

$temp_user_update = $sponser->ID.time();

UPDATE table set temp_user_update='{$temp_user_update}' WHERE user_id = %s AND credited = FALSE

After this step you can select and update with the WHERE temp_user_update='{$temp_user_update}'

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.