Ever have that big project that you gotta do that requires alot of MySQL work? Well, I believe this is just the tutorial for you

Here's what you will have (hopefully) learned by the end of this tutorial.

• Create and use classes
• Use MySQL (if you didn't know already)
• Use define() for security

Ok, might as well start huh?
PART 1
Well, first we need to esablish or mysql user name, password, all that stuff. We're going to use the define() function so we can make things more secure, so that you don't have to worry about anything being overwritten by the user.

Change "user" to your MySQL username, "password" to your MySQL password, and "localhost" to your MySQL host. You can usually leave the host alone, since 90% of web hosts have MySQL on the same server as everything else.

Well, we establish the class name, and start it

We establish our two class-wide variables

Remember, you can't do a ton here. And these can be overwritten in the functions (whch they are).

Ok, this is what is called a "constructor". In PHP4, the constructor is always the name of the class that it's in.
If you notice that some of the variables in the defining of the function are set, don't think it's weird. This simply allows you to leave them out when you call them later. I call these "default settings" Ok, onto the inner code of this function.

First of all, we check to see if variable $nc is set to false, if it is, we run the code, if not, we just return true, but do nothing. If it's false, we connect to the MySQL server, or if that doesn't work out, we call a function that we'll later create. If you notice, we also assign the class-wide variable $mdc to the connection. Then we go on to select the database we want, then set the class-wide variable $connected, to true
Ok, I'll tell what each of the variables in the function line do.
$db is the name of the database you want in a string.
$nc can be set to true or false. if it's true, we don't connect to MySQL. This by default, is false.

Now for the close function.

What this function does is close the MySQL connection. It checks to see if the class-wide variable $connected is true or false. If it's anything BUT true, it reports that you aren't connected, and returns false, meaning that the function didn't succeed in it's task. But if $connected is set to true, it disconnects from the server and returns true, meaning that it did succeed in its task.


Oh boy... hopefully you understand what I've said so far to understand some of this. Here is our select function. This allows you to quickly and securely select rows from a MySQL database. I understand this looks really hectic, but I assure you that it works First we define our function-wide variables, and set some of them to defaults. Then we go and check if the class-wide variable $connected is true again, and reports if it's not. If it is, we go ahead and check more stuff. Each one of those does things a tad differently. I'd be here forever explaining everything that it's checking. Basically it's checking to see if the function-wide variables are null (or empty/blank) and doing things accordingly. if you notice, I have an @ sign before the mysql_query() function. This hides any errors the function might report itself, and runs our own error function instead. Again, we make this later.
Now for what the variables do/mean.
$table is the MySQL table that you want to select from.
$columns are the table columns that you want to select from, if any.
$ord is the column you want to order everything by.
$sort is the direction you want to sort things in (ascending, descending), if any.
$limit is the maximum number of rows you want to "grab", if any.


Here's a simple update function. First we check and see if we're connected, then if we are, run the update query. There's not alot to explain in this one, as it's pretty straight-forward.
The variables:
$table is, again, the table we want to update.
$data is the new column data we're putting in.
$where is what column that you want to update, like if you only want certain ones updated.

This is our delete function. Again, not too much to explain.
$table ... I think you get the idea
$where is about the same as the last one. Basically what must be true for the row to be deleted (certain ID number, username, ect).
$limit same as the select one. this one is defaulted to one, but if you want to loop through and delete alot of things at once, then set this to a higher number.

Ok, here's our all-purpose query function. Use this wisely. I use it for when I need to run advanced queries like selecting muliple tables.
$query... sorry, but if you can't figure this out... This is what query you want to run.

"Ho...ly...crap.." I hear you non-advanced php users saying. Don't worry, I commented the crap out of this one. This is fairly advanced stuff. It's mostly array reading. Since the insert MySQL command is really touchy, the code has to be fancy. First we establish the first part of the query, then we jump into the array thing. It checks to see if the variable $cols is an array or not. If it is, loop through them and add them to the query. Since the last column you put in the query has to be different, I threw in that check, and made it different . The second part is almost the same as the first, but it loops through the $vals variable. After all that, we finish off the query, then return it
Variables used:
$table, you get the point...
$cols is the columns that you are inserting into.
$vals is the data that you are inserting into the columns. make sure that they corrospond!

Finally, our error function. What this does is handle any MySQL error that could come up. First it tells the user what happened, then that it sent you an email and logs it. Then it sends you an email, with the users IP address, and what the MySQL error was. I find this is good so if a user tries to hack it, and they slip and make it wrong, you can see what they tried to do, and you have thier IP address so you can report them to their ISP.

Don't forget to close the class!


So that about wraps it up for part 1

PART 2 - Using the class

this includes the file that the mysql class is in, and then 'initiates' the class.

and that's it. pretty easy huh?
---
If you have any questions or comments, leave them!
learn more about classes, define(), MySQL functions, MySQL itself

You did a great job on this tutorial, it will definately give people who don't know OOP and Classes in PHP a great jump-start!!

I like it

would be nice to see a .phps source file for this, and almost a query counter added in too...

could be added onto it later i suppose, good tutorial

Wicked tutorial

nice, should prove useful to users.
Great work.

Very well written tutorial that comes in handy for every php-er that haven't used this befor!

very nice. I try not to use OOP thoughl. There are many people who dont know how to use it. Modders and Themers tend to go to projects that are none oop, I believe.

actually, most modders and themers go toward OOP. It's SOO much easier to use and code, because you only have to code it ONCE!

I can almost garuntee that P2L is written in OOP (please let me be right Jay )

nice tutorial man! could use a better example on how to apply this class though

I understand how the classes work now. Thanks!

I just discided to learn them.

I have one problem though. I cant get it to write to the database.

Here is my code im using to call this.


Is there something wrong. I know a lot of PHP but just cant figure it out. Thanks.

remember to use public instead of var on php5 object orientated code;). heh, you can do the same thing with just using functions, or variables. Class's are just pretty, and have been used before.

I don't see why you would define the mysql details.
You could use class variables instead!
Example:

So let me get this straight... the reason for using this class would be because you're not repeating "blocks" of code.

And hiding your functions within a class?

Simply using functions would also illiminate the repeating of code so why do it?

What are the real benifits of using classes (as apposed to simple grouped functions)?

Actually, to be honest, not all of it is written in OOP. I was surpsied not to see as much OOP as I hoped.



The major advantages of classes are that they maintain a professionalism, more organized, cleaner, easier to use, and such forth. The backend may be longer at times, but the front end part of the OOP when you are actually using it is so short and sweet. Let me show you an example where OOP > Normal Functions....

class.User.php


Using it:


Doesn't that look short and sweet? The point is, from a development standpoint, like Invision Power Board for instance, makes it much easier for developers to expand more on the software by creating mods and such. With classes, you can basicly categorize your functions, make them hold variables like deciding whether a user is logged in or not which makes it much easier to find the right line of coding withing 100 php files. Say that you want to look for a bbcode function. You could look in the bbcode class, which would hold things such as parsing it from html->bbcode, and vise versa. I hope that helps....but doing all coding in OOP isn't required, I would only use OOP on large projects and scripts such as a cms, membership system, tutorial index? , not things like contact forms and navigations

the forum portion of P2L IS written in OOP. since P2L utilized Invision Board, i've seen the source for ipb and it's SOOOO OOP.