MYSQL :: Table Fields

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

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

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");

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

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.

Storing German Letters In Table Fields
i need to store some informations in german in one of my table.I am using Mysql5.0 .

How can i make this is possible. Currenltly the german letters showing as junk character.

POST Fields From Dynamic Table List
Scenario:
I'm connecting to "points" table in a Records posting form.

SELECT pointCode, pointamt, pointcount, amt_type FROM points
Now I do the select list and tell it to only display "pointName" in the form

So far so good. Then I get lost lol. I don't know where to go from here to get those values of the pointCode, pointamt, pointcount, and amt_type set up for POST once the user has made their selection. I'm thinking they are locked up nice and snug in my $row var but not sure and if so, how do I get them OUT?

Joining Multiple Fields To A Single Table?
I have 1 table with 2 columns, 'id' and 'name':

tbl_names:
idname
------
1Bob
2Jeff
3Fred
4Joe
5Bill

I then have another table which contains several fields which hold id's
from the above table:

tbl_output:
idperson1person2person3
-----------------------
1231
2543

I need a query that will return the names for the specified id from
tbl_output.
If I have just one 'person' field in tbl_output I would do it with an
inner join like this:

SELECT name from tbl_names
INNER JOIN tbl_names on tbl_names.id = tbl_output.person
WHERE tbl_output.id = ?

but I can't figure it out when theres multiple fields to be joined from
the same table...e.g I want to specify tbl_output.id = 1, and it give me:

person1person2person3
---------------------
JeffFredBob

Time/Space Costs Of Adding Fields To A Table?
I've got a products table for the site I'm working on, and have been asked to extend it considerably so as to accommodate a smallish subset of items.

For 20,000 or so of the products, these new fields I'm adding won't be used. For about 3,000 they will.

I would be adding the following fields:

2 varchars (50, 50)
8 Floats
1 text (average 800 bytes)
1 tinyint

So, assuming 40 bytes for the varchars, that's an average 913 bytes per entry.

My question is, for rows that do not have values, will those 913 bytes still be spoken for?

Maybe this is moot -- if my calculations are right, 913 bytes x 20,000 items is just around 17k and not a tremendously big deal? Are there any speed hits? I'm pretty careful about never doing a SELECT * FROM, so that shouldn't be an issue.

Would it be useful to put all these new values in a new table, linked to the unmodified product table by the product's UID?

Limit To The Number Of Columns/fields That Can Be Held In A Table?
is there a limit to the number of columns/fields that can be held in a table?

Database Access Speed: More Tables Or More Fields In A Table?
I have a question: it is better to create a database where there are more tables or more fields in few tables? What I want is: high speed accessing, indexing and searching. So what do you suggest me?

Updated Table Fields Rolling Back After Update
I'm having to correct some scripts written by a collegue who is presently not around. I had to update 2 fields with over 13000 rows. The update do work well but the problem is that somehow, the initial values in the fields do roll back in again - kind of auto updating to the former values itself. Any ideas what could be wrong? Any way round it?

Selecting Language Depended Fields Through Multiple Table References
For a long time i think i need your instructions dear people on sitepoint. I'm making a sql query for getting user info, but i dont know how to handle this situation:

SELECT
user.name, user.firstname, user.lastname, ___ as user.city, ___ as user.province
FROM
user, languagefield, city, province
WHERE
user.id = %i
AND languagefield.language = %i
AND city.id = user.city
#tricky part
AND city.name = languagefield.id
AND city.province = province.id
AND province.name = languagefield.id
So i'm trying to get users first and last names plus city and province names from language tables. Is it possible with one query? How would you make SELECT and WHERE clauses?

Im using this db tbl scheme:

table : city
id (int4) primary key
name (int6) ref languagefield.id
province (int4) ref province.id

table : province
id (int4) primary key
name (int6) ref languagefield.id

table : user
id (int4) primary key
firstname (varchar32)
lastname (varchar32)
city (int4) ref city.id

table : language
id (int3) primary key
char2name (varchar2)
name (int6) ref languagefield.id

table : languagefield
id (int6)
language (int3) ref language.id
value (varchar255)

id + language unique

*****

I'm also thinking and planning to set more current language depended content to my application, so this is very important part of my design, 'cause it could be reused when dealing with other tables and fields.

Using DATE_FORMAT To Format Fields Returned From The LEFT JOINed Table
I am attempting to create a query that joins a table to my main table, but I would like to format the date and time fields in the joined table using DATE_FORMAT. I've done successful joins before when the all columns or fields * are selected, but I haven't created a join where you only want to return specific fields from the joined table.

Example:

"SELECT DATE_FORMAT(table_1.date_1, '%m') AS month, DATE_FORMAT(table_1.date_1, '%d') AS day, DATE_FORMAT(table_1.date_1, '%y') AS year, DATE_FORMAT(table_2.date_1, '%m') AS month2, DATE_FORMAT(table_2.date_1, '%d') AS day2, DATE_FORMAT(table_2.date_1, '%y') AS year2 FROM table_1 left join table_2 on table_1.column_a = table_2.column_a WHERE table_1.column_c = $a_variable"

This query is only returning results that match BOTH conditions, so NULL returns from table_2 are not being disregarded like they should be when using LEFT JOIN (I think). Can someone point me in the correct direction?

Delimited Fields Or Unused Fields
I have a table which has, for instance, 5 fields and maybe people only need the first one to be filled in often.  So 4 might well be empty.  Is it better to join them into 1 delimited field or leave them as their own fields?
And - if I have some fields that are only relevant to some types of rows, is it better to have 1 field for them delimited, or have them as separate fields?  (the same issue there really)

The other side of it is - is it better to split such delimited fields in the mysql call, or split them afterwards in php?
I might know which is easier though I can do it either way now I've researched, but I don't know which is better.  I also don't know how big this db will get or how often these fields won't be used so it comes down more to the overall design issue of which is better.  I'm thinking efficiency, speed, db load, and so on.

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.