[SOLVED] How to extract from one table into another

[MadTechie]

full credit to Roopurt18, i was just here, he was the one who did all the work, and would of posted the same thing (maybe better)

[roopurt18]

I want to be sure you understand why the query was failing.

 

vote  CREATE TABLE `vote` (
  `choice` int(11) NOT NULL default '1',
  `charity` varchar(50) NOT NULL default '',
  `votes` int(11) NOT NULL default '0',
  PRIMARY KEY  (`choice`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

The insert statement is only updating the `charity` field, which means each of the entries was getting a `choice` value of 1, since that's the default you set.  However, `choice` is also set as a PRIMARY KEY; there can only be one PRIMARY KEY defined per table and it must be unique across all of the rows.  In other words, once a value is assigned to the PRIMARY KEY of one row that same value can not be used in the PRIMARY KEY field of another row.  That is the reason you got the duplicate index.

 

Now, you are likely to encounter a problem with your script that you weren't expecting.  From the structure of your CREATE TABLE for `vote`, it appears you want to update each charity with the number of users that have it in their profile.  Basically, let's say there are 3 users that chose charity ABC and 2 users that chose charity XYZ.  Which of the following sets of data should appear in `vote`:

 

`vote` dataset 1

+---------+-------+
| charity | votes |
+---------+-------+
|  ABC    |   1   |
|  ABC    |   1   |
|  XYZ    |   1   |
|  ABC    |   1   |
|  XYZ    |   1   |
+---------+-------+

 

`vote` dataset 2

+---------+-------+
| charity | votes |
+---------+-------+
|  ABC    |   3   |
|  XYZ    |   2   |
+---------+-------+

 

Likely you are wanting to query these later and count the number of users supporting a particular charity.  You can do this with either table structure, although structure 2 is more compact.

 

Also, with your current DB structure, it will be difficult for your users to support more than one charity.  Are you sure they will only ever support a single charity?  Or in the future do you think you will need to allow them to support multiple charities?

[bri4n]

Hi Roopert!

 

The idea is for each user to nominate a charity and for users to vote for the nominated charities.

 

Thanx,

Brian :-)