Hi; I have been doing this stuff for years now, but largely "by the seat of my pants" (with much help from y'all here).

Anyway, as I am currently retired (and do not have a crush of "do this NOW by whatever means necessary" type requests/orders) I have the time to step back and wonder if I do this stuff (e.g. mysqli queries) as correctly as I ought to, or if I am being "sloppy" with respect to the overall process.

For example: For select statements I typically create a link statement, a query statement, a result statement and (often) have the resulting data dumped into a $_SESSION variable so that I can quickly display (or redisplay) the retrieved facts on my pages as the visitor moves around on the website.

The part that I am wondering about is stuff like killing or closing links and threads and stuff like that that I have seen in other people's code. I don't know much about that or how necessary it might be. If I _don't do things like that is it a burden on the server? Will my scripts tend to run less well (e.g if/when I have multiple query processes going on to serve the site visitor's interactions on my site.

So that's the question, if I am doing simple select queries against a database is there something I really OUGHT to be in the habit of doing in a script beyond these three statements?

$link=mysqli_connect("localhost", "uname", "pword", "dbname") or die (echo "Could not connect : " . mysqli_error($link));
$sql="SELECT * FROM table ORDER BY id DESC";
$result=mysqli_query($link, $sql);
while ($line=mysqli_fetch_array($result, MYSQLI_ASSOC)) {
$SESSION[blah][]=$line;
}
...from which I go on my merry way, displaying results contained in the array of data $SESSION[blah].

Am I leaving some "proper" parts of the process out that a more professional programmer would be sure to do in order to prevent potential server problems, security stuff, etc.?

    Dumping query results into an array can have scalability issues. Unless you know a query should only return one row (or some fixed limit of rows), I'd rather pass the $result object around within the script and loop through it as needed. If you truly need to keep it in session (and aren't just prematurely optimizing), then it might be more robust to look at query caching on the DB server side versus storing potentially large amounts of data in PHP sessions. (IMHO)

    Also, maybe it's time to look into using the OOP syntax with MySQLi? (Or, my preference would be to use PDO instead of MySQLi -- with a MySQL DSN -- but that's a whole different discussion. 🙂 )

      I think I have a glimmering of understanding about "...pass the $result object around within the script..." and will see if I can get the hang of that (it does sound simpler than the things I've been doing with sessions). I am totally clueless about the OOP thing...and beyond. But I am interested in gaining a better conceptual understanding of what the heck I am doing and trying to find more efficient ways of doing them.

      If I give a $result set a distinct name (e.g. "$result_A", will it survive if the visitor goes to a different page on the site on which parts of that set might be used? I guess that's why I use sessions...to save time re-querying when the visitor goes to different but related pages.

        If you do, in fact, have to store the results in session data (i.e. across separate page accesses), then no, you cannot store a query result resource.

        Again, if you know it will be a fixed and reasonably limited set of results (for some undefined value of "reasonably limited"), then storing the results directly into a session array may not be a big deal -- but just be positive it will be limited.

        And, of course, it begs the question: do you really need to store it in session data, or will performance be just fine if you re-query the DB on each request? (I.e.: how much faster does PHP read the session data from a file than MySQL takes to read it from its files -- along with its additional processing -- and how often does this actually have to happen?) And again, I'm only concerned here if the amount of data that could be returned by the query is open-ended, not a fixed and known value you're willing to store on disk and in memory (PHP arrays are notoriously inefficient, memory-wise).

          Another consideration is will the data become out dated by storing it on the session? What I mean is, if you store say user roles in the session, and the user's roles change after said storage - the change will not take affect unless you A) Query the db again to check or 😎 start a new session.

            Joseph Sliker wrote:

            I guess that's why I use sessions...to save time re-querying when the visitor goes to different but related pages.

            Sessions have to be stored somewhere. By default they use file based storage, but you can also opt for database based storage, or some other storage. Consequently, as NogDog noted in post #4, you won't necessarily find it faster to retrieve from a session than to query the database. If you opt for say, Redis storage in memory for sessions, then chances are it will be faster than querying the database even for simple well-optimised queries, but then you have to consider memory usage, Redis setup, loss of sessions on reboot, etc.

              Write a Reply...