PHP, MySQL & Stripe API Payment App

PHP, MySQL & Stripe API Payment App – Part 2

alright guys in the last video we created a payment page for buying a react course which of course you could put any any product here and it goes

through and gets submitted to our charge dot PHP which uses a stripe API it creates a customer and then it charges the customer and sends them to a success page so now what I want to do is

implement a database and just more I want to add more to our back-end I want to add some models so that we can submit the data we want to our own database and then after that we'll move on to create

a couple pages where we can view the transactions and so on in our own application so first thing we're going to do here is create our database so I'm using PHP myadmin so I'm going to go to

local host PHP myadmin and this comes with xampp if that's what you're using if you want to use a different database client or if you want to use your SQL shell that's fine as well ok but I'm

doing it the easy way which is through a GUI program so let's create a database called pay page and we're gonna have two tables first is going to be customers and let's see as far as fields we want

five fields I believe okay so we're gonna have an ID the ID is actually going to be a varchar' this is going to be the transact I'm sorry the customer ID from stripe I'm gonna say 255 max and

let me make this a little bigger this is going to be the primary key so I'm going to choose primary but it's not going to be auto increment so don't check the AI and then the second field here is going

to be the first name first underscore name which will be varchar' last name email and let's also do a created at and that's going to be a date time which is right here a date time and I'm going to

choose a default of the current timestamp so that'll get automatically inserted so let's save that that's our first table let's go back to our database and create a new table called

transactions and this is gonna have let's see it's gonna have quite a few more fields it's gonna have seven and it's gonna have an ID which will be a voucher 255 and this is also going to be

the primary key but not auto increment because it's going to be the actual transaction ID from stripe then we're going to have the customer underscore ID we're gonna have the product which will

be the product name or the description we're gonna have the amount that it costs I'm just doing everything as for our char it's we're going to have the currency we're gonna have the status

and then we're gonna do a created at which will be date/time and cut in current timestamp as the default and there's a lot of other stuff you saw the response that we get back from stripe

there's a lot of other stuff you could store but I don't want to go too crazy here I think this is good enough so now that we have our tables we want to start to implement our models but before we do

that we need a way to access our database okay so we're going to use PDO and let's see let me just go to my gist real quick I'm not gonna type out the entire or is it so right here I'll have

the link in the description by the way for this this is a class that implements PDO which is PHP data objects it's a way to it's the best way to connect to a database through PHP in my opinion it's

better than using MySQL I or MySQL improved basically we have a bunch of properties here that's going to be your host your user and database user pass or a database name this stuff will all come

from a config file that we'll create and then it has a database handler an error property and a statement property because PDO uses prepared statements and if you know nothing about PDO I would

suggest watching my PDO crash course that takes you from knowing nothing about it to knowing all about prepared statements and all that stuff okay so in our constructor we're creating our DSN

which is just basically a connection string some options that have to do with persisting the error mode and all that and then here we're creating a new new PDO instance passing in our DSN the user

the pass or at all that so this is what's connecting to our database using PDO then we just have a simple query function that will prepare a statement we have our bind function that will bind

our values and execute which will execute results set that's if you want to fetch data and you want to return an array or I'm sorry yeah an array of objects single which

will return a single object row count will count your rows and then if you want the last inserted ID you can get that as well but we're not even going to be using these we're actually not going

to be using any of these functions here all right just these ones but what what I'll do is just grab all this okay and if you took my my PHP MVC course on udemy this is the exact same class so

you're probably familiar with it already so what I'll do is create a folder called Lib because it's basically what this is is a library and I'm gonna create a file called PDO underscore DB

dot PHP and then we're gonna paste that in alright so we'll save that and then this is what we're going to use in our models to interact with our database now we're gonna need a config file with all

of our database parameters so I'm going to create another folder called config and then in there I'll create a file called DB dot PHP okay and inside here let's see we're gonna actually going to

define some constants so these will be our DB params so let's define a constant of DB underscore host and our host of course is gonna be localhost and then let's we're gonna have four of these so

this one is going to be our DB user so this is your database username I'm using my root user then we have our DB pass so your password which mine is just one through six and then you want your DB

name which I called pay page okay and that's it so we'll save that so now our database library's all set our parameters are in a config file now we just need to include those inside of our

charge dot PHP file alright so let's go right under here and we'll say require once and we want the config first so config DB dot PHP and then we want our Lib slash PDO

underscore DB dot PHP okay make sure you have the config first because the database library needs to read the parameters from the config file alright so now that we did that we're gonna

create start to create our models so let's say new folder call it models and I'm gonna create a file called customer dot PHP okay models should be singular let's also create a file called

transaction dot PHP and I like to have my models capitalized as well alright so we'll start with the customer so we'll have a class called customer and we're gonna be using our PDO library

so I'm going to create a property a private property called dB okay and then we're gonna create our constructor so in PHP to create a constructor you do double underscore construct and a

constructor is just a function or method that runs when your colinear objects your object is instantiated and all we need all we want to do here is instantiate our database so we're going

to take that DB property we can access properties with this so this DB we want to set it to new database okay because database is the name of the class our database class so

if we look at our PDL file you'll see we have class database so all we're doing is instantiated this class or this object alright so let's go back to customer and that's all we want to do in

our construct function so next thing we want is a function to add add a customer so we'll say public function add customer and then this is gonna take in some data okay and we're gonna do all

this in the charge dot PHP we're going to instantiate this class and then call this add customer and pass what we need in alright and what's going to be passed in this data array this is gonna be an

array it's gonna have the ID the first name the last name and the email so what we want to do is then say this actually let's put a comment here we'll say prepare query so we're gonna save this

DB remember when we use this DB we can access anything that's in this database class so we want to call the query method which is right here okay we want to prepare our statement so let's say

this DB query and then all we have to do is pass in our query which is going to be insert into customers the table customers and then our fields we're going to insert

our ID first name last name and email alright and then we want to say values now the values we're going to bind we're going to use named parameters here so we want to do : ID : first name : last name

and : email all right so this is we're binding our data here this is this is a secure way to to work with your database now up to this point it doesn't know what we want to bind this there should

be no space here it doesn't know what we want to bind so this is where we call our database bind method which if we look in our PDO file we have right here so we want to call this and bind our

data so we're gonna go back and we're gonna say bind values so we'll save this DB bind and we want to bind to the ID : ID which is our named parameter and we want to bind from the data array the ID

field that's being passed in right here okay we want to do the same for the others so let's do four of these so in addition to ID we're gonna have first-name

oops and we're gonna have last name and we're gonna have email all right so once we bind it we want to execute it so we're gonna say if we're going to put this in an if statement and say if this

DB okay remember we can access anything in that DB and we want to access execute so if this is executed then let's just return true oops else let's return false and that's it

that should add it to our database now of course we need to call this add customer we need to call this from our charge so after we do our striped charge let's go down right here and this is

where we're gonna do it now since we're using object-oriented program we were using classes we need to instantiate the customer object from that class so let's say instantiate customer and before I do

that I actually need to bring in the customer file the model so let's say require once so we want models / customer all right and let's go back down and then we can

simply do it's a customer equals new customer like that and then what we want to do is call the add customer method so we can take that customer object and we can call add customer now we want to put

in here our customer data which we haven't created yet so let's do that I'm actually going to do that up here so we'll say customer data so to be a variable call customer data we'll set it

to an array and we're gonna have an ID and we're gonna get that from the member we get that charge object back and we want to get the customer from that so that's what we're going to use as the ID

and then the rest of the stuff like the first name this is stuff that's coming from our form it's not actually coming back from the charged object so we can just put in first name like that and you

can put you can have any fields if you wanted to have the address come in or any other fields inside of our form here you could put anything you want okay and store it in your database you're not

limited to what comes back from your charge object so it's a last name and email okay so that's our customer data we're instantiating the customer object and then we're calling add

customer so that should do it as far as just adding it adding the customer it's not going to add the transaction yet we haven't done that but let's test that part out

okay so let's reload here we just double check make sure you have your config your database library and your model so we'll go ahead and say no Sally Williams let's try it out okay so that worked out

now let's go to our database let's go to our customers table and there it is so for the ID we have the ID that came back from the stripe API and we have the rest of the fields and of course like I said

you could have more if you wanted and if we look in our dashboard and stripe and reload there it is right here Sally's purchase okay so it's going to both places

so now let's do our transactions now the transaction is going to work pretty much the same way it just has different fields so what I'll do is copy our customer model so everything we have

here inside customer PHP I'm going to go into transaction PHP and paste it in and then we're just going to change the class name and then we want this stuff because we want to access the database

library we're going to change this to add transaction we're gonna get some data passed in and then let's change the field so we will have an ID we can leave

that and then we're gonna have the customer ID we're gonna have the product the amount so this is just all the stuff we added in the database currency and status and then for the values we want

the same stuff but we want to use the named parameters which just have the colon in front so like that and all we want status as well for some reason when vs code the word

status is blue I think it's a reserved word or something but it's not going to affect anything okay now we just want to do the binding so we want the ID then we want the customer ID which will be

passed in lips okay next we want the products and we want the amount whoops and we also want two more which is gonna be the currency and we want the status like that okay and then just run the

execute so let's save it let's go back to our charge dot PHP and pretty much do the same thing we did before the customer we want to create the data so I'm going to copy this this and this

paste it in and we're just going to change up this to transaction data transaction data so let's see for for the transaction ID we don't want customer we want ID all

right and then this is going to be the customer ID which comes from charge customer and then we have the the product which comes from charge description okay but in our database

we're calling it product and then we have our amount which will come from charge amount we also have status and what else oh I've got currency which I'm going to put here

all right so that's our transaction data now we want to instantiate the transaction so instead of new customer we want new transaction we'll change that to transaction and we're gonna call

transaction add transaction and pass in the transaction data so same thing it's just different data okay and I don't think we brought in the model did we know we didn't so let's just add that

yep so we'll bring in that model and then we should have access to all that good so now when we make a payment it should add it to both tables unless I hopefully I didn't misspell anything I

went kind of fast but we'll check it out but first thing I'm going to do is delete the customer Sally and then I'm also going to go to my dashboard and just clear everything out so I'll go to

business settings data and let's delete all test data okay and then we'll go to payments it should be empty good so let's try this again so we'll go to our home page or

pay page let's put in John Doe submit up okay so we're getting an error unknown column customer ID in field list alright let's check out now it did go into customers but we had the error during

the transaction so let me look at the structure here we have customer ID as a field let's look at this let's see let's go to our model our transaction model let's see

so ID cuts customer ID and we look at that error again column not found unknown column customer ID in the field list stack trace charge 61 [Music]

hmm ah right here we're trying to insert into the customers database we want it to go into transactions okay so what I'll do is just delete from our customers database the one we just added

John Doe I'll just leave it in stripe but let's try this again and hopefully it works so we'll use a different name we'll say Tom Smith submit alright so that went through so let's check our

database we'll look at our customers so there's Tom Smith let's go to our transactions and there it is so we have our transaction ID our customer ID our product our amount currency status is

succeeded and our created at and if we go to our dashboard here we'll see the John Doe that we did at first and then we'll see the Tom the Tom at Gmail all right guys so I'm gonna stop the video

here and in the next one we're gonna create the pages so that we can see our transactions and our customers in our application so we don't actually have to go to the you know PHP myadmin or or

search the our SQL shell or anything like that to see what we have alright so thanks guys and I will see you in the next video

In part 2 we will implement the PDO class and the customers and transaction models so we can store custom data from the Stripe API

CODE: Github Repo
https://github.com/bradtraversy/php_stripe_paypage

Stripe API Docs: Card Element Quickstart
https://stripe.com/docs/stripe-js/elements/quickstart

PDO Class Gist:
https://gist.github.com/bradtraversy/a77931605ba9b7cf3326644e75530464

PHP OOP Udemy Course:
https://www.udemy.com/object-oriented-php-mvc/?couponCode=TRAVERSY010

💖 BECOME A PATRON: Show support & get perks!
http://www.patreon.com/traversymedia

Website & Udemy Courses
http://www.traversymedia.com

Follow Traversy Media:
http://www.facebook.com/traversymedia
Posts by traversymedia
http://www.instagram.com/traversymedia

The Developer Hangout: Public Discord Server
https://discord.gg/developers