Mysql – Pulling a username from table one to update credits on table 2 under the same username

MySQLPHP

I currently have 2 tables, one being a table that stores votes, and the other being the members table. I want SQL to pull the username from the "votes" table (as this keeps track of who voted), then add +1 "credits" in the table "members". Along with this, it will have to change the spaces to underscore and remove capital letters if they are used. Does anyone have an idea on how to do this?

Best Answer

Unless you only planning to hold one "Vote" (ever!), I'd suggest you're missing at least one table. 

select * from Users ; 
+----+----------+------------+ 
| id | forename | surname    |
+----+----------+------------+ 
|  1 | Fred     | Flintstone | 
|  2 | Wilma    | Flintstone |
|  3 | Barney   | Rubble     |
|  4 | Betty    | Rubble     |
+----+----------+------------+ 

select * from Votes ; 
+----+------------------------------+
| id | Vote Description             | 
+----+------------------------------+
| 11 | Mid-Winter Festival Location |
+----+------------------------------+

select * from User_Votes ; 
+-----+---------+---------+------------+
| id  | vote_id | user_id | Choice     | 
+-----+---------+---------+------------+
| 111 |      11 |       1 | Bedrock    | 
| 222 |      11 |       2 | Rock Vegas | 
| 333 |      11 |       4 | Rock Vegas | 
+-----+---------+---------+------------+

I want SQL to pull the username from the "votes" table

The Username does not belong in the Votes table.
A User can exist without ever casting a vote, and so needs to exist in its own table.
Try to avoid using anything that might change as a Key. Going around the database changing duplicated values all over the place is (a) Poor Design and (b) terrible in terms of performance.

... it will have to change the spaces to underscore and remove capital letters

You cannot query or join effectively if your data is "noisy". This is another good reason to use a Surrogate Key (user_id) "inside" the database. It eliminates all these Data Entry requirements (for this part of the job, anyway).
The User's name should be stored once and only once so that, when it needs changing, you change it in (one and only) one place.

... this keeps track of who voted ...

Using the tables I describe above, you get a list of who voted on this particular vote like this: 

select u.forename, u.surname, v.choice  
from Users u 
inner join User_Votes uv 
      on   u.id = uv.user_id 
where uv.id = 11  
order by 2, 1 ; 

+----------+------------+------------+ 
| forename | surname    | choice     | 
+----------+------------+------------+ 
| Fred     | Flintstone | Bedrock    |
| Wilma    | Flintstone | Rock Vegas |
| Betty    | Rubble     | Rock Vegas |
+----------+------------+------------+

(And, if you wanted those who haven't voted yet)

select u.forename, u.surname 
from Users u 
left join User_Votes uv 
      on   u.id = uv.user_id 
      and  uv.id = 11 
where uv.user_id is null  
order by 2, 1 ; 

+----------+------------+ 
| forename | surname    | 
+----------+------------+ 
| Barney   | Rubble     | 
+----------+------------+
Related Question