Back To Programming / Scripting / Coding Forum

MySql select column names

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post January 26th, 2011, 1:48 pm

Hi there, I am working with the following table:
SQL Code: [ Select ]
CREATE TABLE `user`(
`id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`country` VARCHAR(100) NOT NULL,
`general_newsletter` CHAR(1) NOT NULL,
`sports_newsletter` CHAR(1) NOT NULL
)ENGINE=MyISAM;
  1. CREATE TABLE `user`(
  2. `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3. `name` VARCHAR(30) NOT NULL,
  4. `country` VARCHAR(100) NOT NULL,
  5. `general_newsletter` CHAR(1) NOT NULL,
  6. `sports_newsletter` CHAR(1) NOT NULL
  7. )ENGINE=MyISAM;


Let's say I have the following data in the table:
SQL Code: [ Select ]
INSERT INTO `user` VALUES(NULL,'RT','SA','1','0');
INSERT INTO `user` VALUES(NULL,'Joe','US','0','1');
INSERT INTO `user` VALUES(NULL,'Bob','UK','1','1');
  1. INSERT INTO `user` VALUES(NULL,'RT','SA','1','0');
  2. INSERT INTO `user` VALUES(NULL,'Joe','US','0','1');
  3. INSERT INTO `user` VALUES(NULL,'Bob','UK','1','1');


I am building a filter which consists of a bunch of dropdowns (name,country,newsletter) which automatically get populated as the other dropdowns change with ajax. So let's say I select RT from the 'name' dropdown, then 'country' will only contain 'SA' (SELECT DISTINCT(`country`) FROM `user` WHERE `name`='RT'). But what I want to know is, how can I automatically populate the newsletter dropdown with a mysql query.

For instance, when I select 'RT' as the `name`, then `newsletter` will only contain 'general_newsletter', but if I select 'Bob' as the `name` then `newsletter` will contain 'general_newsletter' AND 'sports_newsletter'. Is there a way for me to do this in a single query?
Let's leave all our *plum* where it is and go live in the jungle ...
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post January 26th, 2011, 1:48 pm

  • RedBMedia
  • Proficient
  • Proficient
  • User avatar
  • Joined: May 01, 2007
  • Posts: 315
  • Status: Offline

Post January 30th, 2011, 8:26 am

I had a simular issue recently, and I had to write a series of IF statements. It wasn't very clean, but it worked.
Joe Hall

Page 1 of 1

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: Kurthead+1, ScottG and 102 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.