Selecting data using dynamic SQL query

Question

I have a table where i have a column named 'type'. I am keeping table name in that column (as the type name). I want to check the type (table name) of a row and get data from that table using an id. Is it possible to do in one single query?

Here is the structure of main table -

    CREATE TABLE `post` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `column` enum('1','2','3') NOT NULL,
  `type` enum('post_image','post_video') NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 NOT NULL,
  `description` text CHARACTER SET utf8 NOT NULL,
  `thumb` varchar(255) NOT NULL,
  `post_date` datetime NOT NULL,
  `views` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Here are two table for two different types in the above column -

    CREATE TABLE `post_image` (
  `post_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `image` varchar(255) NOT NULL,
  PRIMARY KEY (`post_image_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


    CREATE TABLE `post_video` (
  `video_remote_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `video_type` enum('vimeo','youtube') NOT NULL,
  `video_id` int(11) NOT NULL,
  `video_url` varchar(255) NOT NULL,
  `video_title` int(11) NOT NULL,
  `video_duration` int(11) NOT NULL,
  PRIMARY KEY (`video_remote_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Can you please give table structure , it will be helpful. Thanks. — Alex Zheka
– Alex Zheka, Commented Mar 14, 2013 at 8:45
@AlexZ - i have given the structures in the question description... — rafi
– rafi, Commented Mar 14, 2013 at 8:50
@DevangRathod - i have tried like this select 'select * from post p left join ['+p.type+'] d on p.id = d.post_id' — rafi
– rafi, Commented Mar 14, 2013 at 8:51

Devart · Accepted Answer · 2013-03-14 08:51:40Z

1

In a single query - NO.

Try to use prepares statement. Read the name of the table into the variable, build the SELECT query string and execute it using EXECUTE command.

answered Mar 14, 2013 at 8:51

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rafi Over a year ago

I see... Thanks for the help. Now then i will prefer to do this logic using php rather than mysql... Thanks again!

Collectives™ on Stack Overflow

Selecting data using dynamic SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related