MYSQL :: Assigning Userids To Existing Table

Table Joins On Existing/Non-Existing Data
I have a question that involves a joining tables on potentially non-existing data, but we will know the expected values whether they exist or not.

Say we're keeping statistics on any given number of sports for attendances over weekly time periods. There's two tables to hold data.

------------------------------
TABLE: StatisticTypes
id
name
------------------------------
TABLE: Statistics
id
statisticTypeId
value
date
------------------------------

Then, say, we have three entries in the StatisticTypes table for types of sports we're keeping data on...

Assigning Foreign Key After Completion Of Table
I know how to assign a primary key after completion of a table does anyone know how to set a foreign key?

Assigning Foreign Key After Completion Of Table
I know how to assign a primary key after completion of a table does anyone know how to set a foreign key??????

Help In Assigning Unique Keys To Table Entries
Allow me to share some design problem that I am facing.
I have the following table definition:

CREATE TABLE `image_info` (
`Image_ID` int unsigned not null,
`Title` varchar(255) default '' not null,
PRIMARY KEY (`Image_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
This simple table holds information about images. Each image has a title.
Note that the primary key was NOT defined as auto_increment (explanation below)
Also note that the table engine was defined as MyISAM (a system requirement)

The range of image IDs that can added into the table is split into 2:
1) IDs 1,000,000 and above are reserved for images that come from an external program
which defines the IDs.
2) IDs 1 - 999,999 are reserved for images uploaded by users in runtime in my site.

This means:
When I add an image from my application (1), I already know in advance what Image ID I need
to use and I can safely add it using:

insert into image_info (Image_ID, Title) values (1000010, 'I am a photo from an external program');
I know that image ID 1000010 is reserved just for that photo and so there won't be any duplicates.

When a user uploads a photo to my site (2), that photo needs to be stored in the table
with the next available ID which is < 1,000,000.
I use the following query:

select max(Image_ID) from image_info where Image_ID < 1000000;
I then add 1 to the value I am getting and this is the ID I will use for the image uploaded by the user.

The problem occurs when too many users are uploading photos at the same time.
This is a problem of atomicity. 2 Users may be assigned the same "free" image IDs and then
one of the queries will fail when adding the record to the table because of duplicate IDs.

I cannot change the order of the ID ranges (making 1 - 999,999 for my external program and 1,000,000 and above
for images uploaded by users), as this is a system requirement.
If I didn't have this requirement, I could have set auto_increment on the image_id field.

I am looking for a solution to my problem.
Can anyone think of one?
Maybe using an additional table which uses auto_increment to assign IDs?
I am developing in PHP, so maybe I need an application-level solution on top of MySQL

Assigning Primary Key In MySql After Completion Of Table
I finished completing a large table with 155 rows in it. But I forgot to assign a primary key. Does anyone know how to do this? i 'm think I might need to use an alter function but can't figure out what to put in the command.

Copying Column Names And Properties From An Existing Table To A New Table?
I've got a table with 42 columns, set to different types (string, datetime, date, integer). It has 8 million rows. I want to make a new table with the exact same columns, but with no data in it. Is there an easy way to do this without re-defining every column?

How To Add A New Row To An Existing Table?
I am able to create database, tables, inserting values, and so forth. My question is this:

Let's say I have a table on the list of items I owned, and some time later, I need to add a new item between after row 12 and before 13. How do I do that? I did look at MySQL 5.0 Manual and didn't find anything similar to what I wanted to do.

Foreign Key To Existing Table?
Is it possible to add a foreign key to a table that already exists? Or do I have to drop the table and recreate it?

Insert Row Into Existing Table
I have a table with rougly 70 threads, I now need to go back and insert a couple new entries into my table, but they cant go at the end, they need to go in the middle, say like ID 40,41,and 42. How do I do this?

Copy Of An Existing Table
MySQL: How I make a copy of an existing table in phpMyAdmin or SQL ?

without copy data
with copy and data

Import Data Via Ssh Into An Existing Table
I want to import data from several .sql files into an existing database, into one table.

I tried using the following command:
mysql -e [sqlfile] [db_name]

But the problem was when I imported the next file, it would overwrite the previous imported data.

Could anyone let me know how to do this correctly, please?

Add Multiple Rows To An Existing Table?
I have a table named manufacturers, it has one row in right now. I want to upload all of my manufacturers into the table. Is there a way to add multiple rows to the table at the same time? If not, what is a quick way to get about 1,000 manufacturers uploaded into a table?

Append Existing Data In A Table
I am working on an existing osCommerce store. There are over 200 products, each of which needs the associated image file name changed.

Rather than do this manually (any number of ways), I am unsure if there is a query I can run to save me lots of aggravation. What I would like to do is update whatever is in the field and change it to add the same 5 characters:

The Table is Products
The Field is products_image

Examples of existing data in this field are:

abcdef.jpg
ght.jpg
fire_777.jpg
oops.gif (the replacement image is a jpg)

The result I seek is:

abcdef_th.jpg
ght_th.jpg
fire_777_th.jpg
oops._th.jpg

Logic tells me to start at the end of the data, remove the last 4 characters (.jpg) and append 5, in this case: _th.jpg Could someone please give me a hand with this? I have spent many days redoing the image files and they are nearly ready to go into a live store.

How To Add A Auto_increment Field To An Existing Table
I want to add a field called "id" starting from 1 and auto increment, but it seems that mysql doesn't allow me to do the following.

alter table existing_table add id INT AUTO_increment;

Message: Incorrect table definition; there can be only one auto column and it must be defined as a key. But I don't have a key or auto column in the existing_table.

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.

Search Table Column For Existing Value
I would like to search a table's column so that I can match an entered email address against the data in that column. I'm writing an aplication for an event and I don't want duplicate email addresses in the table. I'm using PHP4.

Auto Increment Existing Table
I have an existing table with many rows of data. I want to add auto increment to it so I do not have to manually asign each row a unique record number.

How To Import Data Into Existing Rows Of A Table?
I have moved 665 records off of Filemaker into mysql-4.0.18. I had a
great deal of trouble exporting a text field, possibly because it had
control characters that interferred with my field delimiter (also it
was from a Mac, and I'm new at this.)

So I have a table called invoices and another table called tasks, that
has only an invoice number and the work billed on that invoice number.
To see the work billed along with the other invoice data I have to do
a join, and this works but is clumsy for me. Since tasks.work is a
text field of 'infinite' variety I don't see the sense of keeping it
in a separate table like I would with clients' info; it belongs *in*
the invoice table, not just with it.

Is it possible to import this text data into a field in the invoice
table? I've messed around with this but it always appends the data as
new records. Perhaps I was doing an insert back then. Is the answer
something like 'load data infile 'workdone.csv' into invoices
(workdone);'

The invoices table was filled in a certain order (by invoice number)
and the workdone.csv was exported in the same order, so each invoice
row should get its correct text.

Is this a good thing, or am I thinking too 'flat-file-ish'?

Inserting 100 Rows Of Data Into An Existing Table
I want to import 100 rows of data from an Excel spreadsheet into an existing MySQL table. Both the spreadsheet table, and the MySQL table have the same exact format & headers.

In MySQl Query Browser, I can locate only the Edit function, which seems to only allow me to type in the data line by line. Can I import the data in MySQL Query Browser, or do I need another product? And if I can import, how do I do this?

Modifying Primary Key And Auto_increment In Pre-existing Table
Given the tables defined below, is there a sql statement that could make num_1 an auto_increment field without rebuilding the table (nevermind any potential numbering conflicts)? I don't see anything in the alter table documentation that will produce this effect.

Also, say I wanted to change the primary key of test_1 (e.g. primary key(num_1,num_2)). I think because num_1 is a foreign key for the table test_2 MySQL grumbles about changing the primary key, in addition to dissalowing the drop and redefinition of the primary key.

Could anyone tell me how to easily handle these situations? This is a simplified example of my actual situaion, but if it can be solved here then I think I would be OK. Code:

Import Additional Field Data To Existing Table
I've got my data all set-up in a mysql table and all functioning well.

We have decided that we need some additional bits of data for each record and we have that data in a csv file. We've created the fields in the mysql table.

In the csv file, I have the userid (to match that in the current sql table) and the additional data with the column names in the csv file matching exactly the new fields in the sql table.

The additional fields in the original sql table are empty, so we can just simply write-over these fields with the data from the csv file.

But...we are having problems with the import using the MySql administration tool on our server.

If I select a csv file upload, I get a number of fields is not same error
If I use csv load data upload, it just overwrites the first few lines of current data and doesn't put the new data in the correct fields.

Can anybody help, please? If csv load data is the correct format to use, what do I put in the import tool for these variables (given that I have a standard csv file that has been created using excel)

Replace table data with file (yes/no default is no)
Ignore duplicate rows (yes/no - default is no)
Fields terminated by(default is
Fields enclosed by(default is ")
Fields escaped by(default is )
Lines terminated by(default is auto)
Column names (default is blank)
Use LOCAL keyword (yes/no - default is yes)

How Do You Discern The Type Of An Existing Mysql Table Column?
I have a program that needs to be able to know whether a MySQL column is a number type or other. Is there a function that does this, or can anyone else suggest a way of discerning this reliably using SQL statements?

Trouble Assigning Value
in my code i have the following:
tableName!Date = "2006/12/07", where "Date" is the name of the column in the mysql table and it is defined/has a type of datetime..

When i get to this line of code however, the value of date stays null. Ive managed to assign other fields in the table similarly (e.g. tableName!fname = "Frank".) I believe it may also be a keyword and thats my problem, but I'm uncertain.

Does any one have any suggestions as to why this wont work for the date field?? If its due to the fact its a keyword, how can i still pass the value without changing the column name in the table?

Assigning Index
I think there is a simple answer to this, but this is an issue I haven't had to explore as of yet for some reason.I am in the process of importing a batch of new records into the database and I need to assign a unique value in one of the fields that will serve as the primary key. The field where this value will appear is obviously already the primary key field and the records in the database already have a unique value found there.

For the life of me, I can't figure out how to assign a unique value for the primary key of these newly imported records that will not duplicate the primary key found in the existing records.

Assigning Status As Reserved
-------
tbl_dates.id
tbl_dates.week_date
--------
tbl_cottages.id
tbl_cottages.cottage_name
--------
tbl_booking.id
tbl_booking.cottage_name
tbl_booking.book_date
-------
tble_dates.week_date contain dates in the format 'January 1 - January 7'.

the tble cottages contain a list of properties

the tble_booking contains entries which I input when a cottage becomes booked so I know when the cottage is unavailable on what dates.

What I want to be able to do is cross reference the top 2 tables against tble_booking.

So if the tble_booking contains
ID = 1
Cottagename: Bluebells
Week_Date: January 1 - January 7

If some one clicks on the webpage to check the availabilty for the week 'January 1 - January 7' the cottage will be marked 'reserved' and the rest will be marked 'available'.

I have tried doing it with a LEFT OUTER JOIN but it marks them all as reserved?

SELECT
tbl_cottages.id,
tbl_cottages.cottage_name,
tbl_bookings.id,
tbl_bookings.book_date,
tbl_bookings.book_cottage,

FROM tbl_cottages

LEFT OUTER JOIN
tbl_bookings ON tbl_bookings.book_date = tbl_cottages.cottage_name

WHERE tbl_bookings.book_date = '#URL.calendar_date#
If you need any more info please ask!!

Unknown Column (assigning Using AS)
SELECT
u.user_id,
u.user_name,
(
SELECT COUNT(friend_from)
FROM friends
WHERE friend_from = u.user_id
) AS friends_added,
(
SELECT COUNT(user_referered)
FROM users
WHERE user_referered = u.user_id
) AS friends_referered,
friends_added + (friends_referered * 10) AS points
FROM users u

Quote:

#1054 - Unknown column 'friends_added' in 'field list'

Assigning Query Results To A Variable
I would like to Execute this Select Statement...

SELECT MAX(VisitorID) FROM Visitors.

Is there any way to store the result directly into a VB variable??

lngVisitorID = ???

Assigning Query Results To A Variable
I would like to Execute this Select Statement...

SELECT MAX(VisitorID) FROM Visitors.

Is there any way to store the result directly into a VB variable?

Assigning Unique ID To Distinct Values
I have a table `books` with booktitle and authorname columns. In my
applicatoin booktitle has unique values but authorname doesn't. ie an
author can have many books but not the other way around.

I need to add more author info in the database. So I need a new
`authors` table with authorname as a column (along with address, email,
phone etc) and an authorid primary key. Then I need to replace
books.authorname column with a column containing the corresponding
authorids

How can this be done in sql?

Assigning User Privileges Error
I created a user 'cody' % and localhost and try to assign privileges to his database 'cal_cody' For some reason it don't work, posting this weird thing in forums didn't help either.

so I tried:

mysql> GRANT ALL PRIVILEGES ON cal_cody.* TO 'cody'@% IDENTIFIED BY 'access';
Query OK, 0 rows affected (0.00 sec)

also tried:

mysql> GRANT USAGE ON cal_cody.* TO 'cody'@'%' IDENTIFIED BY 'access'
Query OK, 0 rows affected (0.00 sec)

then tried the same for 'localhost' then even flashed the privileges and even reloaded the mysql service doesn't do jack

Is there something wrong with my command, or is there a way to do this through phpmyadmin?

Very Easy Question - Assigning A Base A Username And Password In PHPmyadmin
Maybe I am just getting very tired all of a sudden, but I have forgotten how to assign and username and password to a MySQL base - can someone please put me out my misery on how to do this

My.cnf Not Existing
this is my first posting here so plz do not flame me too much if my quest=
ion=20
marks me as a total noob.

My problem is, i play around a bit with mysql (4.0.13) and want to test t=
he=20
Replication. The Documentation at http://www.mysql.com says i have to do =
the=20
following:
Stop databases - done.
Build tarball of data and bring it on slave - done.
Modify my.cnf - ???

Here is my problem. There is no my.cnf existing on my machine. Maybe its=20
called different with the newer version? Or can it be considered empty an=
d i=20
have to build a new one? But if i have to, in which directory would i do=20
that?

ON EXISTING Clause
What I'm trying to do is update a table of mine in mySQL. However, some of the values may not exist in the table yet, while most will already be there.

I have done some searching and I think I need help. I'm assuming UPDATE won't simply act as an INSERT if the existing value is not found. From my searching it seems I need to use the ON EXISTING clause with an INSERT. However, it seems that ON EXISTING can only be used when there is a primary key. In my case, thats not the case here.

However, for me this is query confusing at the moment. Can someone provide a hand?

My table is called "rankings" and has the following structure:
comp_id - int (and an index)
uid - int
networth - int

Check For Existing Row ?
How do I determine if a row already exists ?

// see if a row has id=100

$id=100;
$query="SELECT * FROM mytable WHERE id=$id";
$result=mysql_query($query);

...what do I check now ?

Using Existing Database
I am setting up a new dev. machine and need to get an old database into the new installation of MySql. There are two databases showing on the database list 'mysql' and 'test'.
Can I drop the old database (contained in a folder) straight in with the existing databases and get any sense from it?

Existing Database
I'm using MySQL 4.1.9-nt & VC++ 6.0 .
how to get all the database names and table names?

I'm using the VC++ syntax,

CRecordset rs(&db); //here db is object of CDatabase class.
rs.Open(AFX_DB_USE_DEFAULT_TYPE,"show databases ");

Information From Existing Db
Dont know much about mySQL, so excuse my potential ignorance here :
Is it possible for a table to write information to another table automatically?
For example, if on "table1" I have a column called "code", can I write the entire contents of a specified row to another table called "abc" if the code "abc" appears in the "code" column of "table1"

Add A Record To An Existing Recordset?
I have a MySQL table with about 30 records, I use a form to insert data into the first 24. (like; Name, Phone, etc...)

The last 6 (Photo1, Photo2, Photo3, Photo4, Photo5, Photo6) are the names of uploaded images (which are uploaded from a seperate form.

How do I (Or what MySQL statement do I use) to insert the 6 names into the recordset?
(Each image name will be saved into a php variable. ($Img1, $Img2, $Img3, $Img4, $Img5, $Img6)

Using Update To Add Value To Existing Numeric Value
I have users inputting hours into a mysql database. Is there a way to have a mysql statement take my input and add it to the existing value in a record? I suppose I could just select that value and use php to add the 2 values but was thinking mysql might have a function to do this for me.

How Do I Add Default To Existing Column?
It should be easy but i've searched from mysql manual to here...

Moving Existing Fields Out
Can someone tell me what would be the best way to move fields out of an existing table into new tables?

Best Way To Get Data From Existing Database
what I'm trying to do is get data from an exhisting oracle database that is not looked after by myself but I have access to. What I want is for my MySql database to mirror the oracle one or update every 5mins or so. I have done a quick search but as I have only made simple databases before I'm not sure what to search for!

Attaching An Existing Database
I would like to attach an existing database (I have the files from another system) to MySQL running on a recovery machine - yeah, the Linux system got screwed, but I managed to recover the files.

So, having the database files (.myi, .myd and .frm), how do I go about incorporating them into the current system? (Like "attach database" in MSSQL)

Insert A Duplicate Of An Existing Row
I have a table with an auto-increment columns (absid) and I want to take a row with a certain absid and re-insert it as a new row with a new absid (but all other columns as from the copied row). Running mysql 3.23.55. Might something like the following work?

insert into mytable (absid,*) values 0 select * from mytable where absid=20;

or something similar? With 3.23.55 I suspect it won't work anyway but I have no access to a version 4 installation and wouldn't want to upgrade if what I want to do is inherently not possible.

Accessing Existing DB From Files Off A CD
I inherited a project from another developer who was fired. There is no database documentation. There is both an SQL backup file and the original database files on a CD. I have recreated the db from the backup file and that worked perfectly. However, I don't have full confidence in the accuracy of the backup file. I was wondering if I can somehow access the db from the files on the CD.

I copied the files to a folder on my machine and then changed the MySQL data folder setting to that folder. Unfortunately MySQL wouldn't start. What would I need to do to be able to have MySQL use the files copied from the CD as the data files?

Running An Existing SQL File
I hope this question is not too 'newbie' to answer.. I'm reading a book (PHP and MySQL web development by Luke Welling and Laura Thomson), and am getting an error on an example script.

I'm trying to create tables using an existing SQL file through MySQL. According to the book, it should be done like this:

> mysql -h hostname -u username -D database -p < file.sql
(I've changed the hostname, username, etc.)

However, I get the same error over and over again: ERROR 1064 (42000): ... at line 1. The book didn't even mention where to put the file, I've tried all sorts of things, but can't get it to work..