www.webdeveloper.com
Results 1 to 8 of 8

Thread: alternative to DISTINCT ?

  1. #1
    Join Date
    May 2005
    Posts
    76

    alternative to DISTINCT ?

    Hi,

    I currently have the SQL statement:


    Code:
    $query="SELECT DISTINCT original FROM phrases WHERE original LIKE '".$head."%' 
    		UNION
    		SELECT DISTINCT original FROM phrases WHERE original LIKE '".$subject."%' 
    		UNION
    		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:


    Code:
    $query="SELECT DISTINCT original, date FROM phrases WHERE original LIKE '".$head."%' 
    		UNION
    		SELECT DISTINCT original, date FROM phrases WHERE original LIKE '".$subject."%' 
    		UNION
    		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
    Posts
    76

    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....

    http://www.badnutbeats.co.uk/inotherwords

    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.

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

  3. #3
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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
    Location
    Dundee, Scotland
    Posts
    1,367
    HI,

    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:

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

    Ribs

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

    I worked out this what I want:

    Code:
    $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
    Location
    /ramdisk/
    Posts
    865
    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
    Posts
    76
    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...

    Code:
    $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:


    Code:
    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:

    Code:
    $query="insert into phrases (original, inotherwords, author, tags, subject) values
    ('".mysql_real_escape_string($original)."','".mysql_real_escape_string($inotherwords)."','".
    mysql_real_escape_string($author)."','".mysql_real_escape_string($tags)."','".mysql_real_escape_string($subject)."')";
    
    $result=mysql_query($query);
    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:

    http://www.badnutbeats.co.uk/inother...sttype=popular


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

    Code:
    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
    Posts
    76
    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:


    Code:
    if (get_magic_quotes_gpc()) {
    $original=stripslashes($_POST['original']);
    $inotherwords=stripslashes($_POST['inotherwords']);
    $author=stripslashes($_POST['author']);
    $tags=stripslashes($_POST['tags']);
    $subject=stripslashes($_POST['subject']);
    }

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



Recent Articles