MYSQL :: Two Similar Fields One Table

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

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 ?

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

Similar To IN
okay so like, if i do

blah blah blah IN ('x','y','z') more blah i get results if any of the 3 values x,y or z match, right?

so is there an easier way to return data from somewhere, only when all 3 match, sort of like that IN statement, like...
blah blah blah ONLY IN ('x','y','z') more blah ?
or do i have to do all 3 individually like
where blah = blah AND blah = blah AND blah = blah ?

Most Similar
I am working on a PHP script, and I want it so it searches a database for something (such as "MySQL is very fun to use"). Now I want it so that if there was no result called "MySQL is very fun to use", then it searches for the most similar entry (such as "MySQL is fun to use" or MySQL is easy to use".) Is that at all possible, and if it is, may somebody explain it to me, or point me to a place that does?

Also I understand basic MySQL (how to do searches, and other simple stuff like that).

Also if this isn't possible, is there any way to reverse wild card (wild card the results instead of the search). This would mean that if you searched for "MySQL is fun and easy to use", then "MySQL is fun" could show up.

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?

Similar Entries
What is the best way to find similar entries? If I have some data like "What processor company is Apple going to be using in the future? Intel."
I then want to be able to scan other entries with other data and find entries that are similar. One way would be to take words greater than 3 letters and then try to find those, but I'm not quite sure how to do it.

Similar Names
I'm trying to retrieve a list of similar customer names from a database table (e.g. Inca, Inca Ltd and Inca 12 etc). I've created a new table called 'dups' with all 20000 cust names but with the last 4 characters removed from each.
I've tried the statement below but obviously this isn't correct. Anyone with any ideas?
SELECT c.cust_name FROM customer c, dups d WHERE c.cust_name LIKE "(d.cust_name)%";

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

How To Set Similar Character Set In Ad And Mysql
I am connecting users from AD to Sun Identity Manager (IDM), and the IDM uses MySQL. The strange thing is that Norwegian characters keep showing up as ? or other strange characters like a Y with double line over or A with ~ on top and some other character next to it...

We have set the character set to utf8 and collation to utf8_bin, but we get the same strange characters as we are when running latin1.

AD is running a character set called ISO-10646 (similar til unicode), and our theory is that it might be a conflict with the character sets between the different resources.

Anyone had similiar problems?

Search: Select Similar, Not Like
I want to make a search feature to the content of my website. I suppose, the classic way to do that is by making a form where the typed value $typed becomes a part of a select statement like this:

select from mytable where field like '%$typed%'

I would like to make a statement which allows for e.g. mis-spelling or different forms of a word. Say that the user searches for "woman" - and there happens to be content containing the word "women" instead ...

Is there perhaps also a way of ordering the results by similarity? E.g.:

select from mytable where field like '%$typed%' order by similarity

Similar To Oracle Forms
Do we have something like MySQL forms in the way we have Oracle Forms?

Similar To SQLyog For MySQL On MAC OS X 10.3
I've used SQLyog and MySQL a whole lot on Windows platforms, but now I'm trying to do the same on the Mac for a little demonstration.

where I should look for some free software which provides the same stuff as SQLyog (GUI-driven table and data manaagement)?