Datasets

Hi Guys

So new problem (Yes I appear to be hitting a couple) I'd prefer to keep this a discussion rather than just a question and answer, since I've pretty much decided what I'm going with, but I'd like to have a discussion about it.

So in short I needed to return multiple views from the database and to my surprise, PHP does not cater for the concept of datasets, instead you need to create custom objects and use that. I've opted for a bit more of a devious, shortcut, underhand, lazy approach (yeah yeah, I'll admit it this time) I've created flat files with the query results as a "caching" mechanism. I'm looking into cron jobs to refresh the cache every couple of hours. The main problem I had here was the sitemap, coming from a C# background, I'm rather familiar with a sitemap provider and using datasets to limit DB calls. Since I doubt the sitemap will change more often than every 2 hours, I'm going ahead with the flat file and a "clear cache" option in the backend (For those times your REALLY REALLY need to make an urgent update), my reasoning for this is speeding up the sitemap, since all pages need it and using caching where possible to speed up performance.

Can anyone suggest something more elegant to handle this? As a general rule of thumb I hate flat files, however, I have found it quite usefull in the above example. I populated the database with a bunch of dummy data to test it out, and from 8 seconds in a SQL query I've come down to 2 seconds in processing the flat file, I assume this increase in performance will become larger with larger datasets, but I'd be interested in hearing out any alternative methods to cope with the above example. I know 6 seconds might sounds like nothing, but if the site ever grows to 10X the sitemap is has now, it will be well worth it to have something decent in place, since this will be something that is called extensively.

Looking forward to getting some feedback on this one

Hi there

An option would be to put the information into the $_SESSION variable. You can add a check to the page to see if the $_SESSION['sitemap'] has been set, then if not, go do the query, else read the info from the SESSION.
Hope that helps.

PS> 8 sec for a mySQL query is very long. Are you sure the query is optimal?

http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views

Simpler solution - consider adding it into an array then echo the array when you have all the data.

@Metalaniac

Thanks for the feedback, the 8 seconds is due to the test data I dumped in there, so the be fair, there are thousands of rows which is a join between the instance, latest version and page tables. My biggest concern with dumping this into session, would be the amount of items floating in sessions. It's quite a bit of data and if this model is followed on a site with high traffic volumes, I think it could add some strain to the server. It's a neat idea and on a smaller scale it would work rather nicely...

@Mac

Thanks, I'll have a look at that as well, my main concern is calling this query on each request though, I assume that if I have say 100 visitors flicking through pages and the query executing on each request, I might end up with some locks. Which I'm trying to avoid, I'm trying to stay away from dynamic SQL, currently I'm calling a routine to get the info I need. I could add those column to the view's resultset and then swap out the routine for a view. I understand that I could cain access to all the data via one view...

My main problem (at least in my mind) is having access to all this data without actually querying SQL again?

Well new problem

So apparently you can get datasets back from PHP, took a bit of googling but it is actually supported. I'm using mysqli and I've hit a slight snag with them, apparently stored procs return an extra resultset. So I need to loop through each and every set on every result otherwise I run into an error on the next query "Commands out of sync; you can't run this command now" this won't be an issue with the OOP approach, but for the purposes of this assignment we need to stick to procedural. I'm bashing out my routines idea and sticking with SQL queries so I don't have this problem.

That's it for this debate then. (To be fair I didn't exactly get the discussion I was hoping for going...) If it weren't for the two of you I'd still be stuck, so thanks for that

As for the navigation, I'm sticking to the flatfile solution, I'm just swapping it out with XML and assigning an update function I can use whenever the navigation is updated, so the XML is updated with the latest content from the DB. It's not the prettiest solution, but it seems to work well enough. The only thing I'm a bit scared of is the data going corrupt and taking the whole site with it. I'll see if I can find a fallback solution in the event that the XML isn't valid. Possibly throw that bit of code in a try catch and then get the sitemap from a view in the event that there's an exception.

So anyway, thanks again guys

Just an update in case anyone else runs into the same issue...

I swapped out my js function on images to actual buttons and the problem persisted, this removed javascript as the cause completely. Eventually after playing around with it for hours, I found that the specific piece of code that was responsible for destroying the session was not being called on all requests. It was firing sometimes, but not all the time.

My form's action was left blank, meaning it would post back to itself. The form was on an include file, I swapped out the action to my main page (index.php) and shifted the code over to that, rather than the include. Voila, it works.

It still makes very little sense to me so if anyone could provide some hypthetical theories as to why I'm seeing this behaviour I'll be grateful. I initially thought that the session was recreated somewhere else, but even placing a die command in my if statement yielded no results (and by no results I mean the page loaded normally)?

Thanks