Its now time to become serious with my blog again after such a long lapse in real content.
When building new applications, it always seems like I start with the grand picture requiring no massive data storage and shortly after I begin, I find myself needing a database connection. I’ve made the mistake several times now of not starting with a dedicated database class that connects to MySQL, and I always find myself googling for pre-made classes that don’t do exactly what I need, so I’ve decided to post my own for future reference. As a result, I’m going to make sure that when I Google, I find my own reference before someone else’s Hopefully this is useful to someone else.
PHP’s MySQL singleton class:
<?php
// Copyright (c) 2012 Philip Matuskiewicz www.famousphil.com
// To use:
// require_once("Mysql.php");
// $db = new Mysql();
class Mysql{
private $server = "localhost";
private $username = "";
private $password = "";
private $database_table = "";
private static $instance;
private function __construct(){
$this->connect();
}
public function connect(){
mysql_connect($this->server, $this->username, $this->password);
mysql_select_db($this->database_table);
$this->q("set names 'utf8'");
}
//query the database
public function q($query){
$r = mysql_query($query);
return $r;
}
//returns an array containing all the rows that were returned
public function qr($query){
$r = $this->q($query);
if (mysql_num_rows($r) > 0) {
$res = array();
while ($arr = mysql_fetch_array($r)) {
array_push($res, $arr);
}
return $res;
} else {
return null;
}
}
//number of rows returned
public function nr($query){
return mysql_num_rows($this->q($query));
}
//last inserted row id is returned
public function lid(){
return mysql_insert_id();
}
//close the database connection
public function c(){
mysql_close();
}
public static function singleton(){
if (!isset(self::$instance)) {
$c = __class__;
self::$instance = new $c;
}
return self::$instance;
}
public function __clone(){
trigger_error('no clone', E_USER_ERROR);
}
}
?>
The Python MySQL singleton implementation is similar, but includes an external file named config.py in this example
--> config.py (configuration information for the MySQL class)
dbhost = "localhost";
dblogin = "";
dbpassword = "";
dbname = "";
--> MySQL.py (The MySQL class)
#!/usr/bin/env python
# Copyright (c) 2012 Philip Matuskiewicz www.famousphil.com
#to include / use, insert the following lines in the code
#import imp;
#mysql = imp.load_source("MySQLConnector", "PATH_TO_PYTHON_FILE/mysql.py").MySQLConnector();
#result = mysql.tryquery("Mysql Query Here");
import sys;
import os;
import string;
import base64;
import MySQLdb;#mysql library (you will need to install this on the system)
#MySQL Singleton Class
class MySQLConnector(object):
_connection = None;
_instance = None;
def __init__(self):
try:
if MySQLConnector._instance == None:
MySQLConnector._instance = self;
MySQLConnector._instance.connect();
except Exception, e:
print "MySQL Error "+str(e);
def instance(self):
return MySQLConnector._instance;
def get_connection(self):
return MySQLConnector._connection;
def connect(self, debug=False):
try:
for line in open('includes/config.py'):
#this can be dangerous, but sources / executes lines in config.py, which contains the db info
#alternatively, you can just set the variables here manually
exec('%s = %s' % tuple(line.split('=', 1)));
MySQLConnector._connection = MySQLdb.connect(dbhost, dblogin, dbpassword, dbname);
if debug:
print "INFO: Database connection successfully established";
except Exception, e:
print "ERROR: MySQL Connection Couldn't be created... Fatal Error! "+str(e);
sys.exit();
def disconnect(self):
try:
MySQLConnector._connection.close();
except:
pass;#connection not open
#returns escaped data for insertion into mysql
def esc(self, esc):
return MySQLdb.escape_string(str(esc));
#query with no result returned
def query(self, sql):
cur = MySQLConnector._connection.cursor();
return cur.execute(sql);
def tryquery(self, sql):
try:
cur = MySQLConnector._connection.cursor();
return cur.execute(sql);
except:
return False;
#inserts and returns the inserted row id (last row id in PHP version)
def insert(self, sql):
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return self._connection.insert_id();
def tryinsert(self, sql):
try:
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return self._connection.insert_id();
except:
return -1;
#returns the first item of data
def queryrow(self, sql):
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return cur.fetchone();
#returns a list of data (array)
def queryrows(self, sql):
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return cur.fetchmany();
#end class MySQLConnector
Here is a relatively short blog for a change! A client came to me today to have PDFLib installed onto the Matthouse CPanel server. PDFLib allows you to create PDF files programatically (in this case, PHP), so I can see a lot of uses for this extension. As expected, CPanel wasn’t able to install this extension through PECL without a little help, so below is the procedure that I used.
#get latest pdflib lite 7 from http://www.pdflib.com/download/
#http://www.pdflib.com/download/pdflib-family/pdflib-lite-7/
cd ~
wget http://www.pdflib.com/binaries/PDFlib/705/PDFlib-Lite-7.0.5.tar.gz
tar xzf PDFlib-Lite-7.0.5.tar.gz
cd PDFlib-Lite-7.0.5
#java isn't installed on the server, compile without java
./configure --prefix=/usr/local/pdflib --without-java
make
make install
pecl install pdflib
#when asked for a path, enter "/usr/local/pdflib" then hit <enter>
I opted to install the lite version of PDFLib since its free and my client didn’t need all of the features that the program includes. I installed pdflib to its own directory that isn’t included in path since I don’t anticipate anyone actually using this via other languages on the server (besides PHP). Once PDFLib is installed, we will need to use the command line (as root) to install the pecl extension and tell it where to find the compiled version of pdflib. Note that CPanel’s PECL installer will not be able to install this extension since there is no way to enter the path (and I’d prefer to not add this to the global PATH variable on my server). The extension is now installed, in order to test the extension, make a PHP file for the below code and execute it in a web browser, you should see “Hello World” as a PDF document.
<?php
try {
$p = new PDFlib();
/* open new PDF file; insert a file name to create the PDF on disk */
if ($p->begin_document("", "") == 0) {
die("Error: " . $p->get_errmsg());
}
$p->set_info("Creator", "hello.php");
$p->set_info("Author", "AUTHOR");
$p->set_info("Title", "Hello world (PHP)!");
$p->begin_page_ext(595, 842, "");
$font = $p->load_font("Helvetica-Bold", "winansi", "");
$p->setfont($font, 24.0);
$p->set_text_pos(50, 700);
$p->show("Hello world!");
//$p->continue_text("continue text");
$p->end_page_ext("");
$p->end_document("");
$buf = $p->get_buffer();
$len = strlen($buf);
header("Content-type: application/pdf");
header("Content-Length: $len");
header("Content-Disposition: inline; filename=hello.pdf");
print $buf;
}
catch (PDFlibException $e) {
die("PDFlib exception occurred in hello sample:n". $e->get_errnum() ." " . $e->get_apiname() . " : " .$e->get_errmsg() . "nn");
}
catch (Exception $e) {
die($e);
}
$p = 0;
?>
On August 30, 2009, I made a blog post about my x10 home automation system, but I didn’t really cover it in depth enough to satisfy myself. Therefore, I decided to revisit the topic from that blog (which is here). This blog contains a video detailing the system that I ended up with during my University experience. I expect that my future home will be much more automated than this video shows. In order to appreciate the video, I decided to write some content to supplement what I talk about in the video, which is below.
In 2009, I came up with the first version of my light control system. This consisted of a clapper, a set of cheap computer speakers, and an audio recording of my clapping. My original system used a web interface to have the speakers (connected to the web server / site) clap when I wanted the lights to come on. This quickly became unmanageable since I couldn’t monitor the states of the lights without a webcam monitoring my room (and I wasn’t thrilled about securing a webcam in my room that could be hacked into). This lead into X10 which has done wonders for me over the course of the past two years.
Its funny remembering back to when I first got an x10 system, because the x10 website doesn’t look like your average store, it looks more like an adult site with lots of flashy banners (ha ha). I do have to admit, with x10, the initial impression was very deceiving for me, since their products are really good! Anyways, moving on, My x10 system consists of a USB transceiver module (CM15A located here) which connects to my windows server that runs a WAMP (Windows, Apache, MySQL, PHP). In addition, I have several lamp modules, appliance modules, and remote control wall switches (which wirelessly communicate to the CM15A).
As for the software, I installed the ActiveHome software onto my server (that came with the CM15A). This software installs a few application libraries (dlls) which enabled programmatic access to the CM15A to send and receive x10 commands over the power lines ran in my apartment. Using this api, I wrote a quick program that makes a command line based program that can run certain commands. I have to note that ActiveHome includes a default command line executable, but this didn’t have all the functionality that I wanted, most notably output in HTML for my web interface. With the executable I wrote, I simply have the apache web server run the command as a local user which sends the x10 signals over the power lines ran in my apartment. I should note that I considered using ASP.net which could interface with the API directly, but at the time I wasn’t very familiar with ASP.net and I wanted to use PHP. I didn’t use IIS because I didn’t have the time to go permissions hunting to figure out why PHP wasn’t allowed to run programs as a local user on the web server through IIS. Luckily a standard WAMP install that included Apache and PHP worked out of the box with little configuration. The WAMP that I used was Vertrigo.
For the IR receiver that controls my projector, I use a USB-UIRT that I found on Ebay (I didn’t feel like waiting the estimated 6 weeks at the time for a new one). Fortunately, the executable program that was included could send and receive signals from the USB-UIRT that I required, so I wrote some PHP that invokes it for the commands I used.
In 2009, I mentioned that voice control would make its way into my system. I have to admit that it did! Unfortunately, it didn’t last long because of all the bugs that I ran into. I used the Microsoft speech libraries with a program called SmartButler, which could listen for speech commands and run commands on my server. I initially used a cheap computer microphone, but quickly found that the interference in the audio made commands very inaccurate, and furthermore, if I held a conversation in the room, my lights would act up! I figured that a USB professional Condenser Microphone would fix the problem, so I quickly ordered one and integrated it into my system. This did significantly improve the quality of my system, but normal conversations would still occasionally trigger some of my web control panel controls inaccurately. The final straw that broke the camel’s back (and caused this system to get removed) was when my RA (resident advisor), Stephanie came into the room, said hello to me and my lights went out of control (since I didn’t train it for a higher pitched, female voice, I’d assume). I’d like to approach this problem in the future when I get the time to do it properly, but until then, I’ve been satisfied with web control panels that both my smartphone and computer can access.
Finally, at the very end of my video, I took a moment to show my Linksys PAP Adapter (VoIP, Voice over IP). This works through a service from voip.ms. Voip.ms is a prepaid service for VoIP adapters that allows you to buy a phone number for a very reasonable price (even 800 numbers). They also have really reasonable rates and really good control for those phone numbers. I thought I’d provide a plug for them since they are really good.
I would like to add that the Linksys adapter required an unfirewalled IP address to work properly (for incoming calls) in my experience. It does have a NAT mode for this situation, but the university firewall constantly crushed this mode. To get around this problem (and allow my web server for x10 to work properly), I implemented OpenVPN on my network and pulled in several public IP addresses and left them unfirewalled. This solution worked well for me at UB, and I documented it fairly well in my OpenVPN post.
With nothing more to mention on the topic, here is the video that details my light control system and some other stuff.
Of course, I made many bloopers in this video (I improvised a lot!), so I thought the bloopers video would be appropriate. Warning, there is a little language in it and the audio isn’t exactly balanced.
Disclaimer: I was not paid to endorse any of the products above (and I’m never paid to do that!). I like to think that when I make a recommendation, it carries a lot of weight with it, which it should!
This is a video blog continuation of week 2 (see the previous post).
Background: I was asked by the IEEE student club at UB (http://wings.buffalo.edu/sa/ieee) to redo my lecture series on developing websites. I know that my website isn’t the best visually designed website in the world (and I’m actively looking for talent that can help me fix this in exchange for my programming skills), but I do know a lot about how to code websites well. Anyways, here are the videos from Lecture 3. There will be a fourth and final lecture next week. As always, thanks for reading!
This is a video blog continuation of week 1 (see the previous post).
Background: I was asked by the IEEE student club at UB (http://wings.buffalo.edu/sa/ieee) to redo my lecture series on developing websites. I know that my website isn’t the best visually designed website in the world (and I’m actively looking for talent that can help me fix this in exchange for my programming skills), but I do know a lot about how to code websites well. Anyways, here are the videos from Lecture 2. As always, thanks for reading!
It seems like its been a while since I last posted to my blog. As usual, the mid semester grind is hitting my time hard, so I don’t have the resources to write here as often. Thankfully this is my last semester as a Masters student, *yay*. So coming up soon, I will be writing a blog on regular expressions, I’m still in the process of making it, so it might take a while. I’ve also got a great blog for April fools day, so stay tuned for that!
Now onto the topic of this blog. This is a video blog (go figure, right?). Actually, I was asked by the IEEE student club at UB (http://wings.buffalo.edu/sa/ieee) to redo my lecture series on developing websites. I know that my website isn’t the best visually designed website in the world (and I’m actively looking for talent that can help me fix this in exchange for my programming skills), but I do know a lot about how to code websites well.
Being the kind of person who likes to share knowledge, I agreed to give the lecture series with updated information. John suggested that I video tape the lectures and post them to my blog, which I thought was a great suggestion. So in the next 5 to 6 weeks, I will be posting youtube 720p HD recordings of the lecture series to here. Feel free to watch it and make comments about my presentation skills. I know that I’m not perfect, so I’m always open to suggestions!
It has been a while, but I’ve been moving back to college and such and haven’t really had the time to write new blogs about technical topics. I hope that after this next week is through, my normal college schedule will be finalized and any bugs in the FamousPhil calendar / countdown system will be fixed (yes, there are bugs ). This is a blog that I drafted about 4 months ago, but haven’t really finished until now, so I feel that now is an appropriate time to open the topic then provide video / photos later. Read the rest of this entry »
Hopefully this is useful to someone else.
). This is a blog that I drafted about 4 months ago, but haven’t really finished until now, so I feel that now is an appropriate time to open the topic then provide video / photos later. Read the rest of this entry »