Help me on this mysql

Databases
1

–
– Table structure for table column_ads

CREATE TABLE IF NOT EXISTS column_ads (
id int(5) NOT NULL AUTO_INCREMENT,
column_id_1 int(10) NOT NULL,
column_id_2 int(10) NOT NULL,
column_id_3 int(11) NOT NULL,
column_id_4 int(11) NOT NULL,
column_id_5 int(11) NOT NULL,
column_id_6 int(11) NOT NULL,
column_id_7 int(11) NOT NULL,
column_id_8 int(11) NOT NULL,
column_id_9 int(11) NOT NULL,
page_id int(5) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

–
– Dumping data for table column_ads

INSERT INTO column_ads (id, column_id_1, column_id_2, column_id_3, column_id_4, column_id_5, column_id_6, column_id_7, column_id_8, column_id_9, page_id) VALUES
(1, 6, 2, 3, 2, 3, 3, 3, 3, 3, 1),
(8, 2, 2, 3, 2, 3, 3, 3, 3, 3, 34);

–
– Table structure for table advertisement

CREATE TABLE IF NOT EXISTS advertisement (
ad_id int(11) NOT NULL AUTO_INCREMENT,
title varchar(60) NOT NULL,
script text NOT NULL,
created_on varchar(30) NOT NULL,
PRIMARY KEY (ad_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

–
– Dumping data for table advertisement

INSERT INTO advertisement (ad_id, title, script, created_on) VALUES
(3, ‘gghf’, ‘<html><title>hello</title></html>’, ‘March 22, 2010, 6:56 am’),
(2, ‘sofent’, ‘<script typefghf="text/javascript>\r
hi</script>’, ‘March 22, 2010, 6:51 am’),
(5, ‘vcbcvb’, ‘<html><title>hello</title></html>’, ‘March 23, 2010, 8:37 am’),
(6, ‘fdg’, ‘<html><title>hello</title></html>’, ‘March 23, 2010, 9:47 am’);

Actually
i need to extract the data from script column from advertisement table…

notes: column_id_1,…column_id_9 from column_ads table holds advertisement table ad_id data.
i need to extract the script from advertisement table by column_id_1—column_id_9 where page_id = (example:1)

output like:

column_id_1–>2 means i want to extract <script typefghf="text/javascript>\r
hi</script> bec ad_id is 2

2

If you normalize your first table to this:


CREATE TABLE IF NOT EXISTS `column_ads` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`column_id` int(5) NOT NULL,
`ad_id` int(11) NOT NULL,
`page_id` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Then joining it with the second table becomes much easier.

3

dear nsatheesh87 … see? i’m not the only one who suggests this :slight_smile: :slight_smile:

and i already showed you how to write the series of joins you will need