wrote a php script that indexes youtube video transcripts into a mysql database and it's become the most used internal tool at my agency

i work at a small digital agency and we produce a lot of video content for clients. tutorials, product walkthroughs, webinar recordings. the problem was finding anything after the fact. a client would ask "which video did we explain the checkout flow redesign in" and someone would have to scrub through a dozen youtube videos to find it.

i wrote a php script to fix this. it pulls the full transcript from a youtube video and stores it in a mysql table alongside the video title, client name, publish date, and tags. nothing fancy. the table has a FULLTEXT index on the transcript column.

for pulling transcripts i use transcript api:

npx skills add ZeroPointRepo/youtube-skills --skill youtube-full

the php side is just a cURL call to the api, json_decode the response, and an INSERT into the database. maybe 40 lines of actual code. i wrapped it in a simple form where you paste a youtube url, pick the client from a dropdown, add a couple tags, and hit save.

the search page is even simpler. a text input that runs a MATCH AGAINST query on the transcript column. results come back with the video title, client, date, and a snippet of the transcript around the matching text. click through to the youtube video.

we have about 180 videos indexed now across 12 clients. the account managers use it more than i expected. instead of pinging the video team on slack asking "which video covers X" they just search it themselves. that alone probably saved 3-4 slack threads a day.

the whole thing is one controller, two views, and a model. running on the same LAMP stack we use for everything else. took me a saturday afternoon to build and i've barely touched it since. the only thing i added later was a bulk import page so we could paste a list of youtube urls and process them all at once.