MySQL query concat problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
abshaw
Forum Commoner
Posts: 33
Joined: Thu Feb 19, 2004 7:14 pm

MySQL query concat problem

Post by abshaw »

I have php and mysql running, the database has a table named torders and there are 3 fields that store the date (not my fault, was handed to me like this-i know better than this)

Field Name------------Sample data
date--------------------7
month------------------November
year--------------------2006

all i am trying to do is write an sql query to

1. join / contatinate all these fields to make a string date
2. convert the string date to a real date
3. format the date to display as Tuesday, November 7, 2006
4. Sort order by this date as well.

here is what i have done so far and i am stratching my head up and down.

$query1 = "Select str_to_date(day-month-year, '%d-%b-%Y') as modate from torders order by modate";
second try was
$query1 = "Select str_to_date(CONCAT(day,'-', month,'-', year), '%d-%b-%Y') as modate from torders";
then i gave up and did not try the //DATE_FORMAT(, '%d/%M/%Y')"; at all.

can any one of you great mainds help
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Why not write a query (or series of them) to shift the separate fields into a single date field?
abshaw
Forum Commoner
Posts: 33
Joined: Thu Feb 19, 2004 7:14 pm

Post by abshaw »

how do i do that?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

First you have to add a column to the table that is a DATE type. Second, you figure out an UPDATE query that can combine the three separate fields and assign that result to the DATE field. Finally, you remove the three separate columns.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Moved to Databases.
Last edited by RobertGonzalez on Thu Feb 01, 2007 1:19 am, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

feyd wrote:First you have to add a column to the table that is a DATE type. Second, you figure out an UPDATE query that can combine the three separate fields and assign that result to the DATE field. Finally, you remove the three separate columns.
That's correct, of course, but I'm afraid the original question was how to combine the three separate fields and assign that result to the Date field. :? Converting the day and year are obvious, but you may have to populate a hash array with the month names and their equivalent numbers, to convert the month. Once you have all 3 as numbers, the conversion to date format should be quite easy.
Post Reply