php - How do I import date and price thru phpMyAdmin to then sort correctly? -

php - How do I import date and price thru phpMyAdmin to then sort correctly? -

i'm new mysql , learning lot quickly. main objective learning has been export info 1 source, import phpmyadmin. info first export delivered in next format;

entry_date - mm/dd/yyyy cost - "123,456"

i have thought set fields type;

entry_date type: date cost type: mediumint(10) unsigned

however, such, dates stored 00-00-0000 , cost stored first 3 digits (up until comma ',').

if alter type to;

entry_date type: char(10) cost type: varchar(10) unsigned

now value stored properly, ability sort info defeated, both date , cost sort until first ' / ' , ' , ' (respectively).

what's move here have db recognize date , cost proper sorting, respect format in receive exported info in first place?

i'm not sure input format, want utilize float type cost , date type date. way sorted correctly.

you should utilize proper info types database columns. please see this article farther learning of mysql info types.

update #1

if info specified in wrong format not recognized rdbms maybe it's improve approach pre-process input file in order re-format according mysql requirements.

but rather create programme parse such input file , insert info mysql database manually. way able re-format values according needed formats.

update #2

it possible utilize 2 columns in order info needed format.

create 2 columns:

date_as_char varchar(10) not null -- hold initial date string in original format date_as_date date default null -- hold converted value

then, insert original string dates first column.

after can utilize next query convert info varchar(10) proper date:

update `table` t set t.date_as_date = str_to_date(t.date_as_char, '%m/%d/%y') ;

and can drop first column if want so, there no need store duplicate data.

here's sql fiddle demonstrate solution:

http://sqlfiddle.com/#!2/88e202/1

if info converted null initial info , specified parsing string, i.e. %m/%d/%y doesn't much.

here's documentation str_to_date function.

php mysql sql