diff options
Diffstat (limited to 'db.php')
| -rw-r--r-- | db.php | 209 |
1 files changed, 137 insertions, 72 deletions
@@ -1,7 +1,7 @@ <?php /* - * open database + * open database */ function DB_open() @@ -10,11 +10,11 @@ function DB_open() $DB = @mysql_connect($DB_host,$DB_user, $DB_password); if ( $DB ) { - mysql_select_db($DB_database) or die('Could not select database'); + mysql_select_db($DB_database) or die('Could not select database'); } else return -1; - + return 0; } @@ -55,7 +55,7 @@ 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 @@ -76,7 +76,7 @@ function DB_get_email_by_userid($id) { $result = mysql_query("SELECT email FROM User WHERE id=".DB_quote_smart($id).""); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -87,11 +87,11 @@ function DB_get_email_by_pos_and_gameid($pos,$gameid) { $result = mysql_query("SELECT email FROM User ". "LEFT JOIN Hand ON User.id=Hand.user_id ". - "LEFT JOIN Game ON Game.id=Hand.game_id ". + "LEFT JOIN Game ON Game.id=Hand.game_id ". "WHERE Game.id=".DB_quote_smart($gameid)." ". "AND Hand.position=".DB_quote_smart($pos).""); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -102,7 +102,7 @@ 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 @@ -113,7 +113,7 @@ 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 @@ -123,7 +123,7 @@ 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 @@ -133,7 +133,7 @@ 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 @@ -143,7 +143,7 @@ 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); - + /* test if a recovery password has been set */ if(!$r) { @@ -174,14 +174,14 @@ function DB_check_recovery_passwords($password,$email) return 1; 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 @@ -194,7 +194,7 @@ function DB_get_handid_by_gameid_and_position($gameid,$pos) DB_quote_smart($gameid)." AND position=". DB_quote_smart($pos)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -206,7 +206,7 @@ function DB_get_userid_by_gameid_and_position($gameid,$pos) DB_quote_smart($gameid)." AND position=". DB_quote_smart($pos)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -220,7 +220,7 @@ function DB_get_handid_by_gameid_and_userid($gameid,$userid) DB_quote_smart($gameid)." AND user_id=". DB_quote_smart($userid)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -231,7 +231,7 @@ 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 @@ -242,7 +242,7 @@ 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 @@ -253,7 +253,7 @@ 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 @@ -264,7 +264,7 @@ function DB_get_name_by_email($email) { $result = mysql_query("SELECT fullname FROM User WHERE email=".DB_quote_smart($email)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -275,7 +275,7 @@ 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 @@ -286,7 +286,7 @@ 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 @@ -308,7 +308,7 @@ function DB_get_sickness_by_gameid($id) { $result = mysql_query("SELECT sickness FROM Game WHERE id=".DB_quote_smart($id)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -319,7 +319,7 @@ 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 @@ -337,7 +337,7 @@ 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 @@ -349,7 +349,7 @@ function DB_get_sickness_by_userid_and_gameid($uid,$gid) $result = mysql_query("SELECT sickness 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 @@ -361,7 +361,7 @@ function DB_get_sickness_by_pos_and_gameid($pos,$gid) $result = mysql_query("SELECT sickness FROM Hand WHERE position=".DB_quote_smart($pos). " AND game_id=".DB_quote_smart($gid)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -372,7 +372,7 @@ 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 @@ -391,22 +391,21 @@ function DB_cancel_game($hash) 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; } @@ -440,7 +439,7 @@ function DB_get_cards_by_trick($id) { $cards = array(); $i = 1; - + $result = mysql_query("SELECT card_id,position 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=". @@ -489,7 +488,7 @@ function DB_get_current_trickid($gameid) $sequence = $r[1]; $number++; }; - + if(!$sequence || $sequence==4) { mysql_query("INSERT INTO Trick VALUES (NULL,NULL,NULL, ".DB_quote_smart($gameid).",NULL)"); @@ -509,7 +508,7 @@ 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); } @@ -517,7 +516,7 @@ 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; } @@ -525,7 +524,7 @@ function DB_play_card($trickid,$handcardid,$sequence) 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)) @@ -537,7 +536,7 @@ function DB_get_all_names_by_gameid($id) 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)." ORDER BY position "); while($r = mysql_fetch_array($result,MYSQL_NUM)) @@ -550,7 +549,7 @@ 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 @@ -561,7 +560,7 @@ function DB_get_hash_from_gameid_and_userid($id,$user) { $result = mysql_query("SELECT hash FROM Hand WHERE game_id=".DB_quote_smart($id)." and user_id=".DB_quote_smart($user)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -596,7 +595,7 @@ 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 @@ -606,7 +605,7 @@ 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 @@ -624,7 +623,7 @@ function DB_get_gametype_by_gameid($id) { $result = mysql_query("SELECT type FROM Game WHERE id=".DB_quote_smart($id)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0].""; else @@ -641,7 +640,7 @@ function DB_get_solo_by_gameid($id) { $result = mysql_query("SELECT solo FROM Game WHERE id=".DB_quote_smart($id)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0].""; else @@ -653,7 +652,7 @@ function DB_get_startplayer_by_gameid($id) { $result = mysql_query("SELECT startplayer FROM Game WHERE id=".DB_quote_smart($id)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -670,7 +669,7 @@ function DB_get_player_by_gameid($id) { $result = mysql_query("SELECT player FROM Game WHERE id=".DB_quote_smart($id)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -688,7 +687,7 @@ function DB_get_ruleset_by_gameid($id) { $result = mysql_query("SELECT ruleset FROM Game WHERE id=".DB_quote_smart($id)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -699,7 +698,7 @@ function DB_get_session_by_gameid($id) { $result = mysql_query("SELECT session FROM Game WHERE id=".DB_quote_smart($id)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -710,7 +709,7 @@ function DB_get_max_session() { $result = mysql_query("SELECT MAX(session) FROM Game"); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else @@ -734,14 +733,14 @@ function DB_get_hashes_by_session($session,$user) function DB_get_ruleset($dullen,$schweinchen,$call) { $r = array(); - + $result = mysql_query("SELECT id FROM Rulesets WHERE". " dullen=".DB_quote_smart($dullen)." AND ". " call=".DB_quote_smart($call)." AND ". " schweinchen=".DB_quote_smart($schweinchen)); if($result) $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; /* found ruleset */ else @@ -763,12 +762,26 @@ function DB_get_party_by_hash($hash) { $result = mysql_query("SELECT party FROM Hand WHERE hash=".DB_quote_smart($hash)); $r = mysql_fetch_array($result,MYSQL_NUM); - + if($r) return $r[0]; else return NULL; } + +function DB_get_party_by_gameid_and_userid($gameid,$userid) +{ + $result = mysql_query("SELECT party FROM Hand". + " WHERE game_id=".DB_quote_smart($gameid). + " AND user_id=".DB_quote_smart($userid)); + $r = mysql_fetch_array($result,MYSQL_NUM); + + if($r) + return $r[0]; + else + return NULL; +} + function DB_set_party_by_hash($hash,$party) { mysql_query("UPDATE Hand SET party=".DB_quote_smart($party)." WHERE hash=".DB_quote_smart($hash)); @@ -805,7 +818,7 @@ function DB_get_unused_randomnumbers($userstr) " GROUP BY randomnumbers". " )"); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]; @@ -819,7 +832,7 @@ function DB_get_number_of_passwords_recovery($user) " WHERE user_id=$user ". " AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= create_date". " GROUP BY user_id " ); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]; @@ -831,14 +844,14 @@ function DB_set_recovery_password($user,$newpw) { mysql_query("INSERT INTO Recovery VALUES(NULL,".DB_quote_smart($user). ",".DB_quote_smart($newpw).",NULL)"); - + return; } function DB_get_card_name($card) { $queryresult = mysql_query("SELECT strength,suite FROM Card WHERE id='$card'"); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]." of ".$r[1]; @@ -849,42 +862,42 @@ function DB_get_card_name($card) function DB_get_current_playid($gameid) { $trick = DB_get_max_trickid($gameid); - + if(!$trick) return NULL; - + $queryresult = mysql_query("SELECT id FROM Play WHERE trick_id='$trick' ORDER BY create_date DESC LIMIT 1"); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]; - + return ""; } function DB_get_call_by_hash($hash) { $queryresult = mysql_query("SELECT point_call FROM Hand WHERE hash='$hash'"); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]; - + return NULL; } function DB_get_partner_call_by_hash($hash) { $partner = DB_get_partner_hash_by_hash($hash); - + if($partner) { $queryresult = mysql_query("SELECT point_call FROM Hand WHERE hash='$partner'"); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]; } - + return NULL; } @@ -892,9 +905,9 @@ function DB_get_partner_hash_by_hash($hash) { $gameid = DB_get_gameid_by_hash($hash); $party = DB_get_party_by_hash($hash); - + $queryresult = mysql_query("SELECT hash FROM Hand WHERE game_id='$gameid' AND party='$party' AND hash<>'$hash'"); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]; @@ -905,14 +918,14 @@ function DB_get_partner_hash_by_hash($hash) function DB_format_gameid($gameid) { $session = DB_get_session_by_gameid($gameid); - + /* get number of game */ $result = mysql_query("SELECT COUNT(*),create_date FROM Game". " WHERE session='$session' ". " AND TIMEDIFF(create_date, (SELECT create_date FROM Game WHERE id='$gameid'))<=0 ". " GROUP by session"); $r = mysql_fetch_array($result,MYSQL_NUM); - + return $session.".".$r[0]; } @@ -923,7 +936,7 @@ function DB_get_reminder($user,$gameid) " AND game_id=$gameid ". " AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= create_date". " GROUP BY user_id " ); - + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) return $r[0]; @@ -952,4 +965,56 @@ function DB_is_session_active($session) return -1; } +function DB_get_score_by_gameid($gameid) +{ + /* returns the points of a game from the point of the re parth (<0 if they lost) */ + $queryresult = mysql_query("SELECT COUNT(*),party FROM Score ". + " WHERE game_id=$gameid ". + " GROUP BY party "); + + $re = 0; + $contra = 0; + + while($r = mysql_fetch_array($queryresult,MYSQL_NUM) ) + { + if($r[1] == "re") + $re += $r[0]; + else if ($r[1] == "contra") + $contra += $r[0]; + }; + + return ($re - $contra); +} + +function DB_get_gameids_of_finished_games_by_session($session) +{ + $ids = array (); + + $queryresult = mysql_query("SELECT id FROM Game ". + " WHERE session=$session ". + " AND status='gameover' ". + " ORDER BY create_date ASC"); + + $i=0; + while($r = mysql_fetch_array($queryresult,MYSQL_NUM) ) + { + $ids[$i] = $r[0]; + $i++; + } + + return $ids; +} + +function DB_get_card_value_by_cardid($id) +{ + $queryresult = mysql_query("SELECT points FROM Card ". + " WHERE id=$id "); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]; + else + return NULL; +} + ?>
\ No newline at end of file |