Jump to content
#StayAtHome ×
techker

Select in database by adding user

Recommended Posts

Hey guys i have a database User and a database Codes (all codes)

 

When we add a new user i would like to be able to put the amount of codes needed it would select in the codes database and put his userID

 

so add User assign 25 codes to user

 

in database user it would automatically add the UserID to 25 codes..

 

it's clear in my head..lol but is this clear?

 

 

 

 

Share this post


Link to post
Share on other sites

So third table would contain a row for each code with the user's id

+--------------+                                  +--------------+
| user         |                                  | code         |
+--------------+                                  +--------------+
| user_id      |---+                       +------| code         |
| username     |   |                       |      | description  |
+--------------+   |                       |      +--------------+
                   |                       |
                   |                       |
                   |                       |
                   |    +--------------+   |
                   |    | user_codes   |   |
                   |    +--------------+   |
                   +---<| user_id      |   |
                        | code         |>--+
                        +--------------+

Share this post


Link to post
Share on other sites

i have my table

 

User                                             Table Codes  

 

 

id                                                         id

username                                            Codes

pass                                                     Status 

dateIn                                                   UserID

Credits

 

So your saying to create a table to have the user codes?

 

can't i just update the codes datbase and add the userID to all 25 codes?

 

so when i select codes i filter UserID

Share this post


Link to post
Share on other sites

Then what happens when user number 2 comes along? All the codes have been allocated to user 1. (or are there more than 25 codes, and just 25 of them are allocated to user 1?)

 

In other words , a user can have more than one code. Can a code have more than one user? If the answer to that last question is "yes" you need the third table. If "no" then you can add the user id to the user's codes.

Edited by Barand

Share this post


Link to post
Share on other sites

ya i have i put all the codes in the database and have a counter on the site.

so i would now when im running low

Share this post


Link to post
Share on other sites

ya i have i put all the codes in the database and have a counter on the site.

so i would now when im running low

Can you explain that statement?

What happens when you are "running low"?

Share this post


Link to post
Share on other sites

ok its for an iptv business

 

they buy credits in codes lets say he buys 100

 

drop them in the database.with his pannel he could have resellers.

 

so that is why when he regiaters a reseller they get 25 credits.

 

when they run low there is a warning and they can't sell to clients if they are under 5.

 

in the main admin he see's the total amount of credits and reseller credits.

 

when is database of 100 codes get low he need to buy more and fill it up.

 

so that is why i want to be able to when he registers a reseller to assign 25 codes in the database automatic , not mannuelly go to the database and and do one by one.

 

is this more clear?

Share this post


Link to post
Share on other sites

I'll come back to that reply in a while - lots of questions to compile.

 

Meanwhile, a couple of questions regaring your data structure:

User            
---------      
id             
username       
pass           
dateIn         
Credits                    <-- is this a count of the user's codes?

Table Codes 
-----------
id
Codes                      <-- is this one code, or as the name implies, a list of codes?
Status 
UserID

Share this post


Link to post
Share on other sites

Ok np

 

table users contain knly user information

name , email...

 

tables codes have all the codes lime 100 codes.

in this table

id

code value

status(used or not)

UserId this is were j want to assign a user to the code

 

so i have one database and in my query i filter the codes according to the userId

Share this post


Link to post
Share on other sites

Is this a representation of how the process should work

they buy credits in codes                                     | user  | code |
lets say he buys 100                                          +-------+------+
                                                              |   1   |   1  |   user 1 now has 
drop them in the database.with his pannel                     |   1   |   2  |   codes 1 - 100
he could have resellers.                                      |   1   |   .  |
                                                              |   1   |   .  |
                                                              |   1   |  99  |
                                                              |   1   | 100  |
                                                              +-------+------+



so that is why when he regiaters a                            +-------+------+
reseller (user 2) they get 25 credits.                        | user  | code |
                                                              +-------+------+
                                                              |   2   |   1  |   user 2 now gets 25 codes
                                                              |   2   |   2  |
          UPDATE codes                                        |   2   |   .  |
          SET user = 2                                        |   2   |   .  |
          WHERE user = 1                                      |   2   |  25  |
          LIMIT 25                                            +-------+------+
                                                              |   1   |  26  |   user 1 has the remaining 75
                                                              |   1   |   .  |
                                                              |   1   |   .  |
                                                              |   1   | 100  |
                                                              +-------+------+


            
User 2 sells one to client (user 3)                           +-------+------+
                                                              | user  | code |
                                                              +-------+------+
          UPDATE codes                                        |   3   |   1  |   user 3 has 1 code
          SET user = 3                                        +-------+------+
          WHERE user = 2                                      |   2   |   2  |   user 2 now has 24 codes
          LIMIT 1                                             |   2   |   .  |
                                                              |   2   |   .  |
                                                              |   2   |  25  |
                                                              +-------+------+
                                                              |   1   |  26  |   user 1 has 75 codes
                                                              |   1   |   .  |
                                                              |   1   |   .  |
                                                              |   1   | 100  |
                                                              +-------+------+

when they run low there is a warning and 
they can't sell to clients if they are 
under 5.

          SELECT COUNT(*)
          FROM code
          WHERE user = ?
               

Share this post


Link to post
Share on other sites

lol..you knownwhennyou think to hard about something and it ends up super simple...

 

the update is did not know the LIMIT could be the solution..

 

thx for the help!is you need some tv PM me.

Share this post


Link to post
Share on other sites

You never answered the question about the "credits" field. If (as I suspect) that is meant to be a count of how many codes the users has left, remove it.

Get the value by querying the COUNT() from the table.

Never store derived data.

Share this post


Link to post
Share on other sites

sorry ya.the admin adds credits.

 

and i have a counter that shows the amount left

Share this post


Link to post
Share on other sites

As I have said, don't maintain a counter. When you need to know how many, query the database for the count.

 

That way you guarantee the count reflects what is in the data.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.