[SOLVED] joining two fields

[SusieWoosey]

MySQL client version: 4.1.22 - Server version: 4.0.25-standard - phpMyAdmin - 2.10.0.2 (it's what they provide).

 

My table was originally defined thus:-

 

CREATE TABLE `triviadata` (

  `id` int(11) NOT NULL auto_increment,

  `date` varchar( NOT NULL default '',

  `year` varchar( NOT NULL default '',

  `data` text NOT NULL,

  PRIMARY KEY  (`id`)

) TYPE=InnoDB ;

 

 

date contains the day/month part of the date i.e. 18 Apr, year contains a four digit number i.e. 1969.

 

 

I want to join two fields together from my database into a new field on a search query, namely date and year fields. I have tried searching on the 'net for a solution  but all I can find is ways of joining tables. I can do that, but cannot get two fields to join. My attempts give results which are not really what I require.

 

SELECT id, date & ' ' & year AS newdate, data

FROM `triviadata`

WHERE `year` LIKE '%1969%'

LIMIT 0 , 30

 

it outputs the id, and data fields ok, but the newdate field is a 'zero'. I figure it is ANDing them together maybe?

 

SELECT id, date + ' ' + year AS newdate, data

FROM `triviadata`

WHERE `year` LIKE '%1969%'

LIMIT 0 , 30

 

this doesn't work either. All I get is the year field with a nuber from the date field added to it.

 

SELECT id, date || ' ' || year AS newdate,

DATA

FROM `triviadata`

WHERE `year` LIKE '%1969%'

LIMIT 0 , 30

 

this doesn't work, all I get is the newdate field equal to '1' for all occurences of 1969 that are found.

 

 

I know I probably should have used a proper date field, but it can't cope with dates prior to year 1000 and my trivia database has many entries for years prior to this, including BC years.

 

 

So how do I do it?  I can't write my php till I know how.

 

 

 

Suzanne.

[SusieWoosey]

I solved it.

 

 

Suzanne