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
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
|
<?php
/*
* open database
*/
function DB_open()
{
global $DB,$DB_user,$DB_host,$DB_database,$DB_password;
if ( $DB = mysql_connect($DB_host,$DB_user, $DB_password) )
mysql_select_db($DB_database) or die('Could not select database');
else
die (mysql_error());
return;
}
function DB_close()
{
global $DB;
mysql_close($DB);
return;
}
function DB_quote_smart($value)
{
/* Stripslashes */
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
/* Quote if not a number or a numeric string */
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
function DB_test()
{
$result = mysql_query("SELECT * FROM User");
while($r = mysql_fetch_array($result,MYSQL_NUM))
{
foreach($r as $thing)
echo " $thing ";
echo "<br />\n";
}
return;
}
function DB_get_email_by_name($name)
{
$result = mysql_query("SELECT email FROM User WHERE fullname=".DB_quote_smart($name)."");
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return "";
}
function DB_get_email_by_hash($hash)
{
$result = mysql_query("SELECT User.email FROM User LEFT JOIN Hand ON Hand.user_id=User.id WHERE Hand.hash=".DB_quote_smart($hash)."");
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return "";
}
function DB_get_userid_by_name($name)
{
$result = mysql_query("SELECT id FROM User WHERE fullname=".DB_quote_smart($name));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_userid_by_passwd($passwd)
{
$result = mysql_query("SELECT id FROM User WHERE password=".DB_quote_smart($passwd));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_userid_by_email($email)
{
$result = mysql_query("SELECT id FROM User WHERE email=".DB_quote_smart($email));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_userid_by_email_and_password($email,$password)
{
$result = mysql_query("SELECT id FROM User WHERE email=".DB_quote_smart($email)." AND password=".DB_quote_smart($password));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_handid_by_hash($hash)
{
$result = mysql_query("SELECT id FROM Hand WHERE hash=".DB_quote_smart($hash));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_userid_by_hash($hash)
{
$result = mysql_query("SELECT user_id FROM Hand WHERE hash=".DB_quote_smart($hash));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_pos_by_hash($hash)
{
$result = mysql_query("SELECT position FROM Hand WHERE hash=".DB_quote_smart($hash));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_name_by_hash($hash)
{
$result = mysql_query("SELECT fullname FROM Hand LEFT JOIN User ON Hand.user_id=User.id WHERE hash=".DB_quote_smart($hash));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return "";
}
function DB_get_name_by_userid($id)
{
$result = mysql_query("SELECT fullname FROM User WHERE id=".DB_quote_smart($id));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return "";
}
function DB_get_status_by_hash($hash)
{
$result = mysql_query("SELECT status FROM Hand WHERE hash=".DB_quote_smart($hash));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_set_game_status_by_gameid($id,$status)
{
mysql_query("UPDATE Game SET status='".$status."' WHERE id=".DB_quote_smart($id));
return;
}
function DB_get_game_status_by_gameid($id)
{
$result = mysql_query("SELECT status FROM Game WHERE id=".DB_quote_smart($id));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return NULL;
}
function DB_set_hand_status_by_hash($hash,$status)
{
mysql_query("UPDATE Hand SET status='".$status."' WHERE hash=".DB_quote_smart($hash));
return;
}
function DB_get_hand_status_by_userid_and_gameid($uid,$gid)
{
$result = mysql_query("SELECT status FROM Hand WHERE user_id=".DB_quote_smart($uid).
" AND game_id=".DB_quote_smart($gid));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_get_gameid_by_hash($hash)
{
$result = mysql_query("SELECT game_id FROM Hand WHERE hash=".DB_quote_smart($hash));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_cancel_game($hash)
{
$gameid = DB_get_gameid_by_hash($me);
if(!$gameid)
return;
/* get the IDs of all players */
$result = mysql_query("SELECT id FROM Hand WHERE game_id=".DB_quote_smart($gameid));
while($r = mysql_fetch_array($result,MYSQL_NUM))
{
$id = $r[0];
$tmp = mysql_query("SELECT id FROM Hand_Card WHERE hand_id=".DB_quote_smart($id));
$tmp = mysql_fetch_array($tmp,MYSQL_NUM);
mysql_query("DELETE FROM Play WHERE hand_card_id=".DB_quote_smart($tmp[0]));
mysql_query("DELETE FROM Hand_Card WHERE hand_id=".DB_quote_smart($id));
mysql_query("DELETE FROM Score WHERE hand_id=".DB_quote_smart($id));
mysql_query("DELETE FROM Hand WHERE id=".DB_quote_smart($id));
}
/* delete game */
mysql_query("DELETE FROM User_Game_Prefs WHERE game_id=".DB_quote_smart($gameid));
mysql_query("DELETE FROM Trick WHERE game_id=".DB_quote_smart($gameid));
mysql_query("DELETE FROM Game WHERE id=".DB_quote_smart($gameid));
return;
}
function DB_get_hand($me)
{
$cards = array();
$handid = DB_get_handid_by_hash($me);
$result = mysql_query("SELECT card_id FROM Hand_Card WHERE hand_id=".DB_quote_smart($handid)." and played='false' ");
while($r = mysql_fetch_array($result,MYSQL_NUM))
$cards[]=$r[0];
return $cards;
}
function DB_get_cards_by_trick($id)
{
$cards = array();
$cards[0]=0; /* need to return index 1-4 */
$result = mysql_query("SELECT card_id FROM Play LEFT JOIN Hand_Card ON Hand_Card.id=Play.hand_card_id ".
"LEFT JOIN Hand ON Hand.id=Hand_Card.hand_id ".
"WHERE trick_id=".
DB_quote_smart($id)." ORDER BY position ASC");
while($r = mysql_fetch_array($result,MYSQL_NUM))
$cards[]=$r[0];
return $cards;
}
function DB_set_solo_by_hash($me,$solo)
{
mysql_query("UPDATE Hand SET solo=".DB_quote_smart($solo)." WHERE hash=".DB_quote_smart($hash));
return;
}
function DB_set_sickness_by_hash($hash,$sickness)
{
mysql_query("UPDATE Hand SET sickness=".DB_quote_smart($sickness)." WHERE hash=".DB_quote_smart($hash));
return;
}
function DB_get_current_trickid($gameid)
{
$trickid = NULL;
$sequence = NULL;
$result = mysql_query("SELECT Trick.id,MAX(Play.sequence) FROM Play ".
"LEFT JOIN Trick ON Play.trick_id=Trick.id ".
"WHERE Trick.game_id=".DB_quote_smart($gameid)." ".
"GROUP BY Trick.id");
while( $r = mysql_fetch_array($result,MYSQL_NUM) )
{
$trickid = $r[0];
$sequence = $r[1];
};
if(!$sequence || $sequence==4)
{
mysql_query("INSERT INTO Trick VALUES (NULL,NULL,NULL, ".DB_quote_smart($gameid).")");
$trickid = mysql_insert_id();
$sequence = 1;
}
else
{
$sequence++;
}
return array($trickid,$sequence);
}
function DB_get_max_trickid($gameid)
{
$result = mysql_query("SELECT MAX(id) FROM Trick WHERE game_id=".DB_quote_smart($gameid));
$r = mysql_fetch_array($result,MYSQL_NUM) ;
return ($r?$r[0]:NULL);
}
function DB_play_card($trickid,$handcardid,$sequence)
{
mysql_query("INSERT INTO Play VALUES(NULL,NULL,NULL,".DB_quote_smart($trickid).
",".DB_quote_smart($handcardid).",".DB_quote_smart($sequence).")");
$playid = mysql_insert_id();
return $playid;
}
function DB_get_all_names_by_gameid($id)
{
$names = array();
$result = mysql_query("SELECT fullname FROM Hand LEFT JOIN User ON Hand.user_id=User.id WHERE game_id=".
DB_quote_smart($id)." ORDER BY position ASC");
while($r = mysql_fetch_array($result,MYSQL_NUM))
$names[] = $r[0];
return $names;
}
function DB_get_all_userid_by_gameid($id)
{
$names = array();
$result = mysql_query("SELECT user_id FROM Hand WHERE game_id=".
DB_quote_smart($id));
while($r = mysql_fetch_array($result,MYSQL_NUM))
$names[] = $r[0];
return $names;
}
function DB_get_hash_from_game_and_pos($id,$pos)
{
$result = mysql_query("SELECT hash FROM Hand WHERE game_id=".DB_quote_smart($id)." and position=".DB_quote_smart($pos));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return "";
}
function DB_get_all_names()
{
$names = array();
$result = mysql_query("SELECT fullname FROM User");
while($r = mysql_fetch_array($result,MYSQL_NUM))
$names[] = $r[0];
return $names;
}
function DB_update_user_timestamp($userid)
{
mysql_query("UPDATE User SET last_login = CURRENT_TIMESTAMP WHERE id=".DB_quote_smart($userid));
return;
}
function DB_get_user_timestamp($userid)
{
$result = mysql_query("SELECT last_login FROM User WHERE id=".DB_quote_smart($userid));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return NULL;
}
function DB_get_user_timezone($userid)
{
$result = mysql_query("SELECT timezone FROM User WHERE id=".DB_quote_smart($userid));
$r = mysql_fetch_array($result,MYSQL_NUM);
if($r)
return $r[0];
else
return 0;
}
function DB_insert_comment($comment,$playid,$userid)
{
mysql_query("INSERT INTO Comment VALUES (NULL,NULL,NULL,$userid,$playid, ".DB_quote_smart($comment).")");
return;
}
?>
|
