Running a mysql query multiple times using values from an array in the WHERE clause

Question

I need to run a database query multiple times depending in the User ID. Heres my query:

    $query1="INSERT INTO wp_scloyalty (userid, value) VALUES ('XXXXX', '01234')";

I have an array of user ID's from a seperate query shown here:

while($rows=mysql_fetch_array($allresult)){  

    $birthdays_today[] = $rows['user_id']; 

}

echo $birthdays_today[0];

I want the query to run, but there the "userid" = XXXXX in the query, I want that to be populated with a user ID from the array. This means the query must be ran multiple times and each time, the next ID is entered into the query..

Does this make sense?

Thanks :)

It looks like you might want to use a nested query. If you want the '01234' to be the same value for all of the userId values. Look at stackoverflow.com/questions/8581930/mysql-query-nested-query — Demitrius Nelon
– Demitrius Nelon, Commented Mar 28, 2012 at 15:52

Gohn67 · Accepted Answer · 2012-03-28 16:04:48Z

2

MySQL has a bulk insert feature.

$query1="INSERT INTO wp_scloyalty (userid, value) VALUES ('XXXXX', '01234'), ('XXX2', '23234'), ('XXXX3', '3434545')"

<?php
$query = "INSERT INTO wp_scloyalty (userid, value) VALUES ";
$usersToInsert = array();
foreach($birthdays_today as $user_id){
    $usersToInsert[] = "($user_id, '01234')";
}
$query .= implode(',', $usersToInsert);
?>

edited Mar 28, 2012 at 16:04

answered Mar 28, 2012 at 15:53

Gohn67

10.7k2 gold badges32 silver badges35 bronze badges

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

4 Comments

Steve's a D Over a year ago

Could even get rid of the loop all together with implode :)

Gohn67 Over a year ago

Oh wait, how would that work since we need a value for loyalty too?

Steve's a D Over a year ago

Well, it depends if the OP wants 01234 to change or not. If he doesn't you could just set the separater as ', '01234'),('

Gohn67 Over a year ago

Ah yeah, I see now. Pretty clever.

PRNDL Development Studios · Accepted Answer · 2012-03-28 15:51:55Z

1

Use a for loop and change out the query with sprintf() and string formatting.

answered Mar 28, 2012 at 15:51

PRNDL Development Studios

3,3102 gold badges37 silver badges51 bronze badges

Comments

David Bélanger · Accepted Answer · 2012-03-28 15:52:41Z

0

<?php
foreach($birthdays_today as $uid){
    mysql_query(); // ...
}
?>

but

<?php
while($rows=mysql_fetch_array($allresult)){  
    mysql_query(); // use $rows['user_id'] here
}
?>

answered Mar 28, 2012 at 15:52

David Bélanger

7,4485 gold badges42 silver badges55 bronze badges

Comments

hjpotter92 · Accepted Answer · 2012-03-28 15:53:31Z

0

I will only display the script after your $birthdays_today[] has been populated.

foreach( $birthdays_today as $thisID )
{
  mysql_query( "INSERT INTO wp_scloyalty (userid, value) VALUES ('{$thisID}', '01234');" );
}

answered Mar 28, 2012 at 15:53

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Collectives™ on Stack Overflow

Running a mysql query multiple times using values from an array in the WHERE clause

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related