SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Dave Morton's Avatar
    Join Date
    Sep 2003
    Location
    Carson City, NV
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multi-faceted problem with implementing PEAR DB functions(methods?)

    I'm trying to design an AJAX driven chat room, and I'm having some difficulty with some of the back-end code, and I'm looking for some suggestions/pointers, since I'm completely new to using the PEAR DB class. This is going to be a somewhat lengthy explanation, I fear, so please bear with me.

    Ok, first off, this chat room is somewhat arranged like a house, in that there are several "rooms" available to the user. Aside from regular, general purpose rooms (e.g. Living room, kitchen, den, etc), users will be able to enter a "closet", and chat one on one with another user, no matter where in the "house" they are. So far, so good.

    In order to keep overhead on the DB low (It's a mySQL DB), I'm not only pruning messages that are over a certain age threshold, but I'm also periodically dropping room tables from the DB if they're empty. This especially applies to the PM closets.

    The user, during the login process, is given a standard list of rooms to choose from, with the Living Room as the default. What I want to do (and where I'm having the problem) is finding a way to create the room if it doesn't yet exist. I have a function (listed below) called makeRoom, that creates the new room perfectly. My problem lies in finding the correct way to test for the need to create one. My current login() function, makeRoom() and the various functions I currently use are listed below:

    PHP Code:
      function login() {
    /*
     This function logs the user in, and assigns the values name, avatar, IP, room and isAdmin to session variables,
     and to the data divs on the target page, through ajaxPostEval
    */
        
    $formData parse_form();
        foreach (
    $formData  as $key => $value) {
          $
    $key htmlentities(mysql_escape_string($value));               // split the POST variable into constituent parts
          
    $_SESSION[$key] = ($key != "avatar") ? $value "";              // Save them to the session, unless it's the avatar, which is set later
        
    }
        
    $isAdmin isAdmin($name$pass);                                  // See if we're dealing with an admin
        
    $_SESSION['avatar'] = ($isAdmin !== false) ? $isAdmin $avatar;   // All admins have special avatars, so if the user's an admin, assign it
        
    $out = <<<endScript
    document.getElementByID('uName').innerHTML   = '$name'
    document.getElementByID("uAvatar").innerHTML = "
    $avatar";
    document.getElementByID("uRoom").innerHTML   = "
    $room";
    document.getElementByID("lStat").innerHTML   = "true";
    showDiv("chatForm");
    updateDivs();
    endScript;

        
    $warn "alert(\"You cannot use foul language for your name. Shame on you!\")" ;
        
    $out = (badWords($name)) ? die($warn) : $out;                           // if the user used foul language in his name, then warn them
        
    $_SESSION['lStat'] = "true";
        
    $message " has just entered the $room";
        
    $IP $_SERVER['REMOTE_ADDR'];
        
    addLine($name$avatar$room$message$IP0);
        
    updateUser($name$avatar$room);
        return 
    $out;                                                       // spit out the results and leave
      
    }
    // end function  login

      
    function addLine ($name$avatar$room$msg) {
    /*
     This function creates the SQL string that inserts the message into the correct table of the database.
     it takes the variables $name, $avatar, $room and $msg (which should all be self explanatory), and creates
     the variables $curTime and $IP, to round out the necessary data.
    */
        
    $curTime time();                                                 // Gets the current time
        
    $IP $_SERVER['REMOTE_ADDR'];                                     // Obtains user's IP address
        
    $sql "insert into `$room` (`user_name`, `avatar`, `message`, `IP`, `time`) values ('$name', '$avatar', '$msg', '$IP', '$curTime');";
        
    updateDB($sql);
        
    updateUser($name$avatar$room);
      }
    // end function addLine

      
    function updateDB($sql) {
    /*
     This is the home for any task that involves writing to the DB, or for generic DB queries that don't require a response.
    */
        
    global $dsn$lastFunction;
        
    $lastFunction .= "_updateDB";
        
    $dbh DB::connect($dsn);                                     // Open a channel, Mr. Worf!
        
    $dbh->setErrorHandling (PEAR_ERROR_CALLBACK'handleErrors'); // Set up some error handling
        
    $dbh->setFetchMode (DB_FETCHMODE_ASSOC);                      // Set the output to something usable.
        
    $result $dbh->query($sql);                                  // Perform the desired DB action
        
    $updatedRows $dbh->affectedRows() or false;                 // If the affected rows is zero, send boolean false.
        
    $out $updatedRows or $result;                               // Decide what to return.
        
    $dbh->disconnect();                                           // Unplug, and (un)play?
        
    return $out;                                                  // Send 'er out, and shut 'er down.
      
    }
    // end function  updateDB

      
    function handleErrors($error) {
    /*
     This function handles any errors that may occur from any of the above DB accessing functions.
     I'll update this error routine as needed, during the development of this file.
    */
    //   [nativecode=1146 ** Table 'pchat.family room' doesn't exist]
        
    global $lastFunction;
        
    $errorMessage =  $error->getMessage();
        
    $errorReport  "An error occurred while trying to execute a DB function in $lastFunction.\r\n";
        
    $errorReport .= "Session Vars:\r\n";
        
    $errorReport .= "Error message: $errorMessage\r\n";
        
    $errorReport .= "A more detailed error description: " $error->getDebugInfo() . "\r\n";
        foreach (
    $_SESSION as $key => $value) {
          
    $errorReport .= "$key = $value\r\n";
        }
        
    $errorReport .= "End Session Vars:\r\n\r\n";
    // If it's a non-existant room, just make it, and re-run the query
        
    if (strpos($errorMessage,"nativecode=1146") !== false) {
          
    $room $_SESSION['room'];
          
    makeRoom($room);
          
    updateDB("$sql;");
        }
        else {
          
    $fh fopen("db_errors.txt""a");
          
    $x fwrite($fh,$errorReportstrlen($errorReport));
          
    fclose($fh);
        }
        return;
      }
    // end function  handleErrors

      
    function updateUser($name$avatar$room) {
        global 
    $lastFunction;
        
    $lastFunction "updateUser";
        
    $curTime time();
        
    $IP $_SERVER['REMOTE_ADDR'];
        
    $sql "delete from `online_users` where `user_name` = '$name' order by `user_name`";
        
    $result updateDB($sql);
        
    $rowCount count($result);
        
    $sql "insert into `online_users` (`user_name`, `avatar`, `room`, `IP`, `lastpost`, `lastPing`, `hasPM`) values ('$name','$avatar','$room','$IP','$curTime','$curTime',0);";
        
    $result updateDB($sql);
      }

      function 
    makeRoom($room) {
        global 
    $lastFunction;
        
    $lastFunction "makeRoom";
        
    $sql "CREATE TABLE `pchat`.`$room` (`user_name` TEXT NOT NULL, `avatar` TEXT NOT NULL, `message` DATE NOT NULL, `IP` TEXT NOT NULL, `time` TEXT NOT NULL)TYPE=MyISAM COMMENT='Messages within $room';";
        
    $result updateDB($sql);
      } 
    The full script (minus certain sensitive data, of course) can be seen at http://www.geekcavecreations.com/pChat/pChat.funcs.phps. Unfortunately, my host doesn't support script highiighting for phps files. I know I probably posted a lot more code than absolutely necessary, but I wanted to make sure I got the bases covered, without posting the whole thing.

    What I'm exactly looking for is a way to check the existence of a table before trying to write to it, and creating said table if need be, and THEN entering the data.

    A subsidiary question is: Which is better? dropping long unused tables (i.e. the main ones) to save on overhead? Or would I be better off just pruning the old messages, and occasionally running an optimize on the tables? Either way, I still need an answer to the first question. Thanks for both your time, and for your patience.
    Making a difference, one little psychotic episode at a time
    Geek Cave Creations
    Beta testers needed for pChat
    Dave's Gallery

  2. #2
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Embedding javascript in PHP function is begging for trouble...

    Creating a new table isn't necessary. A simple table named Chatrooms can store unique identifies for each existing chat room. Deleting a chat room is a simple delete query.

    Code:
    CREATE TABLE IF NOT EXISTS...
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  3. #3
    SitePoint Evangelist Dave Morton's Avatar
    Join Date
    Sep 2003
    Location
    Carson City, NV
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by imaginethis View Post
    Embedding javascript in PHP function is begging for trouble...
    Bearing in mind that all the ajax HTTP requests are POST, rather than GET, and that the parse_form function that transforms the POST parameters uses the usual security precautions, I'm wondering just what sort of trouble you may be referring to. I try to cover all the security bases religiously, but there's always a chance that I've missed something, and I'm always willing and eager to learn.

    As to the "CREATE TABLE IF NOT EXISTS..." part, all I can say is "OOPS!" That should work.
    Making a difference, one little psychotic episode at a time
    Geek Cave Creations
    Beta testers needed for pChat
    Dave's Gallery

  4. #4
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP and Javascript are both C style languages. One late night of programming and and a little mixing of curly braces and you can find yourself in a logical labyrinth. There is a better way, which separates PHP and Javascript which reduces the possibility for error.
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  5. #5
    SitePoint Evangelist Dave Morton's Avatar
    Join Date
    Sep 2003
    Location
    Carson City, NV
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, like tonight/today. (pulled an all-nighter, working out how to get this nut to crack)

    I'm not as worried about those types of mistakes, since my editor not only does bracket matching, but also pre-parses the code, and warns me about any PHP errors that may have occurred, but your point is well taken. The only JS code that's passed back to the front-end is what I've posted, and all it does is fill hidden divs with certain data, such as any new room that the user wants to switch to. I can possibly do without that bit of code, but that presents another nut to crack.
    Making a difference, one little psychotic episode at a time
    Geek Cave Creations
    Beta testers needed for pChat
    Dave's Gallery


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •