Tiny Threads

[PHP] MySQL PDO class

Steve Koehler — Wed, 01 Feb 2012 18:47:59 +0000

I had been wanting to get away from the standard MySQL classes for awhile. I knew they were inherently less secure, but part of moving to something different i meant that I would have to change the way I work with the database.

I wanted security, but I also wanted it to be easier to plug in to applications. So I created a mysql class that connects using PDO and has some protections against injections.

Mind you, this is a work in progress, so if you find a security issue or any other sort of issue, please comment so this can get updated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186

// these 4 lines can be moved to a different file if you want to separate them from the class
define('DB_HOST','your host here');
define('DB_USER','your db username');
define('DB_PASS','your db password');
define('DB_NAME','your database name');

class DB
{
private $db;
private $sql='';
private $replace=array();

// initialize connection
function __construct(){
$this->db = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASS);
}

private function build_where($where){
if(!empty($where)){
$this->sql.=" WHERE ";
$c=count($this->replace);
foreach($where as $key=>$w){
// look for any comparitive symbols within the where array value.
if(substr($w,0,1)=='%'){
// prep the query for PDO->prepare
$this->sql.=$key.'=%:'.$c.'% && ';
$this->replace[':'.$c]=$w;
}
else{
if(substr($w,0,2)=='<=')
$eq='<=';
elseif(substr($w,0,2)=='>=')
$eq='>=';
elseif(substr($w,0,1)=='>')
$eq='>';
elseif(substr($w,0,1)=='<')
$eq='<';
elseif(substr($w,0,1)=='!')
$eq='!=';
else
$eq='=';

// prep the query for PDO->prepare
$this->sql.=$key.$eq.':'.$c.' && ';
$this->replace[':'.$c]=$w;
}
$c++;
}
$this->sql=substr($this->sql,0,-4);
}
}

// general query function
function query($query,$vals=''){
$sth=$this->db->prepare($query);
if($vals)
$sth->execute($vals);
else
$sth->execute();
$result=$sth->fetchAll(PDO::FETCH_OBJ);
return $result;
}

// select function
function select($table,$vals='*',$where=array()){
// initialize the sql query
$this->replace=array();
$this->sql="SELECT ";

// add all the values to be selected
if(is_array($vals)){
foreach($vals as $v)
$this->sql.=$v.',';
$this->sql=substr($this->sql,0,-1);
}
else
$this->sql.=$vals;

$this->sql.=' FROM '.$table;

// build the WHERE portion of the query
$this->build_where($where);
$ret=$this->query($this->sql,$this->replace);
return $ret;
}

// insert
function insert($table,$vals){
// empty the replace array
$this->replace=array();

$this->sql="INSERT INTO ".$table." SET ";

// build the replace array and the query
$c=count($this->replace);
foreach($vals as $key=>$v){
$this->sql.=$key.'=:'.$c.', ';
$this->replace[':'.$c]=$v;
$c++;
}
$this->sql=substr($this->sql,0,-2);

// run and return the query
$ret=$this->query($this->sql,$this->replace);
$id=$this->db->lastInsertId();

if($id)
return $id;
else
return $ret;
}

// update
function update($table,$vals,$where=array()){
// empty the replace array
$this->replace=array();

$this->sql="UPDATE ".$table." SET ";

// build the replace array and the query
$c=count($this->replace);
foreach($vals as $key=>$v){
$this->sql.=$key.'=:'.$c.', ';
$this->replace[':'.$c]=$v;
$c++;
}
$this->sql=substr($this->sql,0,-2);

// build the WHERE portion of the query
$this->build_where($where);

// run and return the query
return $this->query($this->sql,$this->replace);
}
function delete($table,$where){
// empty the replace array
$this->replace=array();

$this->sql="DELETE FROM ".$table;

// build the WHERE portion of the query
$this->build_where($where);

// run and return the query
return $this->query($this->sql,$this->replace);
}

// get the number of records matching the requirements
function count($table,$where=false){

// start query
$this->sql="SELECT COUNT(*) FROM ".$table;

// build the WHERE portion of the query
if($where)
$this->build_where($where);

// run and return the query
$sth=$this->db->prepare($query);
if($vals)
$sth->execute($vals);

// return the row count
return $sth->rowCount();
}

// gets value of requested column
function get_value($table,$val,$where=array()){
// run query
$o=$this->select($table,$val,$where);

// convert first object in associative array to array
$v=get_object_vars($o[0]);

// return requested value
return $v[$val];
}
}

?>

[Browser] Window Resizer for Google Chrome

Steve Koehler — Tue, 31 Jan 2012 20:02:48 +0000

I have recently started trying to build web apps and one thing I have to take into account are the dimensions for mobile devices. I could try to guesstimate, or I could use Window Resizer.

This little extension has made a world of difference in the ease of designing mobile web pages. I enter in my custom sizes if they aren’t already there and away I go! I 100% recommend this extension if you use Chrome.

If you use Firefox, I’d recommend Firesizer. Simple and unobtrusive. There is also Browser Window Resizer for IE, but since their Firefox plugin is now unsupported, I hesitate to recommend it, but I am posting it here for anyone who wants to try it. If you do try it, let me know how it goes.

Stop SOPA and PIPA: Useful links and info

Steve Koehler — Wed, 18 Jan 2012 14:35:09 +0000

I want to point out, for those of you who don’t already know, that the big problem with these bills is not the piracy issue. It’s that they are so poorly and broadly written that they can be abused too easily.

If a large company feels a smaller company has infringed on it’s copyright, and it fills out the paperwork correctly, the smaller company is then blocked on the web and has to defend itself. That means it’s up to the smaller company to hire the lawyers and get the defense going. For a lot of small businesses that is a death sentence.

These bills will stifle innovation and growth because of the constant fear of being torn down. Honestly, it deters me from starting any sort of new website. Heaven forbid I link to a website that could cause me to be in violation.

Thanks for your time and for reading this far. If you didn’t just please go sign the petition.

Other useful links (some info may be a little outdated now):
http://en.wikipedia.org/wiki/Stop_Online_Piracy_Act
http://news.cnet.com/8301-31921_3-57329001-281/how-sopa-would-affect-you-faq/
http://mashable.com/2012/01/16/white-house-sopa-petition/
http://act2.freepress.net/sign/media_sopa
http://www.stumbleupon.com/su/2zhuoc/list25.com/25-things-you-need-to-know-about-sopa/

[jQuery] jQuery UI MultiSelect Widget

Steve Koehler — Wed, 26 Oct 2011 17:37:27 +0000

So like many UI developers, I am always looking for new and useful jQuery plugins that I can use to simplify forms and make things easier for the end user. One particular case had me trying to figure out how to make multiple selection possible in a limited space.

The answer came in the form of Eric Hynds’ jQuery UI MultiSelect Widget. This plugin packs so much into such a little area. It replaces the default multiple select tag with a jQuery UI styled combo box. That was really all I needed, but this little plugin packed in much more.

Eric out did himself and created a supporting plugin that helps to filter the content within the combo box. This added to the user friendliness that my project needed.

I think my only gripe is that there is no way to set an fixed width. While the minWidth property is wonderful, there is no way to keep the selection from growing larger. My suggestion would be to have a fixed width with the combo box able to be larger than the initial width of the selection box.

If you are looking for a great plugin that you can use in place of long checkbox lists or multiple select boxes, I highly recommend this one.

[WordPress] Plugin: Multiple Galleries

Steve Koehler — Fri, 30 Sep 2011 19:47:22 +0000

So a few weeks ago I made a post about a plugin I found that helped to reign in a bit of WordPress’s default gallery habits. Today I found an even better solution

Multiple Galleries is so much better in the fact that you can have multiple galleries for each post. That and it’s a lot easier to opt out of having an image in the gallery.

I have no complaints about this plugin, it just works well. Check it out if you want to have multiple galleries in your blog posts or pages and don’t care about having fancy lightboxes.

[WordPress] Plugin: WordPress Gallery Hack

Steve Koehler — Tue, 20 Sep 2011 17:24:36 +0000

I rarely use the built in WordPress gallery feature, but having started a family blog with my wife, I found that it is more likely that I will use it in the future.

Only problem? No way to distinguish between images I have in the gallery and ones I want separate.

While researching my next move, I found that this was a known issue with WordPress and has yet to be addressed. Until that day, however, there is WordPress Gallery Hack. It’s not in the codex, so don’t try looking there.

This plugin adds a little link withing each gallery image that allows you to remove it from the gallery. It will still show up when you edit the gallery, but it will not be displayed in your post or page. This can be confusing when you first try it out, but once you get it, it’s worth it.

Now I don’t always use galleries, but when I do, I use Wordpress Gallery Hack

[WordPress] Plugin: Admin Login As Different User

Steve Koehler — Fri, 16 Sep 2011 20:05:52 +0000

When I have to debug a plugin, it sometimes requires that I use different accounts to make sure everything is working. This also means that in the live setting, if a user has an issue that I can’t replicate, I have to login under there account. So how do I do that without changing their password each time? With a plugin!

I found Admin Login As Different User while searching through Google for a plugin to do exactly what the title implies. It’s so simple to use that I’m not sure I need to explain.

Install and activate the plugin, go to the plugin page under tools, and pick who you want to login as. Simple as that.

The plugin has saved me a lot of time and headaches since it’s an instant switch over to their profile. I think the only thing that I would change is to provide a link to switch back to your original account.

Small gripes aside, this is a wonderful plugin that any admin should seriously consider adding to their arsenal.

[Intro] Intro to HTML: How to build your first page

Steve Koehler — Tue, 13 Sep 2011 21:25:40 +0000

So now that we’ve been introduced to what HTML is, we can begin to create our first page!

First thing first. Create a new file ending with .html or .htm and save it. This will be the starting file for all your learning. Once you have the file, open it in your notepad or another editor-of choiceand we can begin.

Every HTML page has a very basic layout that is necessary to properly render your content. Knowing how to form the page will affect everything.

Where to start?

To start, you need to choose your doctype. Do you want frames? No frames? Strict rules or relaxed rules? These are things you need to think about when choosing your doctype.

The doctype you pick will determine how the browser interacts with your HTML, and how it displays it. You can review the different kinds of doctypes over at w3schools.com.

For the beginner, I would recommend using XHTML 1.0 Transitional doctype. I recommend this because it will help ease the transition to CSS later on as some browsers don’t like to use HTML 4.01 with css properly (I’m looking at you, IE).

So open up your file and enter this on the first line:

Building the basic structure

So you have your doctype. Now what? Why we build, of course!

Every HTML page, regardless of doctype has to have three tags: html, body, and head. While some people will tell you that you can, and that’s correct, it is never a good idea to leave them out, and that may mess up things down the road.

Ok, we have the three tags. How do we make them work? Very simple. Start off by entering both the opening and closing html tags. Put them right after the doctype.

1
2
3

After this, you want to add your body and head tags. The head tag gives the browser information about your webpage itself and the body tag provides the actual content. The go inside your html tags, so it looks something like this:

1
2
3
4
5

You will always have at least this much in every HTML file you ever make. Ever.

Ok, so I have my structure, but how do I display stuff?

With the basic structure out of the way, we can get into making your web page display some content. Let’s give the page a title so that when you look at the browser window title bar it will tell you what it says.

To do this, we use the title tag. The title tag is added inside of the head tag. Once there, you can put whatever text you want in there, and it will show up in the browser’s title bar.

1
2
3
4
5
6
7

<html>
<head>
<title>My Title</title>
</head>
<body></body>
</html>

Save the file and open it in a browser and you should be able to see your title!

Now for the content. It’s very simple. All the content goes between the body tags.

1
2
3
4
5
6
7

<html>
<head>
<title>My Title</title>
</head>
<body>Hello World!</body>
</html>

That’s all you have to do! Now you can start entering all the tags and content you want!

In Closing…

Having covered the very basic beginning structure of our HTML page, we are now ready to move onto the next part of creating our web page, which will be adding more tags to the mix! It’s about to get real. Does anyone say that anymore?

As with the last post, if you find there is anything missing or inaccurate, then please let me know in the comments!

[WordPress] Plugin: Simple Modal

Steve Koehler — Fri, 09 Sep 2011 19:51:08 +0000

I have just created my first publicly available WordPress plugin. I call it Simple Modal.

I had looked for a plugin that would provide me with a simple shortcode I could use to implement a straight forward modal dialog box, and when I couldn’t find on that met my needs, I made my own.

Check out the project page for more details.

[Intro] Intro to HTML: what is it?

Steve Koehler — Fri, 02 Sep 2011 00:54:17 +0000

You have reached a point in your life where one of two things has happened. You either are curious about how to edit HTML or you are required to edit it because of work or school.

Have no fear. I am starting a series dedicated to covering the basic ins and outs of web programming, starting with HTML!

Let’s dive right in.

What HTML is

If you have ever done a “view source” on a webpage, you have probably seen something like this:

It is also possible that viewing that code made your head explode.

After piecing your head back together, you can start dissecting the structure and you may begin to see that the text follows a pattern.

HTML is a language of tags that are nested within one another. To help you visualize, it’s kind of like those Russian dolls. The top to each doll is an open tag and the bottom is a closing tag. If you open up a doll, you find more dolls (tags).

With HTML, however, you can nest more than just one tag at a time. You can nest as many as you’d like.

What HTML isn’t

HTML is sometimes referred to as a programming language. This is an incorrect statement. HTML is actually a markup language. In fact, it stands for Hyper Text Markup Language.

Markup languages are more for storing simple data and formatting rules whereas a programming language is used to execute commands and manipulate data. In the case of HTML, the language is used to tell a web browser how to display the content.

How HTML works

As I said, HTML tells the browser how to display content. This is done by the different types of tags and their placement on a page.

HTML is read from the top down and, as such, content is typically rendered using the top down. So if you have an image tag before a paragraph tag in your code, the image will render before the paragraph. Alternatively, if you were to move that image tag after the paragraph, it would show up after the paragraph on the rendered page.

Along with the placement, tags have attributes that you can use to manipulate how things are rendered. Tags largely control placement and browser interpretation and attributes control the look and feel of a tag.

Attributes

If you look closely at the image tag in the example I laid out above, you will see an attribute at work. The image tag uses the “src” attribute to tell the browser where to find the image that you want to display. So when you have , the browser looks for cat_pic.jpg in the same folder as your html file.

All tags have some attributes, the most useful of which is the style attribute, which I will cover in a later post. These attributes help to modify how the tag looks and acts.

Attributes are always included as part of the tag and are found between the greater-than and less-than symbols. You assign values to each attribute by saying the attribute = “something”. The values must always be contained in either single or double quotes.

In Closing…

That is HTML in a nutshell. In the next installments I plan to cover actual HTML code. Further past that I plan on introducing CSS, HTML5 and Javascript. Should be a good ride for all you newbies out there. For all your code masters, correct anything I say that may be wrong. I’d rather have this accurate than having too much pride to admit I’m wrong.