OK, through some good advise here, i migrated to sqlite. Love it! The switch was a breese.
however the import is not going well as it does not seem to like the punctuation.

"sqlite_exec() [function.sqlite-exec]: unrecognized token: "#" in "

mainly the "!", it seems to be treating it as an operator - example

$text = "That cow is GREEN!" <- this errors out on import.

How do you clean that? I cannot use "!" as then the token error is "\"
cannot use """ then the "#" is the token error.

How do you clean up the strings?

    did you try [man]sqlite_escape_string[/man]?

      Derokorian;11005788 wrote:

      did you try [man]sqlite_escape_string[/man]?

      how do you spell "facepalm". i kept doing mysql_real_escape_string() but obviously those are not interchangeable.

      thanks!

        lol no they are not. This is why there is an escape function for every (most?) db that PHP supports, because they don't all behave the same.

        Edit: Btw I think you spelled it right. 😛

          LOL
          well so far i am running into brick wall after brick wall, and its all concerning punctuation, an issue not apparent in MYSQL.

          i have one piece of data that is a collection of ID's separated by a comma, in MYSQL it works as it it treated as a string variable (or TEXT) in SQLITE its messing up

          id = '2,67,89,122' <-- this crashes

          psuedo code:
          insert into gamedata ('id') values ('2,67,89,122')

          even with id escaped it still crashes with the error:
          sqlite_exec(): 2 is not a valid sqlite database resource

          is TEXT the only option or is something like "blob" or "varchar" do-able?

            here.. this might help...
            all data is stored as a BLOB type:

            Warning: sqlite_exec(): 4 is not a valid sqlite database resource in F:\xampp\htdocs\sqlite_bfg\runmaint.php on line 138
Bad query:INSERT OR REPLACE INTO BFGGAMES (gameid,gamename,family,familyid,productid,genreid,allgenreid,shortdesc,meddesc,longdesc,bullet1,bullet2,bullet3,bullet4,bullet5,foldername,hasdownload,macgameid,oggameid,hasvideo,hasflash,gamerank,releasedate,gamesize,sysreqos,sysreqmhz,sysreqmem,sysreqdx,sysreqhd,ostype) VALUES ('6320','10 Days To Save the World: The Adventures of Diana Salinger','10 Days to Save the World','10 Days to Save the World','4592','4','4:15:83','You only have 10 Days to Save the World!','You only have 10 Days to Save the World! Find the secrets of an ancient amulet to stop the disasters occurring all over the planet!','You only have 10 Days to Save the World! Find the secrets of an ancient amulet to stop flooding in Venice, a drought in Africa and the other disasters occurring all over the planet. Travel around the globe as you help Diana track down her father and explore the mysteries of the timeless amulet in this exciting Hidden Object game. Can you end the cataclysmic events and save the entire planet from destruction?','Gorgeous graphics','Immersive atmosphere','Save the entire world!',' Check out our Blog Walkthrough','','en_10-days-save-world-adventures-diana-salinger','yes','0','0','yes','yes','2193','2009-09-29 00:00:00','76422632','Windows XP/Vista/7','1.0 GHz','256','9.0','88','pc')

            in MYSQL works perfect no errors. in SQLite lots-o-errors.

            for the inquisitive, I want to learn a little bit about SQLITE.

              klyxmaster wrote:

              Bad query:INSERT OR REPLACE INTO BFGGAMES (gamei...........'88','pc')

              I had no trouble running that query (after "create table BFGGAMES(gameid blob, gamename blob, ....)" first, of course, since you say they're all blobs). Let's dig up the PHP docs for the sqlite functions....oh; I won't be able to help with escaping after all.

              Those functions are for SQLite 2. SQLite 3, with a different interface has been included since PHP 5.3.3, and since v5.4 continuing to use SQLite 2 requires a separate PECL extension. Since I'm using v5.4 and I've been using PDO since before I started using SQLite, I don't have that extension, and therefore I don't have those functions.

              I guess that would explain why I'd never heard of sqlite_escape_string - the PDO drivers escape bound values automatically.

              i have one piece of data that is a collection of ID's separated by a comma

              No use for database normalisation, then?

                unfortunately all the data i get comes from xml file from my vendor. I tried changing it to:

                1,2,3,4,5
                1-2-3-4-5
                1:2:3:4:5
                and still no go
                I suppose i could serialize it then store it?

                edit: nope
                just constantly getting that

                sqlite_exec(): x is not a valid sqlite database resource

                where x= some number

                  klyxmaster wrote:

                  How do you clean up the strings?

                  Use a preparaed statement: you then bind the input to the parameter as a string. Oh, and ditch the SQLite extension in favour of the PDO extension or the SQLite3 extension.

                  klyxmaster wrote:

                  i have one piece of data that is a collection of ID's separated by a comma, in MYSQL it works as it it treated as a string variable (or TEXT) in SQLITE its messing up

                  id = '2,67,89,122' <-- this crashes

                  psuedo code:
                  insert into gamedata ('id') values ('2,67,89,122')

                  What is the type of the id column? If it is an integer, then your query is wrong since you are trying to insert a string.

                  klyxmaster wrote:

                  unfortunately all the data i get comes from xml file from my vendor. I tried changing it to:

                  1,2,3,4,5
                  1-2-3-4-5
                  1:2:3:4:5
                  and still no go
                  I suppose i could serialize it then store it?

                  What is this data for? Certainly, you could store everything as blob, but if there is a relational structure to your data, then you're not using your database efficiently when you don't normalise.

                    Hi Nice of you to stop by! Im finding the transition from MYSQL to SQLITE is not as easy as I thought, Im doing MYSQL stuff to the SQLITE lol - "iiittt don't work so good! 😛"

                    laserlight;11005797 wrote:

                    Use a preparaed statement: you then bind the input to the parameter as a string. Oh, and ditch the SQLite extension in favour of the PDO extension or the SQLite3 extension.

                    Not sure what is meant by that. And it looks like i have sqlite 2.x

                    laserlight;11005797 wrote:

                    What is the type of the id column? If it is an integer, then your query is wrong since you are trying to insert a string.

                    I did it as a TEXT first, then as BLOB (in mysql its a text).

                    laserlight;11005797 wrote:

                    What is this data for? Certainly, you could store everything as blob, but if there is a relational structure to your data, then you're not using your database efficiently when you don't normalise.

                    There is one column for specifica genreid (all game genre's are broken down into numerical id's), the allgenreid is a list of other related genre's - so while one games' main genre is a hidden object(15) it may have other elements about the game such as puzzle, match3 etc..

                      klyxmaster wrote:

                      Not sure what is meant by that. And it looks like i have sqlite 2.x

                      You have SQLite 2 because you're using the legacy SQLite extension. If I remember correctly, at the time this extension was introduced, SQLite 3 was new, so they made it for SQLite 2. However, SQLite 3 is now the major version of SQLite to use for new projects.

                      If you choose the PDO extension (which is enabled by default), the SQLite driver (which is enabled by default) will use SQLite 3. (Note the enabled by default thing: hence users of your script are not likely to require additional configuration to get PDO and/or its SQLite 3 driver working.)

                      To understand what I am talking about concerning prepared statements, read the PHP manual's entry on Prepared statements and stored procedures for PDO.

                      klyxmaster wrote:

                      There is one column for specifica genreid (all game genre's are broken down into numerical id's), the allgenreid is a list of other related genre's - so while one games' main genre is a hidden object(15) it may have other elements about the game such as puzzle, match3 etc..

                      I suggest that you take a step back to look at how you can design your database. Then, when you are parsing the XML document, you break down the data such that it will populate your database accordingly. XML is just being used here as a transport format. The numeric ids you have here are in CSV format, again used as a transport format. The idea is to convert the data from the transport format(s) into a storage format from which you can efficiently access and update the data.

                      In the event that you are unfamiliar or need a refresher into basic database normalisation, the article on Database Normalization and Design Techniques would be a place to start.

                        @
                        THHHHHAAAANNNKK U!

                        so one good start is to get the sqlite updated? and then just do a refresher course in db normalization?

                        check!

                        i love it when someone tells me WHERE to get the info, instead of just handing it to me.. otherwise Im just a "script kiddy"

                        thanks again..
                        i'll post what i have when I get it running. so that anyone else with a similar issue can learn from it.

                          klyxmaster wrote:

                          THHHHHAAAANNNKK U!

                          You're welcome 🙂

                          klyxmaster wrote:

                          so one good start is to get the sqlite updated?

                          Well, you aren't really updating SQLite. You would just use a more recent version of SQLite by using either the PDO extension or the SQLite3 extension.

                          klyxmaster wrote:

                          and then just do a refresher course in db normalization?

                          That article is only a starting point, but yeah. The thing is, a multi-valued field like '1,2,3,4,5' is usually frowned upon because you can't efficiently leverage on SQL's capabilities that way. I would expect something like a Genre table, then the genre_id might be populated by using [man]explode/man to get the individual ids. Furthermore, since the ids are integers, you would bind them to the prepared statement as integers, not strings.

                            so in this particular instance (at leaset if i want to go sqlite) it would be better to make a new table with a key representing the gameid and then the related allgenreid's associated with it?

                            EDIT:
                            no thats backwards, I would make a table with all the possible genreid's (there are 80 I think, but most sites use just the normal 25, 7 for a small site) then list games as they come in that are assciated with those genres?

                              Whether you want to add new genres when needed or whether you want to create the entire list first is up to you. Of course, the latter is problematic if you get an unrecognised genre.

                                laserlight;11005799 wrote:

                                You have SQLite 2 because you're using the legacy SQLite extension. If I remember correctly, at the time this extension was introduced, SQLite 3 was new, so they made it for SQLite 2. However, SQLite 3 is now the major version of SQLite to use for new projects.

                                Also, by the time SQLite 3 was firmly established, PHP's PDO extension was pretty much ditto, with PHP's developers already having made the decision to migrate towards it and concentrate future development there (the MySQL extension has also been deprecated for the same reason). The database file format went through some major changes between versions 2 and 3 as well. The upshot was that SQLite's PDO driver was not developed with any support for 2 and the sqlite extension was not upgraded to support 3; the latter was basically kept around afterwards to give users a migration path.

                                  well after some "bug" checking, i completely removed that "allgenreid" field and re ran it (everything with a simple single data) and it still errored out

                                  Warning: sqlite_exec(): 4 is not a valid sqlite database resource

                                    Well, please switch to the PDO extension or SQLite3 extension first, then if you are still having problems, post your create table statement along with the smallest and simplest script that demonstrates the problem.

                                    That said, it looks like you are just calling sqlite_exec wrongly.

                                      OK, back to basics.. Lets take a look at this piece of data that I am attempting to INSERT but the query is failing.

                                      with PDO the command $dbhandler->exec($query) fails
                                      with SQLITE command sqlite_queryexec fails.

                                      loading the database with sqlite

                                      $dbhandle = @sqlite_open('data/games.db', 0666, $error);

                                      or PDO

                                      $dbhandle = new PDO('sqlite:data/games.db');

                                      either of the above work fine and create the table as needed. The problem arrise when I go to INSERT a line of data.

                                      So I want to start with looking at the string and the data. There must be something amiss in it. (NOTE: this data INSERTS fine with MYSQL as is)

                                      INSERT INTO BFGGAMES (gameid,gamename,family,familyid,productid,genreid,allgenreid,shortdesc,meddesc,longdesc,bullet1,bullet2,bullet3,bullet4,bullet5,foldername,hasdownload,macgameid,oggameid,hasvideo,hasflash,gamerank,releasedate,gamesize,sysreqos,sysreqmhz,sysreqmem,sysreqdx,sysreqhd,ostype) VALUES ('7318','1 Penguin 100 Cases','1 Penguin 100 Cases','5444','5296','1','1:4:21:26:32','Help Pengoo find his way home!','Guide Pengoo from hatching in the Antarctic, all the way to his first flight!','Oh no! Pengoos alarm clock didnt go off and he didnt hatch in time! Journey with Pengoo the penguin inside a fishing net to New Zealand, and stick with him as he struggles to find his way back home. Surprises are in store as Pengoo meets up with colorful characters and mystical creatures, each with their own problems. Solve mind-bending puzzles and kooky conundrums in this charming Hidden Object game!','Over 90 levels','Funny storyline','Guide Pengoo home!','none','none','en_1-penguin-100-cases','yes','0','0','yes','no','680','2010-02-06 00:00:00','100799704','Windows XP/Vista/7','800 Mhz','128','8.1','102','pc')

                                      ps
                                      For those just "Google-ing" by, This is a hobby of mine. I want to expand my knowledge of other databases (besides MySQL). Any tips are greatly appreciated - currently looking for a type of file based - no config - database. SQLite seems to be it, but its stuborn (oooook, it's me) 😛

                                        Write a Reply...