views:

35

answers:

2

I have some users setup in a MySQL table with different variables. I am trying to figure out what would be the best way to do this. Basically I want to award all of my registered and active users with bids which are stored in another table.

So for the Table "users" I have ran this query:

SELECT *  
 FROM `users` 
WHERE `active` = 1 
  AND `admin` = 0 
ORDER BY `users`.`id` ASC

Which will show all active users who are not administrators.

Now I would like to give each one of these users which are identified by the "ID" field in another table.

So in the "bids" table I would need to add a new row for each one of those users with all of the same values except for the "user_id" field which will basically match the "id" field of the table "users"

What would be the best approach for this. There are approximately 6,000+ users coming up in the first query.

+1  A: 

Can you do something like this?

INSERT INTO bids 
   (col1, col12, col3)
SELECT
   users.col1, users.col2, users.col3 FROM users
WHERE users.active = 1 and users.admin = 0
ORDER BY users.id ASC
Randy Simon
Would that basically copy over the data from the users table and insert it into the bids table? If so that is not what I was looking for unfortunately. I want to insert a rows into the bids table with only the "user_id" field matching that of the "id" field in the users table. All of the other columns I would like to insert other data.
Nickelbids
It won't copy over anything, only insert new rows into the bids table
Matt Ellen
Where does this "other data" come from?
Randy Simon
A: 

Typically not a good idea to duplicate data like that but there is also:

create table bids as select <fields> from users