MYSQL :: Retaining Text Formats

Retaining Control From MyODBC
I'm developing a system with an Excel front-end that uses ADODB and MyODBC to fire off instructions to a (local machine) MySQL server. As I step through my VBA code, it sends an instruction to MySQL (e.g. gconMySQL.Execute "DROP TABLE IF EXISTS MyData;" ), waits for the instruction to be executed, before returning control to the code and moving on to the next VBA statement. All well and good; this works fine for the majority of the processing.

However there is some initialisation of the MySQL database, particularly loading some data tables into MEMORY, that takes up to half a minute to complete. I'd rather fire off the instruction(s) to MySQL to do this so that MySQL initialises invisibly in the background, whilst the user continues playing around with the front-end themselves.

Date Formats
Im not sure if dates in sql are seen as strings or not? will sql find a match between, for example: 2007-April-1 and 2007-04-1 18:23:29.0
the latter value is entered into the db using now(). the former value is generated from an html drop down list for year, month and day. im just wondering if i need to format what's generated by now() so it is stored in the format that matches what my drop down lists create, which can probably only be interepreted as a string.

Password Formats 3.23-4.x
I tried to get some experience with converting between the different password
formats as describd here:

http://www.mysql.com/doc/en/Password_hashing.html

So I took this sample

mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

modified it a bit and found out that sometimes it would not let me do this
with an logical operator like = but instead wants LIKE or RLIKE.

I am using 4.1 alpha , so it is possible hat this is a bug of course. Does
anybody have similar experience ?

mysql> UPDATE user SET Password=OLD_PASSWORD('mypassword') WHERE Host like
'localhost' AND User like 'root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE user SET Password=OLD_PASSWORD('mypassword') WHERE Host rlike
'localhost' AND User rlike 'root';
Query OK, 1 row affected (0.24 sec)
Rows matched: 1 Changed: 1 Warnings: 0

See that finally the RLIKE worked. So Ia m a bit confused now as I would have
expected the LIKE function to work, but perhaps this is a special case?

Password Formats 3.23-4.x
mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

modified it a bit and found out that sometimes it would not let me do this
with an logical operator like = but instead wants LIKE or RLIKE.

I am using 4.1 alpha , so it is possible hat this is a bug of course. Does
anybody have similar experience ?

mysql> UPDATE user SET Password=OLD_PASSWORD('mypassword') WHERE Host like
'localhost' AND User like 'root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE user SET Password=OLD_PASSWORD('mypassword') WHERE Host rlike
'localhost' AND User rlike 'root';
Query OK, 1 row affected (0.24 sec)
Rows matched: 1 Changed: 1 Warnings: 0

See that finally the RLIKE worked. So Ia m a bit confused now as I would have
expected the LIKE function to work, but perhaps this is a special case.

Time Formats
I have problem with importing data from another mysql database. I was originally made in php, an used date("d. m. Y - g:i a") to generate a date which was then inserted into the table. I don't have good access to the structure of the db, i can only select , so I can't check what type of field the original programmers used (datetime or varchar?) but the output when I select is 14. 04. 2007 - 2:13 pm as an example, which. according to the docs on mysql.com isn't a valid date format. Or is it? I have exported the table as a .csv, and I'm ready to import it with phpmyadmin, but I have feeling that unless i choose a varchar for the date field, i will just get 0000.00.00 00:00:00

So what to do? Any easy way to reformat the data or to make mysql understand the format?

Multiple Formats
I'm setting up a schema that will store sessions between mentors and students. Now this would be simple but for the fact that the session details are to be stored in one of three formats depending on the preference of the mentor.
The formats vary in complexity from just one big text field, to 5 different pieces of info to as many as 20 different pieces of info. I've attached an image depicting a preliminary stab at the schema. But it seems very bulky...
What do you think?

InnoDB Row Formats
When creating a table, I have the following row formats to choose from:

- Fixed
- Dynamic
- Compressed
- Redundant
- Compact

What do they mean? What do each of them do? How does the selection of one over the other affect data?

Converting Date Formats
I have a varchar column in one of my tables which currently has dates stored in it in the form dd/mm/yyyy. I want to use this string to create a date column of format yyyy-mm-dd but I can't find any way to this.

How To Search Phone Number Column With Inconsistent Formats
I'm working with a legacy database that stores phone numbers in whatever format the customer entered them in an online form. This might be:

(212) 555-1234
212.555.1234
2125551234
etc.

We now need to implement searchability on this field. Although it would be possible to refactor the code that stores phone numbers to start storing them all in a standardized format, I'm wondering if there's a way to avoid that.

Is there a way, perhaps using REGEXP, to search that column for 2125551234 and return any of the above examples?

Something like running s/[^0-9]//gi on the column before searching?

Exporting Blob / Text Column With Hyphen Delimited Text
I have a table that contains a column (blob, text type, formatted as MEDIUMTEXT) with text hyphen (-) delimited, listing

rooms by numbers, and I need to export or insert that data into another table, where all other info on the other columns

remains the same, but I need the data on the blob field to be single listed

another words, i have a table like this (example):

School Rooms
Your Hometown High School 1034-1035-1037-1039
My Hometown High School 208-178-1432-1728

I need it like this:

School Rooms
Your Hometown High School 1034
Your Hometown High School 1035
Your Hometown High School 1037
Your Hometown High School 1039
My Hometown High School 208
My Hometown High School 178
My Hometown High School 1432
My Hometown High School 1728

Automatically Generate Text Excerpt For Text Near Keyword Hits?
I would like to generate an automatic text excerpt showing the text close to the first occurance of a keyword needle in a text haystack.

For example: searching for

keyword = "foo"

in a table called Webpages in the column Content = "Yadda, yadda, yadda. Bla, bla, bla foo yadda yadda yaddda bla bla bla"

would return a column called Excerpt = "...a, bla foo yadda yadd..."

Is this best done with PHP after fetching the entire column Content, or will it be more efficient to do directly with MySQL?

What would that SQL query look like?

Need To Insert Data From A Text File Into A Medium Text Field.
How can I go about inserting data into a MEDIUMTEXT field from an existing text file using Java.

How To Insert New Text At Specific Point In Text Field?
I'd like to insert "rackmount" in a database text field in front of "radio." The term "radio" appears in each "description" column but it is surrounded by other, random, text. Is there a way to accomplish this?

Help: Replacing Same Text With New Text In Hundred Rows
I have about a couple hundred email addresses in a table... every email address includes @domain.com (the same domain). However, that domain is changing to a new domain... and I need to replace only the @domain.com to @newdomain.com.
Any ideas how I do that? Ie.. a table might look like this:
ID -- email
1 -- aaron@domain.com
2 -- brian@domain.com
3 -- chase@domain.com
needs to change to
1 -- aaron@newdomain.com
2 -- brian@newdomain.com
3 -- chase@newdomain.com

Insert Text File In A Text Field
2 questions:

1) How can I insert a text file into a TEXT field (of a table)
by a mysql instruction ?;

2) How can I insert a microsoft word file into a BLOB field (of a table)
by a mysql instruction?

Converting Database Text To Html Text
Is there a function in MySQL or PHP that will automatically convert a text with paragraphs etc. to an html ready text. i.e. a function that will add <p> tags etc.

Adding Text To Existing Text
I am trying to add a string of text to the end of text that is already stored in the DB.

For example
likes = "I like television"
is already stored in the DB

The user then wants to add "-I like music".

So I want the end result to be:
likes = "I like television-I like music"

I tried:
UPDATE table SET likes = likes + "-I like music"
but that gives me an error and only works for numbers

What is the text equivilent of adding to an existing field?

Full Text Index And Full Text Search On Several Columns And Indivdiual Columns
1. Say if I want to do the full text search on three columns, "title", "description", "keyword". The search could be done only on one column, or combine any two columns, or combine all three columns, do I need to create full text index for all these possiblilities, like full text index on each column, and then full text index on any two columns and then full text index on all three columns?

If I have to make so many different kind full text index, that will slow my database performance or not?

2. Will the relevance also be calculated based on which column, such as the "title" will have higher relevance than "keyword", the "keyword" will have higher relevance than "description"?

Text + Text
i was wondering if it is possible to add text to an excisted text
whit an number it is possible

UPDATE `forum` SET `number` =`number` + 1

but is this also posseble whit text
UPDATE `forum` SET `text` =`text` ?? 'example'

Bin Log --> Text
Generally, I use mysqlbinlog to read MySQL binary log files, but I don't think that's convenience.

Can I convert binary log file to text file? How to?

Use Text ID
How do I create a table that has a TEXT ID in mySQL? In other words, what query/how do I make it so the address mydomain.com/page.php?username=jsmith (instead of something like mydomain.com/page.php?id=12)? Also, what would be the PHP query to pull up a record with such a table?

Showing Text Instead Of Img
just set up a new table with some imgs in it and when i go to display them, it just shows the img name and not the img.

my database field is blob BINARY, or should it be unsigned, unsigned zerofill?
whats the diffrence?

Cannot Add Long Text To DB
can anyone help I have built a very basic news feature - which allows users to add news stories to a website.

However, the MySQL database doesn't seem to be accepting multiple paragraphs of text it only accepts one or two words.

I have altered the field to longtext - but it doesn't make any difference.

Text Vs Varchar
I have a table that contains preferences, most of which are integers or small text fragments, so I've been able to get by with a varchar(255) field for the data -- until now.

Would I face any penalties if I were to switch the varchar field to text? Or, is there another option that would allow for more characters to be inserted into that field?

FULL-TEXT Vs. LIKE
I'm attempting to write a simple search for a website I'm developing. Basically, the search will look for matches in "artist_name" and "album_title" in my table, "music". Simple enough, no?

Which search method is more effective, FULL-TEXT indexed searching or simply using LIKE in my MySQL query? Note that the table will eventually have thousands if not tens of thousands of rows.

From what I've read, FULL-TEXT appears to be geared towards searching for text in longer articles, whereas the most my MySQL fields will contain is a couple of words.

I wrote a quick script using FULL-TEXT, and the results are pretty ambiguous already. I've added 5 rows of records to the table, and searching for the album "Very Fast Very Dangerous" using just the word "Very" yields zero results. I assume it's because the word occurs twice in the record. However, since this doesn't constitute over 50% of the records in the table, why is this?

Can't Insert Text
I'm trying to insert text from text file to MySQL. I'm creating field with TEXT data type. When I try to insert simple text like "lala", everything works ok.

But when I try to read real text file (read by strings then connect all strings into one variable) I get error about wrong syntax.

Example:
INSERT INTO t
VALUES ('$a', '$b', '$text');

Maybe problem with ' symbols in text file? Maybe problem with some special symbols like
or
etc?

How can I fix it?

Text Formatted
It is possible to write text in a textarea, convert in HTML and insert in a mysql DB, to show it later accurately as it was in the "editor"?

Where To Put TEXT Field?
I have a table to which I want to add, optionally for each record, a rather long descriptive text, as a TEXT field. Only some fraction of the records will have this field filled, and the field will not be used in queries (besides to retrieve its value). I can either put this field right in the main table, or create a child table with a link back to the primary key of the main table. Is the second approach more efficient with database space than the first?

Import From Text
I have a text file which contains names and dates. the date is in the format
Sep-11-2001 12:30 AM.
I tried to put the text into Excel and then used Navicat to import from Excel.
But everytime the dates are converted to 1/1/2001.

Text Import
I need to append records from an Excel spreadsheet to an existing table. I think I should start by converted the Excel sheet to a text file then importing from there. I have to do this using myPHPAdmin. Am I on the right path and how do I start and import using myPHPAdmin?

Text File
I have a CSV text file. Is possible insert that file in mysql database from command line script running from console?

Text Comparison
I'm having a problem with one query. My database has the following
fields: id(int), time(int), and groupfield(enum - 5 possibilities). I
want to select all the rows where the time is between let's say 12 and
24 and the group is specified by the user using a html drop list. The
variable is named $group. Code:

Using Text Fields
I am currently working on a site which has been coded in PHP and uses a MySQL database. I have been told that i need to do something with my text fields to stop people accessing info in my database. Apparently PHP and MySQL code can be entered into the text fields to allow users access to info in my database as a means of hacking.

Can anyone tell me whether this is true, and how i would go about preventing this from happening if it is possible?

Text File
I have a CSV text file. Is possible insert that file in mysql database from command line script running from console?

Text Search...
I have a table "general" that contains some columns. I want to do a search thru
it and find posts that have a specific word in the title and text column.

This is the query that I use that creates an error.

$Query = "SELECT * FROM general WHERE MATCH(title,text)
AGAINST('$searchstring')";

This is just modified SQL from the manual on www.mysql.com

Any hints?

Formatting Text
I have a mysql database that contains guitar tabs and song lyrics (kind of
like my own personal taborama.com),anyway i have everything stored in the
database and use php to display all the records on a web page but my problem
is i can't keep any of the formatting for the tabs or lyrics, i enter
everything into the database via mysqlcc. When i enter the tabs in mysqlcc
it all looks nice and formatted but when i print it in php it all goes on
one line, is there anyway of preserving the format of the text in the mysql
database or should this question be aimed at a php newsgroup?

Text, LongText
I have a little problem with my database and php. In one of my tables i've
creted field of type Text. When i try to put some larger than 255 characters
to this field from php, that doesnt work. Script dont want to put text to my
table, strange. I try to chande type of this field to MediumText and
LongText and result is the same. This script send only max 255 characters by
field Textarea. Is this a problem whith my database or php problem with
controls textarea ?.

Formatting Text
I have selected a row from a table where the column names are:

id, money, com1, com2, com3, com4, com5, com6

I want to do a loop where I run a function on each one of the com1, com2 etc. values one at a time.

Is there any way I can format the text to be say

for ($i=1;$i<=20;$i++)
{
$answer = com$i +whatever
}

So I can run the same loop for each of the com values.

Converting To Text
have downloaded 5.0 Community onto my computer but have been told to convert it into text before I can upload to my Web page. Err, how d'I do that.

Text Search
I have a varchar field to search. There are 5k records.

I'm using: WHERE name LIKE '%outer banks%'

It seems to be very slow. I was reading about full text searches.
Maybe I could do something like: WHERE MATCH ('name') AGAINST ('outer
banks')

I'm looking for a quick way to search.

Does anyone have any suggestions?

Text Search
I have a table "general" that contains some columns. I want to do a search thru
it and find posts that have a specific word in the title and text column.

This is the query that I use that creates an error.

$Query = "SELECT * FROM general WHERE MATCH(title,text)
AGAINST('$searchstring')";

This is just modified SQL from the manual on www.mysql.com

Any hints?

Text Wrapping
I have written a database for a quote of the day. When my php script pulls it into a table, it doesn't wrap. The cell that it is in is wrappable.

Text Is Truncated
I am using MYSQL 4.4 / PHP. On updating the table from a from the text gets truncated after special characters, like ' or &. Also the text gets truncated after 34,745 chracters.

What can be done to avoid this problem.

Full-Text
I'm having a problem using Full-Text index.

My db is being hosted on a normal web hosting service.
They are using MySQL version : 4.1.13-standard

Now, I created a table, with some text/vchar/char fields.
I added to this table a full-text index that is using only the text/vchar/char fields.

When i send a query, for some words it works fine, and for some (that absolutly appears inside those fields) - i get no results.

I tried to use " SELECT * FROM tbl WHERE txt LIKE '%abc%' " and recieved many records back.
I used the same word ("abc" for exm.) in MATCH-AGAINST query, using the currect fields order (as defined in the full-text index), and i got none.

I must say again : for some words i receive perfect results, but for some, that are ablolutly part of the field(s) content i get nothing.

Using Text Columns
this is my first time using MySQL. I tried looking in the manual but I couldnÂ´t get detailed information on using Text columns.
Here is what IÂ´m in doubt:
1. What is the maximum size of a Text column?
2. What is the maximum size of information I can send through the Internet and what is the impact of this when using Text column?

Update Text
this update is work but sometimes textarea include sql command sql command does not work.
error_log as follow
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that correspondsI..............

CODE:

$text5 = $q->param('textarea');

$sql = "UPDATE webs SET ackla='$text2', admin='$text3', admin2='$text4', sql_info='$text5' where ktgori='$text' ";

$sqlh = $dbh->prepare($sql);
$sqlh->execute();

Non-english Text
I'm working on application which stores web page content. Generally I'm
turning the whole page into base64 for ease of storage (into a TEXT field).

But I have another field which opens a socket to the page, sucks down
the HTML source, runs strip_tags and other PHP cleansing functions on
it, and inserts the remaining words into a mySQL TEXT column which is
straight text (not turned to base64).

I encounter a problem with foreign languages when I do a mysqldump.
Some of the characters are non-standard ASCII and I can't merely "cat"
the file back in to a mySQL database.