PHP custom session object containing MySQL queries 1

[nickruiz]

Hello,

I am trying to add a session variable that is an object that contains all of the MySQL queries I need to use to interact with my database. Currently, I call the constructor of my object on each page.

Attached is my test file (testSession.php)

<?php
  // testSession.php
  require_once("scripts/PrayerDatabase.php");
  session_start();
  if(!(isset($_GET["revisit"])))
  {
    $db = new PrayerDatabase();
    //session_register("db");
    $_SESSION["db"] = $db;
    $rs = $db->selectUserById(1);
    if($rs)
    {
      print("Query successful");
    }
    else
      print("Query failed");
    print("<script language='javascript'>document.location='testSession.php?revisit=true';</script>");
  }
  else
  {
    phpinfo();
    $db = $_SESSION["db"];
    $rs = $db->selectUserById(1);
    if($rs)
    {
      print("Query successful");
    }
    else
      print("Query failed");
  }
?>

When this test page redirects, I receive the following output (I echo back the query from the PrayerDatabase object to make sure the query is correct):

SELECT txt_fname, txt_lname, txt_picurl, txt_about, DATE_FORMAT(dt_birthday, '%m/%d/%y'), DATE_FORMAT(dt_created, '%M %d, %Y'), DATE_FORMAT(dt_updated, '%M %d, %Y'), txt_email FROM users WHERE id_user = '1' LIMIT 1

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/.../scripts/Database.php on line 42
Query failed.

PrayerDatabase extends Database. My problem is that the SQL query works normally. However, when I try to retrieve the same PrayerDatabase object from a session variable, I cannot execute the same query. Does anyone know why this session variable did not store the MySQL link to my database? Thank you very much for your time.

Thank you,
Nick Ruiz

 

[jpadie]

the connection to mysql is closed at the end of the script. thus although you will have some "data" stored in the session variable, it will not be a *current* connection handle. i.e. to the best of my knowledge you cannot store a connection handle in a session variable and expect it to survive from page to page.

as you are already including your constructor on each page why not just include a further small file that creates the DB connection?

 

[nickruiz]

That's what I was afraid of. I figured that MySQL connection handles weren't persistent. I was hoping that I could make my PrayerDatabase object persistent, as it is currently 51KB and growing due to all of the functions it contains. Perhaps, instead of calling the constructor on each page, I could write a function within the object that will create a new MySQL connection.

My current solution works, but my goal is to make it more efficient so that the server does not have to read a 51KB script each time I load a different page. Hopefully this will kick up the page load time a little bit. It's not bad now, but I haven't tested my site with a high volume of users.

Thanks for your help. I'll send an update if that idea works (probably will if it doesn't, also ;-)).

Thanks,
Nick Ruiz

 

[jpadie]

bear in mind: php either keeps its session variables in the filesystem or a database. if the former, you will not get any speed increase by referencing a session variable (kept in the fs) than actually requiring a file (other than the minimal execution time of the script, perhaps).

if your session store is a database then i still think you are unlikely to see any performance increase but the arguments about speed of an fs vs a db depend on other factors too.

personally I use the pear::db abstraction layer for all my database stuff. this results in objects with more properties than i care to count and i have not noticed any particular speed penalty. i guess because the delta between the time taken to process even a very large script, interact with a database a couple of dozen times is so small compared with the time taken to deliver even a small page of html down a thin pipe to a pc. i.e. we coders may get het up about processor time but sometimes it's a good idea to take a step back and see whether the execution time is just a rounding error in the overall latency delivered by the internet and browser rendering speeds.

and if it is a big deal: i often find that shoving a bit more ram in the box is a more cost effective solution than a day of my time trying to optimise the code!

 

[nickruiz]

Thanks for your help. It turns out that each page would still have to read the database script file to interpret the Database session variable. Also, it required that the object was declared before session_start() was invoked. Too much of a hassle for a result that would've likely made everything slower (e.g. both storing a large session variable and interpreting a script, instead of just interpreting the script). What a waste of a few hours.