Just started teaching myself PHP in the last month or so, and I have begun to develop some very useful little tools that I use daily.
I thought I would share this class I made for MySQL. Here is the code:
So basically you put this code into a file called "mysql.php." Then at the top of the script where you are going to be doing DB queries, put
The meaning of those constructor variables should be pretty self evident. Each time you create a new mysql object, you need to set some basic variables. These are defined in the __construct() function.
Then when you have to make a query, specifiy which db you want to connect to, putting
To insert data from array $matches, for example, use
I use a function within the class itself to format the data properly for SQL calls. I like to assign a variable with the returned data, to see any errors and such. If you see nothing after calling insertData, that means that it was successful. Perhaps you could add a "$match was added!\n"; or something along those lines. Also, you may want to use an instance variable if you are running a very long script to save on memory after your echo it or var_dump() it out.
I realize this will be rather basic to a lot of you, but I def think this could help out some PHP n00bs like myself. If you are using mysql a lot, this is very useful and saves a lot of time. It is similiar to the PEAR DB class, in that it makes your life a whole lot easier working with db's, although PEAR is obv a lot more robust.
Speaking of which, if any of the more experienced coders would like to jump in and point out ways which I could expand upon this script and make it more robust, please do so.
I am working on a class that expands lerchmo's bot class that solves captchas, along with one that auto follows a link in an email (verification link).
Look for these in the near future.
lucab
I thought I would share this class I made for MySQL. Here is the code:
PHP:
<?
######################################
########### MySQL Class ##########
######################################
class mysql{
//define our variables
var $sql, $server, $username, $password, $db, $table, $fields, $columns, $data, $purpose, $vars, $isData, $isColumns, $isFields, $match, $row, $mvalue, $matches;
//constructor variables required when defining a new object
function __construct($server, $username, $password){
$this -> server = $server;
$this -> username = $username;
$this -> password = $password;
}
//check to see if we can make a connection
function connect($db){
$server = $this -> server;
$username = $this -> username;
$password = $this -> password;
mysql_connect($server, $username, $password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
}
//create a table
function createTable($db, $table, $fields){
$this -> connect($db);
$isFields = "fields";
$fields = $this -> implodeData($fields, $isFields);
mysql_query("CREATE TABLE $table($fields)");
}
//insert data into table
function insertData($db, $table, $columns, $data){
$isData = "data";
$isColumn = "column";
$this -> connect($db);
$data = $this -> implodeData($data, $isData);
$columns = $this -> implodeData($columns, $isColumn);
$sql = "INSERT INTO $table ($columns) VALUES ($data)";
mysql_query($sql) or die(mysql_error());
}
//delete one or more entries from a given column
function deleteData($db, $table, $column, $match){
$this -> connect($db);
if (is_array($match)){
foreach($match as $mValue){
$mValue = "'$mValue'";
$sql = "DELETE FROM $table WHERE $column = $mValue";
$matches = mysql_query($sql) or die(mysql_error());
}
}else{
$match = "'$match'";
$sql = "DELETE FROM $table WHERE $column = $match";
$matches = mysql_query($sql) or die(mysql_error());
}
return $row;
}
//create a string from an array for our sql query
function implodeData($vars, $purpose){
if ($purpose == "data"){
$vars = implode("\", \"", $vars);
$vars = "\"$vars\"";}
else{
$vars = implode(", ", $vars);
}
return $vars;
}
//query using select with a where operator
function selectData($db, $table, $column, $match){
$this -> connect($db);
if (is_array($match)){
foreach($match as $mValue){
$mValue = "'$mValue'";
$sql = "SELECT * FROM $table WHERE $column like $mValue";
$matches = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($matches)){
foreach($row as $rValue){
echo "$rValue<br />";
}
}
}
}else{
$match = "'$match'";
$sql = "SELECT * FROM $table WHERE $column like $match";
$matches = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($matches)){
foreach($row as $rValue){
echo "$rValue<br />";
}
}
}
return $row;
}
//here is the updating function
function updateData($db, $table, $column, $old, $new){
$this -> connect($db);
$old = "'$old'";
$new = "'$new'";
$sql = "UPDATE $table SET $column=$new WHERE $column=$old";
mysql_query($sql) or die(mysql_error());
}
}
?>
PHP:
require('mysql.php');
$mysql = new mysql($server, $login, $password);Then when you have to make a query, specifiy which db you want to connect to, putting
PHP:
$mysql_connect = $mysql -> connect($db);
PHP:
foreach ($matches as $match){
$mysql_insert = $mysql -> insertData($db, $table, $columns, $match);
}I realize this will be rather basic to a lot of you, but I def think this could help out some PHP n00bs like myself. If you are using mysql a lot, this is very useful and saves a lot of time. It is similiar to the PEAR DB class, in that it makes your life a whole lot easier working with db's, although PEAR is obv a lot more robust.
Speaking of which, if any of the more experienced coders would like to jump in and point out ways which I could expand upon this script and make it more robust, please do so.
I am working on a class that expands lerchmo's bot class that solves captchas, along with one that auto follows a link in an email (verification link).
Look for these in the near future.
lucab
