Download Subtitles and Closed Captions (CC) from YouTube

Enter the URL of the YouTube video to download subtitles in many different formats and languages.

3 11 PHP and MySQL Fetch data from database with English subtitles   Complain

alright so the UI is done we're

connected to the database what we want

to do now is I want to pull the

questions and choices from the database

but right now we don't have any

questions of choices in there so we can

easily insert questions and choices

through PHP myadmin

all right so let's click on questions we

got a login again

so questions and then click on insert

alright so I'm just gonna grab a few

questions I'm gonna use the w3schools

PHP quiz

all right and this is actually the

question that I art that I used so I'm

gonna copy that

and we'll just add that here okay so the

question number I want this to be

question 1 and then question 2

the answer here is this anyways okay so

actually I don't want this question too

many confusing characters

okay this is a good one copy this


all right so we get two questions

right here okay so now we want to add

our choices insert and let's do

five rows

so question number one the first choice

is this which is the correct choice

since this is the correct choice we want

to change this to a one this is correct


alright okay next one you wanna make

sure these ignores are unchecked or it

won't go through

okay so next question number one

question number one it's going to be

zero let's see

get this

question number one again personal

homepage okay let's get the last one

that's question one and let's submit


and correct integer

oh we submitted an empty one so we can

just delete that all right so now we

have all our choices for question number

one okay and we can see that the correct

answer is marked one four is correct

so let's add in the choices for question

number two

change this to five

all right so up at the top we want

question number two let's look at the

answers here okay choice one looks like

the correct one two

okay so we'll say one that's the correct


okay question two what

question two again

Oh click there's only three choices for

this one and that's absolutely fine the

way we're building this application it

doesn't matter how many choices you have

you can do a true-false if you want

all right

so let's click on go okay so now we have

all of our choices okay so what I want

to do is I want to spit that out

in this page okay and we're going to pay

attention to this N equals one because

this is going to be the number of the

question so let's go to question dot PHP

and we want to include the database

because we haven't done that yet so

let's just grab what we did on the index


all right

now what we want to do is open up

another PHP block here to work in and

this is where we're going to do all of

our queries and all of our PHP logic

when working on a production app

something bigger than this you usually

would want to have a separate page for

your queries or follow a framework like

the model-view-controller framework but

this is just going to be a very simple

very minimal project so we're just going

to do it up here alright so what we want

to do first is we want to get the number

of the question so this is question 1 so

how do we get this we get this using a

super superglobal called get all right

so let's see set

question number so we want what is that

I didn't want that I'm going to create a

variable called number and like I said

the way that we get that value in the

URL is through the get superglobal so we

can use this and what we want to put in

there is whatever we have right here

which is n okay so put that here and and

that's going to make this this number

accessible to us and we can actually use

it inside of our queries okay so that's

good and let's actually typecast this to

be an integer so we can do that just

with parentheses and the word int all

right so let's see we want to run our

first query I like to kind of use this

this convention here

so we want to get the question

and the first thing we're gonna do is

create a query okay so if you know MySQL

that's great if you even if you know

just a little bit because we're not

doing any complex queries here we're

going to do a simple select here so we

want to say select all we use the

asterisk asterisk for all and then from

and then the name of the table which I

put in these little back ticks okay the

name of the table is questions so this

is a query select all from questions if

we write ran that we would get

everything that we have inside of the

questions table okay which is our two

questions all right but we don't want to

get both questions because this is just

a one question page so we want to say

select all from questions where the

question number equals this okay which

we set into this variable all right so

after from questions I'm just gonna go

to a new line here I'm going to say

where question number is equal to number

all right so that number is coming from

here now what all we did here was put

the query into a variable we haven't ran

anything yet

all right so the next thing you want to

do is you want to get the results from

this query so we can do that let's

create a variable called result and

we're going to equal that to my sqli

query okay so this is the method that

actually runs our query which we get a

pass in okay and we're gonna say run the

query or Die which is just gonna cut the

script script down cut it out all right

and then inside here we're going to pass

in the error MySQL I ever and then the

line number so that will give us an

informative error if something goes


now the last thing we want to do is

assign a variable that we can use okay

so I'm going to create a variable called

question and I'm going to set that to

result and then fetch underscore

associative array with our our data that

we requested and we can use that down


alright so from the questions table you

want the questions text alright and we

want to replace the question this static

text with the dynamic text from the

database okay so let me just stop get

rid of that and we just want to put in

some PHP tags and we want to echo out


and then in here we want to pass in text

okay so this text is coming from the

database right here okay so we want the

text for the first question

okay so let's save that now it shouldn't

change okay it shouldn't change but this

is actually now coming from the database

and to prove that let's just go in here

and let's just rearrange the question

we'll say PHP stands for what okay so

now if we go back and reload PHP stands

for what so it's now coming from the

database all right so for the choices

now the choices aren't coming from the

questions table they're coming from the

choices table so we have to do a

separate query for that okay so back to

our question dot PHP file

I'm actually going to copy this

now this this is gonna be a little

different because the question that we

pulled we had an ID for so we only got

one record of that one question with

this we have to get all the choices for

one question so we're going to be

getting multiple values so we're gonna

have to assign it to a variable like we

did but we're gonna have to loop over it

and then spit out each iteration or each

choice alright so we're gonna say select

select all from not questions but

choices all right and we're gonna say

we're question number equals number

we're going to keep that there because

remember choices has a question number

field okay so if we go ahead and look at

that you can see that our choices table

has this question number so we only want

to question the choices for question

number one so what you get all of these

right so that query is done now we want

to get the results all right so we're

actually um

we're not going to use this here okay

but we're gonna say results no actually

no I'm going to say results I'm going to

change this to choices alright and then

the same thing with passing in this

query alright and then what we need to

do is go through a loop down here okay

so what we want to do is spit it out

here so we'll say PHP we're going to use

a while loop so we're gonna say while

row equals choices fetch Assoc are ready

but we had it up up top because we were

just getting one one record here we need

to scroll through multiple records

alright so while that now instead of

using curly braces I'm going to use a

colon because I want to end the the the

line of PHP line okay and then down here

we can simply say

and while and that ends the while loop

okay so while they're still records

that's basically what this is saying

we're saying while there's still choice

records for this query then we want to

I'm just going to copy one of these

because we only need one because it's

going to iterate four times or however

many choices there are and I can

actually delete all these all right now

what I want to do here is change these

to dynamic values okay we'll start with

the text so what we did we're saying

while row equals choices fetch

associative array so we can use this row

variable okay so in here we're going to

say PHP echo row okay so this is an

associative array and what I want is the

text okay so we have this text field so

that's what I'm going to put in here

text now the value I also want I want to

be dynamic but I want it to be the ID

okay so the ID of the choice is what I

want so all we have to do here is say

echo row and then ID

actually wait a minute

all right so now our value and the texts

are going to be dynamic

okay so let's save this and hope that it


okay so we have an unexpected slash

question line 46

oh I didn't end the PHP block right here

semicolon and then end it

all right so now the question itself and

the choices are coming from the database

so now that we have that done that

that's going to submit to process dot

PHP which we'll do in the next video

because there's quite a bit of logic we

have to do there as far as processing

and redirecting all right but what last

thing I want to do in this video is

change index for the index file I want

the number of questions in the minutes I

want that to be dynamic so that we won't

have to hard code that into the it's

into the HTML okay so we'll go to the

index page and we're just going to do a

little bit of PHP up here alright so

let's go ahead and

what we want to do is get the total of

get the total number of questions

actually should be I don't really use

notepad plus plus for PHP I use Eclipse

which has a better better hints code

hints and things like that I think in

the next project we will install Eclipse

or another IDE that is good for working

with PHP alright so what I want to do

here I want to come on a query just like

we've been doing and it's gonna be

really simple we're just going to select

all from questions

all right and then what I want to do is

get results

actually set this to results results

equals and I can just copy

from here

okay so basically we created the query

then we're running it here and assigning

it to results now what we can do is we

can use this results object to get the

number of rows that it found

so we'll say total equals results num


alright so that'll give us the total now

that we have that total the number of

questions we can go down here and change

this five PHP echo echo total

okay so let's go ahead and reload and

now you can see that it's changed too

because we have to record two questions

in our database and as far as the

minutes I just want to change this to

echo total times 0.5 all right and

that'll give us the minutes just an


okay so one minutes I'm basically saying

that one question takes 30 seconds

alright and you can change that you can

change that logic to whatever you'd like

alright so looks good in the next video

we will actually process or create the

functionality to process each question

and redirect us to the next question

Download Subtitles Download Video Download Audio

↑ Return to Top ↑ - contextual dictionary