SELECT DISTINCT returning duplicates

[kirkh34]

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

?>

[Jessica]

NEVER RUN QUERIES IN LOOPS.

 

Do 1 query at the start.

SELECT DISTINCT user_id, title_id, prog_id FROM title_views GROUP BY title_id, prod_id

[xyph]

Use

 

GROUP BY user_id

 

DISTINCT will return only unique results over all of your selected columns combined.

[Jessica]

I guess a better description of what the OP wants would help.

DISTINCT will return only unique results over all of your selected columns combined.

OP only selected user_id, so it would work that way except he's running the query for every title, so he's going to get each user for each title.

[kirkh34]

thank you for your replies.  I understand that it is wrong to run queries in loops, but I am not sure how I would complete it otherwise? I have to run that foreach loop in order to get the title_id needed to run the query.  Can you explain a different structure that would work for what I need?

[Jessica]

It's too bad I didn't already provide that.

[kirkh34]

I'm confused at what you mean? run one query at the start of what? I need the title_id in the array. I must use that loop?

[xyph]

By using a comma-delimited list in your table, you've made it very difficult to query against data within that list.

 

You're going to want to normalize that data, give each comma delimited item it's own row that references back to a specific program.