Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication?
for example..:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(40) NOT NULL default '',
`Password` VARCHAR(40) NOT NULL default '',
`Lastlogin` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like:
lastip
createdon
lastlogin
active ('yes', 'no', 'banned') -
View Complete Forum Thread with Replies
Related Forum Messages:
Allowing Users To Edit Or Create Fields In The Database
Here is what we want to be able to do: Users upload a flat-file containing their data. They will have custom column names. Is there any way the user can interact with the database to create or edit the field names? Or is it a matter of setting up a predefined number of fields the user will import data to, and then define a label to the column? Example: The database has a field called User1. The user is then able to name it for their own customization.
View Replies !
Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state. I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different. Example: The 'objects' table looks like this: object_id, name, room 1, Hammer, 1 2, Mirror, 1 3, Spade, 2 So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade. Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table player_id, object_id, room 12, 1, 2 So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*
View Replies !
One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices: Is it better to have one table which has many fields or many tables which each has few fields? Is it true that the latter is worse because it will require many join operations? What is the limitation of the first option (one table with many fields)?
View Replies !
Logon FTP Users In A Table
i have proftpd on my server and the user is in the database koll and the table ftpusers. how can i get out information abut when the custummer have loget on the last time and also how can i se witch custummer thats have not been loged on the last 30 days.
View Replies !
Join Two Users Table
I have a wordpress blog(and it's user database is integrated with bbPress, so I know this is possible) and need to synchronize the user database with some tournament management code, which has it's own user tables. Could I change the "users" table in the APL tournament management code to wp_users as it appears in the Wordpress database structure? Wordpress integrates with bbPress somehow by taking bbPress's table prefix, _bb. I'd be most grateful for any direction and input in my road block which I've stopped at. Thank you for your time and consideration.
View Replies !
Grab All Users Not In Second Table.
I have two tables. I want to grab all the users from the users table who are not found in the second table. It's just seems to be one of those days where I can't perform the simple tasks, I think I need to go back to bed. [table] users [field] userid [table] user_details [field] userid Select userid from users where userid not in user details
View Replies !
Best Practices For A 'Users' Table
I am wondering what would be the best way to design the table(s) for users in a database, or at least get some opionions of how people would go about it. Say I have the normal stuff about a user such as name, email, phone etc, a good number of user attributes. Then I also have profile kind of attributes such as photo, more personal details, various preferences etc, again pleanty of attibutes. Then I also have a bunch of access or priviledges attributes such as status and whether they can access/modify certain modules. I could get all these in a single table since a user is going to potentially have all of these attributes once (though a lot could initially be null) but this is going to be one huge (wide) table maybe 30 columns or more. Would it make sense to split it into 3 tables like 'users', 'profiles', 'priviledges'? If so, should all these be created at the same time when a user signs up or as needed when a user decides to add a profile or get assigned special proviledges. If I create then on demand I will probably be saving a lot of unnecessary rows but I will have to be dealing with outer joins and defaults most of the time, ie more complex code. On the other hand if I depend on all three existing (besides the waste issue) I might get the wrong results if, for any reason, a row for each user fails to get created in all tables (or is accidentally removed).
View Replies !
Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?
View Replies !
Obtain Most Popular Cities From My Users Table
I've got a 'users' table: USER_ID --- CITY_ID 1 ---- 32 2 ---- 20 3----- 32 4 ---- 32 5 ----- 17 .... I would like to obtain the most popular cities from this table, and the number of occurrences. Is this possible to make it easy with an SQL statement?
View Replies !
Replace Some Username Values With The Users ID In A Different Table
I really can't figure this out. I want to replace some username values with the users ID in a different table. The tables are pictures user (Example FRED) picture (Example pic.gif) users id (Example 5233) user (Example FRED) What I want to do is replace the user column in pictures with the correct id from the users table So in the pictures table, it would no longer be FRED it would be 5233 Is there a query I can run to do this? I'd really appreciate some feedback
View Replies !
Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores: TABLE: feedback identities_id int(10) <- contains the user id which is stored in another table feedback int(10) <- contains the feedback score for a specific date and time date_recorded datetime <- date the feedback was captured I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results): mysql SELECT user1.feedback AS user1feedback , user2.feedback AS user2feedback , UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded FROM feedback as user1 LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded WHERE user1.identities_id = 1 OR user2.identities_id = 2 AND user1.date_recorded > NOW() - INTERVAL 30 DAY ORDER BY date_recorded ASC This returns virtually the same data for both users which means I am not distinguishing the two properly.
View Replies !
Query All Users From Users_table Which It's Ids Not Stored In First Table.id ?
i have table to store users informations , it's name is (users_table) and it's columns are : id,name and i have second table with name (first_table) and it's columns are : id,users_tableid i want sql query to fetch all users name from users_table but which it's id (users_table.id) not stored in the table first_table(first_table.users_tableid) so what's the true sql cmmand to do this ?
View Replies !
Making Site Login Add Users To Phpbb Database Table
I have a login script for my site that I got off the net and was planning to use, but, I also wanted to make it so that when a user signed up thru the registration form it would also add them to the phpbb database's table. It wasn't discussed heavily from where i got the script but from what was mentioned the encryptions differ which results in a rejected login. Code:
View Replies !
Join Two Fields In The Same Table To A Field In Another Table
I have two tables, Table1 ID Name ---------------------------------- 1 John 2 Joe 3 James Table2 Current_ID Last_ID ---------------------------------- 3 1 2 3 I want my output in a select statement as follows by joining the two tables. CurrentID_Name LastID_Name ---------------------------------------------------- James John Joe James I am basically trying to resolve the IDs with actual names in my output.
View Replies !
Max 3 Fields In A Table?
I am new to using mysql and php and am running into an issue which absolutely baffles me. I am trying to create a table in a database and simply print the values back. Everything works fine if I do it as below: ....
View Replies !
Db Table Fields
Is there an industry standard or best practice some of you more experienced developers could pass along concerning the number of characters I should define in my tables for a person's first name, last name, username, phone, email... and any other fields you think worth mentioning? My Password is encrypted with sha1(), so that field is 40 characters long. I'm looking to keep the tables as efficient as possible, so I'd like to follow some 'norms' if there are any.
View Replies !
Many To Many Table With Two Fields
two scenarios: 1. Many to Many table with two fields Each id could have up to 24 numbers mem_id | number (tinyint 1) 2 | 5 2 | 6 2 | 7 4 | 2 7 | 8 7 | 12 10| 9 SELECT * FROM table1 WHERE number = 5 AND mem_id = 2; 2. one to one mem_id | numbers (varchar 49) or (char 49) 2 | "|5|6|7|" 4 | "|2|" 7 | "|8|9|" SELECT * FROM table1 WHERE numbers LIKE '%|5|%' AND mem_id = 2; Which would you prefer, and is there any red flags with using method 2? If I used method 2, I can add the field to an existing table. I could index the field of course. Method one could result in thousands more rows in the separate table. 2000 rows could easily turn into 15 or 20k extra rows. I tested the speed and they are both similar, but that's at a small level.
View Replies !
Table Fields
Is it ok to have a big table with 27 fields or should I try and split it down into smaller tables. It kind of makes sense all being in one table to me, but if that is bad design then I would break it down.
View Replies !
More Than 50 Fields In A Table
I had made a table which contains more than 50 fields.. I am afraid this will hamper later .. What i want to know ? 1>how many fields should be in a table for optimization ? 2>what should taken into consideration for future hazards in case there are more than 50 fields in table ?
View Replies !
Sum 2 Fields From 2 Table
I have facing a problem to sum 2 fields from 2 table. SELECT Table1.Stud_Number, Sum(table1.School_fee), Sum(table2.libary) FROM Table1, Table2 WHERE Table1.Stud_Number = Table2.Stud_Number GROUP BY Stud_Number
View Replies !
How Many Fields Can A Table Have?
I'm presuming you can have as many fields as you'd like, but I'm just curious as to what the maximum recommended is. Is it ever better to split the fields over 2 tables instead of one? even though most the time if you're grabbing data from table1, you'll also be getting data from table2. The question popped into my head as I was making a "Orders" table for an online store. It has 39 fields so far and I'll probably need to add a few more (depending on what other information the client needs to know about their customer orders).
View Replies !
Getting All Fields From A Table And ONLY ONE From Another One
maybe it's time to get some rest, today... SELECT * , User_Avatar FROM restaurantsrate, users WHERE Card_ID =222443 AND Ratings_Deleted =0 AND Ratings_UserID = User_ID LIMIT 0 , 30 I'm trying to get all fields from table restaurantsrate and only User_Avatar from table users, but I'm getting ALL fields from both tables? anyone can help?
View Replies !
Two Similar Fields One Table
I am trying to figure how to make a single select query in one table between two fields to see if they are similiar. I need the second one to have a wildcard. I can easily do this with no wildcard to see if they are the same but I can't get anything to work when it should be LIKE. Example: Table: test Field1 = "testing 123" Field2="testing" I need something that selects that row because the word "testing" is in both.
View Replies !
SHOW FIELDS FROM Table?
My code is shown below, I can select * from employees but I cannot get any data back from SHOW FIELDS, I do not receive an error, in fact, something is returned but I'm not sure what. I can execute the command: SHOW FIELDS FROM test.employees in the Control Center just fine, even cmd.ExecuteReader will enter reader.Read() once, something is returned but what does it look like?
View Replies !
93 Fields In A Single Table
I'm putting a database together for a client, he has a questionaire that comprises of 93 questions that range between yes or no's to if no why not (text and varchar fields) I think his questionaire is ridiculous but he is the client at the end of the day. I have been trying to decide how to put this monstrosity together and it looks as though a table with 90+ fields is going to be the result because there are no field repeats. I could design a generic "yes and no" table then populate it with the "yes no" questions linking it with a relationship but then the coding to ensure order is going to be a lot of extra work...Dwane
View Replies !
Maximum Fields In A Table
I have a website which runs pretty slow... I have turned on the SLOW-QUERY-LOG, and there's one table that is in the log over and over again... It's the table which stores 99% of the user information. This table has 61 fields, and alot of these are varChar and their set at 100 or so.... Anyways, I was curious what is the maximum amount of fields you should have in table, and in my case should I split the data between several tables and just use a complex Join statement?
View Replies !
Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't. ID int(10), std varchr(50), atd varchr(50), dtd varchr(50), ctd varchr(50) I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also. If I change to another record it may all work or not. Any Ideas at all?
View Replies !
LIKE On Multiple Fields In Same Table.
Is it possible to make a LIKE search on multiple fields in the same table? (as the following sql example without that great repetition of word 'like'). My problem is: I have a single input on a browser, and inserting a single or multiple words (like a search engine) I would want to find that words. If I insert 'phrase example' I would want to find 'phrase' or 'example' or 'phrase example' (so the following example for this last possibility is wrong) I don't care about match against because I don't want to find exact words because it is not an advanced search. In the following example I would want to search words like 'orange', 'rank', 'frank' etc... I would want something like this following example on: same table, multiple fields and multiple words too.....
View Replies !
Table Fields Do Not Show Up
When creating a connection to my mysql database using dreamweaver mx, or various other programs, i can see the table names once the connection is made, but the fields do not show up with the exception of a couple of tables. I've looked over those tables several times and cannot find any differences that would cause this problem. The only program this does not seem to affect is phpmaker.
View Replies !
Merging Two Fields In A Table!
I have used a join to bring two tables together, which works fine, but what I now have is 2 fields with dates in which I would like to order by, and at no point are these two dates overlapping, is there a way I can merge the two together, or order by both so as they overlap. My current SQL is: SELECT * FROM tblblog LEFT JOIN tblfilmreview ON tblblog.blog_date = tblfilmreview.date_watched UNION SELECT * FROM tblblog RIGHT JOIN tblfilmreview ON tblblog.blog_date = tblfilmreview.date_watched ORDER BY blog_date DESC, date_watched DESC Thankyou for any advice you may be able to give Dan Duke
View Replies !
SUM Of Two Foreign Table Fields
I've a project that has many timesheets (time) and many expenses (money) I'm trying to work out the cost of the project base on a set amount per hour for timesheets plus expenses. A project may not always have expenses. A project has zero or many timesheets A project has zero or many expenses I need an SQL statement to return total time for a project and total expenses so i can do my calculations then in php. I'd prefer do this in one SQL statement. What i have so far: SELECT project.name, SUM(timesheet.time) AS time, SUM(expenses.amount) AS expense FROM project, timesheet, expenses WHERE project.id = timesheet.project_id AND project.id = expenses.project_id GROUP by project.id ==TABLES== PROJECT id PK name TIMESHEET id PK project_id FK time EXPENSES id PK project_id FK amount This will work fine for all projects that have both at least one expense and one timesheet. But if they are missing one then it returns no row for that project and therefore i can't display the cost. There are other pieces of data but these are the only important ones. I can do it in two different SQL statements but i really think there has to be a way to do it in one.
View Replies !
How Many Fields Of A Table Are Recommended?
May be a stupid question, but just want to know, how many fields/columns are recommended ? I know, it depends how many someone needs, but incase if a person needs alot of fields, then for example, is it okay to make 200 fields of a table of mysql database? or its not good to add so many fields ?
View Replies !
Authentication
I'm new with mysql and I feel very stupid for having to ask this but I'm trying to program against it and I don't know what my username and pw are. When I installed it, I put in a pw so I'm pretty sure I know what that is, but what is the username I should be using when trying to execute sql commands against it with php?
View Replies !
How To Get All Distinct Words From Many Fields Of A Table
I have a table with many text and varchar fields and I would like to get all distinct words from these filelds. For example: table Pet: id int(10) unsigned legende text notes varchar(255) #id #legende #notes 1 mysql is very very good' is it true 2 just mysql test I would like to get a list (sorted if possible) like: #word #occurence number #id good 1 1 is 1 1 just 1 2 mysql 2 1,2 test 2 1 very 2 1
View Replies !
Query 3 Fields In Same Table As If They Were One Field
I have a very simple test table which I am using to try and get the query right: id | col1 | col2 | col3 | 1 | 3 | 2 | 3 | 2 | 4 | 3 | 1 | 3 | 2 | 1 | 1 | 4 | 3 | 2 | 4 | 5 | 3 | 1 | 3 | 6 | 3 | 1 | 3 | So the table contains and id field and 3 integer fields - each of the integer fields contains an integer between 1 and 4. What I want to achieve is the value of the most frequently occuring entry across the 3 integer fields, as though all 3 fields were actually one - in my example, this would be 3 as there ar 8 occurenecs. I will also require the lowest occuring value also, in this case it is 4. I cannot work out to query all 3 columns simultaneously to obtain my results - I have been looking at joins but cannot work out how to join within the same table
View Replies !
Mysql5.0 And Phpmyadmin Cant Add Fields To Table
I have loaded the phpmyadmin the latest version to manage my databases in mysql5.0. The problem I am having is when go to create a table and attempt to add fields to the table I get an Error MySQL said The field count is empty I aslo just tried to delete the database and get this error message. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 I been looking for last few days on a way to fix this problem. I have only been able to create database but that is it. I am the only user with all privileges.
View Replies !
How Can I Compare Two Table Fields In One Query?
This is my problem: "SELECT * FROM a,b WHERE a.field LIKE b.field " --> this works "SELECT * FROM a,b WHERE a.field LIKE %b.field% " --> this works not My Question: Is it possible to compare two filds with the substitute symbol % if the fields are stored in different tables?
View Replies !
Multi Results From Table Fields
I'm trying to get results from querying two tables - let's call them users and profiles. Within the profiles table, there are 3 fields; the user id (uid), a question id (qid), and the text value of the response (resp). What I'm trying to do is to get the user information from the users table (SELECT * FROM users), dependent upon answers given to two separate questions. Example: the user completes 3 questions, with a qid of 1, 2 and 3. Broadly, I'd like to do a SELECT using LEFT JOIN profiles ON users.uid = profiles.uid -but- I want to pull the info WHERE the response to qid 1 = "20 - 25" and qid 2 = "Male", thereby getting a list for which both the conditions apply (i.e. a list of users who are male and aged between 20-25). I'm failing at pretty much the first hurdle; anyone has any suggestions before I go the php looping route?
View Replies !
Using Dateadd To Compare Two Fields In A Table
I am trying to find out if there is a way to query a table for the following criteria: (I ma using CF so the variables enclosed in octothorpes) SELECT endDate, extendedDays FROM foo Where endDate>= #now()# OR endDate + extendedDays >= #now()# The columns are formatted as: endDate:datefield extendedDays: integer So today is prior to end date or prior to endate plus 5 days (if extendedDays = 5) Is this possible? I am having troubles thinking it through.
View Replies !
Import Fields To A Table From A Sql File?
I have a vbulletin forum and to be honest I don't really need to know a lot about mysql. But I have a problem and I was wondering if someone could help me out please? I lost 7 fields from one of the tables in the forum database. I have restored a backup of the database to my local machine and I have saved each of the 7 fields to my PC as sql files. I've been using phpMyAdmin with the hope that I can import these files to the live database and restore the single fields. Is this possible? It's not feasible for me to restore the entire database because there's too many new threads and users since the last backup.
View Replies !
|
TRACKER
