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