unserilized file Array

klyxmaster

Real quick I have a flatfile database. (I also have a sql one, but working on a flatfile version - not related to this topic).

each line of the db is a serialized array. when unserialized it looks like:
game['gamename'] = 'cool game
game['id'] = '123456'
game['desc'] = this is a cool game
game['released'] = date of game
and so on (its huge)

currently i have this:

 function _sortRecords($game_lib, $by_field)
    {
        //sort game_lib by top rank down
        foreach($game_lib as $linenum=>$row)
        $recent[$linenum] = $row[$by_field];
        array_multisort($recent,SORT_ASC,$game_lib);
        return $game_lib;

}

and called:

$game_sort = _sortRecords(file('data/game.db'),'recentrelease');

where the $game_lib is the db tossed into an array
$game_lib = file('path/to/game.db')
field is what I want to sort it by. Usually it will be release date or popularity

The above DOES NOT WORK or at least does not sort correctly.
IT DOes not seem to matter if I use SORT_ASC or SORT_DESC the results are the same. It shows 2009 - 2012 (if I am using a release date as the above example) if I use SORT_DESC its STILL 2009-2012.

Is there another method to sort an multi array?

Emma

I might be wrong here but isn't the function going to return as soon as it's gone through the first loop? Surely, the return has to be outside the foreach if you want it go reorder everything in the array and not just the first line.

NogDog

Emma;11005724 wrote:
I might be wrong here but isn't the function going to return as soon as it's gone through the first loop? Surely, the return has to be outside the foreach if you want it go reorder everything in the array and not just the first line.

Since no curly braces were used with the foreach(), only the first line after it will be executed as part of the loop, as if it were written (and I would suggest better written 😉 ):

 function _sortRecords($game_lib, $by_field)
    {
        //sort game_lib by top rank down
        foreach($game_lib as $linenum=>$row) {
            $recent[$linenum] = $row[$by_field];
        }
        array_multisort($recent,SORT_ASC,$game_lib);
        return $game_lib; 
    }

NogDog

I always end up using [man]usort/man, as array_mutlisort() tends to confuse me. 🙂

klyxmaster

@
@
This is incorrect - while it may not be "proper" programing, most languages will cycle through completely on a single line loop (for, foreach, do/while etc...).

try the following:
<?php
for( $i = 1; $i<=10; $i++)
echo $i;
echo 'done';
?>

you will see it will loop 10x then print 'done'.

However, for the sake of completeness, I did add the braces, as there is still no sorting

@, I will look into usort.

I was thinkin of old fashion bubble sort but im dealing with a mulit-dim array. so seems to be tricky.

throw out your napkins.. lets see what you have :-)

klyxmaster

Just an update in case someone missed my first post:
each line of the array is "serialized" (as it is a flat file)
I noticed as I was processing sorting functions, that I forgot to "unserialize".
now I need assistance in where to put that exactly. so the script can sort correctly.

klyxmaster

well shoot wont let me edit my last post for clarification so here we go:

text file is stored as:

a:4:{s:8:"gamename";s:9:"cool game";s:2:"id";s:6:"123456";s:4:"desc";s:19:"this is a cool game";s:8:"released";s:12:"date of game";}
a:4:{s:8:"gamename";s:9:"cool game";s:2:"id";s:6:"234154";s:4:"desc";s:19:"this is a cool game";s:8:"released";s:12:"date of game";}
a:4:{s:8:"gamename";s:9:"cool game";s:2:"id";s:6:"967345";s:4:"desc";s:19:"this is a cool game";s:8:"released";s:12:"date of game";}

then of course when read as a file it will be an array of these lines. If i need to sort say, the ID - it first needs to be unserialzed then sorted. That is where I am stuck at.

klyxmaster

Got it!

function _sortRecords($game_lib, $by_field)
    {
        $i = 1;
        foreach($game_lib as $linenum=>$data){
            $game[$i] = unserialize($data);
            $i++;
        }
        //sort game_lib by top rank down
        foreach($game as $linenum=>$row)
        $recent[$linenum] = $row[$by_field];
        array_multisort($recent,SORT_DESC,$game);
        return $game;

}

Just dumped it in a new array and whoa-la!

laserlight

Ah, good to see that you solved this. Just wondering though: why are you trying to implement a flatfile database? If the complexity of say, MySQL or PostgreSQL setup and maintenance is a problem, why not use SQLite?

klyxmaster

laserlight;11005751 wrote:
Ah, good to see that you solved this. Just wondering though: why are you trying to implement a flatfile database? If the complexity of say, MySQL or PostgreSQL setup and maintenance is a problem, why not use SQLite?

I do have a version of this "engine" in mysql - works good. However, some people that use this, are not very versed in php/sql, so it is written so that all you do is up load to your host, and your done. Just select a theme. SQL would require an additional setup that many who use this are not interested/skilled in.

another issue I found, is SPEED. While the manipulation of the data is faster by mysql (in some instances), I can load the rss file in a text file much faster than loading it in a sql db. I timed it several times. reading the rss file (6mb) to a sql db 1min - 2min, dumping it into a flatfile around 35-45 seconds.

that eliminates a necessity for a nightly cron, as you can just update "on-the-fly"

the sorting was the only real challenge, the rest is lighting fast. I have always liked flatfile systems as they are compact and run smooth vs the sql which is like having "luggage"

more importantly, the original engine written by another author - is very "cryptic" (also flatfile). I was never a big fan of those that offer open source, then give out unreadable code. So there were many "issues" with it, that could not be solved, So, easy way, is to write my own.

and just the pure challenge of manipulating data - for educational purposes - of flatfiles.

While all of this is a preference. I have see many pro apps spring up lately on the flatfile system - including some really nice forums

cheers

Weedpacket

klyxmaster wrote:
...so it is written so that all you do is up load to your host, and your done.

As laserlight said, the same would be true of SQLite.

I obviously can't comment on speed since I don't have your data, and don't have any need to do nightly cron jobs to update my database; but I can do searches on SQLite tables containing on the order of three million records and get results in times on the order of ten milliseconds.

And of course there are a number of pro apps that are using it... 🙂

klyxmaster

Weedpacket;11005761 wrote:
As laserlight said, the same would be true of SQLite.

I obviously can't comment on speed since I don't have your data, and don't have any need to do nightly cron jobs to update my database; but I can do searches on SQLite tables containing on the order of three million records and get results in times on the order of ten milliseconds.

And of course there are a number of pro apps that are using it... 🙂

well hopefully the mod wont be too upset as this is off topic, but I enjoy this one.

here is a good website to view that will address your comment:
http://www.go4expert.com/forums/showthread.php?t=283

point being, in any results i have found, to the NAKED EYE, we as humans can not physically differentiate a microsecond. A normal user visiting your site will sit about an average of 10-15 seconds of nothing happening before moving on, thinking your site it dead.

So I stand by my statement that it is a preference of what you like to work with. SQL has better "access" commands, however, you can easily replicate those and crate your own flat file library.

but I can do searches on SQLite tables containing on the order of three million records and get results in times on the order of ten milliseconds.

Your 3million records are ALREADY STORED on your db. question? How long would it take to import the 3million records if those were a XML(rss) file? Parsing a 3k records into "fields"(using CURL and SIMPLEXML) to a sql db takes a very long time. parsing those same 3k records to a flat file? seconds. As you can see from the info from the site above, ACCESSING those records with either FF, or SQL, is relatively that same time.

Regards

ps
would be happy to chat about this in a different thread as this one is veering off topic

laserlight

klyxmaster wrote:
another issue I found, is SPEED. While the manipulation of the data is faster by mysql (in some instances), I can load the rss file in a text file much faster than loading it in a sql db. I timed it several times. reading the rss file (6mb) to a sql db 1min - 2min, dumping it into a flatfile around 35-45 seconds.

the sorting was the only real challenge, the rest is lighting fast. I have always liked flatfile systems as they are compact and run smooth vs the sql which is like having "luggage"

With proper database design, including proper use of indices, along with things like prepared statements, it is quite possible that SQLite will generally be faster than your flatfile database as the database grows. As stated by its primary author, SQLite is intended to replace fopen() rather than Oracle (or MySQL).

klyxmaster wrote:
more importantly, the original engine written by another author - is very "cryptic" (also flatfile). I was never a big fan of those that offer open source, then give out unreadable code. So there were many "issues" with it, that could not be solved, So, easy way, is to write my own.

It may sound like an easy way, but then "if debugging is the process of removing bugs, then programming must be the process of putting them in".

What SQLite offers is the use of advanced data structures and algorithms for storing and accessing data in a relational database, but without the overhead, including configuration, of a traditional RDMS. All this comes with a an extensive test suite, and usage by companies large and small worldwide. Furthermore, whereas your flatfile database is a home brewed format, your users can inspect and modify the SQLite database file using the command line program and other tools available. Oh, and if your data is indeed well suited for a relational database, then you have all the advantage of that instead of having to re-invent the wheel.

EDIT:

klyxmaster wrote:
How long would it take to import the 3million records if those were a XML(rss) file? Parsing a 3k records into "fields"(using CURL and SIMPLEXML) to a sql db takes a very long time. parsing those same 3k records to a flat file? seconds. As you can see from the info from the site above, ACCESSING those records with either FF, or SQL, is relatively that same time.

If you need to parse 3 million records from an XML document, then you need to parse 3 million records from an XML document. Using a flat file won't make a difference concerning that overhead. Now, if you're treating the XML document as a database, say with the use of xpath or something, then that would be a different issue: you still have the parsing problem, but now you are using a tool that is well known and supported rather than writing your own database handling functions for a home brewed format.

You might say that the "not invented here" or "don't re-invent the wheel" thing is what I'm getting at. Certainly, if your database is not relational at all, being an extremely simple data store, and your requirements for sorting and searching are limited, then it is plausible that a simple flat file database will beat even SQLite hands down.

Weedpacket

klyxmaster wrote:
Parsing a 3k records into "fields"(using CURL and SIMPLEXML) to a sql db takes a very long time. parsing those same 3k records to a flat file? seconds.

Hmm; how are you parsing that XML feed into records into a flat file if you are not using an XML parser (like simplexml), or - for that matter - retrieving the feed in the first place (with something like cURL)?

point being, in any results i have found, to the NAKED EYE, we as humans can not physically differentiate a microsecond. A normal user visiting your site will sit about an average of 10-15 seconds of nothing happening before moving on, thinking your site it dead.

All of which is irrelevant to my point: I'm not having to load and parse the entire table (however long that takes and however much memory that requires) just to retrieve a few records (for however many concurrent accesses).

As I said, I don't have your data or know what your storage or access requirements are, but one test (insert standard caveats about the applicability of specific benchmarks - they're not big records; test script supplied in the source distribution) described on the SQLite site gives a time of 0.757 seconds for the insertion of 25,000 records within a transaction (turning on filesystem synchronisation slowed it down to 0.914 seconds; including an index pushed it over the 1-second threshold).

Actually, just for a giggle, I thought I'd run some of the tests myself - I've got the SQLite Manager extension installed in Firefox - and try a somewhat larger record size as well. 25,000 lines of Lorem Ipsum (line lengths ranging from 323 to 1050 bytes); 16MB worth of INSERT statements.
...
Well, I don't know if the reported "execution time" includes front-end overhead and parsing all that SQL, but it was 1602ms. Putting an index on the text field first made it take 5557 seconds.

NogDog

Maybe a job for Mongo? 🙂

klyxmaster

Hi guys!
looks like the mod is letting this morph to a different topic.. thats cool.

thank you for all the responses. gives me good food for thought.
Im starting to think laserlight is a sales rep for sql lite LOL!! I havent looked into that, and the highlights you bring up are worth me taking a look.

I did not realize that sql lite and mysql were different, or at least to the degree you mentioned.

I can, remod the install app to create the all the db stuff as required so a user would not have to jump into cpanel to make / add databases. Something NOT required with FF.

Is there a good site to check up on sql lite? (yes I'll google it, just getting a jump star here - laser? heheh)

@
here is the curl i use to read the rss page

function getXMLFeed($xml_feed)
{
    ############################################################################
    ##### GRAB GAME XML BY CURL  
    ############################################################################
    $ch = curl_init($xml_feed);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    $data = curl_exec($ch);
    curl_close($ch);
    return $data;
}

and the import routine (flat file - it is very similar to the sql version i have)

    ############################################################################
    ##### OPEN UP (OR CREATE) THE DATABASE TEXT FILE 
    ############################################################################
    $game_file  = fopen('data/game.db','w');

############################################################################
##### PROCESS XML FILE AS PROVIDED BY $DATA 
############################################################################
    $data = getXMLFeed(XML_FEED);
    $data = new SimpleXMLElement($data);
    foreach($data->gamexml->game as $game)
    {
	################################################################
	##### CLEAN UP ALL THE STRINGS
	################################################################
            /**
             *  some games do not have a rank, so we'll fix that here
             *  */
            $gamerank = !$game->gamerank ? 0 : (string)$game->gamerank;

            /**
             * good trick to get exact genre searches
             * */
            $allgenreid = ','.(string)$game->allgenreid.',';

            /**
             * macgameid and oggameid are NULL'ed sometimes so we will set
             * them to 0 here
             * */
	$macgameid = 0;
	$oggameid = 0;
	if($game->macgameid > 0) $macgameid = (string)$game->macgameid;
	if($game->oggameid > 0 ) $oggameid = (string)$game->oggameid;

            $ostype         = ',pc,';
            if($macgameid > 0) $ostype .= 'mac,';
            if($oggameid > 0) $ostype .= 'og,';


            /**
             * of all the nerve LOL
             * descriptions sometimes have a hard return right in the middle
             * of the string, we'll clean it up here
             * */
            $longdesc     = str_replace("\n","",(string)$game->longdesc);
            $shortdesc    = str_replace("\n","",(string)$game->shortdesc);
            $meddesc      = str_replace("\n","",(string)$game->meddesc);
            $bullet1      = str_replace("\n","",(string)$game->bullet1);
            $bullet2      = str_replace("\n","",(string)$game->bullet2);
            $bullet3      = str_replace("\n","",(string)$game->bullet3);
            $bullet4      = str_replace("\n","",(string)$game->bullet4);
            $bullet5      = str_replace("\n","",(string)$game->bullet5);

            ################################################################
	##### PREP DATA TO ARRAY FOR SERIALIZING
	################################################################
	$gamedata = array (
                'gameid'         => trim( (string)$game->gameid),
                'gamename'       => trim( (string)$game->gamename),
                'family'         => trim( (string)$game->family),
                'familyid'       => trim( (string)$game->family),
                'productid'      => trim( (string)$game->productid),
                'genreid'        => trim( (string)$game->genreid),
                'price'          => trim( (string)$game->price),
                'allgenreid'     => $allgenreid,
                'shortdesc'      => $shortdesc,
                'meddesc'        => $meddesc,
                'longdesc'       => $longdesc,
                'bullet1'        => $bullet1,
                'bullet2'        => $bullet2,
                'bullet3'        => $bullet3,
                'bullet4'        => $bullet4,
                'bullet5'        => $bullet5,
                'foldername'     => trim( (string)$game->foldername),
                'hasdonwload'    => trim( (string)$game->hasdownload),
                'macgameid'      => $macgameid,
                'oggameid'       => $oggameid,
                'hasvideo'       => trim( (string)$game->hasvideo),
                'hasflash'       => trim( (string)$game->hasflash),
                'gamerank'       => $gamerank,
                'releasedate'    => trim( (string)$game->releasedate),
                'gamesize'       => trim( (string)$game->gamesize),
                'sysreqos'       => trim( (string)$game->systemreq->pc->sysreqos),
                'sysreqmhz'      => trim( (string)$game->systemreq->pc->sysreqmhz),
                'sysreqmem'      => trim( (string)$game->systemreq->pc->sysreqmem),
                'sysreqdx'       => trim( (string)$game->systemreq->pc->sysreqdx),
                'sysreqhd'       => trim( (string)$game->systemreq->pc->sysreqhd),
                'ostype'         => $ostype
            );

            /**
             * serialize the array and save it to data/game.db
             * */
            $game_data                  = serialize($gamedata);
            fwrite($game_file,$game_data."\n");

        }// xml loop
fclose($game_file);
$count = count(file('data/game.db'));
//echo "<p/>IMPORTED $count GAMES SUCCESSFULLY!";
}

Not perfect, but its served its purpose for the last 4yrs. The last part is where i either dump the info in sql or flat file.

enjoy :-)

klyxmaster

oh wow - i should have researched this before - thanks! All someone had to say was sqlite was filebased lol - for testing purposes i love the fact that flatfiles are local and can be tested local..

or did you guys tell me that already heheh

laserlight

klyxmaster wrote:
looks like the mod is letting this morph to a different topic.. thats cool.

With the exception of Emma, everyone else who has replied to you in this thread are moderators 🙂

klyxmaster wrote:
Im starting to think laserlight is a sales rep for sql lite LOL!!

Yeah, I use PostgreSQL at work, but I'm moonlighting for SQLite for free 😛

klyxmaster wrote:
Not perfect, but its served its purpose for the last 4yrs. The last part is where i either dump the info in sql or flat file.

Yeah, but the part we're getting at is that you're going to have to parse the XML either way.

EDIT:

klyxmaster wrote:
All someone had to say was sqlite was filebased lol - for testing purposes i love the fact that flatfiles are local and can be tested local..

or did you guys tell me that already heheh

I noted this in passing in post #13. Of course, all databases -- whether flat file, relational or like NogDog's suggestion of MongoDB, which is not a relational database system -- are file based at some level, but SQLite's database storage is a single file.

klyxmaster

laserlight;11005777 wrote:
Yeah, but the part we're getting at is that you're going to have to parse the XML either way.

So if i understand what you are saying correctly, its the "parsing" thats the lag, not the actual "INSERT" command?

laserlight

klyxmaster wrote:
So if i understand what you are saying correctly, its the "parsing" thats the lag, not the actual "INSERT" command?

You'll have to profile to find out, but I would guess that the bottle neck is either the download or the parsing, not the insertion to the database. That said, if you are not using prepared statements, the database engine might be preparing the same SQL statement again and again, which is inefficient. Then again, MySQL has a query cache.