how to handle different datas for different users in a registration table


all

I am designing a table.My situations are as follows

I have so many user types.I am defining the user types as type1,type2,type3 etc

I have a registration system and I have different data for each types.Even admin can add the data for these types

I am not getting an idea how to handle my registration table.Should I store everything in a single table for all types




please help me
Posted On: Sunday 21st of October 2012 09:44:38 PM Total Views:  158
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Social website "friends lists". How is the data handled? Table design...

How would you think that busy sites like facebook and myspace manage each users' friends list. With the sites having millions if not hundreds of millions of users, and some users have thousands of one giant table with all friend connections seems like a bad idea. Each user get it's own table Then you could potentially end up with millions of tables. That doesn't seem like good design either. Granted, a table would only really need to be a few columns. record id (bigINT), member_id(int), friend_id(int), If you had 100million users and on average 100 friends per user, that's 10billion records. But it'd be 10billion small, tiny records with only integer based columns. While that'd be a huge record set, you could probably do a lot with caching, and creating different views. Or, just do a table for each letter of the alphabet. If your last name begins with T, you go int the T table for friends. Then that table only contains the friend records of members who's last name begins with T. That would only require 26 tables, and could then cut 10billion records into tables with only 390million records. DBA's, how would you approach this
VIEWS ON THIS POST

143

Posted on:

Thursday 11th October 2012
View Replies!

How to handle error in MySQL script

How can I handle error in mySQL script in c# we use try.. catch.. so, what about MySQL script
VIEWS ON THIS POST

128

Posted on:

Friday 12th October 2012
View Replies!

How many connection a mysql can handle and how can I tell it's overloaded?

Hi. I wonder how many connections a mysql server can normally handle And how can I tell (tools/technique) tell will tell me it's about to get overloaded that I need to do something
VIEWS ON THIS POST

126

Posted on:

Friday 12th October 2012
View Replies!

Setting two different alias names for two successive rows

Quote: Originally Posted by francy There is a "sample" table with unique IDs for each person, a "variation" table with many variation_ids measured on each person, i got lost right here presuming that the "sample" table is actually genotype, i was unable to see how the variations are related to the genotypes i was also unable to see where you reference (no pun intended) the "reference" column of the allele in the query
VIEWS ON THIS POST

70

Posted on:

Friday 12th October 2012
View Replies!

Can't remember how to update multiple rows with different values?

I can't remember how to do this: UPDATE fe_users SET tt_products_creditpoints = (2816,289,300086,303402,308637,309905,311228,311408,323580,400360,400851,405220,405591,418801,419291 ,430573,461828,471521,471777,475054,836,898) WHERE tx_lsysfeusersimport_account_number IN (2816,289,300086,303402,308637,309905,311228,311408,323580,400360,400851,405220,405591,418801,419291 ,430573,461828,471521,471777,475054,836,898)
VIEWS ON THIS POST

66

Posted on:

Friday 12th October 2012
View Replies!

SQL JOIN in PHP - is it possible to retrieve two different values from one column?

, my first time in this forum, i have tried looking for the solution to my problem on this forum as well as others, however i have not found an answer yet. would much appreciate your help. I am building a web based database using php and mysql. i think this problem is more associated with mysql. basically i have a two tables in the database: Location, Distance. Location consists of the fields: Location_ID, name. Distance consists of the fields: Distance_ID, origin, destination, distance. the two fields origin and destination in the distance field, reference the Location_id. what i am wondering is, is it possible to create a query that will out put a table with the following details: Distance_ID, Origin_name, Destination_name, distance. for example: Location_ID, name 1, london 2, paris 3, rome Distance_ID, origin, destination, distance 1, 1, 2, 50 2, 1, 3, 75 3, 3, 2, 12 what i want to display is: Distance_ID, Origin, Destination, Distance 1, london, paris, 50 2, london, rome, 75 3, rome, paris, 15 the code that i have so far is: Code: $query="SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; if ($num==0){ echo "Sorry, there are no distances."; } else while ($i < $num) { $DistanceID=mysql_result($result,$i,"DistanceID"); $origin=mysql_result($result,$i,"Location.name"); $destination=mysql_result($result,$i,"Location.name"); $distance=mysql_result($result,$i,"distance"); echo "$DistanceID$origin$destination$distance"; $i++; } what my code will output is : Distance_ID, Origin, Destination, Distance 1, london, london, 50 2, london, london, 75 3, rome, rome, 15 this is not exactly what i want. i can sort of see where the problem is (the issue with Location.name), but i have no idea how to solve this, or whether it is even possible. i am not familiar with INNER JOIN, but i understand that Code: INNER JOIN Location ON Distance.origin=Location.locationID gets stored in origin Code: $origin=mysql_result($result,$i,"Location.name") aswell as destination Code: $destination=mysql_result($result,$i,"Location.name"); . and that only Code: INNER JOIN Location ON Distance.origin=Location.locationID is declared for origin. but if i also do it for destination, the code breaks. i.e. Code: "SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID INNER JOIN Location ON Distance.destination=Location.locationID" any ideas on how to display the names of both the origin and destination would be much appreciated. edit: i guess i could always create 2 tables named location_origin and location_destination, but my idea was to save space by just having one table to serve the purpose of storing names of places. though i wonder if it would be a better/easier idea to have the two tables instead of one.
VIEWS ON THIS POST

97

Posted on:

Friday 12th October 2012
View Replies!

UNION on different table structures

I want to create a Facebook style news feed. I have the following tables: Files (user posted files) Discussions (forum posts) Categories (categories in which files AND discussions are created) If a timestamp field is present in each of Files and Discussions tables, how can I select the last 100 records from both tables \t
VIEWS ON THIS POST

175

Posted on:

Friday 12th October 2012
View Replies!

Different query order, different query speed

ALL, To optimize a SQL statement, I tested it with 10 queries. Each of them has different query words. They are represented by Q1, Q2,......,Q10 respectively. I found that the query speed for a given query depends on the order of executing the sequency of queries. Before executing the queries, I reboot the MySQL server to clean the cache. The query execution time for the case where I run Q1 first is much longer than the time for the case where I run Q1 as the last query, i.e., executing Q1 after Q2, Q3,......,Q10. THis is true for other queries. Generally, later queries tend to be faster than earlier queries. I am wondering why it is this case. I guess it is due to the cache mechanism. But I am not very sure. Can anyone explain this for me By the way. I found that Code: /usr/local/mysql/bin/mysqladmin shutdown cannot clean the cache. Why. To test the effect of a modification to a SQL statement, should I reboot the MySQL server before testing
VIEWS ON THIS POST

82

Posted on:

Friday 12th October 2012
View Replies!

DECODE function different in SQL vs MySQL?

DECODE(payment.credit_card_no, 'password') returns a 16-digit number -- the correct credit card number that was encrypted using ENCODE(4111222233334444, 'password') --however-- When I paste this same SQL code into a Windows-based SQL browser, connected to the same MySQL database (via ODBC), it returns a 32-digit number that doesn't resemble the original CC number at all. I am totally baffled! Are not ENCODE and DECODE standard SQL statements that behave the same in MySQL and standard SQL
VIEWS ON THIS POST

183

Posted on:

Friday 12th October 2012
View Replies!

Select top three results from different catagories

I cannot seem to get this to work. I have a table full of hockey teams records that I need to get the top three teams based on points but each team of the top three has to be from a different division. Here is an example: Table: Teams TEAM | POINTS | DIVISION T#1 25 DIV#1 T#2 23 DIV#1 T#3 30 DIV#2 T#4 12 DIV#2 T#5 26 DIV#3 T#6 27 DIV#3 The result should look like this: 1. T#3 2. T#6 3. T#1 Not look like this: 1. T#3 2. T#6 3. T#5 Anybody have an idea what a select statement would look like for this
VIEWS ON THIS POST

85

Posted on:

Friday 12th October 2012
View Replies!

How to merge two or three different tables in one?

, I have big problem, I dont know how to merge two or more tables (with it data) into one new. I need to transform my old mysql from my old site into new mysql structure for new site. What I want to do is to transform and copy only user details table from old to new. In old mysql user details are stored into 3 tables with some columns and in new table I have only one table with some columns. That 3 tables have same number of data rows and I array it by ID number. Because I dont need all columns from my user tables from old mysql, I will delete unnecessary columns and leave columns which I need for new table. Now question, how I to merge this three tables to I get only one with certain table structure Too, I need when merge to copy and all user data which are now stored into that 3 tables and that user data to be merged. Database is MyISAM, table 1, 2 and 3 had some same columns (ID of user and user status) but I delete it from table 2 and 3 because I need only one of it in new table. Can anyone help me please Thank you very much, Mladen
VIEWS ON THIS POST

295

Posted on:

Friday 12th October 2012
View Replies!

How to grab two sets from different users

I have a page where a you edit a user's module access, and I need to grab all the modules that you have access to, and the user levels of those modules for the user you're editing. So far I have: SELECT modules.module_id, module_name, user_levels.user_level, user_levels.user_level_name FROM modules LEFT JOIN user_access ON user_access.module_id = modules.module_id LEFT JOIN user_levels ON user_levels.user_level = user_access.user_level WHERE modules.module_id IN ( SELECT module_id FROM user_access WHERE user_id = 'myUserID' ) AND user_access.user_level > ANY( SELECT user_level FROM user_access WHERE user_id = 'theirUserID' ) This however returns redundant info of course; if I specify my user_id, it's not redundant but it returns the modules I have access to only if they have a record in the user_access table.
VIEWS ON THIS POST

73

Posted on:

Friday 12th October 2012
View Replies!

Group by different strings to get sum

hi! i need to get bytes sum for specific content. is there any way to group by different strings that have similarity, like: c=100025&p=1 and c=100025&p=3. both have 100025 and it corresponds to a file. so that i need to calculate sum for them. how can i do it on MySQL level Thanx!
VIEWS ON THIS POST

169

Posted on:

Friday 12th October 2012
View Replies!

Which mysql version is more able to handle heavy traffic

hello, we are currently running mysql 4.1. we have problems with a php/mysql website that generates a lot of traffic and relies heavily on mysql (it's a "find your old schoolmates" community website). Sometimes it just says "too many connections", or just takes a long while before rendering the page. Would upgrading to mysql 5 help What are the best mysql settings in order to optimize the performance
VIEWS ON THIS POST

109

Posted on:

Friday 12th October 2012
View Replies!

Selecting three different pieces of data

I have three tables, a user table that contains a users information, a domain table which contains information about domain names assigned to each user, and a hosting table, which contains information about hosting accounts assigned to each user. Now, I'm wanting to make a list of each user, and all their corresponding domains and hosting accounts, and export it to a CSV file. Domains and hosting can both be seperate of eachother (ie the user could have a domain without having hosting for that domain and vice versa) and if this is the case then I would like to list them on their own rows in the CSV file (ie leave the hosting or domain fields blank), but they can also have hosting which uses a certain domain. If this is the case, I would like both the domain and the hosting listed on the same row in the CSV file. I have written the query below that gets me all domains that have a hosting account attached to them, and also all domains that do not have a hosting account, but I cannot figure out how to get out the hosting accounts that do not have domains attached to them. Code: SELECT us.username, us.fname, us.lname, us.email, us.org, CONCAT(dl.sld, '.', dl.tld) AS domain, dl.expiredate, hl.domain AS hdomain, hl.renew_date FROM users AS us LEFT JOIN domainlist AS dl ON dl.ownerid = us.username LEFT JOIN hostinglist AS hl ON (hl.domain = CONCAT(dl.sld, '.', dl.tld)) OR (dl.sld = '' AND hl.ownerid = us.username) ORDER BY username ASC
VIEWS ON THIS POST

75

Posted on:

Friday 12th October 2012
View Replies!

Comparing different formated dates

Here is my dilemma. I have 2 date formats. One is in the format of 2006-07-11 and the other in 7/7/2006 for example. I need to pull data from MySQL withing a certain date range or where a date matches a certain criteria. So my question is if it is possible to compare different date formats like the ones above when executing a query in MySQL.
VIEWS ON THIS POST

68

Posted on:

Friday 12th October 2012
View Replies!

How can I select additional rows based upon shared variables in different columns?

I thought this would be simple, but I can't seem to figure it out... ... I've got a database table in which some rows have fields in one column that are identical to fields in other rows in a different column. Like... product id | item | sug | sug | _________________________________________ 2 | bag | box | book 3 | box | rock | bread 4 | book | lamp | clock ...with the primary key being the product id. What I had planned to do was this: When selecting & displaying product #2, I wanted to also be able to display item #3 & #4, or at least a link to #3 & #4 -- seperately from product #2, later on the page. I had hoped that because they each shared a variable, albeit in different columns, this would be easy. Instead I'm this right now: Can anyone help me with this
VIEWS ON THIS POST

134

Posted on:

Friday 12th October 2012
View Replies!

Getting different data from 2 tables simultaneously

Hi , I've been busy for a couple of hours and I can't find a solution to the following problem. I'm trying to get a total summary of data from 2 different tables. The first needs a sum of a table, the second the amount of records within a table (hence the sum and the count). This is the query i've tried: select sum(aantal),count(*) from regne,antword where datum > "2005-09-20" and datum < "2006-09-27"; This query gives an ambigous error because on both tables, 'datum' exists and I can't figure out how to do it right. I've tried setting tablenames in front of the datum but this doesn't give me accurate results. Does anybody have an idea to get a total summary of these 2 tables Many
VIEWS ON THIS POST

79

Posted on:

Friday 12th October 2012
View Replies!

how to connect two databases in different machine

end of post
VIEWS ON THIS POST

92

Posted on:

Wednesday 17th October 2012
View Replies!

How to handle this Query

Hi I have a query I don't know how to handle I have a leaders2subordinates table: Code: delimiter $$ CREATE TABLE `leaders2subordinates` ( `leader_uid_number` int(11) NOT NULL COMMENT 'Leaders user number', `leader_collection_id` smallint(4) NOT NULL COMMENT 'leaders type designation', `sub_uid_number` int(11) NOT NULL COMMENT 'Subordinate uid_number', `sub_collection_id` smallint(4) NOT NULL COMMENT 'Subordinate user type', PRIMARY KEY (`leader_uid_number`,`leader_collection_id`,`sub_uid_number`,`sub_collection_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ That has this data leader_uid_number leader_collection_id sub_uid_number sub_collection_id 10006 9 10004 11 10007 10 10005 11 10008 8 10006 9 10009 8 10007 10 I have a leads table where one of its' fields is counselor_id; say for this example it is 10004I have a users table where all users designated by 10004 - 10009 are located. This users table includes u.first_name and u.last_name fields I can get a leads counselor by the following: Code: SELECT CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor' FROM leads as l INNER JOIN users as cnsl ON l.counselor_id = cnsl.uid_number WHERE l.lead_id = 1; This returns 'Bill Parker' as the Counselor. But things get more complicated from here as I also want to find the that same leads Sales Manager I have to determine who 'Bill Parkers' Sales Manager. To do this I need to involve the leaders2subordinates table. If 'Bill Parker' has an uid_number of 10004 and I want to return his Sales Manager then do I have enough information to return the Leads' counselor and the sales manager related to that leads counselor If so what would be the best way to return a row like: Lead Counselor Sales Manager Paulette Johnson Bill Parker Anita Ward
VIEWS ON THIS POST

115

Posted on:

Sunday 21st October 2012
View Replies!