Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Dec 2009
    Location
    Indianapolis
    Posts
    31
    Thanks
    6
    Thanked 0 Times in 0 Posts

    SELECT DISTINCT returning duplicates

    Hello. I have a table title_views with each row being a "view" with a user_id, title_id, prog_id, date, and id. I want to gather just the user_ids without it returning duplicate user_ids. Right now I'm receiving duplicate user_id. Any help is appreciated. Thank you.

    PHP Code:
     <?PHP 
    include("db.php"); 

       
    $qrymysql_query("SELECT * FROM programs")
     or die (
    mysql_error());
     
     while(
    $row mysql_fetch_array($qry)){

    $prog_titles $row['titles'];
    $prog_titles explode(',' $prog_titles);
    $prog_id $row['prog_id'];

    array_shift($prog_titles); 
                        
                        foreach(
    $prog_titles as $prog_title){
                            
                        
    $trimmed trim($prog_title); 
           
                        
    $qrymysql_query("SELECT DISTINCT user_id FROM title_views WHERE title_id = '$trimmed' and prog_id = '$prog_id'  ")
                        or die(
    mysql_error());
                        
    $row mysql_fetch_array($qry);
                        
                        echo 
    $row['user_id'] . "<br />";
                         
                        }  
    // end foreach

                        
    //end while

    ?>
    Last edited by kirkh34; 09-13-2012 at 09:37 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This isn't caused by your query. This is caused by your looping and many queries.
    Since you execute this query and it only retrieves one record:
    PHP Code:
                        $qrymysql_query("SELECT DISTINCT user_id FROM title_views WHERE title_id = '$trimmed' and prog_id = '$prog_id'  ")
                        or die(
    mysql_error());
                        
    $row mysql_fetch_array($qry);
                        
                        echo 
    $row['user_id'] . "<br />"
    If any other of the queries created by the use of the foreach loop pull the same user_id, then these are considered unique as the query is unique.

    If the tables were properly normalized, a query could be written that simply joins as necessary.

    Without it, you need to capture $row['user_id'] in an array, and write a array_unique call to remove duplicates after the foreach (possibly the while, depending on at what level the userid is considered unique - programs or title views). You can also change the where clause to use an IN and provide it with all the title_id's required.

  • #3
    New Coder
    Join Date
    Dec 2009
    Location
    Indianapolis
    Posts
    31
    Thanks
    6
    Thanked 0 Times in 0 Posts
    thank you for the reply. how can i write this without having the queries in the loop to accomplish what i need? I need that title_id to run the query which is in array so I used a loop... I'm not sure how I can accomplish this

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I give up. If all you want are *ALL* the user_ids, once each, why are you involving the programs table, at all?

    And why are you mucking with WHERE title_id = '$trimmed' and prog_id = '$prog_id' ") at all?

    To get what you *SAY* you want, you simply need to do
    Code:
        SELECT DISTINCT user_id FROM title_views
    But you must have *some* reason for doing what you are doing, though I don't think we can figure that out from what you have posted so far.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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