Results 1 to 8 of 8

Thread: alternative to DISTINCT ?

  1. #1
    Join Date
    May 2005

    alternative to DISTINCT ?


    I currently have the SQL statement:

    $query="SELECT DISTINCT original FROM phrases WHERE original LIKE '".$head."%' 
    		SELECT DISTINCT original FROM phrases WHERE original LIKE '".$subject."%' 
    		SELECT DISTINCT original FROM phrases WHERE original LIKE '".$listtype."%'
    		ORDER BY original DESC
    It works fine... however, I want to be able to change the search results so the user can list by date. To do this I tried:

    $query="SELECT DISTINCT original, date FROM phrases WHERE original LIKE '".$head."%' 
    		SELECT DISTINCT original, date FROM phrases WHERE original LIKE '".$subject."%' 
    		SELECT DISTINCT original, date FROM phrases WHERE original LIKE '".$listtype."%'
    		ORDER BY date DESC
    This didn't work properly as the DISTINCT command no longer works once two columns are selected, correct?

    DISTINCT (or an equivalent if there is one) HAS to be used in order to filter out duplicate results.

    What do I do? I'm stuck for ideas.

  2. #2
    Join Date
    May 2005

    Red face

    Actually the original code was wrong too. I'm completely stuck now.. Hopefully someone here will be able to point me in the right direction....


    As you can see on the website there are 3 menus. The idea is for one item on each menu to stay highlighted in order to filter down results. So if 'Art', 'A', and 'Most Recent' were highlighted, then the SQL query would get results that have 'art' as their subject and of them, only the results beginning with 'A'. They would then be ordered by date (most recent).

    Also, as a lot of the results have duplicate titles, they need to be filtered out to so that there is only ever one instance of each title.

    When each button is clicked it's value is added to the URL string and then put into variables ($alphabet, $subject, $listtype)

    I know that's quite a lot to ask so I'll just wait in hope. :| I've been trying to work it out all day and got nowhere so any pointer will be good.

    Last edited by danz321; 02-23-2011 at 11:55 AM.

  3. #3
    Join Date
    Dec 2002
    St. Louis, MO, USA
    I don't have time to go into a whole lot of detail, but I _think_ you can do this by LEFT OUTER JOINing the table to itself and getting (for each DISTINCT "original") all associated subject and listtypes.


  4. #4
    Join Date
    Jan 2003
    Dundee, Scotland

    I maybe reading this wrong so sorry if I am but it looks to me like your wanting to apply 3 filters to 1 table so can't you not just do:

    Select Distinct original FROM phrases WHERE original LIKE '".$head."%' OR original LIKE '".$subject."%' OR original LIKE '".$listtype."%' ORDER BY date DESC


  5. #5
    Join Date
    May 2005
    Thanks ribeyed, you got me one step closer... your query was almost right....

    I worked out this what I want:

    $query="Select Distinct original, subject, date FROM phrases WHERE original LIKE '".$alphabet."%' AND subject LIKE '".$subject."%' ORDER BY date DESC";
    however, as I need to select multiple columns from the table, DISTINCT will not work. At the moment the code is working fine except for the fact it spits out duplicates. How can I fix this?

  6. #6
    Join Date
    Jul 2010
    The "HAVING" clause is intended to be used as a filter.

    You may also GROUP BY (PK) if your records are truly duplicates. But I find that selecting duplicate records is the result of a bad query (from my limited experience anyways).

    Edit: UNION already implies DISTINCT. Saying SELECT DISTINCT ... UNION ... SELECT DISTINCT is redundant. UNION ALL is the non-distinct version.
    Last edited by eval(BadCode); 02-24-2011 at 06:45 PM.

  7. #7
    Join Date
    May 2005
    thanks eval(badcode), I needed to use GROUP BY to get it to work: Here's my final code for those who want to see...

    $query="SELECT original, subject, date FROM phrases WHERE original LIKE '".$alphabet."%' AND subject LIKE '".$subject."%' GROUP BY original";
    I saw that you added a database entry on my site about mysql_escape_string. I thought I had sorted that problem out by using stripslashes:

    echo '<u><a href="define.php?op='.stripslashes($row['original']).'">'.stripslashes($row['original']).'</a></u>';
    echo '<br>';
    but somehow your entry seemed to escape the stripslashes? obviously my code must be wrong somewhere. When the data gets inserted into the database I use the following code:

    $query="insert into phrases (original, inotherwords, author, tags, subject) values
    The above code adds the data to the database correctly (without slashes).. and the code used to display it works OK now.... the only problem is when you add an 'in other words' definition to an already existing phrase, the text box used to display the existing phrase adds slashes. You can see this here:


    The code used to display the existing phrase in the textarea is:

    echo '<p><textarea name="original" cols="50" rows="4" width="200" />'.stripslashes($phrase).'</textarea></p>';   }

    What's wrong?
    Last edited by danz321; 02-25-2011 at 07:04 AM.

  8. #8
    Join Date
    May 2005
    fixed it in the end... it was happening due to magic_quotes being on. I wasn't able to turn them off as I dont have access to the php.ini so I used this code as a work-around:

    if (get_magic_quotes_gpc()) {

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center



X vBulletin 4.2.2 Debug Information

  • Page Generation 0.13327 seconds
  • Memory Usage 2,911KB
  • Queries Executed 13 (?)
More Information
Template Usage (33):
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (9)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (8)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (8)postbit
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (71):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates