MYSQL :: Simple: Adding New Fields To A Table

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

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

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?

Adding Two Fields Together
I have two fields, on called Price one called Qty, i want the Third Field to be Price * Qty.

Is there any way of doing this.

Adding 1 To An INT Fields Value
What is the SQL command for adding 1 to a specific field. This all that I can come up with:

UPDATE poll SET av1 += 1.

Is Adding Two Fields Together In A Query Possible?
Is something like this possible:

startDate, endDate, stop and eventType are all columns in the database.

SELECT * FROM `calendar` WHERE startDate >= ?' and (endDate + stop) <= ?' and eventType = 'Days'

Copying Fields & Adding
Issue 1:

I have a table with say 10 fields, one is called filename, another title. the title field is empty, the filenames fully populated. what i require is for the filename to be copied into the title field.

my thoughts of 2 possible solutions, but requiring a query.....1. copy field filename to new field called filename_copy, then rename to title and delete existing title field. 2.have a looping query to pull in each row by turn, copying filename into title field?

Issue 2:

I want to add multiple keywords to a fields but at different times.....eg using SET i can add 2 keywords to each item WHERE filepath = 'x', but i dont want it to overwrite whats already in the field entry, so i can select some rows and add 3 keywords, then at a later time select 50% of them and add say an addition 3 more keywords.

Adding A Field Between Fields
If i had the following list of fields in my database:
MemberID
Surname
DatofBirth

How would I add FirstName between Surname and DateofBirth?

Adding Datetime Fields
I have a table with
col1 col2 both are datetime columns

I made a derived table to display
col1, col2, col2-col1 as difference

what I want to do now is add all the row values of difference together. The sum function butchers it and date_add does not seem to be for this.

Adding Index Fields
in this situation:

create table xyz as
select a,b,c,d, from mytable
group by a,b,c,d

i wish to add an index to the table xyz on fields a and b

how to do?
(mysql 5.0)

Adding Dollar Sign To Fields
i have a few different fields, in decimal, how would i put the $ sign in front of the numbers, what would i put into query?

Adding All Fields Where Rule Applies
I need to add up and get the sum of all of the "price" entries in a db... each row in the table has a price and a id... i need to find a way to do this... for the example ill put it in structure english type style..

add up each PRICE where id is the same as THIS return final amount to variable

Anyone have any ideas?

Adding A Date Field, Need To Populate The Fields
I have a table and until now there is was date entry for the fields. I have added a timestamp (column "time") and from now on, every time I insert a new entry, the date/time will be recorded.

The thing is, I want a date/time to exist for the old fields. I have about 250 fields and I want each field to be assigned a timestamp based on the fields alphabetical order (ordered by "title"). So, field with title "aaaaa title" will have a timestamp of today. A field with a title "zzzz title" will have a timestamp of 250 days ago.

It doesn't have to be exactly like this. My main goal is to assign dates to my old fields. Anyone know how to do this?

Putting Data Of Several Fields Together And Adding Leading Zeros
the following scenario:

Database Table:
prefix | suffix | serial | digits |
a z 1 5
k d 3 5

By adding prefix character first, then add leading zero's and with serial number and finally end with a suffix character.

To explain things clearer. A statement to put the data together into a single result: i.e. Row 1: a00001z
Row 2: k00003d

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

Adding Visual Table To MySQL Table
I just successfully created my table that is up and running. I also created an HTML form that allows me to submit new information. I have seen people that created tables and when they view the table they add style (color, fonts, etc..) to it as well as actually put the data in a visual HTML table. I was wondering how I would do this.

Simple Create Table Error #1064?
I'm getting the following error:

SQL Error DB function failed with error number 1064
You have an error in your SQL syntax.
SQL =

CREATE TABLE IF NOT EXISTS `jos_random_quotes` (
`id` INT(11) NOT NULL auto_increment,
`author` TEXT(100) NOT NULL default '',
`source` VARCHAR(250) NOT NULL default '',
`year` YEAR(4) NOT NULL default '2000',
`quotation` TEXT(800) NOT NULL default '',
`published` TINYINT(1) NOT NULL default '1',
`ordering` INT(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1

MySQL Database Version: 4.0.25-standard

Error Adding Table
Im trying out an application, there was a table that failed to be created..

MySQL
CREATE TABLE apa_albums (
name VARCHAR(255) NOT NULL,
path VARCHAR(255) NOT NULL,
nb_photos MEDIUMINT UNSIGNED,
nb_comments INT UNSIGNED NOT NULL,
nb_views INT UNSIGNED NOT NULL,
TIME TIMESTAMP,
CHECK TINYINT UNSIGNED DEFAULT Ɔ',
ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID)

Line 8, I thought there wasn't a check in mysql? Also, when I take that line out, I get an error on ID as well.

Adding A Column To A Table
I am looking to add a column to a table, however I only want to add it if it doesn't already exsists. Is there a command to do this?

Adding Table With PHP Code ?
If I use a constant rather than a variable, it works. Can anyone tell me why this doesn't work? I'm running this on a RH9.0 with PHP 4 and MYSQL 3.23.54. Code:

Adding Info To Table
I have a current list of names in a table and I am trying to add another list to it without duplicating the names. is there a way to do something like this?:

"INSERT INTO master_list VALUES ($namefromnewlist) if $namefromnewlist IS NOT IN master_list"

Adding Table To Replication
I am trying to add an InnoDB table to an existing replication
environment. Having some trouble. The slave my.cnf is using
replicate-do-table. I have tried creating the table as MyISAM on the
master and than using LOAD TABLE x FROM MASTER with the intention of
then altering the table type. Not having much luck and can't seem to
find any information on how to do this.

Php Not Adding To Table And No Error
$quest = $_POST['Quest'];
$a1 = $_POST['Answer1'];
$a2 = $_POST['Answer2'];
$a3 = $_POST['Answer3'];
$a4 = $_POST['Answer4'];
$a5 = $_POST['Answer5'];
$poster = $_POST['Poster'];
$id = $_POST['q_id'];
$con = mysql_connect('localhost', 'yawe_site', 'r5kjp9lk');
mysql_select_db('yawe_Site', $con);
if($id == ''){ $id = rand(1, 9999999);}
mysql_query('INSERT INTO `Polls` (`ID`, `Question`, `Answer_1`, `Answer_2`, `Answer_3`, `Answer_4`, `Answer_5`,
`Poster`, `Q_ID`) VALUES (NULL, ''.$quest.'', ''.$a1.'', ''.$a2.'', ''.$a3.'', ''.$a4.'', ''.$a5.'', ''.$poster.'', ''.$id.'');');
mysql_close($con);
echo mysql_error();
echo '<br>created poll';

Output from this is
<<<<<< This is a Line break
created poll

Adding Foreign Key To Table
How do i go about doing this in mysql, Ive checked on the internet but I can't find the syntax for it.

Mysql, Simple Table Join - How To Loop Through Records
I've been gradually learning and using PHP for about a year and I've now decided to start learning MySQL

I've got my first database up and running, and I can display records, edit/delete/search and sort records from that database. But I want to make things a bit more complicated and start making related tables.

I've decided to re-invent the wheel and make a very simple content management system for myself, just to see if I can.

I have a table called posts, which is what I enter my table content into, consisting of headline, creation date, post text and an id field.

I've also set up a table called comments which will allow users to add comments to my posts. In the comments table there's a field called id_post - which is the id of the post that the comment relates to in the posts table

I've already got my loop set up to loop through my posts records, but I'm confused about how I go through looping to get the comments records.

How do I do the loop?

I thought about doing an SQL query to the comments table nested inside my loop through my posts results

SELECT * FROM comments WHERE id_post='$id' ORDER BY stampcreate

But I assume this isn't the most efficient way of doing things....

Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:

-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC

while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id

echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------

I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.

Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?

"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.

Simple Fast Way To Grab The Highest ID From A MySQL Table?
I have a MySQL table on my web server (external web host) that has an
auto-increment integer as it's primary key field.

Is there a quick PHP function that will get me the highest ID in the table,
that is, the ID that belongs to the last record added.

Note, I need it to work across and between connection session, not from
within the same connection session.

Adding Multiple Rows Into A Table At Once
I have to the stage of being able to easily(?) add new rows to my tables using the command

insert into zone_vs_weight
(low_mass, high_mass, zoneA, zoneB)
(1, 250, 4.50, 6.35);
It seems to work well for adding single rows but can we use it to add multiple rows i.e.

insert into zone_vs_weight
(low_mass, high_mass, zoneA, zoneB)
(1, 250, 4.50, 6.35)
(low_mass, high_mass, zoneA, zoneB)
(251, 500, 6.50, 8.50)
.........;
Is there another command apart from the input file insertion or using PHP?
I have been using K. Yank's fine book 'PHP & MySQL' as a good primer but it doesn't seem to mention whether it is feasible.

SQL Error While Adding @ Sign To Table
I was trying to insert my e-mail adress to table and got this error:

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gfhc.jh

My code:

myCommand = New MySqlCommand()
myCommand.CommandText = "insert into mytable (BestEmail) VALUES (" & BestEMail & ")"
myCommand.CommandType = CommandType.Text
myCommand.Connection = myConnection

Where BestEMail = "flowers@bredband.net"

Adding A Field To A Database Table
When adding a field to a table using ALTER TABLE is it possible to check if
the field already exits before the ADD command is run? If so how do I do
this?

Adding New Field To Table That Already Exists??
here is the syntax i used to create a table with certain fields:

CREATE TABLE article (
id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
section_id int(10) unsigned DEFAULT '0' NOT NULL,
category_id int(10) unsigned DEFAULT '0' NOT NULL,
approved enum('N','Y') DEFAULT 'N' NOT NULL,
title varchar(100) NOT NULL,
author varchar(50) NOT NULL,
email varchar(50) NOT NULL,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (id),
KEY id (id, section_id, category_id)
);

now since i have done that i want to add a few new fields:

INSERT INTO TABLE article(
score ??? unsigned DEFAULT '0' NOT NULL,
genre varchar(50) NOT NULL,
fuser varchar(50) NOT NULL,
);

firsty, is the above syntax correct (i just want to add new fields, not delete any existing ones or their content)? secondly for the field 'score' that will be a 2 digit number but not an integer (will have decimal place) - what do i put instead of ??? there?

Adding A Foreign Key Into An Existing Table
May I know how should I do that?

The SQL statement should be:

CREATE TABLE USER_SYSTEM_SECURITY (
USERID VARCHAR(32),
HOSTID VARCHAR(32),
HOME_DIRECTORY VARCHAR(32) NOT NULL,
ACCESS_RIGHTS INT(3) NOT NULL,
PASSWORD_EXPIRY_DATE DATE,
ROLES VARCHAR(32) NOT NULL,
PRIMARY KEY (USERID, HOSTID),
FOREIGN KEY (USERID) REFERENCES MEMBER (USERID) ON DELETE CASCADE,
FOREIGN KEY (HOSTID) REFERENCES SYSTEM (HOSTID) ON DELETE CASCADE);

But I forgot to add in the last row during the table creation.

Adding A Table To All Exisiting Databases
I've been using mysql for 3-4 years now, but am a bit rusty. I'm trying to add to an app that I wrote for my personal business a few years ago.

What I want to do is add a new blank (or populated) identical table to all the databases (except perhaps to the databases 'mysql' and 'test' which come with the system), using the CREATE TABLE command. I already have this command stored as a file (such as addtablescript.sql).

For example I could do:
mysql -u root -p dbname < /pathto/addtablescript.sql

But this would add the new blank table to just the database "dbname".
I want to add it to all of the databases without having to do this manually for each one, so I can write a small batch script for my wife, etc., and she won't have to do the hard work, also others that use the program.

Is there some way I can globally add the table easily to all the databases?

Since I also use php, perhaps if the above is not possible, I could run a loop in php to do this with mysql_fetch_object and then an exec command for each database,
but I'm trying to find the easiest way.

Adding And Populating A New Column In A Table
I would like to add a new column to an existing table, with the following caveats:

1. For existing records, a fixed value (same value for every record) should be added in the new column.

2. This value should not become the default value for records created in the future.

For reference, the column I want to add is Year. Existing records should have this set to 2006, however future records will be set to their respective year.

I know I can do this in two steps - ALTER table to add the column and then UPDATE to add the value 2006 to each record, but I was wondering whether there was a way to do it all in one go.

Adding New Column In A Table With Data From Another
I have been trying to accomplish the following aince quite some time.. I have tried various combinations of Union, Merge, Insert.Select, etc..

Tables:
table_1 with 22 data columns, and a autoincrement primary key column.
table_2 with 1 column (newtimenumber in query below) with double values, no primary key. (Although i can generate it)

Relation between tables:
The number of rows in both the tables are same, and if required can be mapped on one to one basis. (Not yet done as i have not assigned primary keys to the table_2; but row 1 of table_1 corresponds to row 1 of table_2)

The table_2 is generated by performing a set of calculations on one of the columns in table_1; hence they correspond to each other.

Problem:
I want to insert the all the rows in table_2 into a new Column (newtimenumber in query below) in table_1; but maintaining the corresponding row by row relation.

Tried Insert..select :
INSERT INTO dbname.table_1 (newtimenumber) SELECT swapper.newtimenumber FROM dbname.table_2

this did not work, and generated new column in table_1 but the rows were appended at the end.

I would be very glad if some one could help me with this. I have tried many things, but i am not getting something right.

Adding 1000 Rows To A Table
Right, we have a bookings table (first off, I realise how poor this database design is but currently I don't have the time to re-write the entire system that relies on it). The fields in the table are:

bookingID
bookingDate
bookingEstateID
bookingCustomerID
bookingConfirmedID

The bookingDate field contains a date, currently there are 1000 rows ranging from 01-07-2004 to 15-07-2007 (or about that). When a new booking is made the corresponding date rows are marked confirmed. I need to add 1000 more rows to the table with the date increasing correctly by one for each row added. What is the easiest way to do this?

Adding A Random Number To A Table Field During Import?
By habit, my ID fields are usually md5() generated - 32 character varchar field.

When I import data using LOAD DATA INFILE, is there a way to generate that number for the have a table field since it is not in the infile?

If not, is there another mechanism for importing that would allow for that?

If not, are there operational security issues you guys are concerned with using auto-increment like being bale to guess another record number?

Adding Missing Rows In Table In 1 Select (+ Join) Command.
Hi all! Here's what I need to do :

I have two tables :
A B
a b c d
---- ----
1 z 1 k
2 x 5 l
3 c 6 j

I need a SELECT with JOIN that would give me :
A
a b
----
1 z
2 x
3 c
5 NULL
6 NULL

so I need to add the missing rows from the A.a and B.c JOIN,
how can I do that ?

I can't use a Union 'cause I can't use MySQL version 4.

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 ?