CodingForums.com - SELECT DISTINCT returning duplicates

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - SELECT DISTINCT returning duplicates (http://www.codingforums.com/showthread.php?t=273059)

kirkh34

09-13-2012 09:22 PM

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"); 

   $qry= mysql_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); 
       
                    $qry= mysql_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

?>

Fou-Lu

09-13-2012 09:49 PM

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:


		
			
                    $qry= mysql_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.

kirkh34

09-13-2012 10:47 PM

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

Old Pedant

09-14-2012 10:13 PM

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.

All times are GMT +1. The time now is 09:30 AM.