MYSQL :: Fields In Users Db Table For Authentication

Create Users Table For Users To Log On Or ...?
I'm pretty new to MySQL, what I was wondering is, for web systems that you need to login to, do developers generally create a users table and store all their users in there, or do they create users such as the root user in mysql ? If so, how do you add extra fields such as firstname last name etc and how would you go about putting them into groups?

Mysql User Table For Authentication
What is the best way to store usernames/password information within a mysql Table? Is there a way to encrypt the columns that house this data?

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*

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)?

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

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).

Table Of Users Having Many To Many Relationship With Itself?
i hope to implement a user subscription feature on my webdatabase, where users can subscribe to each other's postings.

Can i achieve this by having one table to map the USERIDs to each other, and then relate this to the main USER table?

Currently I have two user tables that are exact mirrors of each other, and then a junction table to relate the two, but im beginning to think this is kinda dumb.

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?

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.

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

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?

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:

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?

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).

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.

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

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.

Fields In Table
How do I fix this error? My tables have data.

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 ?

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.

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

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 ?

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.

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?

Adding Fields To Table
Once a table has been created, can additional fields be added? What would be the syntax?

Update Fields In Table
I am trying to update multiple records,rows,fields in a table. Below is what I am placing in the sql field in phpmyadmin. It works with one update line but not more. What am I missing here?

UPDATE Products SET TitleTag='U.S. Air Force Retired Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=28;

UPDATE Products SET TitleTag='Vietnam Veterans of America Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=29;

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.

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?

Counting Fields In A Table
is there a way to get the field count in a table in MySql?

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?

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

How Can I Count Non-empty Fields Of A Table
there's a form with some fields, after submitting, all non-empty inputs are in mysql table's field. how can i count how many are non-empty fields in a row of mysql table?

Prefix Table Name On Result Fields
this has to be a simple one, but it has me stumped.

I am doing a join across 10 tables. Some tables have conflicting field names (i.e. they are the same). When i access the resultset by fieldname, obviously at times I get a conflict. I cannot access the fields by the index position.

my join is in this form:

Select * FROM table1 LEFT JOIN table2 ON table1.id = table2.id2;

Now, how can I get the table names to prefix the returned columns?

I am using the creole framework for anyone who may be interesed, but have access to the RAW SQL.

Simple: Adding New Fields To A Table
i'm VERY VERY new to mysq! i have an existing table with 4 simple fields: id, lastName, firstName, and email.

i'm trying to create a registration page, and need to add a few more fields to the table: StreetAddress, City, State, and ZipCode.. i just cant remember what the sql statement is, or where to find it, and i'm not sure "what" its called - and that makes it hard to google.

i'll add the new feilds using the command line, or more likely just type the code directly into phpMyAdmin. the table name is tblCustomers

hopefully i've explained myself well enough. i know adding new fields to a table has to be about as basic as it can get.. but i cant remember how to do it!

edit:
so i did find the code i used to create the table

create table tblCustomers(
AdminID int unsigned not null auto_increment primary key,
LastName varchar(50),
FirstName varchar(50),
Email varchar(80),
AdminPW varChar(20)
);

insert into tblCustomers values
(NULL,"Smith","Bob","bob@example.com","newpass"),
(NULL,"Jones","Bill","bill@example.com","newpass"),
(NULL,"Doe","John","john@example.com","newpass"),
(NULL,"Rules","Ann","ann@example.com","newpass");

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?

Update One Field With More Fields From Another Table
I have a select count() that seams be ignoring one clause. Data:

Table:
ID CA-Char CB-Int
1 dsfsd 6
2 dsfsd 0
3 dsfsd 1
4 sdrtt 1

SQL is:
"SELECT count(id) FROM Table WHERE CA-Char = 'dsfsd' AND CB-Int > 0"
Returns 3 - Should be 2

Listing Table Content By Fields
i've got a mysql db with a table called tshirts . Im trying to display all the shirts by category on one page in a table 2 colums wide, been told i need a do while loop ? ive looked up on php.net but didn't really make much sense to me.

while($row= mysql_fetch_array($rs) )
{
$listt .="<td width=25%><div align=center>".$row["tshirtname"]." ";
$listt .="<img src=".$row["mainpicture"]." >";
$listt .=" ".$row["tshirtdesc"]." ".$row["price"]." ";
$listt .="</td>";

the above code just displays all the shirts in a long horizontal line, thats as far as i can get, ive not got that much knowledge of php or mysql so sorry if this is a stupid question,

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.

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

Mysql.event Table Has 18 Fields Instead Of 22
I tried to find out all fields of mysql.event table but there seems to be no info. Error message on 5.1.22 under windows: Column count of mysql.event is wrong. Expected 22, found 18. The table is probably corrupted.

2 Fields Combination Unique In Table...
So I need two unique fields in my table, but they should not be unique separately, just a combination of values in this two fields... Let's say I have table with fields book, page, text... I can have more then one book #1, more than one pages #1 but only one combination of book #1 and page #1! But I have page #1 for each book!

How to restrict this in MySQL?

JOINing From Multiple Fields To One Table
I have two tables in a MySQL database. One has the publication data (title, ISBN etc), and the other has the data on the authors that wrote it (first name, second name, etc.)

I am trying to produce a single recordset that will show the authors' names (first name + a space + second name). The staff.staffID corresponds to publications.author1, publications.author2, etc.

The problem I have is that there are potentially 5 authors for each publication record, so when I try to LEFT JOIN them all them to the staff table, I get a '1066 not unique table / alias: staff' error - I can see why this is happening (becuase of the 5 left joins onto the same table) but how should I change the code to get the results that I need? Code:

What Is The Maximum Possible Number Of Fields In A Table?
What is the theoretical and practical limits of number of fields in a table?
Say I have 1000 or 10000 integer fields in a table, does the query processing speed decrease?

Simple Table With 3 Text Fields
I am trying to setup a simple table with 3 text fields. I amusing Myphpadmin to do this, this is my sql statement:

CREATE TABLE `zones` (`country` TINYBLOB NOT NULL, `rate` TINYBLOB NOT NULL, `time` TINYBLOB NOT NULL, PRIMARY KEY (`country`))

But the extremely unhelpgful error message gives me:

BLOB/TEXT column 'country' used in key specification without a key length

I have tried specifying a length but then get this error:

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 '(25) NOT NULL, `rate` TINYBLOB NOT NULL, `time` TINYBLOB NOT NULL, PRIMARY KEY (`country`))' at line 1

Order Of Fields In InnoDb Table
During my extensive reading a couple of weeks ago,I read that MySQL tables should have their fields in a specific order.All the foreign keys should be first followed by the 'hard data' fields.

explain if this is, in fact,necessary and, if so, why.I can't recall where I read it or I would go back there again.

said it should be like this:

unique_id | business_id | address_id | first_name | last_name |
00001 | 00005 |00003 | Joe | Soap |

Replace A Character In All Fields In Table
I need to remove a " character in all fields in a table. Does anyone have a sample on how i achieve this?

Return Fields Corresponding To Comma Separated Ids In Other Table
I'm attempting a query that gathers product data for a particular
product id.

One of the items is designer(s) which can be more than one.

The product table has comma separated id's of the designers in the
designers table (which has fields like: id, fname, lname).

How can my select return the possibly several designers for a product,
with the rest of the row data, like price, name, etc.?

I'm using 4.01.