MYSQL :: Inserting Dates Properly Formatted

Inserting Null Dates
I have a table that has a birthdate field with date type. My question is when I insert a record I want the birthdate to be null if the value was empty. Currently it enters it as 0000-00-00, I verifed that the field can be null. Is there something in the query I can do that can see if there is a value and if not make it null or do I need to use code to determine the value? I tried STR_TO_DATE() but that returns 0000-00-00 if the value is empty. The format the original value is mm/dd/yy.

Functions For Inserting Dates Into Mysql
Just wondering, besides now(), what are some other functions in mysql for inserting today's date into a database? For example, one of the field's I have is of type "date" that will hold today's date (but not time).

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

Sort By Formatted Date
Hey guys and gals of sitepoint. I am using a select statement to pull some data sorted by date, but I am also wanting to format the way the date looks when displayed on screen. As you can see by my select statement below this is not the problem; my problem is the items are not being displayed by their chronological date correctly (i.e. out of order). Any pointers on how to correct this?
mysql_query("select id, name, body, date_format(date, "%b %d '%y at %h:%i %p") as date from pages where cat_id=Ɖ' order by date desc")

Time Column: Can It Be Formatted To HH:MM:SS.sss
Is it possible to format a time column to HH:MM:SS.sss, If so How?

GETTING FORMATTED TEXT FROM MYSQL
I am storing some data through a textarea submitted via a form in MySQL table.

When we type data in the textarea we press enter for paragraphs or next line and it is stored as follows in the database:

+-----------------------------------------------------------------------------------------------------------+
| PRO_DESC |
+-----------------------------------------------------------------------------------------------------------+
| h gh sbdjhsdb j
jkbdh sdbj dbhaj jhhdbdjhdjd

jsa dbh jhdb dhb ajdj sdjad hajdhajd jdhasdbjad jd
|
+-----------------------------------------------------------------------------------------------------------+

But when i try to read & display the same data through a php page it is displayed as follows:

h gh sbdjhsdb j jkbdh sdbj dbhaj jhhdbdjhdjd jsa dbh jhdb dhb ajdj sdjad hajdhajd jdhasdbjad jd

Formatted String To Number
Is there any way to conver a string like '1,999,999' to simple unformatted number like '1999999'? I mean something which works opposite of FORMAT().

Convert From UNIX_TIMESTAMP To Formatted Date
I'm holding a UNIX_TIMESTAMP (say, 1110715984) and I would like a way of formatting this in MySql into a date and time.

Formatted Output To A Text File
I'd like to be able to write selected fields from a table to a csv text file. However, I need to be able to write some header lines to the file first, then the csv data, then some footer lines.

Question 1: What is the best way to do this. Using the SELECT ... INTO OUTFILE won't allow me to write the headers and footers. TEE includes the mysql> prompts, etc. There doesn't seem to be a straight forward fopen, printf sort of commands.

Question 2: How do you write a string like the header lines? PRINT doesn't seem to work.

Store And Output Formatted Text
I would like to build up a database of poetry but if I store the body text as 'text' it looses its formatting and then prints without the line breaks. How do I get around this?

Storing Large Formatted Documents
I am developing a resume storage system, and don't know the best way to store the resumes that come in to our company in both MS Word and text files. Should I store the files in the original format they come in, and reference the file name in the databse that points to a directory where they are stored, or should I store the text of the resumes directy in the database. There are 2 things that I must follow.
1: I need to have the documents keep their formatting.
2: I also need the capibility of conducting a full text search to pull out key words from the documents.

Using Keys Properly
I have a couple questions on how to properly create keys. Basically i know nothing about them, how they work, how it speeds things up, and most importantly, where to create them to help speed up my tables.

I do have multiple tables that are used and joined together, i just dont know where to make keys. I heard that if you do it improperly, it can slow down your database too?

UPDATE Will Not Add Properly
I have an online comic engine I'm working on, and I just noticed that, as opposed to adding one view (like it should) it will always add two. Can anyone help me out with this? I've already determined it's just this line of code, and that it's not being called twice.

$query = "UPDATE `".SQL_COMICS."` SET comic_views = (comic_views + 1) WHERE comic_id = '".$comic_id."' LIMIT 1";
mysql_query($query) or die(mysql_die_message());
echo($query);

Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like:
2/2/2006 0:00:00, 12/20/2006 9:22:05

Any ideas how to format those into mysql friendly dates?

Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula?

===========================================

LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt'
INTO TABLE auto
(field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9)
SET id = MD5(UUID())
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'

Show Limits Of A Data Interval ... Formatted
I've executed this query successfully...

SELECT NAME,DATE_FORMAT(DATEBORN,'%d/%m/%Y') AS BDAY WHERE WEEK(DATEBORN) = WEEK(CURRENT_DATE()) ORDER BY DATEBORN ASC

This script is executed every friday with cron, and shows all the people whom had birthday in last 7 days...

Now, How could I modify query to show something like:

"Listing of people whom had birthday between 20/07/2005 and 27/07/2005" (I'd like to know how to get the date limits in this interval of 7 days selected)

I mean, how to show date interval/range of people whom were selected in the script execution, no matter which day script was execd.

Properly Querying A Relational Db.
I've recently been working with more relational dbs and I was just wondering...am I working with them properly?

The reason I ask is because I'm wondering if there are easier/more efficient/more "proper" ways to be working with a relational db.

So here is an example: two tables, a project table and an employee table. The project table stores employee id in emp_id and is linked to the employee table by a foreign key (a one to one relation).

SELECT P.project_id, E.first_name, E.last_name, P.name, P.code, P.start_date, P.end_date, P.imdb
FROM project AS P, employee AS E
WHERE E.emp_id = P.supervisor
The thing is eventually we'll start to have tables with 4-5 foreign keys and I'm wondering if this is still "proper". Honestly it may seem a stupid question to some of you experts :-p

Getting Utf8 To Work Properly
I have made a few tables in a database, and I'm trying to get my Japanese text to display properly. I have the collation set to utf8_general_ci, and I even added the mysql_query("SET NAMES 'UTF8'"); code into the php page. But even with all of that in, it still won't display properly. The characters show up as ã‚»ã‚*ãƒã‚¯ã«ã‚“ã˜ã‚ƒãŸã„ã‘ã¤ï¼ and other junk like that.
Now this was working before until I added a second mySQL connection into the page, which inputs the second table. I'm not sure if you can make 2 connections on the same page, but I did and it pretty much messed everything up. I took the second table away, and things were still messed up.
My question is how can I get the Japanese text to display properly? It does display properly on the phpMyAdmin page, but I get garbage on the actual page.

How To Install Mydsl Properly
is there a step by step guide for installing mydsl properly and creating a databse?

Search Does Not Match Properly
i had created a search function on a web application.

the problem is, i try enter some of info to it, certain text can be found but certain text can be match although they are in database.

this my query:

$keywordu = strupper($keyword);
$keywordl = strlower($keyword);

$sql = "SELECT E.* FROM employes AS E, dvi_problem AS DV WHERE AS.empId = DV.empId AND (E.emp_name LIKE '%".$keyWordl."%' OR DV.reference LIKE '".$keywordi."''%".$keywordl."%' OR E.emp_name = '%".$keywordu."%' OR DV.reference LIKE '%".$keywordu."%')"

$query = mysql_query($sql, $db) or die('Fail to search.');

i test with direct copy the data from database, some string i copy can search it but some other string i copy direct it can work wo...

i try show out the variable $sql value, it show with not error, the keywordu n keywordl aslo show out the value when i print out the $sql variable.

Mysqld Is Not Running Properly
I have installed mysql server & client version 3.23.54a from Redhat installation 9.0. I could install the RPM 's with out any problem.

I can see 'mysqld' program . But when I run it shows a message like mysqld started [ok].When I checked 'ps aux' , I could not see any process running with mysqld. Also I could not run any programs like mysqladmin etc. All of them fail. does anybody know what is the mistake ? What should I do ? Also when I try to stop mysqld it fails.

Designing Databases Properly
After what seems like far too much agonizing, I think I've decided to build my own php "shopping cart" and hack my own interface to paypal IPN, because all the other solutions I've come across are massively oversized and overdeveloped for my needs.

But I could use some help in setting up the structure. I'm not sure of the best way to handle "options" with my various products.

First of course I set up a basic database with product fields like:
sku, price, name, etc. etc.

The tricky part is that some products will have special options, some dropdowns and some text fields, and I know I won't be able to predict all these in advance, like:
item color, accessory color, font, initial, initials ...

In addition, some products will call for extra product photos and others won't ...

And then I will need some way of storing all these extra options for a given customer's order when it comes through.

Extended-insert Not Working Properly
I am having a problem with using the --extended-insert option when using mysqldump. As I understand it, this option creates multiple insert statements within the .sql file for tables that contain more than 1 row of data.

According to documentation I have found, this substantially speeds up the restoration process. The main database I am concerned with uses the InnoDB engine, however I also want to backup the mysql database as well (which, to my knowledge, uses the MyISAM engine).

However, when I execute the database dump using this option, there is only 1 insert statement, and all values are seperated by commas. When trying to restore my databases, this causes my system to eventually freeze up due to the inefficiency of inserting multiple rows this way. I am using Windows XP, 1.8 Ghz, 512 MB of RAM.

The command I am issuing is as follows:

mysqldump --opt --single-transaction --u root --all-databases > "C: estbackup.sql"

I have also issued the following command, with the same result:

mysqldump --extended-insert --single-transaction --u root --all-databases > "C: estbackup.sql"

Properly Re-starting Replication Process
I've just tried to re-start a replication process and the slave's
tables came up marked as crashed. I'm doing a repair on one of them but
there are a lot of databases/tables so I can't check them all (or is
there a table entry somewhere that flags crashed tables?) Here is the
sequence I used: Code:

Storing Large Integers Properly
I am storing book isbn numbers in a table. isbn numbers are 10 digit
numbers and many start with 0. The data type of the field I am storing
this info in is a bigint(16) unsigned.

It appears that isbns that start
with 0 are going in as 9 digit numbers, the 0 is being ignored or
stripped. I have experienced this before with integer data types
ignoring leading 0s. I'm wondering how to address this? Should I
change the field to a varchar or char data type?

Two Digit Numbers Not Formatting Properly
I have a table I am using to populate drop-down menus. It is for languages.
each language has a specific code that must be saved to the field in question.They work fine except for two codes: 00 and 01.
I can't change the codes to anything else. They have to be 00 and 01. THey are saving to the spreadsheet as 0 and 1 however.

Is It Good To Store Html-formatted Data In Mysql Tables?
I always thought it is best to keep as much html as you can out of your stored data, in order to have flexible data. I am working now on a user friendly news-editor where the user can add news and format the text as wished (put links, bold/underline/italic etc..).

I am however a bit struggling with how I should proceed with this data. Do I just store it in the appropriate Mysql table and let it come out on the appropriate front-end news page? And not bother about the html in it? What if a user messes with the html? Should I strip all tags before storing it in the database and add them again while retrieving it on the news-page? If so, I know how to convert a few things, but not everything (like links, bold/underline..). Or are there other solutions?

Thanks!

Mysqlcheck: Clients Are Using Or Haven't Closed The Table Properly
I'm using the command mysqlcheck --all-databases --auto-repair --silent
and I see these warnings (listed below) on a daily occurrence. Is mysqlcheck
causing these warnings? I can't see how a table can close inappropriately.
How can I prevent this.

Hoping to here from MySQL users who have came across this. I'm using MySQL
4.1.11 on RHEL. Code:

Getting Text Retrieved From Mysql To Fit Tables Properly
I have been working on a really simple mysql/php set of pages.

Page 1: Input 3 peices of data (Title, Date and Text)

Page 2: Display all news that has been inputted

I have it working, except for one problem. My website is built with html, and tables. I want it so that when the text gets to the edge of the table, it drops down a line and starts again on the next line - so it doesn't carry on and on.

Theres no problem with title and date - they're just a few words, but the text part of the news is usually 5 or 6 lines long. This means that it wont all fit on one line without ruining the entire website display. Code:

How To List All Missing Dates Between 2 Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Stroring Dates Perior To 1 AD, BC Dates
I need to store some dates that can be BC, However I received a problem indicating Incorrect Datetime.

I have read the documentation, and it says that Date&DateTime types can store from 1000-9999 AD, and Timestamp stores less year ranges. What other options do I have to store dates before 1000 AD !

List All Dates Between 2 Diff Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Dates, Dates, Dates - Syntax Help Please!
OK, I have a MySQL table which contains, among others, a field called date which stores dates in the format YYYY-MM-DD HH:MM:SS.

What I'd like to do is select a list of all the months present (without repetition), preferably in the format YYYY-mm

How To ORDER BY DESC Properly. It Makes 150 Before 50 If ASC Order!?
I have this list:
50
150
200
250
300
350
400
450
500

If I order it by ASC order then mysql results this:
150
200
250
300
350
400
450
50
500

What the hell ?? How do I order it properly?

Get From Row After Inserting
I am building an application that will allow users to save a set of data to a database. What I need, is when I insert a new row into the database, to be able to capture the id (auto incrementing) of the row they just entered, and store that as a session cookie, so they can make changes to the data, and overwrite the old information.

I already have a standard INSERT command, but what would be the best way to grab the id of the row that was just entered? I could do a count of rows, and grab the last row, but it won't always be the last row (someone else who is using the application could have added a row right after you)

Inserting A GMT
I was wondering, is there any mysql function or any simple way I can insert the time of GMT 0 hours, into the mysql database?

Inserting '@' Or '[ ]'
I've been looking around for a problem relating to this...there's a guy I'm helping who's trying to update his db with new values, but whenever he puts in the characters '@', '[', or ']' it sends back an error saying there was an error in his syntax. I'm using php and thought that something like htmlspecialchars() might be what I needed but it didn't seem to cover it so I looked at quotemeta() but it doesn't cover the '@' sign.

Inserting Into A Many-to-many Relationship
I'm having a lot of trouble figuring out how to insert data into my setup. I layed out the relevant fields below..

imagespic_idtags_xrefpic_idtag_idtagstag_idtag - UNIQUE
how can I insert all unique tags, at the same time inserting into xref? I was thinking that I would first have to insert ignore into tags.. but then the xref table would have to know the successfully inserted tag_id's PLUS the tag_ids that are already in the tags table that are relevant to pic_id.. in order to do it's own insert.

I hope i'm making sense, if not, please let me know i'll try to explain this better. Any help would be greatly appreciated.

Inserting 0's Into INT Fields
Just wondering if there is a way to make an INT field type accept a number with a 0? for example, a phone number with area code 07 currently inserts as just 7.

I have gone for varchar instead, was this my only option?

Get The Id Before Inserting The Record?
Probably not as complex as it sounds, but basically when someone fills out a form, I'd like to give that group of fields it's own id. So, it would have a group id, and then each item would have that group id associated with it. Is there a way to do this in one query?

Inserting And Checking
I have a user table and a mailing list table. The user table contains names, email addresses, etc. The user can subscribe to mailing lists, in which the user's id is then placed into the mailing list table with the user's id.

USERS
subIDemail
1sub1@email.com

LISTS
idlistIDsubID
111
221
So, when inserting into the db, the admin types/pastes the email address into a textarea and selects the corresponding checkbox next to the mailing list they want to subscribe that user to.

Now the rub: what if you are importing email addresses from multiple lists, and each list contains an instance of an email address? For example, I want to import from 3 separate lists; AAA, BBB, and CCC and each list has the email address foo@bar.com. Once I insert foo@bar.com into the db and assign it the the AAA list, how do I get it into the BBB and CCC lists automatically without having duplicates in the USERS table?

I can't ask the admin to clean the existing lists, because they have 8 of them, and you can imagine the nightmare that would be trying to separate 40,000 email addresses into all of the possibilities.

This is the only thing I can think of right now, but it looks like it would be a killer on resources

upon submission

foreach {
check the user table for foo@bar.com

if foo@bar.com does not exist {
add it to users table
add to chosen list(s)
} else {
get foo@bar.com's id number from the users table
check the lists table for any list that foo@bar.com belongs to

if foo@bar.com doesn't belong to the selected lists {
insert the id number for each chosen list
}

Inserting Into DB Via Form
I want to allow the user to insert data into a DB via a form. It won't be secure or anything. Just a simple script to allow the user to enter information. If anybody knows how I can do this, please speak up.

Inserting Data
Everytime I insert data into a table, The rows are rearraged so the primary key column is in alphabetical order. How does this happen? and how do I fix it?

Inserting The Plus Operator?
I'm attempting to insert into a varchar field a value which includes the operator plus. Meaning I'm trying to enter a value like '10+'. Problem is that mysql doesn't like it.

This value is being entered via a PHP form, I already have mysql_real_escape_string already being performed on it but it seems to not pick up arithmetic operator characters. Can anyone suggest a solution?