techker Posted March 31, 2018 Share Posted March 31, 2018 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted March 31, 2018 Share Posted March 31, 2018 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 |>--+ +--------------+ Quote Link to comment Share on other sites More sharing options...
techker Posted March 31, 2018 Author Share Posted March 31, 2018 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 31, 2018 Share Posted March 31, 2018 (edited) 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 March 31, 2018 by Barand Quote Link to comment Share on other sites More sharing options...
techker Posted March 31, 2018 Author Share Posted March 31, 2018 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 Quote Link to comment Share on other sites More sharing options...
techker Posted April 1, 2018 Author Share Posted April 1, 2018 sorry forgot , 1 code 1 client, depends on how much he paid Quote Link to comment Share on other sites More sharing options...
Barand Posted April 1, 2018 Share Posted April 1, 2018 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"? Quote Link to comment Share on other sites More sharing options...
techker Posted April 1, 2018 Author Share Posted April 1, 2018 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted April 1, 2018 Share Posted April 1, 2018 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 Quote Link to comment Share on other sites More sharing options...
techker Posted April 1, 2018 Author Share Posted April 1, 2018 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 1, 2018 Share Posted April 1, 2018 What does the "credits" field contain? Quote Link to comment Share on other sites More sharing options...
Barand Posted April 1, 2018 Share Posted April 1, 2018 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 = ? Quote Link to comment Share on other sites More sharing options...
techker Posted April 1, 2018 Author Share Posted April 1, 2018 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 1, 2018 Share Posted April 1, 2018 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. Quote Link to comment Share on other sites More sharing options...
techker Posted April 1, 2018 Author Share Posted April 1, 2018 sorry ya.the admin adds credits. and i have a counter that shows the amount left Quote Link to comment Share on other sites More sharing options...
Barand Posted April 1, 2018 Share Posted April 1, 2018 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. Quote Link to comment Share on other sites More sharing options...
techker Posted April 1, 2018 Author Share Posted April 1, 2018 true. i will check that option.thx for the help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.