Converting PostgreSQL code to MySQL code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cashflowtips
Forum Newbie
Posts: 22
Joined: Tue Jul 31, 2007 11:06 pm

Converting PostgreSQL code to MySQL code

Post by cashflowtips »

can anybody here help me to solve this problem. i know lots of u might wondering why do i want to convert to postgreSQL but i really want to know how can i do that. below is my code, if anyone know how, please help me. thanks.

Code: Select all

<?php
  class UserSession {
    private $php_session_id;
    private $native_session_id;
    private $dbhandle;
    private $logged_in;
    private $user_id;
    private $session_timeout = 600;      # 10 minute inactivity timeout
    private $session_lifespan = 3600;    # 1 hour session duration
   
    public function __construct() {
      # Connect to database
      $this->dbhandle = pg_connect("host=db dbname=prophp5 user=ed")  or die ("PostgreSQL error: --> " . pg_last_error($this->dbhandle));
      # Set up the handler
      session_set_save_handler(
          array(&$this, '_session_open_method'),
          array(&$this, '_session_close_method'),
          array(&$this, '_session_read_method'),
          array(&$this, '_session_write_method'),
          array(&$this, '_session_destroy_method'),
          array(&$this, '_session_gc_method')
      );
      # Check the cookie passed - if one is - if it looks wrong we'll scrub it right away     
      $strUserAgent = $GLOBALS["HTTP_USER_AGENT"];
      if ($_COOKIE["PHPSESSID"]) {
       # Security and age check
       $this->php_session_id = $_COOKIE["PHPSESSID"];
       $stmt = "select id from \"user_session\" where ascii_session_id = '" . $this->php_session_id . "' AND ((now() - created) < ' " . $this->session_lifespan . " seconds') AND user_agent='" . $strUserAgent . "' AND ((now() - last_impression) <= '".$this->session_timeout." seconds' OR last_impression IS NULL)";
       $result = pg_query($stmt);
       if (pg_num_rows($result)==0) {
         # Set failed flag
          $failed = 1;
         # Delete from database - we do garbage cleanup at the same time
         $result = pg_query("DELETE FROM \"user_session\" WHERE (ascii_session_id = '". $this->php_session_id . "') OR (now() - created) > $maxlifetime)");
         # Clean up stray session variables
         $result = pg_query("DELETE FROM \"session_variable\" WHERE session_id NOT IN (SELECT id FROM \"user_session\")");
         # Get rid of this one... this will force PHP to give us another
         unset($_COOKIE["PHPSESSID"]);
       }; 
      };
      # Set the life time for the cookie
      session_set_cookie_params($this->session_lifespan);
      # Call the session_start method to get things started
      session_start();
    }
   
    public function Impress() {
      if ($this->native_session_id) {
        $result = pg_query("UPDATE \"user_session\" SET last_impression = now() WHERE id = " . $this->native_session_id);
      };
    }
   
    public function IsLoggedIn() {
      return($this->logged_in);
    }
   
    public function GetUserID() {
      if ($this->logged_in) {
        return($this->user_id);
      } else {
        return(false);
      };
    }
   
    public function GetUserObject() {
      if ($this->logged_in) {
        if (class_exists("user")) {
          $objUser = new User($this->user_id);
          return($objUser);
        } else {
          return(false);
        };
      };
    }
   
    public function GetSessionIdentifier() {
      return($this->php_session_id);
    }
   
    public function Login($strUsername, $strPlainPassword) {
      $strMD5Password = md5($strPlainPassword);
      $stmt = "select id FROM \"user\" WHERE username = '$strUsername' AND md5_pw = '$strMD5Password'";
      $result = pg_query($stmt);
      if (pg_num_rows($result)>0) {
        $row = pg_fetch_array($result);
        $this->user_id = $row["id"];
        $this->logged_in = true;
        $result = pg_query("UPDATE \"user_session\" SET logged_in = true, user_id = " . $this->user_id . " WHERE id = " . $this->native_session_id);
        return(true);
      } else {
        return(false);
      };
    } 
   
    public function LogOut() {
      if ($this->logged_in == true) {
        $result = pg_query("UPDATE \"user_session\" SET logged_in = false, user_id = 0 WHERE id = " . $this->native_session_id);
        $this->logged_in = false;
        $this->user_id = 0;
        return(true);
      } else {
        return(false);
      };
    }
   
    public function __get($nm) {
      $result = pg_query("SELECT variable_value FROM session_variable WHERE session_id = " . $this->native_session_id . " AND variable_name = '" . $nm . "'");
      if (pg_num_rows($result)>0) {
        $row = pg_fetch_array($result);
        return(unserialize($row["variable_value"]));
      } else {
        return(false);
      };
    }
   

    public function __set($nm, $val) {
      $strSer = serialize($val);
      $stmt = "INSERT INTO session_variable(session_id, variable_name, variable_value) VALUES(" . $this->native_session_id . ", '$nm', '$strSer')";
      $result = pg_query($stmt);
    }
   
    private function _session_open_method($save_path, $session_name) {
      # Do nothing
      return(true);
    }
   
    private function _session_close_method() {
      pg_close($this->dbhandle);
      return(true);
    }
   
    private function _session_read_method($id) {
      # We use this to determine whether or not our session actually exists.
      $strUserAgent = $GLOBALS["HTTP_USER_AGENT"];
      $this->php_session_id = $id;
      # Set failed flag to 1 for now
      $failed = 1;
      # See if this exists in the database or not.
      $result = pg_query("select id, logged_in, user_id from \"user_session\" where ascii_session_id = '$id'");
      if (pg_num_rows($result)>0) {
       $row = pg_fetch_array($result);
       $this->native_session_id = $row["id"];
       if ($row["logged_in"]=="t") {
         $this->logged_in = true;
         $this->user_id = $row["user_id"];
       } else {
         $this->logged_in = false;
       };
      } else {
        $this->logged_in = false;
        # We need to create an entry in the database
        $result = pg_query("INSERT INTO user_session(ascii_session_id, logged_in, user_id, created, user_agent) VALUES ('$id','f',0,now(),'$strUserAgent')");
        # Now get the true ID
        $result = pg_query("select id from \"user_session\" where ascii_session_id = '$id'");
        $row = pg_fetch_array($result);
        $this->native_session_id = $row["id"];
      };
      # Just return empty string
      return("");
    }
   
    private function _session_write_method($id, $sess_data) {
      return(true);
    }
   
    private function _session_destroy_method($id) {
      $result = pg_query("DELETE FROM \"user_session\" WHERE ascii_session_id = '$id'");
      return($result);
    }
   
    private function _session_gc_method($maxlifetime) {
      return(true);
    }
   
   
  }
?>
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

So your saying you wrote this code but you don't know how to write MySQL queries?
cashflowtips
Forum Newbie
Posts: 22
Joined: Tue Jul 31, 2007 11:06 pm

Post by cashflowtips »

astions wrote:So your saying you wrote this code but you don't know how to write MySQL queries?
this isn't my code... i found it from the book that i bought... i dont want to install postgreSQL... i know a lil bit of MySQL code n i did some changes on this code but still can't get the actual output and i dont know what is the problem. so i post to this forum the original code... can u help me?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Most of the queries will work as is. You'll need to replace the functions that connect to the database and execute the queries. Also be sure to filter and prepare the variables used in the queries. ie mysql_real_escape_string()

http://www.php.net/manual/en/ref.mysql.php
cashflowtips
Forum Newbie
Posts: 22
Joined: Tue Jul 31, 2007 11:06 pm

Post by cashflowtips »

im not pretty sure how to do it but this is how my current code look like and it doesn't work...

Code: Select all

<?php
  class UserSession {
    private $php_session_id;
    private $native_session_id;
    private $dbhandle;
    private $logged_in;
    private $user_id;
    private $session_timeout = 600;      # 10 minute inactivity timeout
    private $session_lifespan = 3600;    # 1 hour session duration
    
    public function __construct() {
      # Connect to database
      $this->dbhandle = mysql_connect("localhost","root","")
      if (!$this->dbhandle)
      {
         die('Could not connect: ' . mysql_error());
      }
      
      mysql_select_db("prophp5", $this->dbhandle);

      session_set_save_handler(
          array(&$this, '_session_open_method'), 
          array(&$this, '_session_close_method'), 
          array(&$this, '_session_read_method'),
          array(&$this, '_session_write_method'), 
          array(&$this, '_session_destroy_method'), 
          array(&$this, '_session_gc_method')
      );
      # Check the cookie passed - if one is - if it looks wrong we'll scrub it right away      
      $strUserAgent = $GLOBALS["HTTP_USER_AGENT"];
      if ($_COOKIE["PHPSESSID"]) {
       # Security and age check
       $this->php_session_id = $_COOKIE["PHPSESSID"];
       $stmt = "SELECT id FROM \"user_session\" WHERE ascii_session_id = '" . $this->php_session_id . "' AND ((now() - created) < ' " . $this->session_lifespan . " seconds') AND user_agent='" . $strUserAgent . "' AND ((now() - last_impression) <= '".$this->session_timeout." seconds' OR last_impression IS NULL)";
       $result = mysql_query($stmt);
       if (mysql_num_rows($result)==0) {
         # Set failed flag
          $failed = 1;
         # Delete from database - we do garbage cleanup at the same time
         $result = mysql_query("DELETE FROM \"user_session\" WHERE (ascii_session_id = '". $this->php_session_id . "') OR (now() - created) > $maxlifetime)");
         # Clean up stray session variables
         $result = mysql_query("DELETE FROM \"session_variable\" WHERE session_id NOT IN (SELECT id FROM \"user_session\")");
         # Get rid of this one... this will force PHP to give us another
         unset($_COOKIE["PHPSESSID"]);
       };  
      };
      # Set the life time for the cookie
      session_set_cookie_params($this->session_lifespan);
      # Call the session_start method to get things started
      session_start();
    }
    
    public function Impress() {
      if ($this->native_session_id) {
        $result = mysql_query("UPDATE \"user_session\" SET last_impression = now() WHERE id = " . $this->native_session_id);
      };
    }
    
    public function IsLoggedIn() {
      return($this->logged_in);
    }
    
    public function GetUserID() {
      if ($this->logged_in) {
        return($this->user_id);
      } else {
        return(false);
      };
    }
    
    public function GetUserObject() {
      if ($this->logged_in) {
        if (class_exists("user")) {
          $objUser = new User($this->user_id);
          return($objUser);
        } else {
          return(false);
        };
      };
    }
    
    public function GetSessionIdentifier() {
      return($this->php_session_id);
    }
    
    public function Login($strUsername, $strPlainPassword) {
      $strMD5Password = md5($strPlainPassword);
      $stmt = "SELECT id FROM \"user\" WHERE username = '$strUsername' AND md5_pw = '$strMD5Password'";
      $result = mysql_query($stmt);
      if (mysql_num_rows($result)>0) {
        $row = mysql_fetch_array($result);
        $this->user_id = $row["id"];
        $this->logged_in = true;
        $result = mysql_query("UPDATE \"user_session\" SET logged_in = true, user_id = " . $this->user_id . " WHERE id = " . $this->native_session_id);
        return(true);
      } else {
        return(false);
      };
    }  
    
    public function LogOut() {
      if ($this->logged_in == true) {
        $result = mysql_query("UPDATE \"user_session\" SET logged_in = false, user_id = 0 WHERE id = " . $this->native_session_id);
        $this->logged_in = false;
        $this->user_id = 0;
        return(true);
      } else {
        return(false);
      };
    }
    
    public function __get($nm) {
      $result = mysql_query("SELECT variable_value FROM session_variable WHERE session_id = " . $this->native_session_id . " AND variable_name = '" . $nm . "'");
      if (mysql_num_rows($result)>0) {
        $row = mysql_fetch_array($result);
        return(unserialize($row["variable_value"]));
      } else {
        return(false);
      };
    }
    

    public function __set($nm, $val) {
      $strSer = serialize($val);
      $stmt = "INSERT INTO session_variable(session_id, variable_name, variable_value) VALUES(" . $this->native_session_id . ", '$nm', '$strSer')";
      $result = mysql_query($stmt);
    }
    
    private function _session_open_method($save_path, $session_name) {
      # Do nothing
      return(true);
    }
    
    private function _session_close_method() {
      mysql_close($this->dbhandle);
      return(true);
    }
    
    private function _session_read_method($id) {
      # We use this to determine whether or not our session actually exists.
      $strUserAgent = $GLOBALS["HTTP_USER_AGENT"];
      $this->php_session_id = $id;
      # Set failed flag to 1 for now
      $failed = 1;
      # See if this exists in the database or not.
      $result = mysql_query("select id, logged_in, user_id from \"user_session\" where ascii_session_id = '$id'");
      if (mysql_num_rows($result)>0) {
       $row = mysql_fetch_array($result);
       $this->native_session_id = $row["id"];
       if ($row["logged_in"]=="t") {
         $this->logged_in = true;
         $this->user_id = $row["user_id"];
       } else {
         $this->logged_in = false;
       };
      } else {
        $this->logged_in = false;
        # We need to create an entry in the database
        $result = mysql_query("INSERT INTO user_session(ascii_session_id, logged_in, user_id, created, user_agent) VALUES ('$id','f',0,now(),'$strUserAgent')");
        # Now get the true ID
        $result = mysql_query("SELECT id FROM \"user_session\" WHERE ascii_session_id = '$id'");
        $row = mysql_fetch_array($result);
        $this->native_session_id = $row["id"];
      };
      # Just return empty string
      return("");
    }
    
    private function _session_write_method($id, $sess_data) {
      return(true);
    }
    
    private function _session_destroy_method($id) {
      $result = mysql_query("DELETE FROM \"user_session\" WHERE ascii_session_id = '$id'");
      return($result);
    }
    
    private function _session_gc_method($maxlifetime) {
      return(true);
    }


  }
?>
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Good job! What errors are you getting? What is failing?
cashflowtips
Forum Newbie
Posts: 22
Joined: Tue Jul 31, 2007 11:06 pm

Post by cashflowtips »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[quote="astions"]Good job!  What errors are you getting?  What is failing?[/quote]

it just showing a blank screen... i don't know what is the problem, thats [s]y[/s] [size=150][color=green]why[/color][/size] i post it here. if any error show on the screen, then it more easier for me to trace... here is the original database which using the postgresql database.

[syntax="sql"]
CREATE TABLE user_session (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "ascii_session_id" character varying(32),
  "logged_in" bool,
  "user_id" int4,
  "last_impression" timestamp,
  "created" timestamp,	
  "user_agent" character varying(256)
);

CREATE TABLE "user" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "username" character varying(32),
  "md5_pw" character varying(32),
  "first_name" character varying(64),	
  "last_name" character varying(64)
);

CREATE TABLE "session_variable" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "session_id" int4,
  "variable_name" character varying(64),
  "variable_value" text
);
i changed all the "id"s from SERIAL PRIMARY KEY NOT NULL into datatype (integer) PRIMARY KEY NOT NULL AUTO-INCREMENT. i think that should be ok to run the script but it didn't happen. do you see anything wrong here?


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

[quote="[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1"][b]11.[/b] Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.

Some examples of what not to do are ne1, any1 (anyone); u (you); ur (your or you're); 2 (to too); prolly (probably); afaik (as far as I know); etc.[/quote]
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Your missing a semicolon on line 13. Please turn on error reporting so you can see errors.
cashflowtips
Forum Newbie
Posts: 22
Joined: Tue Jul 31, 2007 11:06 pm

Post by cashflowtips »

astions wrote:Your missing a semicolon on line 13. Please turn on error reporting so you can see errors.
i fixed the semicolon, can u tell me how to turn on error reporting?

i put this one inside my code but nothing appear...

error_reporting(E_ALL);
ini_set('display_errors', True);
cashflowtips
Forum Newbie
Posts: 22
Joined: Tue Jul 31, 2007 11:06 pm

Post by cashflowtips »

anyone know what is the problem with the code?
Post Reply