[PHP] Convert number to Excel column letter

I had a situation where I had to convert an array to an Excel spreadsheet. I decided to use PHPExcel to accomplish this.

One caveat to the way I had to do this was I needed to convert my column numbers to letters. So I did what I always do and wrote a quick function to handle it for me.

I wrote it so it goes on toward infinity, so as to accommodate any future increases in column numbers.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
function get_col_letter($num){
    $comp=0;    
    $pre='';
    $letters=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
   
    //if the number is greater than 26, calculate to get the next letters
    if($num > 26){
        //divide the number by 26 and get rid of the decimal
        $comp=floor($num/26);
       
        //add the letter to the end of the result and return it
    if($comp!=0)
        // don't subtract 1 if the comparative variable is greater than 0
        return get_col_letter($comp).$letters[($num-$comp*26)];
    else
            return get_col_letter($comp).$letters[($num-$comp*26)-1];
    }
    else
    //return the letter
        return $letters[($num-1)];
}

Update: Fixed an error after 26 that caused all columns to be returned one column off. Thanks to Scott for this catch!

Posted in Code | 2 Comments

[PDO] Big update

I just finished updating PDO, and I have to say that I made some decently large changes behind the scenes. The changes have more to do with how things function and less with how you interact with them. The biggest thing that I added is MORE ERROR REPORTING! I’ll wait for you to stop applauding.

I have added some more error checks as well as added the abilities to return an array instead of an object and use null variables. This also adds a new option to the class: return_type.

return_type defaults to ‘object’, but can be changed to ‘array’ if you want that instead. It can also be changed at any time so no need to use multiple class instances. I know that’s really how classes should work anyway, but I felt the need to point it out.

Anyway, for those of you keeping track at home, here are the changes:
Added return type and more debug
Update to error checking and issue #8

And here is a link to PDO on Github.

Let me know about any more issues!

Posted in Code, PDO, PHP, Projects | Leave a comment

[Code] New Quick test toolbox

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 | Leave a comment

[PHP] PDO Class Update

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, PDO, PHP, Projects | 1 Comment

[PHP] MySQL PDO Class update

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 '<strong>ERROR:</strong>: '.$e[2];
else
echo '<strong>ERROR:</strong>: General Error';
}
}
if($this-&gt;debug)
$this-&gt;_get_query($query,$vals,$e);
return $this-&gt;prep_vars($result);
}

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

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

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

$this-&gt;sql.=' FROM '.$table;

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

$this-&gt;sql.' '.$extra;
$ret=$this-&gt;query($this-&gt;sql,$this-&gt;replace);
return $ret;
}

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

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

// build the replace array and the query
$c=count($this-&gt;replace);
foreach($vals as $key=&gt;$v){
$this-&gt;sql.=$key.'=:'.$c.', ';
$this-&gt;replace[':'.$c]=$v;
$c++;
}
$this-&gt;sql=substr($this-&gt;sql,0,-2);
// run and return the query
$ret=$this-&gt;query($this-&gt;sql,$this-&gt;replace);
$id=$this-&gt;db-&gt;lastInsertId();

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

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

$this-&gt;sql="UPDATE ".$table." SET ";

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

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

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

$this-&gt;sql="DELETE FROM ".$table;

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

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

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

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

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

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

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

// gets value of requested column
function get_value($table,$val,$where=array()){
// run query
$o=$this-&gt;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=&gt;$value)
$query=str_replace($key,"'".$value."'",$query);
echo '<strong>QUERY:</strong>
'
.$query;
if($er){
echo '

<strong>Raw error:</strong>
<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-&gt;select('MyDatabaseTable');

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

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

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

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

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

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

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

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

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

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

//debugging
$database-&gt;debug=true;
Posted in Code, PDO, PHP, Projects | 8 Comments