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
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.
<?php
define("MYSQL_USER", "user");
define("MYSQL_PASS", "password");
define("MYSQL_HOST", "localhost");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.class mysql
{Well, we establish the class name, and start it var $connected = false; var $mdc = NULL;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).
function mysql($db, $nc=false)
{
if(!$nc)
{
$this->mdc = mysql_connect("localhost", MYSQL_USER, MYSQL_PASS) or $this->error();
mysql_select_db($db);
$this->connected = true;
}
return true;
}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.
function close()
{
if($this->connected !== true)
{
$ret = false;
echo "You must connect before you close!";
} else {
mysql_close($this->mdc);
$ret = true;
}
return $ret;
}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. function select($table, $columns=NULL, $ord, $sort = NULL, $limit = NULL)
{
// Again, check our connection
if($this->connected !== true)
{
$ret = false;
echo "You have to connect before you can select!";
} else {
if(!is_null($limit) && is_null($columns))
{
$ret = @mysql_query("SELECT * FROM ".addslashes($table)." ORDER BY ".addslashes($ord)." ".addslashes($sort)." LIMIT ".$limit) or $this->error();
} elseif(is_null($limit) && is_null($columns)) {
$ret = @mysql_query("SELECT * FROM ".addslashes($table)." ORDER BY ".addslashes($ord)." ".addslashes($sort)) or $this->error();
} elseif(is_null($limit) && !is_null($columns)) {
$ret = @mysql_query("SELECT * FROM ".addslashes($table)." WHERE ".$columns." ORDER BY ".addslashes($ord)." ".addslashes($sort)) or $this->error();
} elseif(!is_null($limit) && !is_null($columns)) {
$ret = @mysql_query("SELECT * FROM ".addslashes($table)." WHERE ".$columns." ORDER BY ".addslashes($ord)." ".addslashes($sort)." LIMIT ".$limit) or $this->error();
}
}
return $ret;
}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.
function update($table, $data, $where)
{
if($this->connected !== true)
{
$ret = false;
echo "You have to connect before you can update!";
} else {
$ret = @mysql_query("UPDATE ".$table." SET ".$data." WHERE ".$where." LIMIT 1") or $this->error();
}
return $ret;
}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.
function delete($table, $where, $limit="1")
{
if($this->connected !== true)
{
$ret = false;
echo "You have to connect before you can delete anything!";
} else {
$ret = @mysql_query("DELETE FROM ".$table." WHERE ".$where." LIMIT ".$limit) or $this-error();
}
return $ret;
}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.
function query($query)
{
if($this->connected !== true)
{
$ret = false;
echo "You have to connect before you can run any queries!";
} else {
$ret = @mysql_query($query) or $this->error();
}
return $ret;
}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.
function insert($table, $cols, $vals)
{
$ret = "INSERT INTO ".$table." ( ";
// check to see if the columns is an array or not..
if(is_array($cols))
{
// it is! so lets loop through them, and add them to the query
$t = count($cols);
$i = 0;
foreach($cols as $col)
{
$i++;
//checking to see if it's the last one in the array.
if($i !== $t)
{
$ret .= "`".$col."` , ";
} else {
$ret .= "`".$col."` ";
}
}
} else {
//it's not an array.. so lets just slap it in there
$ret .= "`".$cols."`";
}
$ret .= " ) VALUES (";
//same thing as above, but with the insert values
if(is_array($vals))
{
$t = count($vals);
$i = 0;
foreach($vals as $val)
{
$i++;
if($i !== $t)
{
$ret .= "'".$val."', ";
} else {
$ret .= "'".$val."'";
}
}
} else {
$ret .= "'".$vals."'";
}
$ret .= ")";
return @mysql_query($ret) or die($this->error());
}"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!
function error()
{
global $REMOTE_ADDR, $_SERVER;
echo "Uh oh, looks like we got an error in MySQL! <br /><br />The error returned was <br /><div style=\"margin-left: 10px; border: 1px solid #000;\">".mysql_error()."</div><br />Logging this and emailing the admin<br /><br />";
$fp = fopen("logs/error.mysql.log", "a");
fwrite($fp, $d." ".$REMOTE_ADDR." ERROR: ".mysql_error()."\n");
mail("[email protected]", "MySQL Error", $d." ".$REMOTE_ADDR." ERROR: ".mysql_error().", "FROM: [email protected]");
return false;
}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
Edited by Vouksh, 10 October 2005 - 09:29 PM.
