root/dev/common/db.php @ 92

Revision 92, 17.5 KB (checked in by exi, 14 years ago)

Updated about page for 1.2.
Made table bgcolor black for alliance images.
Made table bgcolor black for corp images.
The parser now reparsers dupes and deletes comments on killmail deletion.
Changed parser to output some error messages if he doesn't like the mail.
Added conversion code for those funny german mailheader.
Fixed bugs #3 and #4.
Included a fix from Coni to class.ship.php.
Removed the error for unknown solar systems, this is now covered by the parser itself.
Now ordering comments by date.
Fixed db.php:getRow to return false in case of a non valid mysql resource id.
Added the sql killmail limit for home.php.
Added default width/heigth for pictures in kill details.
The searchpage will now redirect you to your hit if it only gets one serach result.

Line 
1<?php
2require_once('config.php');
3require_once('globals.php');
4
5class DBConnection
6{
7    function DBConnection()
8    {
9        if (!$this->id_ = mysql_pconnect(DB_HOST, DB_USER, DB_PASS))
10            die("Unable to connect to mysql database.");
11
12        mysql_select_db(DB_NAME);
13    }
14
15    function id()
16    {
17        return $this->id_;
18    }
19
20    function affectedRows()
21    {
22        return mysql_affected_rows($this->id_);
23    }
24}
25
26class DBQuery
27{
28    function DBQuery()
29    {
30        $this->executed_ = false;
31        $this->dbconn_ = new DBConnection;
32    }
33
34    function execute($sql)
35    {
36        $t1 = strtok(microtime(), ' ') + strtok('');
37
38        $this->resid_ = mysql_query($sql, $this->dbconn_->id());
39
40        if (!$this->resid_)
41        {
42            if (defined(DB_HALTONERROR) && DB_HALTONERROR)
43            {
44                echo "Database error: " . mysql_error($this->dbconn_->id()) . "<br>";
45                echo "SQL: " . $sql . "<br>";
46                exit;
47            }
48            else
49            {
50                return false;
51            }
52        }
53
54        $this->exectime_ = strtok(microtime(), ' ') + strtok('') - $t1;
55        $this->executed_ = true;
56
57        if (KB_PROFILE == 2)
58        {
59            file_put_contents('/tmp/profile.lst', $sql . "\nExecution time: " . $this->exectime_ . "\n", FILE_APPEND);
60        }
61
62        return true;
63    }
64
65    function recordCount()
66    {
67        return mysql_num_rows($this->resid_);
68    }
69
70    function getRow()
71    {
72        if ($this->resid_)
73        {
74            return mysql_fetch_assoc($this->resid_);
75        }
76        return false;
77    }
78
79    function rewind()
80    {
81        @mysql_data_seek($this->resid_, 0);
82    }
83
84    function getInsertID()
85    {
86        return mysql_insert_id();
87    }
88
89    function execTime()
90    {
91        return $this->exectime_;
92    }
93
94    function executed()
95    {
96        return $this->executed_;
97    }
98
99    function getErrorMsg()
100    {
101        $msg = $this->sql_ . "<br>";
102        $msg .= "Query failed. " . mysql_error($this->dbconn_->id());
103
104        return $msg;
105    }
106}
107/*
108 * this looks like REALLY old code and isnt used anywhere
109 * commented out for now
110 *
111class DBAction
112{
113    function DBAction()
114    {
115        $this->dbconn_ = new DBConnection();
116    }
117
118    function setDateTime($timestamp)
119    {
120        $this->timestamp_ = $timestamp;
121    }
122
123    function addPilot($pilot, $corpid, $timestamp)
124    {
125        $sql = "select * from kb3_pilots where plt_name = '" . addslashes(stripslashes($pilot)) . "'";
126        $qry = new DBQuery($this->dbconn_, $sql);
127        $qry->execute() or die($qry->getErrorMsg());
128
129        if ($qry->recordCount() == 0)
130        {
131            $sql = "insert into kb3_pilots values ( null, '" . addslashes(stripslashes($pilot)) . "', " . $corpid . ", 0, 0, 0, date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') )";
132            $qry = new DBQuery($this->dbconn_, $sql) or die ($qry->getErrorMsg());
133            $qry->execute() or die($qry->getErrorMsg());
134            $pilotid = $qry->getInsertID();
135        }
136        else
137        {
138            $row = $qry->getRow();
139            $pilotid = $row['plt_id'];
140            $dbaction = new DBAction();
141
142            if ($dbaction->isPilotUpdatable($pilotid, $timestamp) && $$row['plt_crp_id'] != $corpid)
143            {
144                $qry = new DBQuery($this->dbconn_, "update kb3_pilots set plt_crp_id = " . $corpid . ", plt_updated = date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') where plt_id = " . $pilotid);
145                $qry->execute() or die($qry->getErrorMsg());
146            }
147        }
148
149        return $pilotid;
150    }
151
152    function setCharacterId($pilotid, $charid)
153    {
154        $sql = "update kb3_pilots set plt_externalid = " . $charid . " where plt_id = " . $pilotid;
155
156        $qry = new DBQuery($this->dbconn_, $sql);
157        $qry->execute();
158    }
159
160    function addAlliance($alliance)
161    {
162        $sql = "select * from kb3_alliances where all_name = '" . addslashes(stripslashes($alliance)) . "'";
163        $qry = new DBQuery($this->dbconn_, $sql);
164        $qry->execute() or die($qry->getErrorMsg());
165
166        if ($qry->recordCount() == 0)
167        {
168            $sql = "insert into kb3_alliances values ( null, '" . addslashes(stripslashes($alliance)) . "', 'default' )";
169            $qry = new DBQuery($this->dbconn_, $sql);
170            $qry->execute() or die($qry->getErrorMsg());
171            $allianceid = $qry->getInsertID();
172        }
173        else
174        {
175            $row = $qry->getRow();
176            $allianceid = $row['all_id'];
177        }
178
179        return $allianceid;
180    }
181
182    function addCorp($corp, $allianceid, $timestamp)
183    {
184        $sql = "select * from kb3_corps where crp_name = '" . addslashes(stripslashes($corp)) . "'";
185        $qry = new DBQuery($this->dbconn_, $sql);
186        $qry->execute() or die($qry->getErrorMsg());
187
188        if ($qry->recordCount() == 0)
189        {
190            $sql = "insert into kb3_corps values ( null, '" . addslashes(stripslashes($corp)) . "', " . $allianceid . ", 0, date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') )";
191            $qry = new DBQuery($this->dbconn_, $sql);
192            $qry->execute() or die($qry->getErrorMsg());
193            $corpid = $qry->getInsertID();
194        }
195        else
196        {
197            $row = $qry->getRow();
198            $corpid = $row['crp_id'];
199
200            $dbaction = new DBAction();
201
202            if ($dbaction->isCorpUpdatable($corpid, $timestamp) && $row['crp_all_id'] != $allianceid && $row['crp_trial'] != 1)
203            {
204                $sql = "update kb3_corps set crp_all_id = " . $allianceid . ", crp_updated = date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s')
205                   where crp_id = " . $corpid;
206                $qry = new DBQuery($this->dbconn_, $sql);
207                $qry->execute() or die($qry->getErrorMsg());
208            }
209        }
210
211        return $corpid;
212    }
213
214    function addShip($ship, $description, $baseprice)
215    {
216        $sql = "select * from kb3_ships where shp_name = '" . addslashes(stripslashes($ship)) . "'";
217        $qry = new DBQuery($this->dbconn_, $sql);
218        $qry->execute();
219
220        if ($qry->recordCount() == 0)
221        {
222            $sql = "insert into kb3_ships values ( null, '" . addslashes(stripslashes($ship)) . "', 0, 0, 0, null, 0 )";
223            $qry = new DBQuery($this->dbconn_, $sql);
224            $qry->execute();
225            $shipid = $qry->getInsertID();
226        }
227        else
228        {
229            $row = $qry->getRow();
230            $shipid = $row['shp_id'];
231            if ($description != "")
232            {
233                $sql = "update kb3_ships set shp_description = '" . addslashes(stripslashes($description)) . "', shp_baseprice = " . $baseprice . " where shp_id = " . $shipid;
234                $qry = new DBQuery($this->dbconn_, $sql) or die($qry->getErrorMsg());
235                $qry->execute();
236            }
237        }
238
239        return $shipid;
240    }
241
242    function addItem($item, $description, $volume, $type, $icon)
243    {
244        $sql = "select * from kb3_items where itm_name = '" . addslashes(stripslashes($item)) . "'";
245        $qry = new DBQuery($this->dbconn_, $sql) or die ($qry->getErrorMsg());
246        $qry->execute();
247
248        if ($qry->recordCount() == 0)
249        {
250            $sql = "insert into kb3_items values ( null, '" . addslashes(stripslashes($item)) . "', '" . addslashes(stripslashes($description)) . "', " . $volume . ", " . $type . ", null, 1, '" . $icon . "' )";
251            $qry = new DBQuery($this->dbconn_, $sql);
252            $qry->execute() or die ($qry->getErrorMsg());
253            $itemid = $qry->getInsertID();
254            return -1;
255        }
256        else
257        {
258            $row = $qry->getRow();
259            $itemid = $row['itm_id'];
260            if ($description != "")
261            {
262                $sql = "update kb3_items set itm_description = '" . addslashes(stripslashes($description)) . "', itm_volume = " . $volume . ", itm_type = " . $type . ", itm_icon = '" . $icon . "' where itm_id = " . $itemid;
263                $qry = new DBQuery($this->dbconn_, $sql);
264                $qry->execute() or die ($qry->getErrorMsg());
265            }
266        }
267
268        return $itemid;
269    }
270
271    function setIcon($itemid, $icon)
272    {
273        $sql = "update kb3_items set itm_icon = '" . $icon . "' where itm_id = " . $itemid;
274        $qry = new DBQuery($this->dbconn_, $sql);
275        $qry->execute();
276    }
277
278    function addSystem($system, $systemsec)
279    {
280        $sql = "select * from kb3_systems where sys_name = '" . addslashes(stripslashes($system)) . "'";
281        $qry = new DBQuery($this->dbconn_, $sql);
282        $qry->execute() or die ($qry->getErrorMsg());
283
284        if ($qry->recordCount() == 0)
285        {
286            $sql = "insert into kb3_systems values ( null, '" . addslashes(stripslashes($system)) . "', '" . $systemsec . "', 0 )";
287            $qry = new DBQuery($this->dbconn_, $sql);
288            $qry->execute() or die ($qry->getErrorMsg());
289            $systemid = $qry->getInsertID();
290        }
291        else
292        {
293            $row = $qry->getRow();
294            $systemid = $row['sys_id'];
295        }
296
297        return $systemid;
298    }
299
300    function addInvolved($killid, $pilotid, $secstatus, $allianceid, $corpid, $shipid, $weaponid, $finalblow, $order)
301    {
302        $sql = "insert into _" . KB_SITE . "_involved values ( " . $killid . ", " . $pilotid . ", '" . $secstatus . "', " . $allianceid . ", " . $corpid . ", " . $shipid . ", " . $weaponid . ", " . $finalblow . ", " . $order . " )";
303        $qry = new DBQuery($this->dbconn_, $sql);
304        $qry->execute() or die ($qry->getErrorMsg());
305    }
306
307    function addDestroyedItem($killid, $itemid, $quantity, $location)
308    {
309        $qry = new DBQuery($this->dbconn_, "select itl_id from kb3_item_locations where itl_location = '" . $location . "'");
310        $qry->execute() or die($qry->getErrorMsg());
311        $row = $qry->getRow();
312        if ($row['itl_id'] != "")
313            $locationid = $row['itl_id'];
314        else
315            $locationid = 0;
316
317        $sql = "insert into _" . KB_SITE . "_items_destroyed values ( " . $killid . ", " . $itemid . ", " . $quantity . ", " . $locationid . " )";
318        $qry = new DBQuery($this->dbconn_, $sql);
319        $qry->execute() or die ($qry->getErrorMsg());
320    }
321
322    function addKill($timestamp, $victimid, $allianceid, $corpid, $shipid, $systemid)
323    {
324        $killid = 0;
325        // check if the killmail already exists
326        $sql = "select * from _" . KB_SITE . "_kills where kll_timestamp = date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') and kll_victim_id = " . $victimid . " and kll_ship_id = " . $shipid;
327        $qry = new DBQuery($this->dbconn_, $sql);
328        $qry->execute();
329
330        if ($qry->recordCount() == 0)
331        {
332            $sql = "insert into _" . KB_SITE . "_kills values ( null, date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s'), " . $victimid . ", " . $allianceid . ", " . $corpid . ", " . $shipid . ", " . $systemid . " )";
333            $qry = new DBQuery($this->dbconn_, $sql);
334            $qry->execute() or die ($qry->getErrorMsg());
335            $killid = $qry->getInsertID();
336            // log entry
337            // $sql = "insert into kb3_log values ( ".$killid.", '".$_SERVER['REMOTE_ADDR']."', now() )";
338            // $qry = new DBQuery( $this->dbconn_, $sql );
339            // $qry->execute() or die ( $qry->getErrorMsg() );
340        }
341
342        return $killid;
343    }
344
345    function rollback($killid)
346    {
347        $qry = new DBQuery($this->dbconn_, "delete from _" . KB_SITE . "_kills where kll_id = " . $killid);
348        $qry->execute();
349
350        $qry = new DBQuery($this->dbconn_, "delete from _" . KB_SITE . "_involved where inv_kll_id = " . $killid);
351        $qry->execute();
352
353        $qry = new DBQuery($this->dbconn_, "delete from _" . KB_SITE . "_items_destroyed where itd_kll_id = " . $killid);
354        $qry->execute();
355    }
356
357    function getRawMail($killid, $site = "")
358    {
359        if ($site == "")
360            $site = KB_SITE;
361        $sql = "select kll.kll_timestamp, plt.plt_id, plt.plt_name, plt.plt_externalid, crp.crp_id, crp.crp_name, ali.all_id, ali.all_name, shp.shp_name, shp.shp_externalid, scl.scl_class, sys.sys_name, sys.sys_sec, kll.kll_system_id
362                from _" . $site . "_kills kll, kb3_pilots plt, kb3_corps crp,
363                     kb3_alliances ali, kb3_ships shp, kb3_ship_classes scl,
364                     kb3_systems sys
365       where kll.kll_id = " . $killid . "
366         and plt.plt_id = kll.kll_victim_id
367         and crp.crp_id = kll.kll_crp_id
368         and ali.all_id = kll.kll_all_id
369         and shp.shp_id = kll.kll_ship_id
370         and sys.sys_id = kll.kll_system_id
371         and scl.scl_id = shp.shp_class";
372
373        $qry = new DBQuery();
374        if (!$qry->execute($sql)) die($qry->getErrorMsg());
375        $row = $qry->getRow();
376
377        $mail .= substr(str_replace('-', '.' , $row['kll_timestamp']), 0, 16) . "\r\n\r\n";
378        $mail .= "Victim: " . $row['plt_name'] . "\r\n";
379        $mail .= "Alliance: " . $row['all_name'] . "\r\n";
380        $mail .= "Corp: " . $row['crp_name'] . "\r\n";
381        $mail .= "Destroyed: " . $row['shp_name'] . "\r\n";
382        $mail .= "System: " . $row['sys_name'] . "\r\n";
383        $mail .= "Security: " . roundsec($row['sys_sec']) . "\r\n\r\n";
384        $mail .= "Involved parties:\r\n\r\n";
385
386        $sql = "select plt.plt_id, plt.plt_name, plt.plt_externalid, crp.crp_name, ali.all_name, shp.shp_name, shp.shp_externalid, scl.scl_class,
387                 inv.inv_final_blow, itm.itm_name, inv.inv_sec_status
388            from _" . $site . "_involved inv, kb3_pilots plt, kb3_corps crp, kb3_alliances ali,
389             kb3_ships shp, kb3_ship_classes scl, kb3_items itm
390       where inv.inv_kll_id = " . $killid . "
391         and plt.plt_id = inv.inv_plt_id
392         and crp.crp_id = inv.inv_crp_id
393         and ali.all_id = inv.inv_all_id
394         and shp.shp_id = inv.inv_shp_id
395         and scl.scl_id = shp.shp_class
396         and itm.itm_id = inv.inv_wep_id
397      order by inv.inv_order";
398
399        $qry = new DBQuery();
400        $qry->execute($sql);
401
402        while ($row = $qry->getRow())
403        {
404            $mail .= "Name: " . $row['plt_name'];
405            if ($row['inv_final_blow'] == 1)
406                $mail .= " (laid the final blow)";
407            $mail .= "\r\n";
408
409            $mail .= "Security: " . $row['inv_sec_status'] . "\r\n";
410            $mail .= "Alliance: " . $row['all_name'] . "\r\n";
411            $mail .= "Corp: " . $row['crp_name'] . "\r\n";
412            $mail .= "Ship: " . $row['shp_name'] . "\r\n";
413            $mail .= "Weapon: " . $row['itm_name'] . "\r\n\r\n";
414        }
415
416        $sql = "select itd_quantity, itm.itm_name, itd.itd_itl_id
417            from _" . $site . "_items_destroyed itd, kb3_items itm
418           where itd.itd_kll_id = " . $killid . "
419             and itm.itm_id = itd.itd_itm_id
420           order by itd.itd_itl_id";
421
422        $qry = new DBQuery();
423        $qry->execute($sql) or die($qry->getErrorMsg());
424
425        if ($qry->recordCount())
426        {
427            $mail .= "\r\nDestroyed items:\r\n\r\n";
428
429            while ($row = $qry->getRow())
430            {
431                $mail .= $row['itm_name'];
432                if ($row['itd_quantity'] > 1)
433                    $mail .= ", Qty: " . $row['itd_quantity'];
434                if ($row['itd_itl_id'] == 4) // cargo
435                    $mail .= " (Cargo)";
436                if ($row['itd_itl_id'] == 6) // drone
437                    $mail .= " (Drone Bay)";
438                $mail .= "\r\n";
439            }
440        }
441
442        return $mail;
443    }
444
445    function getPostPassword()
446    {
447        $qry = new DBQuery($this->dbconn_, "select cfg_value from kb3_config where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_password'");
448        $qry->execute();
449        $row = $qry->getRow();
450
451        return $row['cfg_value'];
452    }
453
454    function getPostMailto()
455    {
456        $qry = new DBQuery($this->dbconn_, "select cfg_value from kb3_config where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_mailto'");
457        $qry->execute();
458        $row = $qry->getRow();
459
460        return $row['cfg_value'];
461    }
462
463    function setPostPassword($password)
464    {
465        $qry = new DBQuery($this->dbconn_, "update kb3_config set cfg_value = '" . $password . "' where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_password'");
466        $qry->execute();
467    }
468
469    function setPostMailto($mailto)
470    {
471        $qry = new DBQuery($this->dbconn_, "update kb3_config set cfg_value = '" . $mailto . "' where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_mailto'");
472        $qry->execute();
473    }
474
475    function isCorpUpdatable($crp_id, $timestamp)
476    {
477        $qry = new DBQuery($this->dbconn_,
478            "select crp_id from kb3_corps
479                            where crp_id = " . $crp_id . "
480                              and ( crp_updated < date_format('" . $timestamp . "', '%Y.%m.%d %H:%i') or crp_updated is null )");
481        $qry->execute() or die($qry->getErrorMsg());
482        return $qry->recordCount();
483    }
484
485    function isPilotUpdatable($plt_id, $timestamp)
486    {
487        $qry = new DBQuery($this->dbconn_,
488            "select plt_id from kb3_pilots
489                            where plt_id = " . $plt_id . "
490                              and ( plt_updated < date_format('" . $timestamp . "', '%Y.%m.%d %H:%i') or plt_updated is null )");
491        $qry->execute() or die($qry->getErrorMsg());
492
493        return $qry->recordCount();
494    }
495}
496*/
497?>
Note: See TracBrowser for help on using the browser.