MYSQL :: Quick Tip: Switching Field On/off

Switching Databases
I have a vbulletin forum right now and would like to switch to phpBB. But I cannot loose any members or posts. Someone told me I can switch the vbulletin database with the phpbb one, or import everything to phpbb from vbulletin(except the hacks/mods). Is this even possible? Is there a program for it?

Switching Domains
I switched domain names through my web host and I'd like to know if my My SQL tables/users, etc. can typically be found somewhere in/on my server?

Here's the server info (from netcraft if that help):

Linux Apache/1.3.33 Unix mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 PHP/4.3.11 FrontPage/5.0.2.2635 mod_ssl/2.8.22 OpenSSL/0.9.7a

Switching Characters
I've got a text string like a date eg 01-Jan-2001, is there a neat way
in an SQL statement to replace Jan with 01, Feb with 02 etc?

Switching From Ms Access To MySql
1. Is Mysql secure than MsAccess (there are softwares avilable to
retrieve Ms Office Password)

2. Which 3rd Party tool Would You recommend for Administration,Query
creation.... Etc

3.Any tools for setup Mysql database to ship with ApplicationProgram
(Access need not be installed in a Windows machine)

4.Any known problems in a VB6/VB.Net - Mysql Combinations

Switching To Shell Prompt
how to switch to shell prompt from mysql prompt.

Switching Indexing Types
I am still a novice when it comes to MySQL and I was wondering if switching
the indexing from INDEX to a FULLTEXT would have any dramatic
repercussions on current searches based on the the INDEX format?

I have 3 indexed fields: unit_id, customer_id, facility_id all in one
table called units. I would like to do text searches unit ids but
also account for user mistakes, like incomplete unit ids and return
all possible matches.
Doing some research on the MySQL site, they have lots of examples
based on FULLTEXT indexed fields.

Switching From Access To MySQL
On the "programming part" am i going to have to do my queries differently? Updates and all that differently? I recall people asking my database, then saying "I dont know how access does that" when having problem programming something... i have over 100 pages and 30 tables and don't really want to change but heard i should have done it about 100 pages and 30 tables ago :P So after I switch am i doing to have to change my code too?

Need Advice On Switching From Sql2000 & Access To Mysql
Im a coldfusion programmer, so it all started for me learning on access db's. Then sql 2000. Along the way, I started installing the cms systems and learning how mysql | phpmyadmin worked and really started to prefer this.

The .htaccess control is whats making my decision for me. I just cant control urls enough using IIS & coldfusion.

I have found a host that supports coldfusion running on the lamp platform.

To convert the sites, I figure 2 major tasks:
1. Converting the databases over to mysql
2. Making sure all my queries function properly.

I figure converting wont be too bad, but Im wondering how much structural differences exist between T-sql and the syntax mysql uses.

At most, my queries to convert are 3 tables using LEFT OUTER JOIN or INNER JOINS.

Any feedback on the process will be most appreciated
(I cant wait to get off IIS)

Switching Assigned Database For An Established Connection
I am implementing a connection pool using the C API. In my pool, The first time a connection is made, I call mysql_init(&myConn), and then use this myConn connection structure to call mysql_real_connect() with the appropriate user name, password, and a database name to connect to. Once the connection is established, it is used for the current database query and then put back into the pool.

My question is regarding using multiple databases with a single connection pool. If I pop an established connection off the pool and the database it is currently assigned to does not match the requested database of the new query, what do I do?

Do I close the existing connection and create another with the new database name? If so, wouldn't this defeat the purpose of having sustained connections?

What I first tried was to simply call mysql_real_connect() again with the new database name. This worked, but using MySQLAdministrator to look at the list of open connections/threads, I see the original connection was not closed, and I no longer have a handle to it, so it is lost.

Here is some pseudo code:

Quick Join
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys.

Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id

How do I get the loser name?

Quick Fix 4 MySQL Pro
I will have a few instances like this and since I'm new to MySQL, perhaps a good example will help down the road. All I want to do is display a list of the first and last name of the names in my DB so they appear like so:

John Smith
Suzie Johnson
Etc...

first and last are two separate fields in the DB so I just need to join them together. The code I have now gives me this error:
PHP Notice: Undefined index: first in e:etc...

<?php
// Request the names in db
$result = mysql_query("SELECT artist.first, artist.last FROM artist");
if (!$result) {
echo("No artists posted at this time");
exit();

code...

Quick Join
I have a quick SQL question:
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys. Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id
How do I get the loser name?

Quick PhpMyAdmin
I believe I got phpMyAdmin setup to work right, I used HTTP authentication, and I get a log in screen when I goto the url. How do I set up an account for that?

GRANT ALL PRIVILEGES ON acct1_db.* TO 'Brian'@localhost IDENTIFIED BY 'password';

Is that right? I wish to host websites off this computer and have multiple users. Do I need to log in to the controluser account to do it? Or am I way off?

Quick Query
this one shoud be easy, but I just can't think of the best way (other than writing a php script to do it). I have a CMS and need to change one word in the description field for 64 rows. For example, where we now have 64 entries with "Bilbo" in the middle of the data, we need to change it to say "Frodo" instead. (Actually, I need to replace a misspelled javascript toLowerCase() call...darned non-programmers! They used toLowercase() - note the lower case C)

Quick Queries
Is it possible to use a regular expression or similar in a mySQL query, to make 'fuzzy' comparisons, or is LIKE the best we can hope for?

QUICK AND EASY ONE
im fairly new to php/mysql so please bear with me
I am trying to upload a txt.file created from an excel spreadsheet using the following query:

LOAD DATA INFILE 'path/file.txt' INTO TABLE my_table;

im getting a syntax error!
what could be wrong? are there any other attributes i should be adding to this query? i also tried uploading a csv version of the same file and get the same error.

Quick COUNT()
The MySQL documentation says:

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.
How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.

How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

Quick Ones To Speed Up Access
------=_NextPart_000_0032_01C35FC2.62884DE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I am querying from a table containing more than 40,000 records. Earlier =
when the records were 10,000 it was taking 9 sec and now after 1 year =
and 40,000 records its taking 30 sec. Code is the same.=20

I am pretty sure that it has something to do with database only. No body =
can think of spending 30 secs for retrieving values from tables unless =
it's very huge in the sense of millions of records. =20

I don know whether I have to modify my database or do some sort of =
restructuring or reindexing so as to make it fast enough.

Is there some methods or optmization which can be applied to this =
database which hasn't been touched since design to enhance the spped.=20

If there are some quick ones but valued alottttttt please let me know.

Dump (quick) A Table Into Another One
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

I'm looking for a faster way. Does anybody know it? Thank you very
much.

PS: I was told about 'mysqldump', but I do not know if it's faster
than mine.

Quick Date Subtraction Question
When I do the following, I get the number of days in difference between two dates however for the dates that have expired, I get the number of days back negated, ie -3 for example?

What I actually want is that if there has been an expiration, I want the result for a given row to be 0. Anyone got an idea how to?

// timestamp is datetime creation of record
// expires is date in future, ie 7, 14, 21 or 28 days in that increments
mysql> select day( expired ) - day( now() ) as days from polls;
Here is an example of the data returned,

+------+
| days |
+------+
| 7 |
| 7 |
| -3 |
| 7 |
| 7 |
| 21 |
| 7 |
| 7 |
| 14 |
| 21 |
| 7 |
| 21 |
| 7 |
| -3 |
| 21 |
| -3 |
| 7 |
| 7 |
| 7 |
| 14 |
| -3 |
+------+

Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes.

I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?

Quick Question - Id Mediumint(8) - Does The (8) Matter?
just a quick question really..

Just setting up 1 of my tables and just wondering if it matters if i set my id to mediumint(8) but i only ever get to 6, will this effect anything in anyway?

Same goes for - address varchar(50) and say that no address ever reachs 50?

Quick & Easy MySQL Sync?
Does anyone know of any quick and easy way to sync a local development mysql server to a remote server and back. I'd like to be able to optionally sync either direction?

Quick Mysql Auto_increment Question
i noticed that after adding some test data into my table the autoincrement is working fine. but after i delete them, even though the table is empty again the new data i put in will have primary key thats in auto_increment as if the old data was there.

for example i added in 2 rows. now i have an id 1 and id 2. i delete them and the table has no rows. but now i add another two rows after wards and they get assigned id 3 and id 4. is there anyway for me to make the counting start over??? thanks for any replies or thoughts on this.

Quick Question About Reseting Demo Database?
I would like to set up a cron job to reset my demo database on a regular basis. Is there a way to export an sql file via phpmyadmin, and run it as a whole via the PHP mysql commands? The only way I can see to do it, I have to split up the exported sql from phpmyadmin into individual queries. I'd like to just process the entire .sql file at once, as it would make maintenence much easier.

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

Quick "WHERE"
Part of a WHERE criteria I'm trying to construct needs to return only those records where a date field in my adkey table contains a date older than a user-specified date (submitted as variable {sRemoveDate} from a web-page form). The following works:
CODEWHERE
(adkey.D_RemoveDate < '{sRemoveDate}' Or
adkey.D_RemoveDate is null)

Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id.

How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously?

My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense).

Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.

How To Convert A Varchar Field Into Proper Mysql Date Field?
I have a database in which the date is stored in varchar field in a following format: d-m-Y (06-08-2007), now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06)..

There are about 300 old entries..is there a way I can do that automatically without manually re-entering the dates again?

Extracting Field Data And Putting Each Field Into A Shell Variable?
In a MySQL query on the database (one table with 15 variable length fields,
I want to put each field into a Bash variable so that I can handle each
field as an entity.

The query I have is something like this:
mysql -e "use $database; select field1, field2, field3..., from Table1 where
fieldN like '%something%';"

can I / How do I do something like:
mysql -e "use $database; select field1,..., var1=field1, var2=field2,....;"

do
stuff with var1, var2...,
done

One Field With Multiple Attached Rows, Only Show The Field Once?
I have a table called "Customers", with a field called "Customer name" and a "Customer ID". I have another table called "Projects", with a field called "Project name" and a field called "Customer ID" which references to "Customers"

What i am trying to do is: Select a customer. Select the projects that are related to the customer by comparing the Customer ID field. Code:

Create A Field By Deleting A Part Of The String From Another Field!
I have a table for "Addresses", which has a column titled "Address" and the records in this column are a combination of house#, street name and street suffix, which typically looks like "100 Main Street".

I am looking for a query result wherein a new column would be created which would have only the street name and the suffix and would eliminate the house #s, hence the result would look like "Main Street".

Copy Field Over, Then Update The Original Field
I want to change a price of a product, but back up the old price. I was trying to do this through two queries, an INSERT and an UPDATE, but it's not working.

INSERT the row that will store the backup:

INSERT
INTO
s01_MUS_SalesXProducts
(
s01_MUS_SalesXProducts.sale_id
, s01_MUS_SalesXProducts.prod_id
) VALUES (
".(int)$sale_id."
, ".(int)$prod_id."
)
Then I would copy the prices over and update the original with one UPDATE query:

UPDATE
s01_Products
LEFT
JOIN
s01_MUS_SalesXProducts
ON
s01_Products.id = s01_MUS_SalesXProducts.prod_id
SET
s01_MUS_SalesXProducts.old_price = s01_Products.price
, s01_Products.price = ".(float)$sale_price."
WHERE
s01_Products.id = ".(int)$prod_id."
However it is copying the new price to the backup field. Is there a way to do this in two queries? I know if I select the product price before inserting I can do it in 3. Truely I would like to do this in 1, but I don't believe this is possible due to the nature of the queries.

Creating Field In Table To Sum Field Values
i was wondering if there was a way in mysql to have one record that will sum up all each field value(one record with each field having sum totals)? i have a table with over 60 statistical fields that i want to get the sum for.

Updating Field With Contents Of Temporary Field
I have a table, that when new information is entered for it the new info is held in a temporary table that has exactly the same fields (the only difference is the auto increment pid in the original table is not auto increment in the temporary one - it holds the pid from the original).

I need to be able to update the original with the contents of the temporary field, all fields are updated.

Is there an easy way to do this, or will I need to select from the temporary and then run the update setting all the fields explicitly.

I have tried something like [code]
Update villas SELECT * FROM updatevilla WHERE pid='$villapid'
[/code ] in php but it doesn't work. I think I see what some of the problem is but am not sure how to fully write the query.

Orderby A Text Field As It Were An Integer Field
Is it possible to construct a query so that the orderby 'field' returns the rows as though the 'field' were an integer field, even though it is a text field? I do not want e.g. 10 to be before 2.

The field in question is called 'lagerantal' and is of type 'text'

Import NULL Or Empty Field Field Value
If you are importing data from an external data source and you have the choice of populating empty fields with NULL, what are the pros and cons of doing so?

Copy Partial Field Value To New Field
I have a table with state and county in one field. The first 2 characters in all the records indicates the state. Is there a way to copy just these first two characters into a new field in mysql?

Given Field Name, Find Table That Has Tha Field Name?
Is there a quick way to identify tables that have a specific column in them? For example, if I have a column name 'RuleName', how can I need to find all the tables in a database that have a column with that name?

Date Field, Time Field
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) and to be able to sort the query results on the resulting field.

Using A Field Alias In Another Field
I'm migrating a DB from Access to MySQL and I having problems with queries similar to this:

SELECT 1 AS x, x+1 as y;

I get a "ERROR 1054 (42S22): Unknown column 'x' in 'field list'" error.

Is there a way to do this without calculating 'x' twice?

I Want Field Data Without Field Name, How?
I am retrieving data from mysql under linux. something like mysql -e
"select id from table1", I get output

id
1
2

but I dont want the field name "id" but only the data. How can I do it?

Field Contains Form Field
I have a form putting variable search_var

Select * from training.itdtms_content where content_Body like #search_var#

the # on each side are for the Coldfusion variable.
I want to be able to find records that have a search term inside them, can anyone help?

Part Of A Field Into Another Field
I have a table with an email field that contains values of the form
uid@domain. I need to populate a new field called uid with just the uid
part of the email address. Is there any way of using select to just
retrieve the part before the @ and insert it into the uid field?

Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked!

I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email.

The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.

Update One Table Field With Another Table Field
i am having a problem in my update function. I have two tables:

buyselldata table and autozack_signal table

buyselldata bsignal coloum has to be updated with Close_Price in autozack_signal table if macd_buy in autozack_signal is 1

common field in both tables are Script_Name

"Update buyselldata b
set b.buy = a.Close_Price
where exists(select Close_Price from autozack_signal a
where a.script_Name = b.script_Name
and a.macd_buy=1)";

i am getting -1 for mysql_error()

Updating Table Field From Another Table Field
I have a field called 'tel1' in table 'live_properties'
and I want to update a 'tel1' field in 'live_properties'

with

'phone_num' field from 'usersTBL'

MYSQL code is here but giving error any suggestions

UPDATE live_properties SET tel1 (SELECT phone_no FROM usersTBL WHERE username = live_properties.username)