SQL results displays same result multiple times

I have a query that searches all videos that a selected instructor is in. The query seems to be working just fine and it pulls all the proper rows but it shows each result multiple times until the last row that is only displayed once like the rest of them should be. any ideas?

> <?php
> $sql = "SELECT videos.videoId, videos.title, thumbs.thumbnail
> 					FROM info
> 					LEFT JOIN videos
> 					ON info.videoId=videos.videoId
> 					LEFT JOIN thumbs
> 					ON videos.videoId=thumbs.videoId
> 					LEFT JOIN link
> 					ON videos.videoId=link.videoId
> 					WHERE info.star LIKE '%$instructor%'
> 					";
> $result = $connect->query($sql);

> if ($result->num_rows > 0) {
> 	$row_cnt = $result->num_rows;
> 	echo "<h1>" . $row_cnt . " videos found</h1>";
> 	$i = 0;
> 	echo '<table width="700px" align="center">';
>     // output data of each row
>     while($row = $result->fetch_assoc()) {
> 		
> 		if($i == 0){
> 		echo "<tr>";
> 		$i++; //$i = $i + 1 - counter + 1 
> 		}
> 		if($i > 0) {
> 		if(!empty($row['thumbnail'])){
> 			echo "<td width='300px' title='" . $row['title'] . "'>" . $row['title'	] . "<br /><a href='watch.php?vid=" . $row['videoId'] . "'><img src='" . $row['thumbnail'] . "' width='286' height='161'></a></td>";
> 		}
> 		else {
> 		echo "<td width='300px' title='" . $row['title'] . "'>" . $row['title'	] . "<br /><a href='watch.php?vid=" . $row['videoId'] . "'><img src='thumbs/default.jpg'></a></td>";
> 		}
> 		$i++; //$i = $i + 1 - counter + 1 
> 		}
> 		if($i>5){
>             $i=0;
>             echo '</tr>';
>         };  
>          
>     }
> 	echo "</tr></table>";
> } else {
>     echo "0 results";
> } ?>`

`

Is there something missing from this part of the code you posted?

Not that I can see. I’m not sure why the code is not broken up by lines in my last post or why $row_cnt is bold. Having played around with it a little more I noticed that some query display just fine and only pull up each video once while others still continue to display the same result 5 to 7 times each showing a total of 45 videos found because it counts and shows the same video so many times

$result = $connect->query($sql);

> if ($result->num_rows > 0) {
> 	$row_cnt = $result->num_rows;
> 	echo "<h1>" . $row_cnt . " videos</h1>";
> 	$i = 0;

I don’t see a line of code where you actually fetch the results from the query, where it populates the $row array. Or am I blind?

Im not sure why it did not copy all of my code and thats my fault for not looking over the code before posting it.

  <?php
    		$sql = "SELECT videos.videoId, videos.title, thumbs.thumbnail
    					FROM info
    					LEFT JOIN videos
    					ON info.videoId=videos.videoId
    					LEFT JOIN thumbs
    					ON videos.videoId=thumbs.videoId
    					LEFT JOIN link
    					ON videos.videoId=link.videoId
    					WHERE info.star LIKE '%$instructor%'
    					";
    $result = $connect->query($sql);

    if ($result->num_rows > 0) {
    	$row_cnt = $result->num_rows;
    	echo "<h1>" . $row_cnt . " videos</h1>";
    	$i = 0;
    	echo '<table width="700px" align="center">';
        // output data of each row
        while($row = $result->fetch_assoc()) {
    		
    		if($i == 0){
    		echo "<tr>";
    		$i++; //$i = $i + 1 - counter + 1 
    		}
    		if($i > 0) {
    		if(!empty($row['thumbnail'])){
    			echo "<td width='300px' title='" . $row['title'] . "'>" . $row['title'	] . "<br /><a href='watch.php?vid=" . $row['videoId'] . "'><img src='" . $row['thumbnail'] . "' width='286' height='161'></a></td>";
    		}
    		else {
    		echo "<td width='300px' title='" . $row['title'] . "'>" . $row['title'	] . "<br /><a href='watch.php?vid=" . $row['videoId'] . "'><img src='thumbs/default.jpg'></a></td>";
    		}
    		$i++; //$i = $i + 1 - counter + 1 
    		}
    		if($i>5){
                $i=0;
                echo '</tr>';
            };  
             
        }
    	echo "</tr></table>";
    } else {
        echo "0 results";
    }
    		
    		?>

I have a vague idea you might want to use INNER JOIN instead of LEFT JOIN if you don’t want duplicate rows returned. Have a read through on the various joins.

Thanks for the reply. I had already tried inner join and I get the exact same results. Sometimes the results display properly while other results with a different instructor are still duplicated.

Clutching at straws, but what about if all of your JOIN clauses joined to the info table:

LEFT JOIN videos ON info.videoId=videos.videoId
LEFT JOIN thumbs ON info.videoId=thumbs.videoId
LEFT JOIN link ON info.videoId=link.videoId

If your query returns “duplicates” it’s because your join conditions are not unique, in other words, in one or more of the joined tables the videoId returns multiple rows.
You could use SELECT DISTINCT if the columns returned by the SELECT statement all contain the same duplicate data. Otherwise you’ll have to change the query in such a way that the joins don’t produce duplicates. But that depends entirely on the content of those tables and the logic the query needs to follow to give you the data you need.

Don’t know if my reply will help but

What’s the code for $instructor?

$instructor = //php code here

and did you try WHERE info.star='$instructor’

also would turning that into this help? (just try this, leave out the videos.title and thumbs.thumbnail for now, you can add those later if the query works)

$sql = "SELECT videos.videoId FROM videosLEFT JOIN info ON info.videoId=videos.videoId, info.star = '$instructor' LEFT JOIN thumbs ON thumbs.videoId=videos.videoId LEFT JOIN link ON link.videoId=videos.videoId

I had not considered that the video may have been mistakenly added to the database multiple times. I have checked all the tables to insure that there is only one match in each table and there are no duplicates.

In my info table I have a column called “instructors” this column is populated from a page meant to update all the information on any given video in the database. The instructor(s) are added in a text field with a , between each instructor if there are multiple people starring in that video. I then retrieve that string from the table and display each result like this

$StarsArray = explode(‘,’, $stars);

foreach($StarsArray as $link) {
echo “” . $link . ", "; // print each link etc
}

You should never store comma separated lists in a database as

  1. the field will run out of space when there are too many names
  2. it becomes almost impossible to extract based on individual nales

Make it a separate table instead.

Thank you for the tip. If I created a separate table would I need to have each name under its own unique row? something like: instructorId, videoId, instructor

What would be the ideal way to handle cases where the instructor is in multiple videos?

Two fields - one to identify the video and the other to identify the instructor.

Wouldn’t you have a table for instructors (instructor_id, instructor_name) and another table that links the instructor to the video (video_id, instructor_id) which would link multiple instructors to a single video, or single instructors to multiple videos, or multiple instructors to multiple videos?

1 Like

In that case could you please give us a data example of a video that should be there only once, but the query gives multiple results? The data for that video present in each table.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.