51 users online (1 members and 50 guests)  


  Results 1 to 3 of 3

Related

  1. Select Query    Forum: Databases
    Replies: 2
  2. reading data line by line from textarea    Forum: ASP Forum
    Replies: 3
  3. Replies: 1
  4. Replies: 5
  5. document.write() query    Forum: Javascript Forum
    Replies: 4
  1. #1
    Synpax's Avatar
    New User

    Status
    Offline
    Join Date
    Feb 2005
    Posts
    2

    Will Paypal you $20 to write this one line select query

    I will paypal you $20 to write this single line of query.

    I have a MySQL table that I am querying. The query is quiet complicated so I was wondering if one of you brilliant fellows could help out someone new.

    I tried looking through the documentation for this - and normally the documentation is very helpful, but while I am sure that this select can be done i a single query, I'm at a loss as to how to write it.

    I am selecting all of the fields in the table known as content. Where the rub is is in the sort.

    An example would be to "Select * from content where parent = 'press' order by (you fill in this blank)"


    There are three fields involved in the sort. They are priority, pubdate, and publisher_TS.

    The priority field has numbers 0-9 in it. The pubdate has an eight digit date (YYYYMMDD) or may be blank. The publisher_TS has a full timestamp in it (YYYYMMDDHHMMSS).

    First I want priorities sorted from 1 to 9 excluding anything with a 0 priority. Then I want pubdate and publisher_TS sorted together - if a record has a pubdate entered, pad it out to the left with six zeroes and sort using that. If it doesn't have a pubdate, go ahead and use the publish_TS.

    Is this possible to do with one query?

    ---

    More Explanation:

    This is a table that controls news stories. THe query is determining what order they show up on a web page.

    Normally, they show up in order of the publisher_ts (timestamp). There are two over-rides to this.
    1) Pubdate. By entering a pubdate, they can specify the date that the story appears and what it's order is regardless of the publisher_ts. Normally this is empty.
    2) Priority. If they want to force a story to be in the top position, the can enter a 1-9. This way, no matter how many new stories are posted, these priority ranked records will get pulled first. Give three stories the priorities 1,2 and 3, and they will be the top 3 stories pulled.


    ----

    Table Design:
    Code:
    CREATE TABLE `content` (
      `uniquename` varchar(10) NOT NULL default '',
      `parent` varchar(10) NOT NULL default '',
      `creator` varchar(10) NOT NULL default '',
      `creator_ts` timestamp(14) NOT NULL,
      `editor` varchar(10) NOT NULL default '',
      `editor_ts` timestamp(14) NOT NULL default '00000000000000',
      `publisher` varchar(10) NOT NULL default '',
      `publisher_ts` timestamp(14) NOT NULL default '00000000000000',
      `draft` char(1) NOT NULL default '',
      `publish` char(1) NOT NULL default '',
      `priority` tinyint(4) NOT NULL default '0',
      `title` varchar(100) NOT NULL default '',
      `subtitle` varchar(100) NOT NULL default '',
      `credit` varchar(100) NOT NULL default '',
      `graphic_s` varchar(100) NOT NULL default '',
      `graphic_l` varchar(100) NOT NULL default '',
      `graphic_text` varchar(100) NOT NULL default '',
      `previewgraph` longblob NOT NULL,
      `body` longblob NOT NULL,
      `pubdate` int(8) NOT NULL default '0'
    ) TYPE=MyISAM;
    ----

    Sample Data:
    Code:
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('home', '', '', '20050215225428', 'braynard', '20050215225428', 'braynard', '20050215225428', '', '1', 0, 'Home', 'Welcome to the Media Freedom Project''s Website', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('aboutus', 'home', '', '20050215225055', 'braynard', '20050215225055', 'braynard', '20050215225055', '', '1', 0, 'About Us', 'Learn More About The Media Freedom Project', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press', 'home', '', '20050215225122', 'braynard', '20050215225122', 'braynard', '20050215225122', '', '1', 0, 'Press', 'Get the Latest News on Media Issues', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('join', 'home', '', '20050215225136', 'braynard', '20050215225136', 'braynard', '20050215225136', '', '1', 0, 'Join Us', 'Join Our Organization', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('contactus', 'home', '', '20050215225322', 'braynard', '20050215225322', 'braynard', '20050215225322', '', '1', 0, 'Contact Us', 'Get in Touch with The Media Freedom Project''s Staff', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('contribute', 'home', '', '20050215225402', 'braynard', '20050215225402', 'braynard', '20050215225402', '', '1', 0, 'Contribute', 'Support the Cause', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press01', 'press', 'braynard', '20050216170243', '', '00000000000000', '', '20050216170243', '', '1', 1, 'Press 01', '', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press02', 'press', 'braynard', '20050216170300', '', '00000000000000', '', '20050216170300', '', '1', 2, 'Press 02', '', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press03', 'press', 'braynard', '20050216170318', '', '00000000000000', '', '20050216170318', '', '1', 3, 'Press 03', '', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press04', 'press', 'braynard', '20050216170620', '', '00000000000000', '', '20050216170620', '', '1', 0, 'Press 04', '', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press05', 'press', 'braynard', '20050216170937', 'braynard', '20050216170937', 'braynard', '20050216170937', '', '1', 0, 'Press 05 with pubdate January 1', '', '', '', '', '', '', '', 20050101);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press06', 'press', 'braynard', '20050216170946', 'braynard', '20050216170946', 'braynard', '20050216170946', '', '1', 0, 'Press 06', '', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press07', 'press', 'braynard', '20050216170952', 'braynard', '20050216170952', 'braynard', '20050216170952', '', '1', 0, 'Press 07', '', '', '', '', '', '', '', 0);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('Press09', 'press', 'braynard', '20050216170838', '', '00000000000000', '', '20050216170838', '', '1', 0, 'Press 09 with March 30 Pub Date', '', '', '', '', '', '', '', 20050330);
    INSERT INTO `content` (`uniquename`, `parent`, `creator`, `creator_ts`, `editor`, `editor_ts`, `publisher`, `publisher_ts`, `draft`, `publish`, `priority`, `title`, `subtitle`, `credit`, `graphic_s`, `graphic_l`, `graphic_text`, `previewgraph`, `body`, `pubdate`) VALUES ('press08', 'press', 'braynard', '20050216170903', '', '00000000000000', '', '20050216170903', '', '1', 4, 'Press 08', '', '', '', '', '', '', '', 0);
    Last edited by HTML; 02-16-2005 at 06:05 PM.

  2. #2
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Hi, welcome to the forums.

    Personally, this looks to me to be a level of control that should be controlled either in the code itself, or possibly in a stored procedure. The padding and conditional sort are too complicated for a single query.

    Is there a specific reason why this has to be done in a single query? Or can we move this into the code itself?
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  3. #3
    Synpax's Avatar
    New User

    Status
    Offline
    Join Date
    Feb 2005
    Posts
    2
    Hi,
    I know this could be shifted elsewhere, but what I really want to know is IF it is possible and if so, how can it be done.



Tags for this Thread