Tiny Threads

[Code] New Quick test toolbox

Posted on March 20, 2013 by

When I’m programming, I tend to have a need to test strings a lot. Making sure that what I’m outputting is correct and stuff like that.

Normally I would write a quick bit of code to test it, but this got tedious for me so I built a small toolbox to help with this. I find it useful, so I think others might too. Feel free to check it out and make use of it where you want.

If you think of a quick bit of code that you test with a lot, and would like it to be added to the toolbox, leave a comment and I will see if I can get it added.

Tiny Threads Toolbox

Posted in Blog Info, Code |

[PHP] PDO Class Update

Posted on February 27, 2013 by

Steve Koehler

I have just updated the PDO class, and I have placed it over on Github. You can check that out here.

I’ve never used Git or anything like it, so this is new for me. Please help me if you find something wrong with how I have it set up.

Changes:

Disabled emulated prepared statements
Added Error class and implemented within DB class to handle errors silently
Added more error checking

Posted in Code, PHP |

[PHP] MySQL PDO Class update

Posted on September 13, 2012 by

Steve Koehler

Terri Paradowski liked this post

This version is out of date. A newer version is available.

A few months ago, I had written a class to handle PDO MySQL queries. Since then I have made bug fixes and added a new method that have made my life a little bit easier.

As far as usage is concerned, I have added the ability to select a single row, and now you can turn debugging on so you can see what the query being run is as well as the raw error.

So without further ado, here is the class:

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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242

class DB{

private $db;
private $sql='';
public $debug=false;
private $replace=array();

// initialize connection
function __construct(){
try {
$dsn="mysql:dbname=".DB_NAME.";host=".DB_HOST;
$this->db = new PDO($dsn, DB_USER, DB_PASS);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "
";
die();
}
}

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);
}
}

// remove slashes from all retrieved variables
private function prep_vars($vars){
if(is_array($vars)){
foreach($vars as $key=>$value)
$ret[$key]=$this->prep_vars($value);
}
elseif(is_object($vars)){
foreach($vars as $key=>$value)
$ret->$key=$this->prep_vars($value);
}
elseif(is_string($vars)){
$ret=stripslashes($vars);
}
else{
$ret=$vars;
}
return $ret;
}

// 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);
$e=$sth->errorInfo();
if($e[0]!='00000'){
if($this->debug){
if($e[2])
echo 'ERROR:: '.$e[2];
else
echo 'ERROR:: General Error';
}
}
if($this->debug)
$this->_get_query($query,$vals,$e);
return $this->prep_vars($result);
}

//select and return only one row
function select_one($table,$vals='*',$where=array(),$extra=''){
$s=$this->select($table,$vals,$where);
return $s[0];
}

// select function
function select($table,$vals='*',$where=array(),$extra=''){
// 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);

$this->sql.' '.$extra;
$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 get_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];
}

//debugging function
private function _get_query($query,$val,$er=0){
echo '

';
foreach($val as $key=>$value)
$query=str_replace($key,"'".$value."'",$query);
echo 'QUERY:
'.$query;
if($er){
echo '

Raw error:
<pre>';
print_r($er);
echo '</pre>
';
}
echo '

<hr />

';
}
}

Usage:

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

//assigning my database information
define('DB_HOST','MyHost');
define('DB_USER','MyUsername');
define('DB_PASS','MyPassword');
define('DB_NAME','MyDatabase');

//instantiate class
$database = new DB();

//select all rows in a table
$database->select('MyDatabaseTable');

//select based on criteria
$database->select('MyDatabaseTable','*',array('id'=>1));

//select certain values based on criteria
$database->select('MyDatabaseTable','name,id',array('id'=>1));

//select based on criteria with a set order
$database->select('MyDatabaseTable','*',array('id'=>1),'ORDER BY name ASC');

//select single row based on criteria
$database->select_one('MyDatabaseTable','*',array('id'=>1));

//insert into table; returns primary key of that row
$options=array(
id=>1,
name=>'test'
);
$database->insert('MyDatabaseTable',$options);

//update table
$options=array(
name=>'test_again'
);
$database->update('MyDatabaseTable',$options,array('id'=>1));

//delete from table
$database->delete('MyDatabaseTable',array(id=>1));

//get a count of rows matching the criteria
$database->get_count('MyDatabaseTable',array(id=>1));

//select a single value from a table
$database->get_value('MyDatabaseTable','name',array('id'=>1));

//run your own query; NOT AS SECURE AS USING THE OTHER FUNCTIONS
$database->query("SELECT * FROM MyDatabaseTable WHERE id=1");

//debugging
$database->debug=true;

Posted in Code, PHP |

8 Comments

[PHP] Get Latitude and Longitude with Google Maps API

Posted on August 17, 2012 by

Steve Koehler

I am in the process of converting a CMS I wrote a year or so to a standalone program instead of plugins for another system, and I found this handy function I’d written for it.

Basically it references the Google Maps API to convert an address string to a latitude and longitude array. It’s very simple and I’m sure it can be expanded on, but if you just need a quick conversion, this should fit the bill.

1
2
3
4
5
6
7
8
9
10
11
12

function get_latlng($address){
// connect with Google and grab the information
$geocode=file_get_contents('http://maps.google.com/maps/api/geocode/json?address='.urlencode($address).'&sensor=false');

// convert from json to usable info
$output= json_decode($geocode);
$lat = $output->results[0]->geometry->location->lat;
$long = $output->results[0]->geometry->location->lng;

// return just the latitude and longitude
return array('lat'=>$lat,'lng'=>$long);
}

Posted in Code |

[PHP] MySQL PDO class

Posted on February 1, 2012 by

Steve Koehler

This version is out of date. A newer version is available.

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.

// 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];
}
}

?>

Posted in Code, PHP |

6 Comments

[Code] New Quick test toolbox

[PHP] PDO Class Update

Changes:

[PHP] MySQL PDO Class update

[PHP] Get Latitude and Longitude with Google Maps API

[PHP] MySQL PDO class

Categories