MYSQL :: Convert Existing Data

Convert Existing Dates To UNIX Timestamps
I have a DB of roughly 700 records, growing by about 100 a day. The first 600 odd records have a field called issue_submitted_date which is in the format Month Name DayNumber, Year Number, Hour:Minute AM/PM, like August 5, 2004, 5:00 pm

That was a bad idea...

So now I store issue_submitted_date as a UNIX timestamp. I'm trying to come up with a query to update the other 600 records as unix timestamps as well, but I cant find any way to convert the way I have stored the month name and such, only when the record has been stored with all numeric types (such as 2004-01-01 21:22:00).

update issues set issue_submitted_date = unix_timestamp( "formatting options", issue_submitted_date );

I was looking at GET_FORMAT and MONTHNAME, but these don't seem to be capable of doing what I'm trying to do.

Can anyone give me some insight or assistance building this query?

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

Add Additional Data To Existing Data In A Blob Field
I have a Blob field called "category" that we use to store different categories of job types that our users set up so they receive emails based off of the ones that they have picked. What I need to do is to add 4 new values to the beginning of each users "category" field.

I am not really sure how to do this except that I might need to use UPDATE to get it done. This is where it will get tricky, I need to check for the 4 values to see if they already have them and just add the oines that they don't have.

Here are the 4 values that I need to check for and add. Allied Health Care, Nursing, Rehab/Therapy, and Other/Pharmacy/Physicians. Here is what a current list of values would look like in the users table and the category column: Code:

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!

Edit Existing Data
I have just started to learn MySQL and this question might be silly. I have a MySQL table with user information. I want to edit this table in a website. I need to load existing data in a HTML form and then edit the fields and update them.
I searched the net but could not find anything .

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?

MySQL -- Editing Existing Data In A Row
I looked through ALTER table, and some tuts over at w3schools (my heros) but I can't seem to find a way to edit EXISTING data in an EXISTING row of 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.

Append Data To Existing Cells
I have an existing table. I need a way to append data in one of the columns. How do I append data in cells so I do not overwrite the existing data already in the cells in the column?

The fields in my table are `Source` , `Theswords` , `Topic` , `Subtopic` , `References`

The table name is ViewNew

The column I want to append is Theswords

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?

SQL*plus Inserting New Row With Values = To Existing Rows Data
I am trying to insert a new employee into an empolyee table and get some of the values (namely phone no.) to be the same as what another employee currently has. This new employee is replacing the old employee, but i have to keep the old employee alive in the db as other data is reliant on its existance.

I was wondering how do i insert the new employee asking the, VALUE ('etc', 'etc',) line, to assign a value to certain fields equal to the value contained in other employee rows.

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)

Duplicate Existing Database (innodb), Structure Only, Without Data
There's a database (innodb) named "test1". I want to create a new database with exact the same strcuture, but since there are many tables in this db i do not want to retype the whole commands.

I'm looking for a possibility to duplicate this database, any ideas / tools?

How Do I Generate Data Definition Statements For Existing Tables?
I cannot remember how to generate a create statement for an existing table. I tried searching the documentation but I don't think I'm searching for the right text.

I'd also like to know if there is a specific name for this task and what section it is under in the docs.

Migrating Data Into Existing Mysql Schema Tables
i understand how to migrate data into mysql and to use the migration tool to convert a database to a mysql schema, however, I'm not sure I understand how to migrate specific data tables/columns in the source file to specific tables/columns in the destination mysql tables (where the schema already exists and I'm simply importing data itself).

Convert Ini Data To Timedate
I'm importing data from an old blog application into a new one. The date table used to sort the data in the old application is integer, ini(11). The new application uses timedate. How do I convert the integer to time date?

What's The Best Way To Convert A Data Type?
I have a column of type Text in a table, but when my client software (C# .NET application) is calling the SELECT statement, it doesn't seem to know how to handle Text. What's the best way to convert the column to a Char or something like that in the SELECT statement?

I tried CAST(column_name AS CHAR) but it doesn't seem to be actually casting if more than one record is returned (works fine for just one record though). Any ideas?

Convert Data Types
MSSQL:

select convert(int, '111')
select convert(float, '111')

MySQL:

how is this made?
I just got a MySQL.chm

Convert Mysql Data
I was wondering how to convert my current mysql data into UTF-8?
I don't have access to the latest version of mysql (with my hosting - using version 4.017 - pair networks). I would like to convert the data to UTF-8 so that I can use utf8_encode() to insert all different languages/types of characters from the webpage into the database and use utf8_decode() to pull data from the database and display correctly in all languages.
Is this the correct procedure? Will I still be able to search the database as UTF-8 encoded?

Convert MySQL Data
if anyone knows of reliable software to convert MySQL table data to Excel format?

Convert Field Data To Another Format
I have a field that have wrong typed data, for example the date in that
field looks like 20-04-2006 (day month year) and i need to convert it to
2006-04-20.

How To Apply New Database Structure To An Existing Database, Without Overwriting Data?
I am writing a simple blog software application. We released a version of this software a while ago.

It comes with a sql-tables.sql file, that you can just import into your mysql database and it will setup all the tables for you, for a fresh installation. We made the sql file by just doing an export I believe, without exporting the data that was in OUR database.

Well, since then several people have downloaded and installed our software, and are using it.

We are about to release a new version soon, however. Since it is all still under development, many things have changed in our database structure. We've added new tables, and modified or even removed fields from existing tables.

So for the new release, we're going to include a new sql-tables.sql file obviously, so that for fresh installations they can still install it just like normal.

However, the question is... how do you "upgrade" all the previous version's users's databases, without losing any of their data?

Is there anyway to export just the structure of our current database... then somehow apply it so that it just goes through and changes another database to match the new structure?

Or any ideas at all how to do this?

How Do I Convert Data From Mysql 5.0.27 To Mysql 4.025
I am trying to migrate to a professional web hosting company. The problem is that when I backup my database from my computer and import it into their mysql database, I am getting an error that says, no database selected. I believe that this is due to the different table structure between my version of mysql (5.0.27) and their version of mysql (4.025).

How do I do a data dump from mysql (5.0.27) that would be compatible with their mysql (4.025)?

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

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.

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?

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)

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?

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

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

Uploading Existing Database
I just got a dedicated server and can't upload some of the larger databases. I have tried compressed (gz) as well as the none zipped and automatic. None works for a 50MB + database.

I am not sure what's wrong, maybe it times out, but then I never get the error. If that's the case then how do I change the timeout settings in MySQL. Also I am using Plesk, not sure if that matters.

List Of Non-existing Users
I have 2 tables: tableFrom , tableTo.

I want to have the list of users that ARE in tableFrom, but NOT in tableTo.
The common field is "username".
I want all the rows from tableFrom as long as they don't already exist in tableTo.
An important point is that we use MySQL 4.0.20, so I can't use "NOT EXISTS(...)".
This query needs to be pretty fast since it might be ran many times an hour, even minutes.

Check Existing Values
In my web site I'm using PHP, & MySQL.
There its needed if a user is trying to be registerd using a existing user ID.
That is I think, it should check such a value existing and if exists it must display "Existing User ID".

Assigning Userids To Existing Table
I have a table of existing users in a database. I want to add a "userid" column to the database that automatically assigns an ID to each user when new accounts are added to the system. I know I would do something like this if I had the brains to have added the column at the time of creating the table:

ALTER TABLE writers ADD (
userid int PRIMARY KEY auto_increment
);

However, the table is now already active with users, and I want userids assigned to existing accounts on the system.

Change Existing Search Code
On my site I have this code which works fine when searching for single words or where they are adjacent words, but gives no results if 2 words are entered and are not adjacent

if ($_GET["page"]=='search')
{
$sql="SELECT C.category_description,C.category_name,A.article_title,A.article_author,A.article_snippet,A.article_category_id,A.article_id,A.article_views FROM article_master A,category_master C where A.article_category_id=C.category_id and C.category_status=1 and A.article_status=1 and (A.article_title like '%".$_GET["q"]."%' OR A.article_snippet like '%".$_GET["q"]."%' OR A.article_summary like '%".$_GET["q"]."%'OR A.article_author Like'%".$_GET["q"]."')order by A.article_title asc";
$cmdartcat = mysql_query($sql);
$metatitle="Search Results For ".$_GET["q"];

someone kindly suggested this, I have been trying to adapt the original all night but have got no where (i am not really sure what does what)

$query = "SELECT {$columns} WHERE {$ADJACENT_ONLY}";
/* Load this data into an array */
$query = "SELECT {$columns} WHERE {$ALL_WORDS_ANYWHERE}";
/* Append this data to the previously loaded */
$query = "SELECT {$columns} WHERE {$ANY_OF_THE_WORDS}";
/* Append this data to the previously loaded */

Could someone change the original code to work for ANY_OF_THE_WORDS and then hopefully I may be able to then adapt the rest. (do I have to do anything to the search box results which in this case is "q")